npm package: @glitch_protocol/auth-adapter-drizzle
Description: DatabaseAdapter implementation for Drizzle ORM + PostgreSQL
Author: drcount-root
Latest version: 0.2.0

# @glitch_protocol/auth-adapter-drizzle

Drizzle ORM implementation of `DatabaseAdapter` for PostgreSQL. Brings-your-own-schema — pass table references and the adapter reads/writes the columns it needs.

## Install

```bash
npm install @glitch_protocol/auth-adapter-drizzle drizzle-orm postgres
```

## Usage

### 1. Define your schema

Your tables must have these columns. You can add extras as needed.

```ts
import { pgTable, text, boolean, timestamp } from "drizzle-orm/pg-core";
import { createId } from "@paralleldrive/cuid2"; // or your preferred ID generator

export const users = pgTable("users", {
  id: text("id").primaryKey().$defaultFn(() => createId()),
  email: text("email").notNull().unique(),
  name: text("name").notNull(),
  passwordHash: text("password_hash").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  // Add your custom fields: role, avatar, etc.
});

export const sessions = pgTable("sessions", {
  id: text("id").primaryKey().$defaultFn(() => createId()),
  userId: text("user_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  deviceName: text("device_name"),
  ipAddress: text("ip_address").notNull(),
  userAgent: text("user_agent"),
  browser: text("browser"),
  os: text("os"),
  socketId: text("socket_id"),
  isActive: boolean("is_active").default(true).notNull(),
  lastActivity: timestamp("last_activity").defaultNow().notNull(),
  expiresAt: timestamp("expires_at").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
  // Add your custom fields: description, customData, etc.
});

export const refreshTokens = pgTable("refresh_tokens", {
  id: text("id").primaryKey().$defaultFn(() => createId()),
  userId: text("user_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  sessionId: text("session_id")
    .notNull()
    .references(() => sessions.id, { onDelete: "cascade" }),
  token: text("token").notNull().unique(), // SHA-256 hash
  rotatedFrom: text("rotated_from"),
  isRevoked: boolean("is_revoked").default(false).notNull(),
  revokedAt: timestamp("revoked_at"),
  expiresAt: timestamp("expires_at").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});
```

### 2. Create the adapter

```ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import { DrizzleAdapter } from "@glitch_protocol/auth-adapter-drizzle";
import { users, sessions, refreshTokens } from "./schema";

const client = postgres(process.env.DATABASE_URL!);
const db = drizzle(client);

const dbAdapter = new DrizzleAdapter(db, {
  users,
  sessions,
  refreshTokens,
});

export { dbAdapter };
```

### 3. Use with engine

```ts
import { glitch_protocolEngine } from "@glitch_protocol/auth-server";
import { RedisCacheAdapter } from "@glitch_protocol/auth-adapter-redis";
import { dbAdapter } from "./db-adapter";

const engine = new glitch_protocolEngine(
  dbAdapter,
  new RedisCacheAdapter(redis),
  socketBroadcaster,
  { jwtSecret: process.env.JWT_SECRET! },
);
```

---

## Schema Column Reference

The adapter expects these exact column names. You can add custom columns — the adapter will ignore them.

### users table

| Column | Type | Required | Notes |
|--------|------|----------|-------|
| `id` | text | Yes | Primary key. Use CUID2 or UUID. |
| `email` | text | Yes | Unique. Used for login. |
| `name` | text | Yes | Display name. |

**Example with custom columns:**
```ts
export const users = pgTable("users", {
  id: text("id").primaryKey(),
  email: text("email").notNull().unique(),
  name: text("name").notNull(),
  passwordHash: text("password_hash").notNull(),
  // Your custom columns:
  role: text("role").default("user"),
  avatar: text("avatar"),
  createdAt: timestamp("created_at").defaultNow(),
});
```

### sessions table

| Column | Type | Required | Notes |
|--------|------|----------|-------|
| `id` | text | Yes | Primary key. |
| `userId` | text | Yes | Foreign key → users.id |
| `deviceName` | text | No | Optional user-provided device name. |
| `ipAddress` | text | Yes | Request IP address. |
| `userAgent` | text | No | Browser User-Agent header. |
| `browser` | text | No | Parsed browser name (Chrome, Firefox, etc). |
| `os` | text | No | Parsed OS name (Windows, macOS, etc). |
| `socketId` | text | No | Socket.IO socket ID for real-time events. |
| `isActive` | boolean | Yes | Soft-delete flag. Default: true. |
| `lastActivity` | timestamp | Yes | Last request timestamp. Updated debounced. |
| `expiresAt` | timestamp | Yes | Session expiration time. |
| `createdAt` | timestamp | Yes | Session creation time. |
| `updatedAt` | timestamp | Yes | Last updated timestamp. |

### refreshTokens table

| Column | Type | Required | Notes |
|--------|------|----------|-------|
| `id` | text | Yes | Primary key. |
| `userId` | text | Yes | Foreign key → users.id |
| `sessionId` | text | Yes | Foreign key → sessions.id |
| `token` | text | Yes | SHA-256 hash of the raw token. Unique. |
| `rotatedFrom` | text | No | Previous token ID if rotated. |
| `isRevoked` | boolean | Yes | Soft-delete flag. Default: false. |
| `revokedAt` | timestamp | No | Revocation time if isRevoked. |
| `expiresAt` | timestamp | Yes | Token expiration time. |
| `createdAt` | timestamp | Yes | Token creation time. |
| `updatedAt` | timestamp | Yes | Last updated timestamp. |

---

## API

### Constructor

```ts
new DrizzleAdapter(db: DrizzleDatabase, schema: DrizzleSchemaTablesInput)
```

**DrizzleSchemaTablesInput:**
```ts
interface DrizzleSchemaTablesInput {
  users: any;        // Drizzle table reference
  sessions: any;
  refreshTokens: any;
}
```

### Implementation Details

The adapter implements all 17 methods of `DatabaseAdapter` from `@glitch_protocol/auth-core`. Uses Drizzle's parameterized queries (no SQL injection risk).

---

## Custom Columns

You can extend your tables with custom columns. The adapter only reads/writes the columns it knows about.

```ts
// Extra fields are fine
export const sessions = pgTable("sessions", {
  // ... required fields above
  customData: jsonb("custom_data"),
  tags: text("tags").array(),
});

// Adapter ignores customData and tags; your app can use them:
const { customData } = await db.query.sessions.findFirst({
  where: eq(sessions.id, sessionId),
});
```

---

## Indexes

For production, add indexes on frequently-queried columns:

```sql
CREATE INDEX idx_sessions_user_id ON sessions(user_id);
CREATE INDEX idx_sessions_is_active ON sessions(is_active);
CREATE INDEX idx_sessions_expires_at ON sessions(expires_at);
CREATE INDEX idx_refresh_tokens_token ON refresh_tokens(token);
CREATE INDEX idx_refresh_tokens_session_id ON refresh_tokens(session_id);
```

---

## Peer Dependencies

- `drizzle-orm: ^0.45.0` — ORM library
- `postgres: ^3.0.0` or `better-sqlite3: ^8.0.0` — database driver

## Node Version

**ESM-only.** Requires Node.js >=18.