Troubleshooting Guide

Common issues and solutions when using @pressw/threads-drizzle.

Common Errors

Schema Validation Errors

Error: "Required field 'userId' not found in table"

Cause: The column name in your database doesn't match the expected field name.

Solution: Use field mapping to map the thread model field to your column:

const adapter = new DrizzleAdapter(db, {
  provider: 'postgres',
  tables: {
    thread: 'threads',
    // ...
  },
  fields: {
    thread: {
      userId: 'user_id', // Map userId to user_id column
    },
  },
});

Error: "Table 'threads' not found in schema"

Cause: The table doesn't exist or isn't included in your Drizzle schema.

Solution: Ensure the table is defined and passed to Drizzle:

// Define your schema
export const threads = pgTable('threads', {
  id: uuid('id').primaryKey(),
  // ... other columns
});

// Include in Drizzle instance
const db = drizzle(connection, {
  schema: {
    threads, // Make sure this is included
    users,
    feedback,
  },
});

Type Errors

Error: Type constraints with Record

Cause: TypeScript strict mode requires the Thread interface to extend Record.

Solution: Ensure your Thread type extends the proper base type:

interface Thread extends Record<string, unknown> {
  id: string;
  title?: string;
  userId: string;
  // ... other fields
}

Error: "Property 'metadata' is not assignable to type 'string'"

Cause: JSON fields need proper type handling based on database support.

Solution: Configure JSON support in your adapter:

const adapter = new DrizzleAdapter(db, {
  provider: 'sqlite',
  supportsJSON: false, // SQLite stores JSON as text
  // ...
});

Database-Specific Issues

PostgreSQL: JSONB operator errors

Cause: Trying to use JSONB operators on a text column.

Solution: Ensure your column is JSONB type:

export const threads = pgTable('threads', {
  metadata: jsonb('metadata'), // Not text('metadata')
});

MySQL: "Data too long for column 'metadata'"

Cause: JSON data exceeds column size limit.

Solution: Use JSON type instead of VARCHAR:

export const threads = mysqlTable('threads', {
  metadata: json('metadata'), // Not varchar with length limit
});

SQLite: JSON function not found

Cause: SQLite version doesn't support JSON functions.

Solution: Update SQLite or handle JSON in application:

// Check SQLite version
const version = db.get('SELECT sqlite_version()');

// For older versions, handle JSON in application
const adapter = new DrizzleAdapter(db, {
  provider: 'sqlite',
  supportsJSON: false,
  transformOutput: (model, data) => {
    if (data?.metadata && typeof data.metadata === 'string') {
      try {
        data.metadata = JSON.parse(data.metadata);
      } catch {}
    }
    return data;
  },
});

Performance Issues

Slow Queries

Symptom: Thread listing takes several seconds

Diagnosis: Check for missing indexes:

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM threads
WHERE user_id = 'uuid'
ORDER BY created_at DESC
LIMIT 20;

-- Look for "Seq Scan" in output

Solution: Add appropriate indexes:

CREATE INDEX idx_threads_user_created
ON threads(user_id, created_at DESC);

Symptom: JSON queries are slow

Solution: Add GIN index for JSONB (PostgreSQL):

CREATE INDEX idx_threads_metadata
ON threads USING GIN (metadata);

Memory Issues

Symptom: Out of memory when processing large datasets

Solution: Use streaming and pagination:

// Instead of loading all at once
const allThreads = await adapter.findMany({
  model: 'thread',
  limit: 1000000, // Bad!
});

// Use cursor-based iteration
async function* iterateThreads() {
  let cursor = null;

  while (true) {
    const batch = await adapter.findMany({
      model: 'thread',
      where: cursor ? [{ field: 'id', value: cursor, operator: 'gt' }] : [],
      limit: 100,
      sortBy: { field: 'id', direction: 'asc' },
    });

    if (batch.length === 0) break;

    for (const thread of batch) {
      yield thread;
    }

    cursor = batch[batch.length - 1].id;
  }
}

// Use the iterator
for await (const thread of iterateThreads()) {
  // Process one at a time
}

Connection Issues

Connection Pool Exhaustion

Symptom: "too many connections" or "connection timeout"

Solution: Configure connection pool properly:

// PostgreSQL
const sql = postgres({
  max: 20, // Reduce if hitting database limits
  idle_timeout: 30,
  connect_timeout: 10,
});

// MySQL
const pool = mysql.createPool({
  connectionLimit: 20,
  waitForConnections: true,
  queueLimit: 0, // Set limit to prevent memory issues
});

Connection Drops

Symptom: "Connection terminated unexpectedly"

Solution: Implement retry logic:

class ResilientAdapter extends DrizzleAdapter {
  async executeWithRetry(operation: () => Promise<any>, retries = 3): Promise<any> {
    for (let i = 0; i < retries; i++) {
      try {
        return await operation();
      } catch (error) {
        if (i === retries - 1) throw error;

        if (this.isRetryableError(error)) {
          await this.delay(Math.pow(2, i) * 1000); // Exponential backoff
          continue;
        }

        throw error;
      }
    }
  }

  private isRetryableError(error: any): boolean {
    return (
      error.code === 'ECONNRESET' ||
      error.code === 'ETIMEDOUT' ||
      error.message.includes('connection')
    );
  }
}

Data Integrity Issues

Duplicate Key Violations

Error: "duplicate key value violates unique constraint"

Solution: Handle conflicts appropriately:

// PostgreSQL/MySQL - Insert or update
await db
  .insert(threads)
  .values(threadData)
  .onDuplicateKeyUpdate({
    set: {
      title: threadData.title,
      metadata: threadData.metadata,
      updatedAt: new Date(),
    },
  });

// PostgreSQL - Insert or ignore
await db.insert(threads).values(threadData).onConflictDoNothing();

Foreign Key Violations

Error: "violates foreign key constraint"

Solution: Ensure referenced records exist:

// Validate before insert
const userExists = await adapter.findOne({
  model: 'user',
  where: [{ field: 'id', value: threadData.userId }],
});

if (!userExists) {
  throw new Error('User not found');
}

// Or use transaction with proper order
await db.transaction(async (tx) => {
  // Create user first if needed
  await tx.insert(users).values(userData).onConflictDoNothing();

  // Then create thread
  await tx.insert(threads).values(threadData);
});

Debugging Tips

Enable Query Logging

// Development logging
const db = drizzle(connection, {
  logger: true, // Logs all queries
});

// Custom logger
const db = drizzle(connection, {
  logger: {
    logQuery(query, params) {
      console.log('Query:', query);
      console.log('Params:', params);
    },
  },
});

Inspect Generated SQL

// Get SQL without executing
const query = db.select().from(threads).where(eq(threads.userId, 'user-123')).toSQL();

console.log('SQL:', query.sql);
console.log('Params:', query.params);

Test Database Connection

async function testConnection() {
  try {
    // Simple query to test connection
    await db.execute(sql`SELECT 1`);
    console.log('Database connected successfully');
  } catch (error) {
    console.error('Database connection failed:', error);
  }
}

Environment-Specific Issues

Docker/Container Issues

Symptom: Cannot connect to database from container

Solution: Use proper host names:

// Instead of localhost
const connection = postgres({
  host: 'localhost', // Won't work from container
});

// Use container name or host.docker.internal
const connection = postgres({
  host: process.env.DB_HOST || 'postgres', // Container name
});

Timezone Issues

Symptom: Dates are off by several hours

Solution: Ensure consistent timezone handling:

// PostgreSQL - Set timezone
await db.execute(sql`SET timezone = 'UTC'`);

// MySQL - Connection config
const pool = mysql.createPool({
  timezone: '+00:00', // UTC
});

// Application-level handling
const adapter = new DrizzleAdapter(db, {
  transformInput: (model, data) => {
    // Ensure dates are UTC
    if (data.createdAt) {
      data.createdAt = new Date(data.createdAt.toISOString());
    }
    return data;
  },
});

Getting Help

Diagnostic Information

When reporting issues, include:

// Version information
console.log({
  node: process.version,
  threads: require('@pressw/threads/package.json').version,
  threadsDrizzle: require('@pressw/threads-drizzle/package.json').version,
  drizzle: require('drizzle-orm/package.json').version,
  database: adapter.provider,
});

// Configuration
console.log('Adapter config:', {
  provider: config.provider,
  tables: config.tables,
  fields: config.fields,
  capabilities: {
    supportsJSON: config.supportsJSON,
    supportsDates: config.supportsDates,
    supportsBooleans: config.supportsBooleans,
  },
});

// Test query
try {
  const result = await adapter.findOne({
    model: 'thread',
    where: [{ field: 'id', value: 'test-id' }],
  });
  console.log('Test query successful');
} catch (error) {
  console.error('Test query failed:', error);
}

Common Solutions Checklist

• Database connection is working
• Tables exist and have correct schema
• Required indexes are created
• Field mappings are configured
• Database user has necessary permissions
• Connection pool is properly sized
• JSON support is correctly configured
• Timezone settings are consistent

Next Steps

• Review performance guide for optimization
• Check examples for working implementations
• Join community forums for additional support