Prisma-like Migrations and type safety with Kysely

README.md

This was originally written for a Remix app, but you could adapt it to work for any SSR framework.

Check the docs for these packages to learn how to use these snippets

https://www.npmjs.com/package/kysely-migration-cli https://www.npmjs.com/package/kysely-codegen https://www.npmjs.com/package/kysely#migrations

It's configured to use MySQL on Planetscale, but you could adjust it to use Postgres or SQLite if you want.

Basic usage:

npm run db:migrate create migrationName

Then write your migration in the file based on Kysely's recommendations.

npm run db:migrate up

If you need to go back and adjust your migration, run

npm run db:migrate down

And then make sure you run this to get your Type definitions up to date

npm run db:build

You might need to open the kysely-codegen/DB file to get VSCode to recognize the changes. Or you could manually trigger the types to refresh.

db.ts

import { env } from "./env.server";
import { Kysely } from "kysely";
import { PlanetScaleDialect } from "kysely-planetscale";
import type { DB as Database } from "kysely-codegen";

let db: Kysely<Database>;

declare global {
  var __db__: Kysely<Database>;
}

// this is needed because in development we don't want to restart
// the server with every change, but we want to make sure we don't
// create a new connection to the DB with every change either.
// in production we'll have a single connection to the DB.
if (process.env.NODE_ENV === "production") {
  db = new Kysely<Database>({
    dialect: new PlanetScaleDialect({
      host: env.PLANETSCALE_DB_HOST,
      username: env.PLANETSCALE_DB_USERNAME,
      password: env.PLANETSCALE_DB_PASSWORD,
    }),
  });
} else {
  if (!global.__db__) {
    global.__db__ = db = new Kysely<Database>({
      dialect: new PlanetScaleDialect({
        host: env.PLANETSCALE_DB_HOST,
        username: env.PLANETSCALE_DB_USERNAME,
        password: env.PLANETSCALE_DB_PASSWORD,
      }),
    });
  }
  db = global.__db__;
}

export { db };

migration.ts

import { run } from "kysely-migration-cli";
import { Migrator, FileMigrationProvider } from "kysely";
import path from "path";
import fs from "fs/promises";

(async () => {
  const { config } = await import("dotenv");
  config();
  const { db } = await import("~/lib/db.server");
  const migrator = new Migrator({
    db,
    provider: new FileMigrationProvider({
      fs,
      path,
      migrationFolder: path.resolve("./app/db/migrations"),
    }),
  });

  run(db, migrator, path.resolve("./app/db/migrations"));
})();

package.json

{
  "scripts": {
    "db:build": "kysely-codegen",
    "db:migrate": "npx tsx ./app/db/migrate.ts",
    "db:setup": "npm run db:migrate up && npm run db:build",
  },
  "dependencies": {
    "@planetscale/database": "^1.7.0",
    "kysely": "^0.24.2",
    "kysely-migration-cli": "^0.1.1",
    "kysely-planetscale": "^1.3.0",
    "mysql2": "^2",
  },
  "devDependencies": {
    "kysely-codegen": "^0.10.0",
  },
}