Type-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 your Supabase Dashboard and store them in an .env file:
.env
_10DB_HOSTNAME=_10DB_PASSWORD=_10DB_SSL_CERT="-----BEGIN CERTIFICATE-----_10GET YOUR CERT FROM YOUR PROJECT DASHBOARD_10-----END CERTIFICATE-----"
Create a DenoPostgresDriver.ts file to manage the connection to Postgres via deno-postgres:
DenoPostgresDriver.ts
_151import {_151 CompiledQuery,_151 DatabaseConnection,_151 Driver,_151 PostgresCursorConstructor,_151 QueryResult,_151 TransactionSettings,_151} from 'https://esm.sh/[email protected]'_151import { freeze, isFunction } from 'https://esm.sh/[email protected]/dist/esm/util/object-utils.js'_151import { extendStackTrace } from 'https://esm.sh/[email protected]/dist/esm/util/stack-trace-utils.js'_151import { Pool, PoolClient } from 'https://deno.land/x/[email protected]/mod.ts'_151_151export interface PostgresDialectConfig {_151 pool: Pool | (() => Promise<Pool>)_151 cursor?: PostgresCursorConstructor_151 onCreateConnection?: (connection: DatabaseConnection) => Promise<void>_151}_151_151const PRIVATE_RELEASE_METHOD = Symbol()_151_151export class PostgresDriver implements Driver {_151 readonly #config: PostgresDialectConfig_151 readonly #connections = new WeakMap<PoolClient, DatabaseConnection>()_151 #pool?: Pool_151_151 constructor(config: PostgresDialectConfig) {_151 this.#config = freeze({ ...config })_151 }_151_151 async init(): Promise<void> {_151 this.#pool = isFunction(this.#config.pool) ? await this.#config.pool() : this.#config.pool_151 }_151_151 async acquireConnection(): Promise<DatabaseConnection> {_151 const client = await this.#pool!.connect()_151 let connection = this.#connections.get(client)_151_151 if (!connection) {_151 connection = new PostgresConnection(client, {_151 cursor: this.#config.cursor ?? null,_151 })_151 this.#connections.set(client, connection)_151_151 // The driver must take care of calling `onCreateConnection` when a new_151 // connection is created. The `pg` module doesn't provide an async hook_151 // for the connection creation. We need to call the method explicitly._151 if (this.#config?.onCreateConnection) {_151 await this.#config.onCreateConnection(connection)_151 }_151 }_151_151 return connection_151 }_151_151 async beginTransaction(_151 connection: DatabaseConnection,_151 settings: TransactionSettings_151 ): Promise<void> {_151 if (settings.isolationLevel) {_151 await connection.executeQuery(_151 CompiledQuery.raw(`start transaction isolation level ${settings.isolationLevel}`)_151 )_151 } else {_151 await connection.executeQuery(CompiledQuery.raw('begin'))_151 }_151 }_151_151 async commitTransaction(connection: DatabaseConnection): Promise<void> {_151 await connection.executeQuery(CompiledQuery.raw('commit'))_151 }_151_151 async rollbackTransaction(connection: DatabaseConnection): Promise<void> {_151 await connection.executeQuery(CompiledQuery.raw('rollback'))_151 }_151_151 async releaseConnection(connection: PostgresConnection): Promise<void> {_151 connection[PRIVATE_RELEASE_METHOD]()_151 }_151_151 async destroy(): Promise<void> {_151 if (this.#pool) {_151 const pool = this.#pool_151 this.#pool = undefined_151 await pool.end()_151 }_151 }_151}_151_151interface PostgresConnectionOptions {_151 cursor: PostgresCursorConstructor | null_151}_151_151class PostgresConnection implements DatabaseConnection {_151 #client: PoolClient_151 #options: PostgresConnectionOptions_151_151 constructor(client: PoolClient, options: PostgresConnectionOptions) {_151 this.#client = client_151 this.#options = options_151 }_151_151 async executeQuery<O>(compiledQuery: CompiledQuery): Promise<QueryResult<O>> {_151 try {_151 const result = await this.#client.queryObject<O>(compiledQuery.sql, [_151 ...compiledQuery.parameters,_151 ])_151_151 if (_151 result.command === 'INSERT' ||_151 result.command === 'UPDATE' ||_151 result.command === 'DELETE'_151 ) {_151 const numAffectedRows = BigInt(result.rowCount || 0)_151_151 return {_151 numUpdatedOrDeletedRows: numAffectedRows,_151 numAffectedRows,_151 rows: result.rows ?? [],_151 } as any_151 }_151_151 return {_151 rows: result.rows ?? [],_151 }_151 } catch (err) {_151 throw extendStackTrace(err, new Error())_151 }_151 }_151_151 async *streamQuery<O>(_151 _compiledQuery: CompiledQuery,_151 chunkSize: number_151 ): AsyncIterableIterator<QueryResult<O>> {_151 if (!this.#options.cursor) {_151 throw new Error(_151 "'cursor' is not present in your postgres dialect config. It's required to make streaming work in postgres."_151 )_151 }_151_151 if (!Number.isInteger(chunkSize) || chunkSize <= 0) {_151 throw new Error('chunkSize must be a positive integer')_151 }_151_151 // stream not available_151 return null_151 }_151_151 [PRIVATE_RELEASE_METHOD](): void {_151 this.#client.release()_151 }_151}
Create an index.ts file to execute a query on incoming requests:
index.ts
_82import { serve } from 'https://deno.land/[email protected]/http/server.ts'_82import { Pool } from 'https://deno.land/x/[email protected]/mod.ts'_82import {_82 Kysely,_82 Generated,_82 PostgresAdapter,_82 PostgresIntrospector,_82 PostgresQueryCompiler,_82} from 'https://esm.sh/[email protected]'_82import { PostgresDriver } from './DenoPostgresDriver.ts'_82_82console.log(`Function "kysely-postgres" up and running!`)_82_82interface AnimalTable {_82 id: Generated<bigint>_82 animal: string_82 created_at: Date_82}_82_82// Keys of this interface are table names._82interface Database {_82 animals: AnimalTable_82}_82_82// Create a database pool with one connection._82const pool = new Pool(_82 {_82 tls: { caCertificates: [Deno.env.get('DB_SSL_CERT')!] },_82 database: 'postgres',_82 hostname: Deno.env.get('DB_HOSTNAME'),_82 user: 'postgres',_82 port: 5432,_82 password: Deno.env.get('DB_PASSWORD'),_82 },_82 1_82)_82_82// You'd create one of these when you start your app._82const db = new Kysely<Database>({_82 dialect: {_82 createAdapter() {_82 return new PostgresAdapter()_82 },_82 createDriver() {_82 return new PostgresDriver({ pool })_82 },_82 createIntrospector(db: Kysely<unknown>) {_82 return new PostgresIntrospector(db)_82 },_82 createQueryCompiler() {_82 return new PostgresQueryCompiler()_82 },_82 },_82})_82_82serve(async (_req) => {_82 try {_82 // Run a query_82 const animals = await db.selectFrom('animals').select(['id', 'animal', 'created_at']).execute()_82_82 // Neat, it's properly typed \o/_82 console.log(animals[0].created_at.getFullYear())_82_82 // Encode the result as pretty printed JSON_82 const body = JSON.stringify(_82 animals,_82 (key, value) => (typeof value === 'bigint' ? value.toString() : value),_82 2_82 )_82_82 // Return the response with the correct content type header_82 return new Response(body, {_82 status: 200,_82 headers: {_82 'Content-Type': 'application/json; charset=utf-8',_82 },_82 })_82 } catch (err) {_82 console.error(err)_82 return new Response(String(err?.message ?? err), { status: 500 })_82 }_82})