NextAuth.js Postgres Adapter
How to implement a postgres adapter for next-auth without dependencies
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.
You can find the code on my github as well.
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)
VALUES ($1, $2, $3, $4)
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);