@pressw/threads-drizzle
Drizzle ORM adapter for @pressw/threads, providing SQL database support for thread management with PostgreSQL, MySQL, and SQLite.
Overviewâ
@pressw/threads-drizzle
enables you to use your existing SQL database for thread storage with the flexibility of Drizzle ORM. It supports:
- ð PostgreSQL - Full JSON support and advanced features
- ðŽ MySQL - Wide compatibility and performance
- ðŠķ SQLite - Lightweight local storage
- ð Field Mapping - Use your existing database schema
- ð Type Safety - Full TypeScript support with Drizzle
- ð Performance - Optimized queries with proper indexing
Installationâ
npm install @pressw/threads @pressw/threads-drizzle drizzle-orm
# Database drivers (install the one you need)
npm install postgres # For PostgreSQL
npm install mysql2 # For MySQL
npm install better-sqlite3 # For SQLite
Quick Startâ
1. Define Your Schemaâ
import { pgTable, varchar, text, timestamp, uuid, jsonb } from 'drizzle-orm/pg-core';
export const users = pgTable('users', {
id: uuid('id').primaryKey().defaultRandom(),
name: varchar('name', { length: 255 }).notNull(),
email: varchar('email', { length: 255 }).notNull().unique(),
createdAt: timestamp('created_at').defaultNow().notNull(),
updatedAt: timestamp('updated_at').defaultNow().notNull(),
});
export const threads = pgTable('threads', {
id: uuid('id').primaryKey().defaultRandom(),
title: varchar('title', { length: 255 }),
userId: uuid('user_id')
.notNull()
.references(() => users.id),
organizationId: uuid('organization_id'),
tenantId: varchar('tenant_id', { length: 255 }),
metadata: jsonb('metadata'),
createdAt: timestamp('created_at').defaultNow().notNull(),
updatedAt: timestamp('updated_at').defaultNow().notNull(),
});
2. Create the Adapterâ
import { DrizzleAdapter } from '@pressw/threads-drizzle';
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
// Create database connection
const sql = postgres(process.env.DATABASE_URL!);
const db = drizzle(sql, { schema: { users, threads } });
// Create adapter
const adapter = new DrizzleAdapter(db, {
provider: 'postgres',
tables: {
user: 'users',
thread: 'threads',
feedback: 'feedback',
},
});
3. Use with Thread Clientâ
import { createThreadUtilityClient } from '@pressw/threads';
const threadClient = createThreadUtilityClient({
adapter,
getUserContext: async (request) => {
// Your auth logic
return { userId: 'user-123' };
},
});
// Now you can use all thread operations
const thread = await threadClient.createThread(request, {
title: 'My Thread',
metadata: { category: 'support' },
});
Configurationâ
Basic Configurationâ
const adapter = new DrizzleAdapter(db, {
provider: 'postgres', // 'postgres' | 'mysql' | 'sqlite'
tables: {
user: 'users', // Your user table name
thread: 'threads', // Your thread table name
feedback: 'feedback', // Your feedback table name
},
});
Advanced Configurationâ
const adapter = new DrizzleAdapter(db, {
provider: 'postgres',
tables: {
user: 'users',
thread: 'threads',
feedback: 'feedback',
},
// Map your existing column names
fields: {
thread: {
id: 'thread_id', // Map 'id' to 'thread_id' column
userId: 'user_id', // Map 'userId' to 'user_id' column
title: 'thread_title', // Map 'title' to 'thread_title' column
metadata: 'extra_data', // Map 'metadata' to 'extra_data' column
},
},
// Database capabilities
supportsJSON: true, // PostgreSQL, MySQL 5.7+
supportsDates: true, // All databases
supportsBooleans: true, // PostgreSQL, MySQL
supportsReturning: true, // PostgreSQL only
// Custom ID generation
generateId: () => nanoid(),
});
Database Schemasâ
PostgreSQL Schemaâ
-- Users table
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
-- Threads table
CREATE TABLE threads (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
title VARCHAR(255),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
organization_id UUID,
tenant_id VARCHAR(255),
metadata JSONB,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
-- 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);
MySQL Schemaâ
-- Users table
CREATE TABLE users (
id VARCHAR(36) PRIMARY KEY,
name VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Threads table
CREATE TABLE threads (
id VARCHAR(36) PRIMARY KEY,
title VARCHAR(255),
user_id VARCHAR(36) NOT NULL,
organization_id VARCHAR(36),
tenant_id VARCHAR(255),
metadata JSON,
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,
INDEX idx_user_id (user_id),
INDEX idx_organization_id (organization_id),
INDEX idx_tenant_id (tenant_id),
INDEX idx_created_at (created_at DESC)
);
SQLite Schemaâ
-- Users table
CREATE TABLE users (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
);
-- Threads table
CREATE TABLE threads (
id TEXT PRIMARY KEY,
title TEXT,
user_id TEXT NOT NULL,
organization_id TEXT,
tenant_id TEXT,
metadata TEXT, -- JSON stored as text
created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- 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);
Using Existing Databasesâ
If you have an existing database with different column names, use field mapping:
// Your existing schema
const legacyThreads = pgTable('conversations', {
conversationId: uuid('conversation_id').primaryKey(),
subject: varchar('subject', { length: 500 }),
ownerId: uuid('owner_id').notNull(),
companyId: uuid('company_id'),
customData: jsonb('custom_data'),
createdDate: timestamp('created_date').notNull(),
modifiedDate: timestamp('modified_date').notNull(),
});
// Map to thread model
const adapter = new DrizzleAdapter(db, {
provider: 'postgres',
tables: {
user: 'users',
thread: 'conversations', // Your table name
feedback: 'feedback',
},
fields: {
thread: {
id: 'conversation_id',
title: 'subject',
userId: 'owner_id',
organizationId: 'company_id',
metadata: 'custom_data',
createdAt: 'created_date',
updatedAt: 'modified_date',
},
},
});
Advanced Featuresâ
Custom Queriesâ
Access the underlying Drizzle instance for custom queries:
import { sql } from 'drizzle-orm';
// Get schema from adapter
const threadSchema = adapter.getSchema('thread');
// Custom query with Drizzle
const popularThreads = await db
.select()
.from(threadSchema)
.where(sql`metadata->>'views' > 100`)
.orderBy(sql`metadata->>'views' DESC`)
.limit(10);
Transactionsâ
Use Drizzle transactions for atomic operations:
await db.transaction(async (tx) => {
// Create thread using adapter with transaction
const thread = await adapter.create({
model: 'thread',
data: threadData,
});
// Additional operations in same transaction
await tx.insert(activities).values({
threadId: thread.id,
action: 'created',
});
});
Database-Specific Featuresâ
// PostgreSQL: Use JSONB operators
const threads = await adapter.findMany({
model: 'thread',
where: [
{
field: 'metadata',
value: { category: 'support' },
operator: 'contains', // Uses @> operator
},
],
});
// MySQL: Full-text search
const results = await db
.select()
.from(threads)
.where(sql`MATCH(title) AGAINST(${searchTerm} IN NATURAL LANGUAGE MODE)`);
// SQLite: Use JSON functions
const threads = await db
.select()
.from(threads)
.where(sql`json_extract(metadata, '$.priority') = 'high'`);
Performance Optimizationâ
1. Use Indexesâ
-- Most important indexes
CREATE INDEX idx_threads_user_org_tenant
ON threads(user_id, organization_id, tenant_id);
CREATE INDEX idx_threads_updated_at
ON threads(updated_at DESC);
2. Optimize Metadata Queriesâ
-- PostgreSQL: GIN index for JSONB
CREATE INDEX idx_threads_metadata
ON threads USING GIN (metadata);
-- MySQL: Virtual columns for frequently queried fields
ALTER TABLE threads ADD COLUMN priority VARCHAR(20)
GENERATED ALWAYS AS (metadata->>'$.priority') STORED;
CREATE INDEX idx_threads_priority ON threads(priority);
3. Connection Poolingâ
// PostgreSQL with connection pool
const sql = postgres(process.env.DATABASE_URL, {
max: 20, // Max connections
idle_timeout: 30,
});
// MySQL with pool
const pool = mysql.createPool({
uri: process.env.DATABASE_URL,
connectionLimit: 20,
});
Troubleshootingâ
Common Issuesâ
1. Schema Validation Errors
// Ensure all required fields exist
Error: Required field "userId" not found in table "threads"
// Solution: Check field mapping
fields: {
thread: {
userId: 'user_id', // Map to your column name
},
}
2. JSON Support
// SQLite doesn't have native JSON
Error: SQLite does not support JSON operators
// Solution: Adapter handles this automatically
supportsJSON: false, // Set based on your database
3. Date Handling
// SQLite stores dates as integers
// The adapter automatically converts between formats
Migration from Other Adaptersâ
From Direct SQLâ
// Before: Direct SQL
const threads = await db.query('SELECT * FROM threads WHERE user_id = $1', [userId]);
// After: With adapter
const threads = await adapter.findMany({
model: 'thread',
where: [{ field: 'userId', value: userId }],
});
From Other ORMsâ
// From Prisma
const threads = await prisma.thread.findMany({
where: { userId },
});
// To Drizzle adapter
const threads = await adapter.findMany({
model: 'thread',
where: [{ field: 'userId', value: userId }],
});
API Referenceâ
See the API documentation for detailed method signatures and options.
Examplesâ
Next Stepsâ
- Explore advanced queries
- Learn about performance optimization
- See migration guide from other databases