Edge Functions

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

_10
DB_HOSTNAME=
_10
DB_PASSWORD=
_10
DB_SSL_CERT="-----BEGIN CERTIFICATE-----
_10
GET 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

_151
import {
_151
CompiledQuery,
_151
DatabaseConnection,
_151
Driver,
_151
PostgresCursorConstructor,
_151
QueryResult,
_151
TransactionSettings,
_151
} from 'https://esm.sh/kysely@0.23.4'
_151
import { freeze, isFunction } from 'https://esm.sh/kysely@0.23.4/dist/esm/util/object-utils.js'
_151
import { extendStackTrace } from 'https://esm.sh/kysely@0.23.4/dist/esm/util/stack-trace-utils.js'
_151
import { Pool, PoolClient } from 'https://deno.land/x/postgres@v0.17.0/mod.ts'
_151
_151
export interface PostgresDialectConfig {
_151
pool: Pool | (() => Promise<Pool>)
_151
cursor?: PostgresCursorConstructor
_151
onCreateConnection?: (connection: DatabaseConnection) => Promise<void>
_151
}
_151
_151
const PRIVATE_RELEASE_METHOD = Symbol()
_151
_151
export 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
_151
interface PostgresConnectionOptions {
_151
cursor: PostgresCursorConstructor | null
_151
}
_151
_151
class 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

_82
import { serve } from 'https://deno.land/std@0.175.0/http/server.ts'
_82
import { Pool } from 'https://deno.land/x/postgres@v0.17.0/mod.ts'
_82
import {
_82
Kysely,
_82
Generated,
_82
PostgresAdapter,
_82
PostgresIntrospector,
_82
PostgresQueryCompiler,
_82
} from 'https://esm.sh/kysely@0.23.4'
_82
import { PostgresDriver } from './DenoPostgresDriver.ts'
_82
_82
console.log(`Function "kysely-postgres" up and running!`)
_82
_82
interface AnimalTable {
_82
id: Generated<bigint>
_82
animal: string
_82
created_at: Date
_82
}
_82
_82
// Keys of this interface are table names.
_82
interface Database {
_82
animals: AnimalTable
_82
}
_82
_82
// Create a database pool with one connection.
_82
const 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.
_82
const 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
_82
serve(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
})