NextAuth.js Postgres Adapter

How to implement a postgres adapter for next-auth without dependencies

A handsome young man (me).
Jakob Maier
Feb 7, 2022

NextAuth.js is authentication solution for Next.js, designed work with with various kinds of authentication providers. NextAuth uses Adapters to connect to databases to store user data and persist sessions. There are various official adapters available:

And while ORMs like prisma and sequelize can work with most popular databases, it is one more dependency to install and learn on my already dependency heavy project. So because there is no Adapter available to work with postgres directly this tutorial shows how to implement an adapter for use with node-postgres, without any other dependencies. This tutorial asumes you already have a working Next.js website, if not you can easily create one. We will also install all the necessary dependencies.

npx create-next-app
npm install pg next-auth

To implement an adapter there are 10 methods that we need to implement:

  • createUser
  • getUser
  • getUserByEmail
  • getUserByAccount
  • linkAccount
  • createSession
  • getSessionAndUser
  • updateSession
  • deleteSession
  • updateUser

We put the implementation in lib/adapter.js.

To use next-auth we create a file called [...nextauth].js in pages/api/auth. Finally we need to create the schema in your database, you can find it in schema.sql. This file can be executed like this: psql -f schema.sql.

mkdir -p pages/api/auth/[...nextauth].js
import NextAuth from "next-auth";
import GoogleProvider from "next-auth/providers/google";
import PostgresAdapter from "../../../lib/adapter";

const pool = new Pool({
	user: "postgres",
	host: "localhost",
	database: "postgres",
	password: "postgres",
	port: 5432,
});

export default NextAuth({
	providers: [
		GoogleProvider({
			clientId: process.env.GOOGLE_ID,
			clientSecret: process.env.GOOGLE_SECRET,
		}),
	],
	adapter: PostgresAdapter(pool),
});

lib/adapter.js

export default function PostgresAdapter(client, options = {}) {
	return {
		async createUser(user) {
			try {
				const sql = `
        INSERT INTO users (name, email, email_verified, image, username) 
        VALUES ($1, $2, $3, $4, $1) 
        RETURNING id, name, email, email_verified, image`;
				let result = await client.query(sql, [user.name, user.email, user.emailVerified, user.image]);
				return result.rows[0];
			} catch (err) {
				console.log(err);
				return;
			}
		},
		async getUser(id) {
			try {
				const sql = `select * from users where id = $1`;
				let result = await client.query(sql, [id]);
				return result.rows[0];
			} catch (err) {
				console.log(err);
				return;
			}
		},
		async getUserByEmail(email) {
			try {
				const sql = `select * from users where email = $1`;
				let result = await client.query(sql, [email]);
				return result.rows[0];
			} catch (err) {
				console.log(err);
				return;
			}
		},
		async getUserByAccount({ providerAccountId, provider }) {
			try {
				const sql = `
          select u.* from users u join accounts a on u.id = a.user_id 
          where 
          a.provider_id = $1 
          and 
          a.provider_account_id = $2`;

				const result = await client.query(sql, [provider, providerAccountId]);
				return result.rows[0];
			} catch (err) {
				console.log(err);
			}
		},
		async updateUser(user) {
			try {
			} catch (err) {
				console.log(err);
				return;
			}
		},
		async linkAccount(account) {
			try {
				const sql = `
        insert into accounts 
        (
          user_id, 
          provider_id, 
          provider_type, 
          provider_account_id, 
          access_token,
          access_token_expires
        )
        values ($1, $2, $3, $4, $5, to_timestamp($6))`;

				const params = [
					account.userId,
					account.provider,
					account.type,
					account.providerAccountId,
					account.access_token,
					account.expires_at,
				];

				await client.query(sql, params);
				return account;
			} catch (err) {
				console.log(err);
				return;
			}
		},
		async createSession({ sessionToken, userId, expires }) {
			try {
				const sql = `insert into sessions (user_id, expires, session_token) values ($1, $2, $3)`;
				await client.query(sql, [userId, expires, sessionToken]);
				return { sessionToken, userId, expires };
			} catch (err) {
				console.log(err);
				return;
			}
		},
		async getSessionAndUser(sessionToken) {
			try {
				let result;
				result = await client.query("select * from sessions where session_token = $1", [sessionToken]);

				let session = result.rows[0];

				result = await client.query("select * from users where id = $1", [session.user_id]);
				let user = result.rows[0];

				return {
					session,
					user,
				};
			} catch (err) {
				console.log(err);
				return;
			}
		},
		async updateSession({ sessionToken }) {
			console.log("updateSession", sessionToken);
			return;
		},
		async deleteSession(sessionToken) {
			try {
				const sql = `delete from sessions where session_token = $1`;
				await client.query(sql, [sessionToken]);
			} catch (err) {
				console.log(err);
				return;
			}
		},
	};
}
schema.sql
CREATE TABLE accounts
(
  id                   SERIAL,
  compound_id          VARCHAR(255), -- removed not null
  user_id              INTEGER NOT NULL,
  provider_type        VARCHAR(255) NOT NULL,
  provider_id          VARCHAR(255) NOT NULL,
  provider_account_id  VARCHAR(255) NOT NULL,
  refresh_token        TEXT,
  access_token         TEXT,
  access_token_expires TIMESTAMPTZ,
  created_at           TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

CREATE TABLE sessions
(
  id            SERIAL,
  user_id       INTEGER NOT NULL,
  expires       TIMESTAMPTZ NOT NULL,
  session_token VARCHAR(255) NOT NULL,
  access_token  VARCHAR(255), -- removed not null
  created_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

CREATE TABLE users
(
  id             SERIAL,
  name           VARCHAR(255),
  email          VARCHAR(255),
  email_verified TIMESTAMPTZ,
  image          TEXT,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

CREATE UNIQUE INDEX compound_id ON accounts(compound_id);

CREATE INDEX provider_account_id ON accounts(provider_account_id);

CREATE INDEX provider_id ON accounts(provider_id);

CREATE INDEX user_id ON accounts(user_id);

CREATE UNIQUE INDEX session_token ON sessions(session_token);

CREATE UNIQUE INDEX access_token ON sessions(access_token);

CREATE UNIQUE INDEX email ON users(email);

Back to Top

Edit This Page

© 2022 Jakob Maier