How do I make this express-session logic work with prisma?

Issue

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;

Solution

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("-------------------------");
});

Answered By – Paulliano

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published