我是 prisma 的新手，我想將它與 PostgreSQL 一起使用。我需要向后端添加身份驗證，但無法創建會話表。我能夠在使用原始 SQL 時添加此身份驗證邏輯，但我不知道如何在使用 prisma 時使其作業。
這就是邏輯的樣子。
--------------------使用原始 SQL 開始我的邏輯--------------------- -
---------------------認證表------------- ---------------------
-- 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}`);
});
-------------------------------- 使用原始 SQL 結束我的邏輯------------ --------------------
讓我困惑的部分是使用 Prisma 創建模式（會話表），如您所見，我connect-pg-simple在使用原始 SQL 時自動創建模式，這是我在 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;
uj5u.com熱心網友回復：
我撰寫了 SQL 查詢，使用原始 SQL 語法在 pgAdmin 中的資料庫中創建一個會話表，通過使用查詢工具，然后我使用yarn prisma db pull. 這會生成Prisma用于創建類似會話表的語法。
在我的server.js檔案中，我只是像這樣匯入 PrismaClient
const { PrismaClient } = require("@prisma/client");
總之，這就是我的代碼的樣子。
---------------會話模式-----------
model session {
sid String @id @db.VarChar
sess Json @db.Json
expire DateTime @db.Timestamp(6)
@@index([expire], map: "IDX_session_expire")
}
---------------會話模式-----------
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("-------------------------");
});
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/508471.html