Kysely + Turso + Migrations

readme.md

Boilerplate to use kysely + Turso db (Sqlite) + Migrations

For this example I'm using the dir structure

src/lib/db/
  migrations/
    20240612T063030-creating_your_table.ts
    20240613T063030-another_migration.ts
  index.ts
  migrate.ts

Feel free to modify to your needs.

---

ENV

TURSO_DATABASE_URL=libsql://........turso.io
TURSO_AUTH_TOKEN=......

DATABASE_URL=libsql://YOUR_TOKEN_HERE.@HOST HERE

• Create required files listed bellow following the correct directory structure.
  • migrate.ts
  • index.ts

Migrations

pnpm db:migrate:up // Perform all new migrations
pnpm db:migrate:down // Rollback to previous migration
pnpm db:migrate:create // Create a new blank migration file
pnpm db:generate:types // Generate the types you can use on your product

From here you should be able to perform queries using kysely.

const response = await db
  .selectFrom('YOUR_TABLE')
  .select(['id', 'OTHER_COLUMN'])
  .where('id', '=', id)
  .executeTakeFirst()

This a simple example of how I'm running my projects using sqlite integrated with turso.

If you have a nodejs/bun project using postgres, have a look on this boilerplate that I also use on my API projects.

kysely + turso + migrations (sqlite)

{
  "scripts": {
    // ...
    "db:migrate:up": "bun run -r dotenv/config ./src/lib/db/migrate latest",
    "db:migrate:down": "bun run -r dotenv/config ./src/lib/db/migrate down",
    "db:migrate:create": "bun run -r dotenv/config ./src/lib/db/migrate create initial",
    "db:generate:types": "bunx kysely-codegen --out-file=src/lib/db/schema/Database.ts",
  },
  "dependencies": {
    "kysely": "^0.27.3",
    "kysely-migration-cli": "^0.4.0",
    "@libsql/kysely-libsql": "^0.3.0",
    "dotenv": "^16.4.5",
    // ...
  }
  "devDependencies": {
   // ...
    "kysely-codegen": "^0.15.0",  
  }
}

src-lib-db-index.ts

// src/lib/db/index.ts
import { Kysely } from 'kysely'
import { LibsqlDialect } from '@libsql/kysely-libsql'

import { DB } from './schema/Database'

export const db = new Kysely<DB>({
  dialect: new LibsqlDialect({
    url: process.env.TURSO_DATABASE_URL,      // You can get these on turso website.
    authToken: process.env.TURSO_AUTH_TOKEN
  })
})

src-lib-db-migrate.ts

// src/lib/db/migrate.ts
import { promises as fs } from 'fs'
import { Migrator, FileMigrationProvider } from 'kysely'
import { run } from 'kysely-migration-cli'
import path from 'path'

import { db } from '.'

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

run(db, migrator, './src/lib/db/migrations')

src-lib-db-migrations-20240612T063030-creating_your_table.ts

// src/lib/db/migrations/20240612T063030-creating_your_table.ts

import { Kysely, sql } from 'kysely'

import { withTimestampsSqlite } from '../utils'

export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable('your_table')
    .addColumn('id', 'varchar(100)', col => col.primaryKey().notNull())
    .addColumn('name', 'varchar(100)', col => col.unique().notNull())
    // .$call(withTimestampsSqlite)
    .execute()
}

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

src-lib-db-utils.ts

// src/lib/db/utils.ts

import { RawBuilder, sql } from 'kysely'

export const withTimestampsSqlite = (qb: any) => {
  return qb
    .addColumn('created_at', 'text', (col: any) =>
      col.defaultTo(sql`CURRENT_TIMESTAMP`).notNull()
    )
    .addColumn('updated_at', 'text', (col: any) =>
      col.defaultTo(sql`CURRENT_TIMESTAMP`).notNull()
    )
}