Database Migrations purely with Kysely

[StillWorking]

I'm testing out Kysely personally to eventually recommend at my company who are about to create a new app. We don't plan on using any ORM just use Kysely. I have run into an issue running database migrations though and I wanted to see how other people were going about doing it. I have a runMigrations.ts file like so

import { Migrator, FileMigrationProvider } from 'kysely'
import { promises as fs } from 'fs'
import path from 'path'
import { db } from './db'

const migrator = new Migrator({
    db,
    provider: new FileMigrationProvider({
        fs,
        path,
        migrationFolder: 'backend/database/migrations'
    }),
})

migrator.migrateToLatest()

and this is going to create tables like

import { Kysely } from 'kysely'

export async function up(db: Kysely<any>): Promise<void> {
  // Create MuscleGroup table
  await db.schema
    .createTable('muscle_group')
    .addColumn('id', 'serial', (col) => col.primaryKey())
    .addColumn('name', 'varchar')
    .execute();

  // Create Exercise table
  await db.schema
    .createTable('exercise')
    .addColumn('id', 'serial', (col) => col.primaryKey())
    .addColumn('name', 'varchar')
    .addColumn('muscle_group_id', 'integer', (col) =>
      col.references('muscle_group.id')
    )
    .execute();
}

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

But since this is in typescript I have to run ts-node but that doesnt work because its using import statements and I cant add "type": "module" into the package.json

So do you guys just run migrations when the app is built? I'm usually used to just running a command in the console for it to migrate and go about my coding

[waptik]

You can use deno for that. Check this out. I built mine based on that and just need to run deno task migrate:* in my terminal and i'm good to go.

[koskimas]

Simply build using tsc and use node to run the migrations.

[koskimas]

Also if the issue is the await import statement inside FileMigrationProvider, you can create your own MigrationProvider that doesn't use await import. The interface you need to implement is very simple:

export interface MigrationProvider {
  /**
   * Returns all migrations, old and new.
   *
   * For example if you have your migrations in a folder as separate files,
   * you can use the {@link FileMigrationProvider} that implements this
   * method to return all migrations in a folder.
   *
   * The keys of the returned object are migration names and values are the
   * migrations. The order of the migrations is determined by the alphabetical
   * order of the migration names. The items in the object don't need to be
   * sorted, they are sorted by Kysely.
   */
  getMigrations(): Promise<Record<string, Migration>>
}

export interface Migration {
  up(db: Kysely<any>): Promise<void>

  /**
   * An optional down method.
   *
   * If you don't provide a down method, the migration is skipped when
   * migrating down.
   */
  down?(db: Kysely<any>): Promise<void>
}

You can find more info in the documentation and in the source code.

[StillWorking]

I have used tsc build before as well as I did again but I run into issues while compiling @koskimas @waptik

node_modules/kysely/dist/cjs/dialect/mysql/mysql-dialect.d.ts:39:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

39     #private;
       ~~~~~~~~

node_modules/kysely/dist/cjs/dialect/mysql/mysql-driver.d.ts:7:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

7     #private;
      ~~~~~~~~

node_modules/kysely/dist/cjs/dialect/mysql/mysql-driver.d.ts:42:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

42     #private;
       ~~~~~~~~

node_modules/kysely/dist/cjs/dialect/mysql/mysql-introspector.d.ts:4:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

4     #private;
      ~~~~~~~~

node_modules/kysely/dist/cjs/dialect/postgres/postgres-dialect.d.ts:39:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

39     #private;
       ~~~~~~~~

node_modules/kysely/dist/cjs/dialect/postgres/postgres-driver.d.ts:7:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

7     #private;
      ~~~~~~~~

node_modules/kysely/dist/cjs/dialect/postgres/postgres-driver.d.ts:45:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

45     #private;
       ~~~~~~~~

node_modules/kysely/dist/cjs/dialect/postgres/postgres-introspector.d.ts:4:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

4     #private;
      ~~~~~~~~

node_modules/kysely/dist/cjs/dialect/sqlite/sqlite-dialect.d.ts:32:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

32     #private;
       ~~~~~~~~

node_modules/kysely/dist/cjs/dialect/sqlite/sqlite-driver.d.ts:5:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

5     #private;
      ~~~~~~~~

node_modules/kysely/dist/cjs/dialect/sqlite/sqlite-introspector.d.ts:4:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

4     #private;
      ~~~~~~~~

node_modules/kysely/dist/cjs/driver/default-connection-provider.d.ts:5:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

5     #private;
      ~~~~~~~~

node_modules/kysely/dist/cjs/driver/single-connection-provider.d.ts:4:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

4     #private;
      ~~~~~~~~

node_modules/kysely/dist/cjs/dynamic/dynamic-reference-builder.d.ts:4:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

4     #private;
      ~~~~~~~~

node_modules/kysely/dist/cjs/kysely.d.ts:56:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

56     #private;
       ~~~~~~~~

node_modules/kysely/dist/cjs/kysely.d.ts:230:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

230     #private;
        ~~~~~~~~

node_modules/kysely/dist/cjs/kysely.d.ts:393:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

393     #private;
        ~~~~~~~~

node_modules/kysely/dist/cjs/kysely.d.ts:400:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

400     #private;
        ~~~~~~~~

node_modules/kysely/dist/cjs/migration/file-migration-provider.d.ts:19:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

19     #private;
       ~~~~~~~~

node_modules/kysely/dist/cjs/migration/migrator.d.ts:42:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

42     #private;
       ~~~~~~~~

node_modules/kysely/dist/cjs/operation-node/operation-node-transformer.d.ts:110:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

110     #private;
        ~~~~~~~~

node_modules/kysely/dist/cjs/operation-node/operation-node-visitor.d.ts:81:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

81     #private;
       ~~~~~~~~

node_modules/kysely/dist/cjs/plugin/camel-case/camel-case-plugin.d.ts:101:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

101     #private;
        ~~~~~~~~

node_modules/kysely/dist/cjs/plugin/deduplicate-joins/deduplicate-joins-plugin.d.ts:11:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

11     #private;
       ~~~~~~~~

node_modules/kysely/dist/cjs/plugin/with-schema/with-schema-plugin.d.ts:6:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

6     #private;
      ~~~~~~~~

node_modules/kysely/dist/cjs/query-builder/aggregate-function-builder.d.ts:9:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

9     #private;
      ~~~~~~~~

node_modules/kysely/dist/cjs/query-builder/aggregate-function-builder.d.ts:328:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

328     #private;
        ~~~~~~~~

node_modules/kysely/dist/cjs/query-builder/delete-query-builder.d.ts:26:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

26     #private;
       ~~~~~~~~

node_modules/kysely/dist/cjs/query-builder/delete-result.d.ts:2:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

2     #private;
      ~~~~~~~~

node_modules/kysely/dist/cjs/query-builder/expression-builder.d.ts:8:5 - error TS18028: Private identifiers are only available when targeting ECMAScript 2015 and higher.

8     #private;
      ~~~~~~~~

I made a bash script that runs if thats helpful to see whats up

#!/bin/bash

# Create a directory for the compiled files
mkdir -p dist

# Compile the TypeScript files and output them to the dist directory
npx tsc --outDir dist backend/database/runMigrations.ts

# Run the migrator.js script from the dist directory
node dist/backend/database/runMigrations.js

# Delete the dist directory and all of its contents
rm -rf dist

my tsconfig file

{
  "compilerOptions": {
    "target": "es5",
    "lib": ["dom", "dom.iterable", "esnext"],
    "allowJs": true,
    "skipLibCheck": true,
    "strict": true,
    "forceConsistentCasingInFileNames": true,
    "noEmit": true,
    "esModuleInterop": true,
    "module": "esnext",
    "moduleResolution": "node",
    "resolveJsonModule": true,
    "isolatedModules": true,
    "jsx": "preserve",
    "incremental": true,
    "paths": {
      "@/*": ["./*"]
    }
  },
  "include": ["next-env.d.ts", "**/*.ts", "**/*.tsx"],
  "exclude": ["node_modules"]
}

[koskimas]

The error you pasted tells you everything you need. You need to target a newer ecmascript version

[marbemac]

Can also leverage something like tsx to skip the build step altogether (https://github.com/esbuild-kit/tsx). Our migration script is as simple as tsx src/migrate.ts.

[varHarrie]

You can use deno for that. Check this out. I built mine based on that and just need to run deno task migrate:* in my terminal and i'm good to go.

Simplified code into:

import { join, resolve } from "std/path/mod.ts";
import { Migration, Migrator } from "kysely";

class FileMigrationProvider {
  #folder: string;

  constructor(folder: string) {
    this.#folder = folder;
  }

  async getMigrations(): Promise<Record<string, Migration>> {
    const migrations: Record<string, Migration> = {};
    const files = await Deno.readDir(this.#folder);

    for await (const file of files) {
      migrations[file.name] = await import(join(this.#folder, file.name));
    }

    return migrations;
  }
}

const migrator = new Migrator({
  db,
  provider: new FileMigrationProvider(resolve("./database/migrations")),
});

[edubacco]

Maybe it's useful for someone. I started my first typescript project, and I'm using kyselky. While compiling, I put compiled js files in the same folders of original typescript files. So when the FileMigrationProvider scan the folder to load all migration files, it load also original .ts files, throwing same errors found in this thread. Compiling to a different folder solved the problem

[igalklebanov]

Hwy 👋

Try kysely-ctl. Let me know how it went.