Fix: Drizzle ORM Not Working — Schema Out of Sync, Relation Query Fails, or Migration Error

The Problem

Drizzle queries return empty results or fail despite data existing in the database:

const users = await db.select().from(usersTable);
// Returns [] — but table has rows

Or drizzle-kit push fails with a type mismatch:

Error: column "created_at" is of type timestamp without time zone but expression is of type text

Or a relation query throws:

const result = await db.query.users.findMany({
  with: { posts: true }
});
// Error: Cannot read properties of undefined (reading 'posts')

Or a migration fails partway through and leaves the schema inconsistent:

Error: relation "users" already exists

Why This Happens

Drizzle ORM has a strict relationship between TypeScript schema definitions and the actual database:

• Schema definition is the source of truth — Drizzle doesn’t auto-discover your database schema. If your TypeScript schema doesn’t match the actual table, queries fail or return wrong types silently.
• Relations must be explicitly defined — unlike Prisma which auto-generates relations, Drizzle requires you to define relations() separately from the table definition. Without it, with: { ... } queries fail.
• drizzle-kit push vs drizzle-kit migrate — push applies schema changes directly (good for development), migrate runs generated SQL migration files (required for production). Mixing them can cause conflicts.
• Table name in schema must match the actual database table — pgTable('users', ...) creates a schema bound to the users table. If the table is named user in the database, all queries silently target the wrong table.

Fix 1: Define Schema Correctly

The schema file is the foundation — every column must match the actual database:

// db/schema.ts — PostgreSQL example
import {
  pgTable, serial, text, varchar, integer,
  boolean, timestamp, uuid, pgEnum
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

// Enum must exist in PostgreSQL too
export const roleEnum = pgEnum('role', ['user', 'admin', 'moderator']);

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: varchar('name', { length: 255 }).notNull(),
  email: text('email').notNull().unique(),
  role: roleEnum('role').default('user').notNull(),
  isActive: boolean('is_active').default(true).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content'),
  authorId: uuid('author_id').notNull().references(() => users.id, {
    onDelete: 'cascade',
  }),
  publishedAt: timestamp('published_at'),
});

// Relations — required for .query API with `with`
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

// MySQL example
import { mysqlTable, int, varchar } from 'drizzle-orm/mysql-core';
export const mysqlUsers = mysqlTable('users', {
  id: int('id').primaryKey().autoincrement(),
  name: varchar('name', { length: 255 }).notNull(),
});

Fix 2: Configure Drizzle Client Correctly

Connect to the database with the right driver and pass your schema:

// db/index.ts — PostgreSQL with node-postgres
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from './schema';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 30000,
});

export const db = drizzle(pool, { schema });
// 'schema' is required for the .query API (relations + type inference)

// PostgreSQL with postgres.js (alternative driver)
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';

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

// SQLite with better-sqlite3
import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';
import * as schema from './schema';

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

// MySQL
import { drizzle } from 'drizzle-orm/mysql2';
import mysql from 'mysql2/promise';
import * as schema from './schema';

const connection = await mysql.createConnection(process.env.DATABASE_URL!);
export const db = drizzle(connection, { schema });

drizzle.config.ts — required for migrations:

import type { Config } from 'drizzle-kit';

export default {
  schema: './db/schema.ts',
  out: './drizzle',          // Where migration files are stored
  dialect: 'postgresql',     // 'postgresql' | 'mysql' | 'sqlite'
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
} satisfies Config;

Fix 3: Use Relations with the Query API

The .query API (with with) requires both schema relations and passing the schema to drizzle():

// WRONG — schema not passed to drizzle()
const db = drizzle(pool);  // No schema!
const result = await db.query.users.findMany({ with: { posts: true } });
// Error: Cannot read properties of undefined

// CORRECT — pass schema to drizzle()
import * as schema from './schema';
const db = drizzle(pool, { schema });

// Now .query API works
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: true,            // Include all posts
  },
});

// Select specific columns
const usersWithPostTitles = await db.query.users.findMany({
  columns: {
    id: true,
    name: true,
    email: true,
    // password: false — exclude sensitive fields
  },
  with: {
    posts: {
      columns: { id: true, title: true },
      where: (posts, { isNull }) => isNull(posts.publishedAt),  // Filter
      orderBy: (posts, { desc }) => [desc(posts.id)],
      limit: 5,
    },
  },
  where: (users, { eq }) => eq(users.isActive, true),
  orderBy: (users, { asc }) => [asc(users.name)],
  limit: 20,
  offset: 0,
});

// Nested relations
const result = await db.query.posts.findFirst({
  with: {
    author: {
      with: {
        // posts: true  ← Don't nest back — creates circular data
      },
      columns: { name: true, email: true },
    },
  },
  where: (posts, { eq }) => eq(posts.id, 1),
});

Fix 4: Run Migrations Correctly

Use drizzle-kit push for development, drizzle-kit migrate for production:

# Generate migration files from schema changes
npx drizzle-kit generate

# Apply pending migrations (production — uses migration files)
npx drizzle-kit migrate

# Push schema directly to database (development only — no migration files)
npx drizzle-kit push

# Inspect the current database schema
npx drizzle-kit introspect

# View migration status
npx drizzle-kit studio  # Opens browser UI

Programmatic migrations (run at app startup):

// db/migrate.ts
import { drizzle } from 'drizzle-orm/node-postgres';
import { migrate } from 'drizzle-orm/node-postgres/migrator';
import { Pool } from 'pg';

async function runMigrations() {
  const pool = new Pool({ connectionString: process.env.DATABASE_URL });
  const db = drizzle(pool);

  console.log('Running migrations...');
  await migrate(db, { migrationsFolder: './drizzle' });
  console.log('Migrations complete');

  await pool.end();
}

runMigrations().catch(console.error);

Handle migration conflicts:

# If "relation already exists" — the migration was partially applied
# Option 1: Manually mark migration as applied
# Insert into drizzle migration table:
INSERT INTO drizzle.__drizzle_migrations (hash, created_at)
VALUES ('your_migration_hash', NOW());

# Option 2: Drop and recreate (development only)
npx drizzle-kit push --force

# Option 3: Generate a new migration to fix the state
# Modify schema to reflect current DB state, then generate
npx drizzle-kit generate

Fix 5: Common Query Patterns

import { eq, and, or, like, gte, lte, inArray, isNull, sql } from 'drizzle-orm';

// WHERE conditions
const activeAdmins = await db.select()
  .from(users)
  .where(
    and(
      eq(users.isActive, true),
      eq(users.role, 'admin')
    )
  );

// OR condition
const results = await db.select()
  .from(users)
  .where(
    or(
      like(users.email, '%@company.com'),
      eq(users.role, 'admin')
    )
  );

// Date range
const recentPosts = await db.select()
  .from(posts)
  .where(
    gte(posts.publishedAt, new Date('2024-01-01'))
  );

// IN clause
const specificUsers = await db.select()
  .from(users)
  .where(inArray(users.id, ['uuid-1', 'uuid-2', 'uuid-3']));

// JOIN
const postsWithAuthors = await db.select({
  postId: posts.id,
  title: posts.title,
  authorName: users.name,
}).from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .where(isNull(posts.publishedAt));

// Aggregation
const postCounts = await db.select({
  userId: users.id,
  userName: users.name,
  postCount: sql<number>`count(${posts.id})::int`,
}).from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))
  .groupBy(users.id, users.name)
  .having(sql`count(${posts.id}) > 0`);

// Insert and return
const [newUser] = await db.insert(users)
  .values({ name: 'Alice', email: '[email protected]' })
  .returning();  // Returns the inserted row with generated id, timestamps

// Update
const [updated] = await db.update(users)
  .set({ name: 'Alicia', updatedAt: new Date() })
  .where(eq(users.id, 'user-uuid'))
  .returning();

// Upsert (INSERT ON CONFLICT)
await db.insert(users)
  .values({ id: 'user-uuid', name: 'Alice', email: '[email protected]' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: sql`excluded.name`, updatedAt: new Date() },
  });

Fix 6: Transactions

Group related operations in a transaction to ensure atomicity:

// Simple transaction
const result = await db.transaction(async (tx) => {
  const [order] = await tx.insert(orders)
    .values({ userId: 'user-1', total: 99.99 })
    .returning();

  await tx.insert(orderItems).values([
    { orderId: order.id, productId: 'prod-1', quantity: 2 },
    { orderId: order.id, productId: 'prod-2', quantity: 1 },
  ]);

  await tx.update(inventory)
    .set({ stock: sql`${inventory.stock} - 2` })
    .where(eq(inventory.productId, 'prod-1'));

  return order;
});

// Transaction with rollback on error
try {
  await db.transaction(async (tx) => {
    const [sender] = await tx.select().from(accounts)
      .where(eq(accounts.id, fromId))
      .for('update');  // Row-level lock

    if (sender.balance < amount) {
      tx.rollback();  // Explicit rollback — throws TxRollbackError
    }

    await tx.update(accounts)
      .set({ balance: sql`${accounts.balance} - ${amount}` })
      .where(eq(accounts.id, fromId));

    await tx.update(accounts)
      .set({ balance: sql`${accounts.balance} + ${amount}` })
      .where(eq(accounts.id, toId));
  });
} catch (e) {
  if (e instanceof TransactionRollbackError) {
    console.log('Insufficient funds');
  } else {
    throw e;
  }
}

Still Not Working?

Column type mismatch between schema and database — if the TypeScript schema says integer but the database column is bigint, queries may return wrong types or fail. Use drizzle-kit introspect to generate a schema from your existing database, then compare it to your hand-written schema. This is the most reliable way to ensure they match.

Timestamps returning strings instead of Date objects — by default, timestamp in Drizzle returns JavaScript Date objects, but the pg driver may return strings depending on the types configuration. Ensure you’re using the correct import (timestamp() from drizzle-orm/pg-core) and check pg’s type parsers. Adding .withTimezone() changes the behavior:

createdAt: timestamp('created_at', { withTimezone: true, mode: 'date' }).defaultNow(),

drizzle-kit push doesn’t detect changes — Drizzle kit compares your TypeScript schema to the database schema. If you’ve manually altered the database table outside of Drizzle, the snapshot may be out of sync. Delete the drizzle/meta/_journal.json and snapshot files, then run drizzle-kit push again (development only).

For related database issues, see Fix: Prisma Migration Failed and Fix: PostgreSQL Relation Does Not Exist.