kysely

// db/types.ts — Database type definitions
import type { Generated, Insertable, Selectable, Updateable } from 'kysely'

export interface Database {
  users: UsersTable
  posts: PostsTable
}

interface UsersTable {
  id: Generated<number>
  name: string
  email: string
  created_at: Generated<Date>   // node-postgres returns timestamps as Date
}

interface PostsTable {
  id: Generated<number>
  title: string
  body: string
  author_id: number
  published: boolean
  created_at: Generated<Date>
}

// Helper types: Generated columns are optional on insert and update.
// Use ColumnType<Select, Insert, Update> for a column that differs per operation,
// e.g. ColumnType<Date, string | undefined, never> for a created_at nobody may change.
type NewUser = Insertable<UsersTable>
type UserUpdate = Updateable<UsersTable>
type User = Selectable<UsersTable>

// db/queries.ts — Type-safe SQL queries
import { Kysely, PostgresDialect, sql } from 'kysely'
import { Pool } from 'pg'
import type { Database } from './types'

const db = new Kysely<Database>({
  dialect: new PostgresDialect({ pool: new Pool({ connectionString: process.env.DATABASE_URL }) }),
})

// Select with joins — fully typed result
const postsWithAuthor = await db
  .selectFrom('posts')
  .innerJoin('users', 'users.id', 'posts.author_id')
  .select(['posts.id', 'posts.title', 'users.name as author_name'])
  .where('posts.published', '=', true)
  .orderBy('posts.created_at', 'desc')
  .limit(20)
  .execute()
// postsWithAuthor is { id: number, title: string, author_name: string }[]

// Subquery
const activeAuthors = await db
  .selectFrom('users')
  .select(['users.name', 'users.email'])
  .where('users.id', 'in',
    db.selectFrom('posts')
      .select('posts.author_id')
      .where('posts.published', '=', true)
      .groupBy('posts.author_id')
  )
  .execute()

// Insert
const newUser = await db
  .insertInto('users')
  .values({ name: 'Alice Moreau', email: 'alice.moreau@northwind.io' })
  .returningAll()
  .executeTakeFirstOrThrow()

// Transaction
await db.transaction().execute(async (trx) => {
  const user = await trx.insertInto('users')
    .values({ name: 'Bob Okafor', email: 'bob.okafor@northwind.io' })
    .returningAll()
    .executeTakeFirstOrThrow()

  await trx.insertInto('posts')
    .values({ title: 'First Post', body: 'Hello!', author_id: user.id, published: true })
    .execute()
})   // commits when the callback resolves, rolls back when it throws

// Update and delete: always add a where(); without one every row changes
await db.updateTable('users').set({ name: 'Alice M.' }).where('id', '=', newUser.id).execute()
await db.deleteFrom('posts').where('author_id', '=', newUser.id).execute()

// Count: PostgreSQL returns bigint as a string, so convert it
const { total } = await db.selectFrom('posts')
  .select((eb) => eb.fn.countAll<string>().as('total'))
  .executeTakeFirstOrThrow()
console.log(Number(total))

// migrate.ts — run with: npx tsx migrate.ts
import * as path from 'node:path'
import { promises as fs } from 'node:fs'
import { Migrator, FileMigrationProvider } from 'kysely/migration'

const migrator = new Migrator({
  db,   // the Kysely instance from Step 2
  provider: new FileMigrationProvider({ fs, path, migrationFolder: path.join(process.cwd(), 'migrations') }),
})
const { error, results } = await migrator.migrateToLatest()
results?.forEach((r) => console.log(`${r.status}: ${r.migrationName}`))
if (error) { console.error(error); process.exit(1) }
await db.destroy()

// migrations/001_create_users.ts
import { Kysely, sql } from 'kysely'

export async function up(db: Kysely<any>) {
  await db.schema
    .createTable('users')
    .addColumn('id', 'serial', (col) => col.primaryKey())
    .addColumn('name', 'varchar(255)', (col) => col.notNull())
    .addColumn('email', 'varchar(255)', (col) => col.notNull().unique())
    .addColumn('created_at', 'timestamp', (col) => col.defaultTo(sql`now()`).notNull())
    .execute()
}

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