Utiliser Kysely avec ClickHouse Managed Postgres

DATABASE_URL="postgresql://postgres:<PASSWORD>@your-instance.pg.clickhouse.cloud:5432/guide_kysely?sslmode=verify-full&sslrootcert=ca-certificate.pem"

{
  "compilerOptions": {
    "target": "esnext",
    "module": "nodenext",
    "types": ["node"],
    "strict": true,
    "noEmit": true,
    "allowImportingTsExtensions": true,
    "erasableSyntaxOnly": true,
    "verbatimModuleSyntax": true,
    "skipLibCheck": true
  },
  "include": ["src", "migrations"]
}

import { Kysely, PostgresDialect, type Generated } from 'kysely'
import { Pool } from 'pg'

export interface TodoTable {
  id: Generated<number>
  title: string
  done: Generated<boolean>
  created_at: Generated<Date>
}

export interface Database {
  todo: TodoTable
}

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

import { type Kysely, sql } from 'kysely'

export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable('todo')
    .addColumn('id', 'integer', (col) => col.primaryKey().generatedAlwaysAsIdentity())
    .addColumn('title', 'text', (col) => col.notNull())
    .addColumn('done', 'boolean', (col) => col.notNull().defaultTo(false))
    .addColumn('created_at', 'timestamptz', (col) => col.notNull().defaultTo(sql`now()`))
    .execute()
}

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

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

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

const { error, results } = await migrator.migrateToLatest()

for (const result of results ?? []) {
  console.log(`${result.status}: ${result.migrationName}`)
}

await db.destroy()

if (error) {
  console.error('Migration failed:', error)
  process.exit(1)
}

import { createServer } from 'node:http'
import { json } from 'node:stream/consumers'
import { db } from './db.mts'

const port = Number(process.env.PORT ?? 3000)

const server = createServer(async (req, res) => {
  const { pathname } = new URL(req.url ?? '/', 'http://localhost')
  const id = Number(pathname.match(/^\/todos\/(\d+)$/)?.[1])

  try {
    let result: unknown

    if (req.method === 'GET' && pathname === '/todos') {
      result = await db.selectFrom('todo').selectAll().orderBy('id').execute()
    } else if (req.method === 'POST' && pathname === '/todos') {
      const { title } = (await json(req)) as { title: string }
      result = await db.insertInto('todo').values({ title }).returningAll().executeTakeFirstOrThrow()
    } else if (req.method === 'PATCH' && id) {
      const { done } = (await json(req)) as { done: boolean }
      result = await db.updateTable('todo').set({ done }).where('id', '=', id).returningAll().executeTakeFirst()
    } else if (req.method === 'DELETE' && id) {
      result = await db.deleteFrom('todo').where('id', '=', id).returningAll().executeTakeFirst()
    } else {
      res.writeHead(404).end()
      return
    }

    res.writeHead(result ? 200 : 404, { 'content-type': 'application/json' })
    res.end(JSON.stringify(result ?? { error: 'not found' }, null, 2))
  } catch (err) {
    console.error(err)
    res.writeHead(500).end()
  }
})

server.listen(port, () => console.log(`Listening on http://localhost:${port}/todos`))