Migrate from Knex to kastor

#From a builder to a typed client

Knex is a good query builder. It writes portable SQL, it has a migration runner, and it stays out of your way. What it does not do is know your schema: knex('users').where(...) returns any[] unless you declare an interface for every table and keep it in sync by hand. When you migrate from Knex to kastor, the schema becomes a TypeScript value, every result is typed from it, and loading related rows becomes an include instead of a join plus a regrouping loop.

You keep the database, the data and the driver. Knex migrations already built your tables, so kastor reads them, records a baseline, and takes over from there.

#Step 1: install kastor and pull the schema

npm install kastor-orm
npx kastor-orm init --db postgres
npx kastor db pull --out src/db/schema.ts

Compare a knex migration with the kastor table it turns into. The migration described changes; the kastor schema describes the result, which is what your code needs to know.

import type { Knex } from 'knex'

export async function up(knex: Knex) {
  await knex.schema.createTable('users', (t) => {
    t.increments('id')
    t.string('email', 255).notNullable().unique()
    t.string('name').nullable()
    t.timestamp('created_at').notNullable().defaultTo(knex.fn.now())
  })
  await knex.schema.createTable('posts', (t) => {
    t.increments('id')
    t.string('title', 200).notNullable()
    t.boolean('published').notNullable().defaultTo(false)
    t.integer('views').notNullable().defaultTo(0)
    t.integer('user_id').notNullable().references('users.id').onDelete('CASCADE').index()
  })
}

import { table, t, index, one, many } from 'kastor-orm'

export const users = table('users', {
  id: t.id(),
  email: t.string(255).unique(),
  name: t.string().nullable(),
  createdAt: t.datetime().map('created_at').defaultNow(),
})

export const posts = table('posts', {
  id: t.id(),
  title: t.string(200),
  published: t.boolean().default(false),
  views: t.int().default(0),
  userId: t.int().map('user_id').references(() => users.id, { onDelete: 'cascade' }),
}, { indexes: (p) => [index(p.userId)] })

export const schema = { users, posts }

export const relations = ({ users, posts }: typeof schema) => ({
  users: { posts: many(posts) },
  posts: { author: one(users, { fields: ['userId'], references: ['id'] }) },
})

Snake case columns keep their database names through .map(), while your code reads createdAt and userId. That replaces the wrapIdentifier and postProcessResponse hooks many Knex projects use for case conversion.

#Step 2: baseline instead of knex_migrations

Knex records applied files in knex_migrations. kastor does not replay them; it records the current state as its first migration:

npx kastor migrate baseline --name init
npx kastor migrate status

Run it once per environment. After that, schema changes are edits to src/db/schema.ts followed by npx kastor migrate dev --name add_slug, which diffs the schema against the database and writes a readable SQL file. If a change looks like a rename, kastor asks instead of dropping the column.

#Step 3: the client, next to the knex instance

Knex creates its own pool from the connection settings, so during the move you have two pools. Lower both max values so their sum stays under the database's connection limit.

import pg from 'pg'
import knexFactory from 'knex'
import { kastor } from 'kastor-orm'
import { postgres } from 'kastor-orm/postgres'
import { schema, relations } from './schema'

// Old code keeps using this until it moves.
export const knex = knexFactory({ client: 'pg', connection: process.env.DATABASE_URL, pool: { min: 0, max: 5 } })

export const db = kastor({
  adapter: postgres(new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 5 })),
  schema,
  relations,
})

#Step 4: rewrite builder chains

import { knex } from '../db'

interface PostRow { id: number; title: string; user_id: number; author_name: string | null }

export async function feed(userIds: number[]) {
  const rows: PostRow[] = await knex('posts')
    .join('users', 'users.id', 'posts.user_id')
    .whereIn('posts.user_id', userIds)
    .andWhere('posts.published', true)
    .select('posts.id', 'posts.title', 'posts.user_id', 'users.name as author_name')
    .orderBy('posts.id', 'desc')
    .limit(20)
  return rows
}

export const bump = (id: number) => knex('posts').where({ id }).increment('views', 1)

export const topAuthors = () =>
  knex('posts').select('user_id').count('* as n').groupBy('user_id').orderBy('n', 'desc').limit(5)

import { db } from '../db'

export const feed = (userIds: number[]) =>
  db.posts.findMany({
    where: { userId: { in: userIds }, published: true },
    select: { id: true, title: true, userId: true, author: { select: { name: true } } },
    orderBy: { id: 'desc' },
    take: 20,
  })

export const bump = (id: number) =>
  db.posts.update({ where: { id }, data: { views: { increment: 1 } } })

export const topAuthors = () =>
  db.posts.groupBy({ by: 'userId', _count: { _all: true }, orderBy: { _count: { _all: 'desc' } }, take: 5 })

The interface at the top of the Knex version disappears: feed() returns objects typed from the schema, with author nested as an object instead of flattened into author_name. Selecting a nested relation also never multiplies rows, which a join to a one to many relation does.

Reports with window functions, CTEs or vendor specific syntax are a good fit for db.$sql. You get the same parameter safety as Knex bindings and a typed result row:

import { db } from '../db'

export const signupsPerDay = (since: Date) =>
  db.$sql<{ day: string; signups: number }>`
    select date(created_at) as day, count(*)::int as signups
    from users where created_at >= ${since}
    group by 1 order by 1
  `

#Step 5: switch migrations in CI

Replace npx knex migrate:latest with npx kastor migrate deploy, and knex seed:run with npx kastor db seed. Remove knexfile and the knex package when nothing imports it.

#Pitfalls to check

• Two pools. Each library opens connections. Keep pool sizes small while both run, or route old code through fewer modules first.
• Return shapes. Knex .insert() returns ids or rows depending on the database. kastor create always returns the full row, typed, on every database.
• Booleans on MySQL and SQLite. Knex hands back 0 and 1 from tinyint and integer columns. kastor decodes t.boolean() to real booleans, so comparisons like row.published === 1 need updating.
• Dates. Columns declared t.datetime() come back as Date, t.date() as a YYYY-MM-DD string. Check code that relied on driver specific parsing.
• Case conversion hooks. If postProcessResponse converted keys to camelCase, remove it from the shared config before reusing settings, since kastor already maps names with .map().

The type safe SQL guide goes deeper into raw queries, and the Express guide shows the client in a typical Knex style API server.

#Questions people ask

#Related guides