Skip to main content

Adapters

Adapters provide the database abstraction layer for @pressw/threads, allowing you to use different database systems while maintaining a consistent API.

Overview

The adapter system is designed to be:

  • Database Agnostic - Support for multiple database providers
  • Type Safe - Full TypeScript support with schema validation
  • Performant - Optimized queries with proper indexing
  • Flexible - Easy to extend with custom adapters

Supported Databases

PostgreSQL

The recommended database for production use with full feature support.

import { createAdapter } from '@pressw/threads/adapters';
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';

const sql = postgres(process.env.DATABASE_URL!);
const db = drizzle(sql, { schema });

const adapter = createAdapter({
provider: 'postgresql',
db,
schema,
});

PostgreSQL Schema

-- Users table
CREATE TABLE users (
id VARCHAR(255) PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(255),
image TEXT
);

-- Threads table with JSONB for metadata
CREATE TABLE threads (
id VARCHAR(255) PRIMARY KEY,
title VARCHAR(255) NOT NULL,
user_id VARCHAR(255) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
organization_id VARCHAR(255),
tenant_id VARCHAR(255),
metadata JSONB DEFAULT '{}',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for performance
CREATE INDEX idx_threads_user_id ON threads(user_id);
CREATE INDEX idx_threads_organization_id ON threads(organization_id);
CREATE INDEX idx_threads_tenant_id ON threads(tenant_id);
CREATE INDEX idx_threads_created_at ON threads(created_at DESC);
CREATE INDEX idx_threads_title_search ON threads USING gin(to_tsvector('english', title));

-- Feedback table
CREATE TABLE feedback (
id VARCHAR(255) PRIMARY KEY,
thread_id VARCHAR(255) NOT NULL REFERENCES threads(id) ON DELETE CASCADE,
user_id VARCHAR(255) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
type VARCHAR(50) NOT NULL,
message TEXT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_feedback_thread_id ON feedback(thread_id);

MySQL

Full support for MySQL 8.0+.

import { createAdapter } from '@pressw/threads/adapters';
import { drizzle } from 'drizzle-orm/mysql2';
import mysql from 'mysql2/promise';
import * as schema from './schema';

const connection = await mysql.createConnection({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
});

const db = drizzle(connection, { schema });

const adapter = createAdapter({
provider: 'mysql',
db,
schema,
});

MySQL Schema

-- Users table
CREATE TABLE users (
id VARCHAR(255) PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(255),
image TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Threads table with JSON for metadata
CREATE TABLE threads (
id VARCHAR(255) PRIMARY KEY,
title VARCHAR(255) NOT NULL,
user_id VARCHAR(255) NOT NULL,
organization_id VARCHAR(255),
tenant_id VARCHAR(255),
metadata JSON DEFAULT ('{}'),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Indexes
CREATE INDEX idx_threads_user_id ON threads(user_id);
CREATE INDEX idx_threads_organization_id ON threads(organization_id);
CREATE INDEX idx_threads_tenant_id ON threads(tenant_id);
CREATE INDEX idx_threads_created_at ON threads(created_at DESC);
CREATE FULLTEXT INDEX idx_threads_title ON threads(title);

-- Feedback table
CREATE TABLE feedback (
id VARCHAR(255) PRIMARY KEY,
thread_id VARCHAR(255) NOT NULL,
user_id VARCHAR(255) NOT NULL,
type VARCHAR(50) NOT NULL,
message TEXT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (thread_id) REFERENCES threads(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX idx_feedback_thread_id ON feedback(thread_id);

SQLite

Perfect for development and testing.

import { createAdapter } from '@pressw/threads/adapters';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';
import * as schema from './schema';

const sqlite = new Database('threads.db');
const db = drizzle(sqlite, { schema });

const adapter = createAdapter({
provider: 'sqlite',
db,
schema,
});

SQLite Schema

-- Users table
CREATE TABLE users (
id TEXT PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
name TEXT,
image TEXT
);

-- Threads table
CREATE TABLE threads (
id TEXT PRIMARY KEY,
title TEXT NOT NULL,
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
organization_id TEXT,
tenant_id TEXT,
metadata TEXT DEFAULT '{}',
created_at TEXT NOT NULL DEFAULT (datetime('now')),
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Indexes
CREATE INDEX idx_threads_user_id ON threads(user_id);
CREATE INDEX idx_threads_organization_id ON threads(organization_id);
CREATE INDEX idx_threads_tenant_id ON threads(tenant_id);
CREATE INDEX idx_threads_created_at ON threads(created_at DESC);

-- Feedback table
CREATE TABLE feedback (
id TEXT PRIMARY KEY,
thread_id TEXT NOT NULL REFERENCES threads(id) ON DELETE CASCADE,
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
type TEXT NOT NULL,
message TEXT,
created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_feedback_thread_id ON feedback(thread_id);

Drizzle Schema Definition

Define your schema using Drizzle ORM:

// schema.ts
import { pgTable, text, timestamp, jsonb, index } from 'drizzle-orm/pg-core';
// or
// import { mysqlTable, varchar, timestamp, json, index } from "drizzle-orm/mysql-core";
// or
// import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core";

export const users = pgTable('users', {
id: text('id').primaryKey(),
email: text('email').notNull().unique(),
name: text('name'),
image: text('image'),
});

export const threads = pgTable(
'threads',
{
id: text('id').primaryKey(),
title: text('title').notNull(),
userId: text('user_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
organizationId: text('organization_id'),
tenantId: text('tenant_id'),
metadata: jsonb('metadata').$type<Record<string, any>>(),
createdAt: timestamp('created_at').notNull().defaultNow(),
updatedAt: timestamp('updated_at').notNull().defaultNow(),
},
(table) => ({
userIdIdx: index('idx_threads_user_id').on(table.userId),
orgIdIdx: index('idx_threads_organization_id').on(table.organizationId),
tenantIdIdx: index('idx_threads_tenant_id').on(table.tenantId),
createdAtIdx: index('idx_threads_created_at').on(table.createdAt),
}),
);

export const feedback = pgTable(
'feedback',
{
id: text('id').primaryKey(),
threadId: text('thread_id')
.notNull()
.references(() => threads.id, { onDelete: 'cascade' }),
userId: text('user_id')
.notNull()
.references(() => users.id, { onDelete: 'cascade' }),
type: text('type').notNull(),
message: text('message'),
createdAt: timestamp('created_at').notNull().defaultNow(),
},
(table) => ({
threadIdIdx: index('idx_feedback_thread_id').on(table.threadId),
}),
);

// Export types
export type User = typeof users.$inferSelect;
export type Thread = typeof threads.$inferSelect;
export type Feedback = typeof feedback.$inferSelect;

Adapter Interface

The adapter implements the ChatCoreAdapter interface:

interface ChatCoreAdapter {
create<T extends TableName>(table: T, data: InsertData<T>): Promise<SelectData<T>>;

findOne<T extends TableName>(table: T, where: WhereConditions<T>): Promise<SelectData<T> | null>;

findMany<T extends TableName>(
table: T,
options?: {
where?: WhereConditions<T>;
limit?: number;
offset?: number;
orderBy?: OrderByConfig<T>;
},
): Promise<SelectData<T>[]>;

update<T extends TableName>(
table: T,
where: WhereConditions<T>,
data: Partial<InsertData<T>>,
): Promise<SelectData<T>>;

delete<T extends TableName>(table: T, where: WhereConditions<T>): Promise<void>;

count<T extends TableName>(table: T, where?: WhereConditions<T>): Promise<number>;
}

Custom Adapters

You can create custom adapters for unsupported databases:

import { ChatCoreAdapter } from '@pressw/threads/adapters';

class CustomAdapter implements ChatCoreAdapter {
constructor(private db: YourDatabaseClient) {}

async create(table, data) {
// Implement create logic
}

async findOne(table, where) {
// Implement findOne logic
}

async findMany(table, options) {
// Implement findMany logic
}

async update(table, where, data) {
// Implement update logic
}

async delete(table, where) {
// Implement delete logic
}

async count(table, where) {
// Implement count logic
}
}

// Use custom adapter
const adapter = new CustomAdapter(yourDbClient);
const threadClient = new ThreadUtilityClient({ adapter });

Performance Optimization

Indexing Strategy

Proper indexing is crucial for performance:

-- User lookup performance
CREATE INDEX idx_threads_user_id ON threads(user_id);

-- Multi-tenant queries
CREATE INDEX idx_threads_organization_id ON threads(organization_id);
CREATE INDEX idx_threads_tenant_id ON threads(tenant_id);

-- Sorting and pagination
CREATE INDEX idx_threads_created_at ON threads(created_at DESC);

-- Full-text search (PostgreSQL)
CREATE INDEX idx_threads_title_search ON threads
USING gin(to_tsvector('english', title));

-- Full-text search (MySQL)
CREATE FULLTEXT INDEX idx_threads_title ON threads(title);

Query Optimization

The adapter automatically optimizes queries:

// Efficient pagination with proper indexes
const threads = await adapter.findMany('threads', {
where: { userId: 'user_123' },
limit: 20,
offset: 40,
orderBy: { createdAt: 'desc' },
});

// Optimized search queries
const searchResults = await adapter.findMany('threads', {
where: {
AND: [{ userId: 'user_123' }, { title: { contains: 'project' } }],
},
});

Connection Pooling

Configure connection pooling for production:

// PostgreSQL
const sql = postgres(process.env.DATABASE_URL!, {
max: 20, // connection pool size
idle_timeout: 20,
connect_timeout: 10,
});

// MySQL
const pool = mysql.createPool({
host: process.env.DB_HOST,
connectionLimit: 20,
waitForConnections: true,
queueLimit: 0,
});

Migrations

Using Drizzle Kit

# Generate migrations
npx drizzle-kit generate:pg --schema=./src/schema.ts

# Run migrations
npx drizzle-kit migrate:pg --schema=./src/schema.ts

# Push schema changes (development)
npx drizzle-kit push:pg --schema=./src/schema.ts

Migration Example

// migrations/0001_create_threads_tables.sql
CREATE TABLE IF NOT EXISTS users (
id VARCHAR(255) PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(255),
image TEXT
);

CREATE TABLE IF NOT EXISTS threads (
id VARCHAR(255) PRIMARY KEY,
title VARCHAR(255) NOT NULL,
user_id VARCHAR(255) NOT NULL REFERENCES users(id),
organization_id VARCHAR(255),
tenant_id VARCHAR(255),
metadata JSONB DEFAULT '{}',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Add indexes
CREATE INDEX IF NOT EXISTS idx_threads_user_id ON threads(user_id);
CREATE INDEX IF NOT EXISTS idx_threads_created_at ON threads(created_at DESC);

Testing with Adapters

In-Memory SQLite for Tests

// test-utils.ts
import { createAdapter } from '@pressw/threads/adapters';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';
import * as schema from './schema';

export function createTestAdapter() {
// In-memory database
const sqlite = new Database(':memory:');
const db = drizzle(sqlite, { schema });

// Initialize schema
db.run(`
CREATE TABLE users (
id TEXT PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
name TEXT,
image TEXT
);

CREATE TABLE threads (
id TEXT PRIMARY KEY,
title TEXT NOT NULL,
user_id TEXT NOT NULL,
organization_id TEXT,
tenant_id TEXT,
metadata TEXT DEFAULT '{}',
created_at TEXT NOT NULL DEFAULT (datetime('now')),
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE feedback (
id TEXT PRIMARY KEY,
thread_id TEXT NOT NULL,
user_id TEXT NOT NULL,
type TEXT NOT NULL,
message TEXT,
created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
`);

return createAdapter({
provider: 'sqlite',
db,
schema,
});
}

// In tests
describe('Thread operations', () => {
let adapter: ChatCoreAdapter;

beforeEach(() => {
adapter = createTestAdapter();
});

test('creates thread', async () => {
const thread = await adapter.create('threads', {
id: 'test_1',
title: 'Test Thread',
userId: 'user_1',
});

expect(thread.title).toBe('Test Thread');
});
});

Troubleshooting

Common Issues

  1. JSON/JSONB Storage

    // Ensure proper JSON serialization
    const thread = await adapter.create('threads', {
    title: 'Thread',
    userId: 'user_1',
    metadata: JSON.stringify({ custom: 'data' }), // SQLite
    // metadata: { custom: "data" } // PostgreSQL/MySQL
    });
  2. Date Handling

    // Different databases handle dates differently
    // PostgreSQL: Native Date objects
    // MySQL: Date objects or strings
    // SQLite: ISO strings
  3. Case Sensitivity

    // Be aware of case sensitivity in searches
    // PostgreSQL: Case-sensitive by default
    // MySQL: Depends on collation
    // SQLite: Case-insensitive by default