Fix: Kysely Not Working — Type Errors on Queries, Migration Failing, or Generated Types Not Matching Schema

The Problem

TypeScript shows a type error on a seemingly correct query:

const users = await db
  .selectFrom('users')
  .selectAll()
  .execute();
// Type error: Property 'users' does not exist on type 'Database'

Or a join produces unexpected type errors:

const result = await db
  .selectFrom('users')
  .innerJoin('posts', 'posts.user_id', 'users.id')
  .select(['users.name', 'posts.title'])
  .execute();
// result[0].name  — TypeScript doesn't know if name comes from users or posts

Or the migration runner can’t find migration files:

Error: Could not find any migrations at path: ./migrations

Or kysely-codegen generates types but they don’t match the actual database:

// Generated type has 'status' as string, but database uses an enum
// Resulting in type mismatches at runtime

Why This Happens

Kysely is fully type-driven — TypeScript types are the source of truth for query construction:

• Database interface must match your schema exactly — every table and column must be declared. Kysely doesn’t introspect the database at build time (unless you use kysely-codegen). Missing or wrong types cause TypeScript errors.
• Ambiguous column names require table qualification — after a join, columns from both tables are in scope. Selecting 'name' is ambiguous if both tables have a name column. You must prefix with the table name: 'users.name'.
• Migrations need a configured provider — Kysely’s migration system needs a FileMigrationProvider (or custom provider) pointing to the right directory and file pattern.
• Generated types reflect the database at generation time — if you add columns or change types after running kysely-codegen, the generated types become stale. Re-run codegen after schema changes.

Fix 1: Define the Database Interface

// database.ts — the type definition for your entire database
import { ColumnType, Generated, Insertable, Selectable, Updateable } from 'kysely';

// Table interfaces — define every table and column
export interface UsersTable {
  id: Generated<number>;          // Auto-increment primary key
  name: string;
  email: string;
  role: 'user' | 'admin';        // Union type for enums
  bio: string | null;            // Nullable column
  created_at: ColumnType<Date, string | undefined, never>;
  // ColumnType<SelectType, InsertType, UpdateType>
  // Date when selecting, string when inserting (auto-set), never on update
}

export interface PostsTable {
  id: Generated<number>;
  user_id: number;
  title: string;
  content: string;
  published: ColumnType<boolean, boolean | undefined, boolean>;
  published_at: Date | null;
  created_at: Generated<Date>;
}

export interface TagsTable {
  id: Generated<number>;
  name: string;
}

export interface PostTagsTable {
  post_id: number;
  tag_id: number;
}

// Combine into Database interface
export interface Database {
  users: UsersTable;
  posts: PostsTable;
  tags: TagsTable;
  post_tags: PostTagsTable;
}

// Generate TypeScript utility types from table interfaces
export type User = Selectable<UsersTable>;           // Select result type
export type NewUser = Insertable<UsersTable>;         // Insert type
export type UserUpdate = Updateable<UsersTable>;      // Update type

Create the Kysely instance:

// db.ts
import { Kysely, PostgresDialect } from 'kysely';
import { Pool } from 'pg';
import { Database } from './database';

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

export const db = new Kysely<Database>({
  dialect: new PostgresDialect({ pool }),
  // log: ['query', 'error'],  // Enable query logging
});

Available dialects:

// PostgreSQL
import { PostgresDialect } from 'kysely';
import { Pool } from 'pg';
new PostgresDialect({ pool: new Pool({ connectionString: '...' }) });

// MySQL
import { MysqlDialect } from 'kysely';
import { createPool } from 'mysql2';
new MysqlDialect({ pool: createPool({ uri: '...' }) });

// SQLite (better-sqlite3)
import { SqliteDialect } from 'kysely';
import Database from 'better-sqlite3';
new SqliteDialect({ database: new Database('./db.sqlite') });

// Cloudflare D1 (use kysely-d1)
import { D1Dialect } from 'kysely-d1';
new D1Dialect({ database: env.DB });

// LibSQL/Turso
import { LibsqlDialect } from '@libsql/kysely-libsql';
new LibsqlDialect({ url: '...', authToken: '...' });

Fix 2: Write Type-Safe Queries

import { db } from './db';
import type { NewUser, UserUpdate } from './database';

// SELECT — basic
const users = await db
  .selectFrom('users')
  .selectAll()
  .execute();
// users: Selectable<UsersTable>[]

// SELECT specific columns
const names = await db
  .selectFrom('users')
  .select(['id', 'name', 'email'])
  .execute();
// names: { id: number; name: string; email: string }[]

// WHERE clauses
const admins = await db
  .selectFrom('users')
  .selectAll()
  .where('role', '=', 'admin')
  .where('created_at', '>', new Date('2024-01-01'))
  .execute();

// WHERE with OR
const results = await db
  .selectFrom('users')
  .selectAll()
  .where((eb) => eb.or([
    eb('name', 'like', '%alice%'),
    eb('email', 'like', '%alice%'),
  ]))
  .execute();

// INSERT
const newUser: NewUser = { name: 'Alice', email: '[email protected]', role: 'user' };
const inserted = await db
  .insertInto('users')
  .values(newUser)
  .returningAll()   // PostgreSQL — returns inserted row
  .executeTakeFirstOrThrow();

// INSERT multiple
await db.insertInto('users').values([
  { name: 'Alice', email: '[email protected]', role: 'user' },
  { name: 'Bob', email: '[email protected]', role: 'user' },
]).execute();

// UPDATE
const update: UserUpdate = { name: 'Alice Smith' };
await db
  .updateTable('users')
  .set(update)
  .where('id', '=', userId)
  .execute();

// Or set individual fields
await db
  .updateTable('users')
  .set({ role: 'admin', bio: 'Updated bio' })
  .where('id', '=', userId)
  .execute();

// DELETE
await db
  .deleteFrom('users')
  .where('id', '=', userId)
  .execute();

// UPSERT (insert or update)
await db
  .insertInto('users')
  .values({ id: 1, name: 'Alice', email: '[email protected]', role: 'user' })
  .onConflict((oc) => oc
    .column('email')
    .doUpdateSet({ name: (eb) => eb.ref('excluded.name') })
  )
  .execute();

Fix 3: Fix Joins and Subqueries

// INNER JOIN — all matching rows
const usersWithPosts = await db
  .selectFrom('users')
  .innerJoin('posts', 'posts.user_id', 'users.id')
  // IMPORTANT: prefix columns with table name to avoid ambiguity
  .select(['users.id', 'users.name', 'posts.title', 'posts.published_at'])
  .where('posts.published', '=', true)
  .execute();
// TypeScript knows exact types for each column

// LEFT JOIN — all users, posts if they exist
const usersWithOptionalPosts = await db
  .selectFrom('users')
  .leftJoin('posts', 'posts.user_id', 'users.id')
  .select(['users.name', 'posts.title'])
  // After leftJoin, posts.title is string | null (might not exist)
  .execute();

// Multiple joins
const result = await db
  .selectFrom('posts')
  .innerJoin('users', 'users.id', 'posts.user_id')
  .innerJoin('post_tags', 'post_tags.post_id', 'posts.id')
  .innerJoin('tags', 'tags.id', 'post_tags.tag_id')
  .select(['posts.title', 'users.name as author', 'tags.name as tag'])
  .execute();

// Subquery in WHERE
const activeUsers = await db
  .selectFrom('users')
  .selectAll()
  .where('id', 'in', (qb) =>
    qb.selectFrom('posts')
      .select('user_id')
      .where('published', '=', true)
  )
  .execute();

// Subquery in SELECT (scalar)
const usersWithPostCount = await db
  .selectFrom('users')
  .select((eb) => [
    'users.id',
    'users.name',
    eb.selectFrom('posts')
      .whereRef('posts.user_id', '=', 'users.id')
      .select(eb.fn.countAll<number>().as('count'))
      .as('post_count'),
  ])
  .execute();
// result[0].post_count: number

// Aggregate functions
const stats = await db
  .selectFrom('posts')
  .select((eb) => [
    eb.fn.count<number>('id').as('total'),
    eb.fn.avg<number>('view_count').as('avg_views'),
    eb.fn.max('published_at').as('latest'),
  ])
  .where('published', '=', true)
  .executeTakeFirst();

Fix 4: Run Migrations

// migrations/2024_01_01_create_users.ts
import { Kysely, sql } from 'kysely';

export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable('users')
    .addColumn('id', 'serial', (col) => col.primaryKey())
    .addColumn('name', 'varchar(255)', (col) => col.notNull())
    .addColumn('email', 'varchar(255)', (col) => col.notNull().unique())
    .addColumn('role', 'varchar(50)', (col) => col.notNull().defaultTo('user'))
    .addColumn('bio', 'text')
    .addColumn('created_at', 'timestamp', (col) =>
      col.defaultTo(sql`now()`).notNull()
    )
    .execute();

  await db.schema
    .createIndex('users_email_idx')
    .on('users')
    .column('email')
    .execute();
}

export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.dropTable('users').execute();
}

// migrate.ts — run migrations
import { Kysely, Migrator, FileMigrationProvider } from 'kysely';
import { promises as fs } from 'fs';
import * as path from 'path';
import { db } from './db';

const migrator = new Migrator({
  db,
  provider: new FileMigrationProvider({
    fs,
    path,
    migrationFolder: path.join(__dirname, 'migrations'),
  }),
});

// Migrate to latest
async function migrateToLatest() {
  const { error, results } = await migrator.migrateToLatest();

  results?.forEach((it) => {
    if (it.status === 'Success') {
      console.log(`Migration "${it.migrationName}" executed successfully`);
    } else if (it.status === 'Error') {
      console.error(`Failed to execute migration "${it.migrationName}"`);
    }
  });

  if (error) {
    console.error('Failed to migrate:', error);
    process.exit(1);
  }

  await db.destroy();
}

// Rollback one step
async function rollback() {
  const { error, results } = await migrator.migrateDown();
  // ...
}

migrateToLatest();

// package.json
{
  "scripts": {
    "migrate": "tsx src/migrate.ts",
    "migrate:down": "tsx src/migrate-down.ts"
  }
}

Fix 5: Use kysely-codegen for Generated Types

Instead of writing Database manually, generate it from the actual database:

# Install
npm install --save-dev kysely-codegen

# Generate types from PostgreSQL
npx kysely-codegen --url postgresql://user:pass@localhost/mydb --out-file src/database.ts

# MySQL
npx kysely-codegen --url mysql://user:pass@localhost/mydb --out-file src/database.ts

# SQLite
npx kysely-codegen --url sqlite://./db.sqlite --out-file src/database.ts

# Add to package.json scripts
{
  "scripts": {
    "codegen": "kysely-codegen --url $DATABASE_URL --out-file src/database.ts"
  }
}

Configure codegen output:

# Camel case column names
npx kysely-codegen --url ... --camel-case --out-file src/database.ts

# Include only specific tables
npx kysely-codegen --url ... --include-pattern "users|posts|tags" --out-file src/database.ts

# Custom type for enums
npx kysely-codegen --url ... --out-file src/database.ts
# Then manually adjust the generated enum types if needed

Fix 6: Transaction and Error Handling

// Transactions
const result = await db.transaction().execute(async (trx) => {
  const user = await trx
    .insertInto('users')
    .values({ name: 'Alice', email: '[email protected]', role: 'user' })
    .returningAll()
    .executeTakeFirstOrThrow();

  await trx
    .insertInto('posts')
    .values({ user_id: user.id, title: 'First Post', content: 'Hello!', published: false })
    .execute();

  return user;
});
// If any query throws, the entire transaction is rolled back

// Set isolation level
await db.transaction().setIsolationLevel('serializable').execute(async (trx) => {
  // ...
});

// executeTakeFirstOrThrow — throws if no rows
try {
  const user = await db
    .selectFrom('users')
    .selectAll()
    .where('id', '=', userId)
    .executeTakeFirstOrThrow();
} catch (error) {
  if (error instanceof NoResultError) {
    throw new Error(`User ${userId} not found`);
  }
  throw error;
}

Still Not Working?

TypeScript error: “Type ‘string’ is not assignable to type ‘never’” — this usually means you’re trying to update a column defined with ColumnType<..., ..., never>. The third type parameter of ColumnType is the update type. never means the column can’t be updated (e.g., auto-managed created_at). Remove that field from your update object.

Query runs but returns wrong results despite correct SQL — Kysely builds parameterized queries. If you’re debugging, log the actual SQL with parameters:

const query = db.selectFrom('users').selectAll().where('id', '=', userId);
console.log(query.compile());
// { sql: 'select * from "users" where "id" = $1', parameters: [123] }

executeTakeFirst() returns undefined when rows exist — this happens if you’re selecting from the wrong table or have a WHERE clause that filters out all rows. Use execute() and check the array length, or use executeTakeFirstOrThrow() to get an error when no row is found. Also check that you’re awaiting the query — forgetting await returns the query builder, not the result.

For related database issues, see Fix: Drizzle ORM Not Working and Fix: Prisma Migration Failed.