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


_24import { createClient } from 'https://esm.sh/@supabase/supabase-js'
_24
_24Deno.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


_39import * 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"
_39const databaseUrl = Deno.env.get('SUPABASE_DB_URL')!
_39
_39// Create a database pool with three connections that are lazily established
_39const pool = new postgres.Pool(databaseUrl, 3, true)
_39
_39Deno.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})

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:

Download the SSL certificate from Database settings
In your local .env file, add these two variables:


_10SSL_CERT_FILE=/path/to/cert.crt # set the path to the downloaded cert
_10DENO_TLS_CA_STORE=mozilla,system

Edit this page on GitHub