Kysely Adapter

The Kysely adapter provides type-safe SQL database operations for AuthHero using the Kysely query builder. It supports PostgreSQL, MySQL, SQLite, and other SQL databases with full TypeScript integration.

Features

• Type-Safe Queries: Full TypeScript support with compile-time query validation
• Multiple Databases: PostgreSQL, MySQL, SQLite, SQL Server, and more
• Migration System: Built-in database migration management
• Query Builder: Powerful and flexible query construction
• Transaction Support: Full ACID transaction capabilities
• Connection Pooling: Efficient database connection management

Installation

npm install @authhero/kysely

Configuration

SQLite (Development)

import { Database } from "@authhero/kysely";
import { createSQLiteDialect } from "@authhero/kysely";

const database = new Database({
  dialect: createSQLiteDialect({
    database: "authhero.db",
  }),
});

PostgreSQL (Production)

import { Database } from "@authhero/kysely";
import { createPostgreSQLDialect } from "@authhero/kysely";

const database = new Database({
  dialect: createPostgreSQLDialect({
    host: "localhost",
    port: 5432,
    database: "authhero",
    user: "username",
    password: "password",
  }),
});

MySQL

import { Database } from "@authhero/kysely";
import { createMySQLDialect } from "@authhero/kysely";

const database = new Database({
  dialect: createMySQLDialect({
    host: "localhost",
    port: 3306,
    database: "authhero",
    user: "username",
    password: "password",
  }),
});

Database Operations

Users

// Create a user
const user = await database.users.create({
  user_id: "user_123",
  tenant_id: "tenant_456",
  email: "user@example.com",
  name: "John Doe",
  provider: "auth0",
  email_verified: true,
  is_social: false,
  app_metadata: "{}",
  user_metadata: "{}",
  created_at: new Date().toISOString(),
  updated_at: new Date().toISOString(),
});

// Get users with filtering
const users = await database.users.list({
  tenant_id: "tenant_456",
  page: 0,
  per_page: 10,
  search: "john",
  include_totals: true,
});

// Update a user
await database.users.update("user_123", "tenant_456", {
  name: "John Smith",
  updated_at: new Date().toISOString(),
});

Applications

// Create an application
const app = await database.applications.create({
  id: "app_123",
  tenant_id: "tenant_456",
  name: "My App",
  client_secret: "secret_key",
  callbacks: JSON.stringify(["http://localhost:3000/callback"]),
  allowed_origins: JSON.stringify(["http://localhost:3000"]),
  disable_sign_ups: false,
  created_at: new Date().toISOString(),
  updated_at: new Date().toISOString(),
});

Sessions

// Create a session
const session = await database.sessions.create({
  id: "session_123",
  tenant_id: "tenant_456",
  user_id: "user_123",
  created_at: new Date().toISOString(),
  expires_at: new Date(Date.now() + 24 * 60 * 60 * 1000).toISOString(),
  device: JSON.stringify({ browser: "Chrome" }),
  clients: JSON.stringify(["app_123"]),
});

Migrations

The Kysely adapter includes a comprehensive migration system to manage database schema changes:

# Run migrations
npx kysely migrate up

# Create a new migration
npx kysely migrate create add_new_table

# Rollback migrations
npx kysely migrate down

Migration Files

Migrations are TypeScript files that define schema changes:

import { Kysely } from "kysely";
import { Database } from "@authhero/kysely";

export async function up(db: Kysely<Database>): Promise<void> {
  await db.schema
    .createTable("new_table")
    .addColumn("id", "varchar(255)", (col) => col.primaryKey())
    .addColumn("tenant_id", "varchar(255)", (col) =>
      col.notNull().references("tenants.id").onDelete("cascade"),
    )
    .addColumn("created_at", "varchar(255)", (col) => col.notNull())
    .execute();
}

export async function down(db: Kysely<Database>): Promise<void> {
  await db.schema.dropTable("new_table").execute();
}

Database Schema

The Kysely adapter implements a comprehensive multi-tenant database schema designed for authentication and authorization. The schema supports:

• Multi-tenancy: Complete data isolation between tenants
• User Management: Comprehensive user profiles and authentication
• OAuth/OIDC: Full OAuth 2.0 and OpenID Connect support
• RBAC: Role-based access control with permissions
• Organizations: Hierarchical multi-tenancy
• Audit Logging: Complete audit trail
• Customization: Themes, branding, and custom domains

Entity Relationship Diagram

Performance Considerations

Query Optimization

• Use Indexes: Ensure proper indexing on frequently queried columns
• Limit Results: Always use pagination for large result sets
• Select Specific Columns: Avoid SELECT * in production queries
• Connection Pooling: Configure appropriate pool sizes for your workload

Production Deployment

• Connection Limits: Configure max connections based on database capacity
• Read Replicas: Use read replicas for query-heavy workloads
• Monitoring: Set up database performance monitoring
• Backups: Implement regular automated backups
• SSL/TLS: Always use encrypted connections in production

Troubleshooting

Common Issues

1. Migration Errors: Ensure database user has DDL permissions
2. Connection Timeouts: Increase connection timeout settings
3. Type Errors: Verify schema types match database structure
4. Performance Issues: Check query execution plans and indexes

Debug Logging

Enable debug logging to troubleshoot issues:

const database = new Database({
  dialect: dialect,
  log: ["query", "error"], // Enable query and error logging
});

This will log all SQL queries and errors to the console, helping with debugging and performance optimization.