Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save mikaelhadler/783c5487d018f964aa5b679ea4d93d47 to your computer and use it in GitHub Desktop.
Save mikaelhadler/783c5487d018f964aa5b679ea4d93d47 to your computer and use it in GitHub Desktop.
Kysely + Turso + Migrations

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.

{
"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
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
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
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
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()
)
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment