Deno + Kysely + Postgres

db.ts

import { PostgresDriver } from './PgDriver.ts'

import {
  ColumnType,
  Generated,
  Kysely,
  Selectable,
  PostgresAdapter,
  PostgresIntrospector,
  PostgresQueryCompiler
} from 'kysely'

import config from './config.ts'

interface UsersTable {
  id: Generated<number>
  display_name: string
  created_at: ColumnType<Date, string | undefined, never>
  updated_at: ColumnType<Date, string | undefined, never>
}

export type Users = Selectable<UsersTable>

interface DbSchema {
  users: UsersTable
}

export class Db {

  static #instance: Kysely<DbSchema>
  private constructor() {}

  public static getInstance(): Kysely<DbSchema> {
    if (!Db.#instance) Db.#instance = Db.#initDb()

    return Db.#instance
  }

  static #initDb() {
    return new Kysely<DbSchema>({
      dialect: {
        createAdapter() {
          return new PostgresAdapter()
        },
        createDriver() {
          return new PostgresDriver(config.db.uri)
        },
        createIntrospector(db) {
          return new PostgresIntrospector(db)
        },
        createQueryCompiler() {
          return new PostgresQueryCompiler()
        }
      }
    })
  }

}

migrate.ts

// Source: https://github.com/CodingGarden/fresh-spots/blob/main/app/db/migrate.ts
// Added Deno args

import {
  FileMigrationProvider,
  Migration,
  MigrationResult,
  Migrator,
} from 'kysely'
import db from './db.ts'

const log = (msg: string) => console.log('[Migrations]', msg)

class DenoFileMigrationProvider extends FileMigrationProvider {
  folder: string

  constructor() {
    super({
      fs: {
        readdir(path) {
          return Promise.resolve(
            [...Deno.readDirSync(path)].map((file) => file.name)
          )
        },
      },
      path: {
        join(...path) {
          return path.join('/')
        },
      },
      migrationFolder: './db/migrations',
    })

    this.folder = './db/migrations'
  }

  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(
        ['./migrations', file.name].join('/')
      )
    }

    return migrations
  }
}

const migrator = new Migrator({
  db,
  provider: new DenoFileMigrationProvider(),
})

const logMigrationResults = (results?: MigrationResult[], error?: Error) => {
  results?.forEach((it) => {
    if (it.status === 'Success') {
      log(`migration "${it.migrationName}" was executed successfully`)
    } else if (it.status === 'Error') {
      console.error(`failed to execute migration "${it.migrationName}"`)
    }
  })

  if (error) {
    log(`${(error as Error).message}`)
  }
}

// Handle args
// usage: deno run migrate.ts --up-full | --up or --down

switch (Deno.args[0]) {
  case '--up-full': {
    const { results, error } = await migrator.migrateToLatest()
    logMigrationResults(results, error as Error)
    break
  }
  case '--up': {
    const { results, error } = await migrator.migrateUp()
    logMigrationResults(results, error as Error)
    break
  }
  case '--down': {
    const { results, error } = await migrator.migrateDown()
    logMigrationResults(results, error as Error)
    break
  }
  default:
    log('Use --up-full, --up, or --down')
    break
}

PgDriver.ts

import { Client } from 'postgres'
import { CompiledQuery, DatabaseConnection, Driver, QueryResult, TransactionSettings } from 'kysely'

type QueryArguments = unknown[] | Record<string, unknown>


export class PostgresDriver implements Driver {

  readonly #connectionMutex = new ConnectionMutex()

  #client?: Client
  #connection?: DatabaseConnection

  db_conn_info: string // db_conn_info postgres uri. Change to postgres connection info

  constructor(conn_info: string) {
    this.db_conn_info = conn_info
  }

  init(): Promise<void> {
    this.#client = new Client(this.db_conn_info)
    this.#connection = new PgConnection(this.#client)
    return Promise.resolve()
  }

  async acquireConnection(): Promise<DatabaseConnection> {
    await this.#connectionMutex.lock()
    return this.#connection!
  }

  async beginTransaction(connection: DatabaseConnection, _settings: TransactionSettings): Promise<void> {
    await connection.executeQuery(CompiledQuery.raw('begin'))
  }

  async commitTransaction(connection: DatabaseConnection): Promise<void> {
    await connection.executeQuery(CompiledQuery.raw('commit'))
  }
  
  async rollbackTransaction(connection: DatabaseConnection): Promise<void> {
    await connection.executeQuery(CompiledQuery.raw('rollback'))
  }

  releaseConnection(): Promise<void> {
    this.#connectionMutex.unlock()
    return Promise.resolve()
  }

  destroy(): Promise<void> {
    this.#client?.end()
    return Promise.resolve()
  }
}

class PgConnection implements DatabaseConnection {
  readonly #db: Client

  constructor(c: Client) {
    this.#db = c
  }

  async executeQuery<R>(compiledQuery: CompiledQuery): Promise<QueryResult<R>> {
    const { sql, parameters } = compiledQuery

    const { rows } = await this.#db.queryObject(sql, parameters as QueryArguments )

    return Promise.resolve({
      rows: rows as []
    })
  }
}

class ConnectionMutex {
  #promise?: Promise<void>
  #resolve?: () => void

  async lock(): Promise<void> {
    while (this.#promise) {
      await this.#promise
    }

    this.#promise = new Promise(resolve => {
      this.#resolve = resolve
    })
  }

  unlock(): void {
    const resolve = this.#resolve

    this.#promise = undefined
    this.#resolve = undefined

    resolve?.()
  }
}