Drizzle

gau has a Drizzle ORM adapter for SQLite and PostgreSQL. The SQLite examples use Turso as the database provider.

1. Install Dependencies

   SQLite
   npm

   npm i drizzle-orm @libsql/client
   npm i -D drizzle-kit

   pnpm

   pnpm add drizzle-orm @libsql/client
   pnpm add -D drizzle-kit

   yarn

   yarn add drizzle-orm @libsql/client
   yarn add -D drizzle-kit

   bun

   bun add drizzle-orm @libsql/client
   bun add -D drizzle-kit

   ni

   ni drizzle-orm @libsql/client
   ni -D drizzle-kit

   PostgreSQL
   npm

   npm i drizzle-orm pg
   npm i -D drizzle-kit

   pnpm

   pnpm add drizzle-orm pg
   pnpm add -D drizzle-kit

   yarn

   yarn add drizzle-orm pg
   yarn add -D drizzle-kit

   bun

   bun add drizzle-orm pg
   bun add -D drizzle-kit

   ni

   ni drizzle-orm pg
   ni -D drizzle-kit

2. Define the Database Schema

   This is the complete schema for gau. Depending on your OAuth provider you may be able to omit some columns.

   SQLite

   schema.ts

   import { integer, primaryKey, sqliteTable, text } from 'drizzle-orm/sqlite-core'
   
   export const Users = sqliteTable('users', {
     id: text().primaryKey().$defaultFn(() => crypto.randomUUID()),
     name: text(),
     email: text().unique(),
     emailVerified: integer({ mode: 'boolean' }),
     image: text(),
     createdAt: integer({ mode: 'timestamp' }).$defaultFn(() => new Date()),
     updatedAt: integer({ mode: 'timestamp' }).$defaultFn(() => new Date()),
   })
   
   export type SelectUser = typeof Users.$inferSelect
   export type InsertUser = typeof Users.$inferInsert
   
   export const Accounts = sqliteTable(
     'accounts',
     {
       userId: text()
         .notNull()
         .references(() => Users.id, { onDelete: 'cascade' }),
       type: text().notNull(),
       provider: text().notNull(),
       providerAccountId: text().notNull(),
       refreshToken: text(),
       accessToken: text(),
       expiresAt: integer(),
       tokenType: text(),
       scope: text(),
       idToken: text(),
       sessionState: text('session_state'),
       createdAt: integer('created_at', { mode: 'timestamp' }).$defaultFn(() => new Date()),
     },
     account => [
       primaryKey({
         columns: [account.provider, account.providerAccountId],
       }),
     ],
   )
   
   export type SelectAccount = typeof Accounts.$inferSelect
   export type InsertAccount = typeof Accounts.$inferInsert

   PostgreSQL

   schema.ts

   import { boolean, integer, pgTable, primaryKey, text, timestamp } from 'drizzle-orm/pg-core'
   
   export const Users = pgTable('users', {
     id: text().primaryKey(),
     name: text(),
     email: text().unique(),
     emailVerified: boolean('email_verified'),
     image: text(),
     createdAt: timestamp('created_at').defaultNow(),
     updatedAt: timestamp('updated_at').defaultNow(),
   })
   
   export type SelectUser = typeof Users.$inferSelect
   export type InsertUser = typeof Users.$inferInsert
   
   export const Accounts = pgTable(
     'accounts',
     {
       userId: text().notNull().references(() => Users.id, { onDelete: 'cascade' }),
       type: text().notNull(),
       provider: text().notNull(),
       providerAccountId: text().notNull(),
       refreshToken: text(),
       accessToken: text(),
       expiresAt: integer('expires_at'),
       tokenType: text(),
       scope: text(),
       idToken: text(),
       sessionState: text('session_state'),
       createdAt: timestamp('created_at').defaultNow(),
     },
     account => [
       primaryKey({
         columns: [account.provider, account.providerAccountId],
       }),
     ],
   )
   
   export type SelectAccount = typeof Accounts.$inferSelect
   export type InsertAccount = typeof Accounts.$inferInsert

3. Configure DrizzleKit

   Configure the schema path and database credentials for migrations.

   SQLite

   drizzle.config.ts

   import { defineConfig } from 'drizzle-kit'
   
   export default defineConfig({
     schema: './schema.ts',
     dialect: 'turso',
     dbCredentials: {
       authToken: process.env.TURSO_AUTH_TOKEN!,
       url: process.env.TURSO_DB_URL!,
     },
   })

   PostgreSQL

   drizzle.config.ts

   import { defineConfig } from 'drizzle-kit'
   
   export default defineConfig({
     schema: './schema.ts',
     dialect: 'postgresql',
     dbCredentials: {
       url: process.env.DATABASE_URL!,
     },
   })

   Now you can run npx drizzle-kit push to apply your schema to the database.

4. Set up the Database Client

   Initialize the Drizzle client with your database connection.

   SQLite

   db.ts

   import { createClient } from '@libsql/client'
   import { drizzle } from 'drizzle-orm/libsql'
   import * as schema from './schema'
   
   const client = createClient({
     url: process.env.TURSO_DB_URL!,
     authToken: process.env.TURSO_AUTH_TOKEN!,
   })
   
   export const db = drizzle(client, { schema, casing: 'snake_case' })

   PostgreSQL

   db.ts

   import { drizzle } from 'drizzle-orm/node-postgres'
   import { Pool } from 'pg'
   import * as schema from './schema'
   
   const pool = new Pool({
     connectionString: process.env.DATABASE_URL!,
   })
   
   export const db = drizzle(pool, { schema })

5. Integrate with createAuth

   Finally, pass the Drizzle client and schema tables to the DrizzleAdapter in your createAuth configuration.

   auth.ts

   import { DrizzleAdapter } from '@rttnd/gau/adapters/drizzle'
   import { createAuth } from '@rttnd/gau/core'
   import { GitHub } from '@rttnd/gau/oauth'
   import { db } from './db'
   import { Accounts, Users } from './schema'
   
   export const auth = createAuth({
     adapter: DrizzleAdapter(db, Users, Accounts),
     providers: [
       GitHub({
         clientId: process.env.GITHUB_CLIENT_ID!,
         clientSecret: process.env.GITHUB_CLIENT_SECRET!,
       }),
     ],
     jwt: {
       secret: process.env.AUTH_SECRET!,
     },
   })
   
   export type Auth = typeof auth