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
-
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
}); -
Date Handling
// Different databases handle dates differently
// PostgreSQL: Native Date objects
// MySQL: Date objects or strings
// SQLite: ISO strings -
Case Sensitivity
// Be aware of case sensitivity in searches
// PostgreSQL: Case-sensitive by default
// MySQL: Depends on collation
// SQLite: Case-insensitive by default