marchellodev/authjs-adapter-sql

Mysql/Postgres adapter for Auth.js

Sql Adapter - NextAuth.js / Auth.js

---

This adapter uses plain sql statements to integrate with Authjs.

Support for Mysql (mysql2) and Postgres (pg-promise).
Also works with the PlanetScale Serverless Driver, Neon Serverless Driver, Kysely, Knex and Slonik.

You can create a custom helper function to support other drivers if needed.

Configuration

You can set an optional table prefix/postgres schema.

const config = {
  prefix: "auth_", //or for example 'auth.' for a postgres schema
};

export default NextAuth({
  adapter: SqlAdapter(helpers, config),
  providers: [],
});

Default vs named imports

If you have problems with default imports in modules, than you can also try named imports.

Instead of this:

import SqlAdapter from "authjs-adapter-sql";
import buildMysql2Helpers from "authjs-adapter-sql/mysql2";

try this:

import { SqlAdapter } from "authjs-adapter-sql";
import { buildMysql2Helpers } from "authjs-adapter-sql/mysql2";

How to use with Mysql2

Install:

npm i authjs-adapter-sql mysql2

use mysql.sql to create the tables. (you can add foreign keys if needed)

import SqlAdapter from "authjs-adapter-sql";
import * as mysql from "mysql2/promise";
import buildMysql2Helpers from "authjs-adapter-sql/mysql2";

function getConnection() {
  return mysql.createConnection({
    host: "127.0.0.1",
    user: "root",
    database: "fancydb",
  });
}

// you can create your own helpers for custom logic
const mysql2Helpers = buildMysql2Helpers(getConnection);

export default NextAuth({
  adapter: SqlAdapter(mysql2Helpers),
  providers: [],
});

When using the new app dir in NextJs you may need this:

const nextConfig = {
  experimental: {
    appDir: true,
    serverComponentsExternalPackages: ["mysql2"],
  },
};

How to use with PlanetScale serverless driver

Install:

npm i authjs-adapter-sql @planetscale/database

use mysql.sql to create the tables.

import SqlAdapter from "authjs-adapter-sql";
import buildPlanetScaleHelpers from "authjs-adapter-sql/planetscale";

const client = new Client(config);

// you can create your own helpers for custom logic
const psHelpers = buildPlanetScaleHelpers(client);

export default NextAuth({
  adapter: SqlAdapter(psHelpers),
  providers: [],
});

• https://planetscale.com/docs/learn/operating-without-foreign-key-constraints
• https://planetscale.com/docs/tutorials/planetscale-serverless-driver

How to use with pg-promise

Install:

npm i authjs-adapter-sql pg-promise

use postgres.sql to create the tables. (you can add foreign keys if needed)

import SqlAdapter from "authjs-adapter-sql";
import pgPromise from "pg-promise";
import buildPgPromiseHelpers from "../src/pg-promise";

const pgp = pgPromise();
const db = pgp("postgres://postgres:postgres@localhost:5432/postgres");

function getConnection() {
  return db;
}

// you can create your own helpers for custom logic
const pgHelpers = buildPgPromiseHelpers(getConnection);

export default NextAuth({
  adapter: SqlAdapter(pgHelpers),
  providers: [],
});

How to use with Neon Serverless Driver

Install:

npm i authjs-adapter-sql @neondatabase/serverless

use postgres.sql to create the tables. (you can add foreign keys if needed)

import SqlAdapter from "authjs-adapter-sql";
import buildNeonHelpers from "authjs-adapter-sql/neon";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

// you can create your own helpers for custom logic
const helpers = buildNeonHelpers(pool);

export default NextAuth({
  adapter: SqlAdapter(helpers),
  providers: [],
});

How to use with Kysely

Install:

npm i kysely mysql2 (or pg)

use mysql.sql or postgres.sql to create the tables. (you can add foreign keys if needed)

import SqlAdapter from "authjs-adapter-sql";
import { Kysely, MysqlDialect } from "kysely";
import buildKyselyHelpers from "authjs-adapter-sql/kysely";

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_DATABASE,
});

const db = new Kysely({
  dialect: new MysqlDialect({
    pool,
  }),
});

// you can create your own helpers for custom logic
const helpers = buildKyselyHelpers(db, "mysql"); //or postgres

export default NextAuth({
  adapter: SqlAdapter(helpers),
  providers: [],
});

How to use with Knex

Install:

npm i knex mysql2 (or pg)

use mysql.sql or postgres.sql to create the tables. (you can add foreign keys if needed)

import SqlAdapter from "authjs-adapter-sql";
import { Knex, knex } from "knex";
import buildKnexHelpers from "authjs-adapter-sql/knex";

const config: Knex.Config = {
  client: "mysql2",
  connection: {
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_DATABASE,
  },
};

const knexInstance = knex(config);

// you can create your own helpers for custom logic
const helpers = buildKnexHelpers(knexInstance, "mysql"); //or postgres

export default NextAuth({
  adapter: SqlAdapter(helpers),
  providers: [],
});

How to use with Slonik

Install:

npm i slonik pg

use postgres.sql to create the tables. (you can add foreign keys if needed)

import SqlAdapter from "authjs-adapter-sql";
import buildSlonikHelpers from "authjs-adapter-sql/slonik";
import { createPool } from "slonik";

const poolPromise = createPool("postgres://postgres:postgres@localhost:5432/postgres");

function getConnection() {
  return poolPromise;
}

// you can create your own helpers for custom logic
const helpers = buildSlonikHelpers(getConnection);

export default NextAuth({
  adapter: SqlAdapter(helpers),
  providers: [],
});