Edge Functions

Connecting directly to Postgres

Connecting to Postgres from Edge Functions.


Connect to your Postgres database from an Edge Function by using the supabase-js client. You can also use other Postgres clients like Deno Postgres

Using supabase-js

The supabase-js client is a great option for connecting to your Supabase database since it handles authorization with Row Level Security, and it automatically formats your response as JSON.

index.ts

_24
import { createClient } from 'https://esm.sh/@supabase/supabase-js'
_24
_24
Deno.serve(async (_req) => {
_24
try {
_24
const supabase = createClient(
_24
Deno.env.get('SUPABASE_URL') ?? '',
_24
Deno.env.get('SUPABASE_ANON_KEY') ?? '',
_24
{ global: { headers: { Authorization: req.headers.get('Authorization')! } } }
_24
)
_24
_24
const { data, error } = await supabase.from('countries').select('*')
_24
_24
if (error) {
_24
throw error
_24
}
_24
_24
return new Response(JSON.stringify({ data }), {
_24
headers: { 'Content-Type': 'application/json' },
_24
status: 200,
_24
})
_24
} catch (err) {
_24
return new Response(String(err?.message ?? err), { status: 500 })
_24
}
_24
})

Using a Postgres client

Because Edge Functions are a server-side technology, it's safe to connect directly to your database using any popular Postgres client. This means you can run raw SQL from your Edge Functions.

Here is how you can connect to the database using Deno Postgres driver and run raw SQL.

Check out the full example.

index.ts

_39
import * as postgres from 'https://deno.land/x/postgres@v0.17.0/mod.ts'
_39
_39
// Get the connection string from the environment variable "SUPABASE_DB_URL"
_39
const databaseUrl = Deno.env.get('SUPABASE_DB_URL')!
_39
_39
// Create a database pool with three connections that are lazily established
_39
const pool = new postgres.Pool(databaseUrl, 3, true)
_39
_39
Deno.serve(async (_req) => {
_39
try {
_39
// Grab a connection from the pool
_39
const connection = await pool.connect()
_39
_39
try {
_39
// Run a query
_39
const result = await connection.queryObject`SELECT * FROM animals`
_39
const animals = result.rows // [{ id: 1, name: "Lion" }, ...]
_39
_39
// Encode the result as pretty printed JSON
_39
const body = JSON.stringify(
_39
animals,
_39
(key, value) => (typeof value === 'bigint' ? value.toString() : value),
_39
2
_39
)
_39
_39
// Return the response with the correct content type header
_39
return new Response(body, {
_39
status: 200,
_39
headers: { 'Content-Type': 'application/json; charset=utf-8' },
_39
})
_39
} finally {
_39
// Release the connection back into the pool
_39
connection.release()
_39
}
_39
} catch (err) {
_39
console.error(err)
_39
return new Response(String(err?.message ?? err), { status: 500 })
_39
}
_39
})

Using Drizzle

You can use Drizzle together with Postgres.js. Both can be loaded directly from npm:

supabase/functions/import_map.json

_10
{
_10
"imports": {
_10
"drizzle-orm": "npm:drizzle-orm@0.29.1",
_10
"drizzle-orm/": "npm:/drizzle-orm@0.29.1/",
_10
"postgres": "npm:postgres@3.4.3"
_10
}
_10
}

supabase/functions/drizzle/index.ts

_14
import { drizzle } from 'drizzle-orm/postgres-js'
_14
import postgres from 'postgres'
_14
import { countries } from '../_shared/schema.ts'
_14
_14
const connectionString = Deno.env.get('SUPABASE_DB_URL')!
_14
_14
Deno.serve(async (_req) => {
_14
// Disable prefetch as it is not supported for "Transaction" pool mode
_14
const client = postgres(connectionString, { prepare: false })
_14
const db = drizzle(client)
_14
const allCountries = await db.select().from(countries)
_14
_14
return Response.json(allCountries)
_14
})

You can find the full example on GitHub.

SSL connections

Deployed edge functions are pre-configured to use SSL for connections to the Supabase database. You don't need to add any extra configurations.

If you want to use SSL connections during local development, follow these steps:


_10
SSL_CERT_FILE=/path/to/cert.crt # set the path to the downloaded cert
_10
DENO_TLS_CA_STORE=mozilla,system