@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

• Basic Setup
• Multi-tenant App
• Custom Field Mapping
• Performance Optimization

Next Steps

• Explore advanced queries
• Learn about performance optimization
• See migration guide from other databases