I am new to prisma and I want to use it with PostgreSQL. I need to add authentication to my backend, but I was unable to create a session table. I was able to add this authentication logic while working with raw SQL, but I have no idea how to make this work while working with prisma.
This is what the logic looks like.
--------------------START OF MY LOGIC WITH RAW SQL------------------------
-----------------------------------Authentication table----------------------------------
-- A simple table that stores email and username, I store all users email as lowercase.
create function make_lower_email() returns trigger
as
$$ begin new.email := lower(new.email);
return new;
end;
$$ language plpgsql;
create table if not exists users (
id serial primary key,
email varchar (100) not null,
password varchar (200) not null,
created_at timestamp with time zone default now(),
constraint uk_users_email unique (email)
);
create trigger ensure_lower_email_trg before
update
or
insert
on users for each row execute procedure make_lower_email();
import bcrypt from "bcrypt";
import dotenv from "dotenv";
import express from "express";
import session from "express-session";
import postgres from "pg";
import connectPgSimple from "connect-pg-simple";
dotenv.config();
const app = express();
app.use(express.json());
const { Client } = postgres;
const client = new Client();
client.connect();
// session store and session config
const store = new (connectPgSimple(session))({
client,
createTableIfMissing: true,
});
app.use(
session({
store: store,
secret: "myscecret",
saveUninitialized: false,
resave: false,
cookie: {
secure: false,
httpOnly: false,
sameSite: false,
maxAge: 1000 * 60 * 60 * 24,
},
})
);
app.get("/", (req, res) => res.send("API Running..."));
app.post("/register", async (req, res) => {
const { email, password } = req.body;
if (email == null || password == null) {
return res.status(400).send("Email or password is missing");
}
try {
const userExist = await client.query("SELECT id, email, password FROM users WHERE email = $1", [email]);
if (userExist.rows.length > 0) {
return res.status(400).send("User already exists");
}
const hashedPassword = bcrypt.hashSync(password, 10);
const data = await client.query("INSERT INTO users ( email, password) VALUES ($1, $2) RETURNING *", [
email,
hashedPassword,
]);
const user = data.rows[0];
req.session.user = {
id: user.id,
email: user.email,
};
return res.status(200).json({ user: req.session.user });
} catch (e) {
console.error(e);
return res.status(500);
}
});
app.post("/login", async (req, res) => {
const { email, password } = req.body;
if (email == null || password == null) {
return res.status(400).send("Email or password is missing");
}
try {
const data = await client.query("SELECT id, email, password FROM users WHERE email = $1", [email]);
if (data.rows.length === 0) {
return res.status(404).send("User does not exist");
}
const user = data.rows[0];
const matches = bcrypt.compareSync(password, user.password);
if (!matches) {
return res.status(404).send("Wrong email or password");
}
req.session.user = {
id: user.id,
email: user.email,
};
return res.status(200).json("Login Successfully");
} catch (e) {
console.error(e);
return res.sendStatus(403);
}
});
app.post("/logout", async (req, res) => {
try {
req.session.destroy();
return res.sendStatus(200);
} catch (e) {
console.error(e);
return res.sendStatus(500);
}
});
app.post("/fetch-user", async (req, res) => {
if (req.sessionID && req.session.user) {
return res.status(200).json({ user: req.session.user });
}
return res.status(404).send("Unauthroized!!!");
});
// now listen on port 3000...
const port = 3000;
app.listen(port, () => {
console.log(`App started on port ${port}`);
});
--------------------------------END OF MY LOGIC WITH RAW SQL-------------------------------
The part that confuse me was creating the schema (session table) with Prisma, as you can see, I use connect-pg-simple to create the schema automatically while working with raw SQL, and here is what the code looks like when I inspect it in pgAdmin.
-- Table: public.session
-- DROP TABLE IF EXISTS public.session;
CREATE TABLE IF NOT EXISTS public.session
(
sid character varying COLLATE pg_catalog."default" NOT NULL,
sess json NOT NULL,
expire timestamp(6) without time zone NOT NULL,
CONSTRAINT session_pkey PRIMARY KEY (sid)
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS public.session
OWNER to postgres;
-- Index: IDX_session_expire
-- DROP INDEX IF EXISTS public."IDX_session_expire";
CREATE INDEX IF NOT EXISTS "IDX_session_expire"
ON public.session USING btree
(expire ASC NULLS LAST)
TABLESPACE pg_default;
CodePudding user response：
I wrote SQL query that create a session table in the database in pgAdmin with the raw SQL syntax, by using query tool, I then use yarn prisma db pull. This generate the Prisma syntax for creating similar session table.
And in my server.js file I just import PrismaClient like this
const { PrismaClient } = require("@prisma/client");
In summary, this is how my code looks like.
---------------Session Schema-----------------------
model session {
sid String @id @db.VarChar
sess Json @db.Json
expire DateTime @db.Timestamp(6)
@@index([expire], map: "IDX_session_expire")
}
---------------Session Schema-----------------------
const express = require("express");
const { PrismaClient } = require("@prisma/client");
const connectPgSimple = require("connect-pg-simple");
const session = require("express-session");
const app = express();
app.use(express.json());
const store = new (connectPgSimple(session))({ createTableIfMissing: true });
app.use(
session({
store: store,
secret: "myscecret",
saveUninitialized: false,
resave: false,
cookie: {
secure: false,
httpOnly: false,
sameSite: false,
maxAge: 1000 * 60 * 60 * 24,
},
})
);
app.get("/", (req, res) => res.send("API Running..."));
// app.use("/user", require("./routes/user"));
app.use("/auth", require("./routes/auth"));
app.use("/post", require("./routes/post"));
app.listen(5000, () => {
console.log("-------------------------");
console.log("Listening on PORT:5000...");
console.log("-------------------------");
});