rembType-Safe SQL with Kysely
Supabase Edge Functions can connect directly to your Postgres database to execute SQL queries. Kysely is a type-safe and autocompletion-friendly typescript SQL query builder.
Combining Kysely with Deno Postgres gives you a convenient developer experience for interacting directly with your Postgres database.
Code#
Find the example on GitHub
Get your database connection credentials from the project's Connect panel and store them in an .env file:
1DB_HOSTNAME=2DB_PASSWORD=3DB_SSL_CERT="-----BEGIN CERTIFICATE-----4GET YOUR CERT FROM YOUR PROJECT DASHBOARD5-----END CERTIFICATE-----"
Create a DenoPostgresDriver.ts file to manage the connection to Postgres via deno-postgres:
1import {2 CompiledQuery,3 DatabaseConnection,4 Driver,5 PostgresCursorConstructor,6 QueryResult,7 TransactionSettings,8} from 'https://esm.sh/kysely@0.23.4'9import { freeze, isFunction } from 'https://esm.sh/kysely@0.23.4/dist/esm/util/object-utils.js'10import { extendStackTrace } from 'https://esm.sh/kysely@0.23.4/dist/esm/util/stack-trace-utils.js'11import { Pool, PoolClient } from 'https://deno.land/x/postgres@v0.17.0/mod.ts'1213export interface PostgresDialectConfig {14 pool: Pool | (() => Promise<Pool>)15 cursor?: PostgresCursorConstructor16 onCreateConnection?: (connection: DatabaseConnection) => Promise<void>17}1819const PRIVATE_RELEASE_METHOD = Symbol()2021export class PostgresDriver implements Driver {22 readonly #config: PostgresDialectConfig23 readonly #connections = new WeakMap<PoolClient, DatabaseConnection>()24 #pool?: Pool2526 constructor(config: PostgresDialectConfig) {27 this.#config = freeze({ ...config })28 }2930 async init(): Promise<void> {31 this.#pool = isFunction(this.#config.pool) ? await this.#config.pool() : this.#config.pool32 }3334 async acquireConnection(): Promise<DatabaseConnection> {35 const client = await this.#pool!.connect()36 let connection = this.#connections.get(client)3738 if (!connection) {39 connection = new PostgresConnection(client, {40 cursor: this.#config.cursor ?? null,41 })42 this.#connections.set(client, connection)4344 // The driver must take care of calling `onCreateConnection` when a new45 // connection is created. The `pg` module doesn't provide an async hook46 // for the connection creation. We need to call the method explicitly.47 if (this.#config?.onCreateConnection) {48 await this.#config.onCreateConnection(connection)49 }50 }5152 return connection53 }5455 async beginTransaction(56 connection: DatabaseConnection,57 settings: TransactionSettings58 ): Promise<void> {59 if (settings.isolationLevel) {60 await connection.executeQuery(61 CompiledQuery.raw(`start transaction isolation level ${settings.isolationLevel}`)62 )63 } else {64 await connection.executeQuery(CompiledQuery.raw('begin'))65 }66 }6768 async commitTransaction(connection: DatabaseConnection): Promise<void> {69 await connection.executeQuery(CompiledQuery.raw('commit'))70 }7172 async rollbackTransaction(connection: DatabaseConnection): Promise<void> {73 await connection.executeQuery(CompiledQuery.raw('rollback'))74 }7576 async releaseConnection(connection: PostgresConnection): Promise<void> {77 connection[PRIVATE_RELEASE_METHOD]()78 }7980 async destroy(): Promise<void> {81 if (this.#pool) {82 const pool = this.#pool83 this.#pool = undefined84 await pool.end()85 }86 }87}8889interface PostgresConnectionOptions {90 cursor: PostgresCursorConstructor | null91}9293class PostgresConnection implements DatabaseConnection {94 #client: PoolClient95 #options: PostgresConnectionOptions9697 constructor(client: PoolClient, options: PostgresConnectionOptions) {98 this.#client = client99 this.#options = options100 }101102 async executeQuery<O>(compiledQuery: CompiledQuery): Promise<QueryResult<O>> {103 try {104 const result = await this.#client.queryObject<O>(compiledQuery.sql, [105 ...compiledQuery.parameters,106 ])107108 if (109 result.command === 'INSERT' ||110 result.command === 'UPDATE' ||111 result.command === 'DELETE'112 ) {113 const numAffectedRows = BigInt(result.rowCount || 0)114115 return {116 numUpdatedOrDeletedRows: numAffectedRows,117 numAffectedRows,118 rows: result.rows ?? [],119 } as any120 }121122 return {123 rows: result.rows ?? [],124 }125 } catch (err) {126 throw extendStackTrace(err, new Error())127 }128 }129130 async *streamQuery<O>(131 _compiledQuery: CompiledQuery,132 chunkSize: number133 ): AsyncIterableIterator<QueryResult<O>> {134 if (!this.#options.cursor) {135 throw new Error(136 "'cursor' is not present in your postgres dialect config. It's required to make streaming work in postgres."137 )138 }139140 if (!Number.isInteger(chunkSize) || chunkSize <= 0) {141 throw new Error('chunkSize must be a positive integer')142 }143144 // stream not available145 return null146 }147148 [PRIVATE_RELEASE_METHOD](): void {149 this.#client.release()150 }151}
Create an index.ts file to execute a query on incoming requests:
1import { serve } from 'https://deno.land/std@0.175.0/http/server.ts'2import { Pool } from 'https://deno.land/x/postgres@v0.17.0/mod.ts'3import {4 Kysely,5 Generated,6 PostgresAdapter,7 PostgresIntrospector,8 PostgresQueryCompiler,9} from 'https://esm.sh/kysely@0.23.4'10import { PostgresDriver } from './DenoPostgresDriver.ts'1112console.log(`Function "kysely-postgres" up and running!`)1314interface AnimalTable {15 id: Generated<bigint>16 animal: string17 created_at: Date18}1920// Keys of this interface are table names.21interface Database {22 animals: AnimalTable23}2425// Create a database pool with one connection.26const pool = new Pool(27 {28 tls: { caCertificates: [Deno.env.get('DB_SSL_CERT')!] },29 database: 'postgres',30 hostname: Deno.env.get('DB_HOSTNAME'),31 user: 'postgres',32 port: 5432,33 password: Deno.env.get('DB_PASSWORD'),34 },35 136)3738// You'd create one of these when you start your app.39const db = new Kysely<Database>({40 dialect: {41 createAdapter() {42 return new PostgresAdapter()43 },44 createDriver() {45 return new PostgresDriver({ pool })46 },47 createIntrospector(db: Kysely<unknown>) {48 return new PostgresIntrospector(db)49 },50 createQueryCompiler() {51 return new PostgresQueryCompiler()52 },53 },54})5556serve(async (_req) => {57 try {58 // Run a query59 const animals = await db.selectFrom('animals').select(['id', 'animal', 'created_at']).execute()6061 // Neat, it's properly typed \o/62 console.log(animals[0].created_at.getFullYear())6364 // Encode the result as pretty printed JSON65 const body = JSON.stringify(66 animals,67 (key, value) => (typeof value === 'bigint' ? value.toString() : value),68 269 )7071 // Return the response with the correct content type header72 return new Response(body, {73 status: 200,74 headers: {75 'Content-Type': 'application/json; charset=utf-8',76 },77 })78 } catch (err) {79 console.error(err)80 return new Response(String(err?.message ?? err), { status: 500 })81 }82})