Home

Connecting to your database

Explore the options for connecting to your Postgres database.

Supabase provides several options for programmatically connecting to your Postgres database:

  1. Programmatic access using the Data APIs
  2. Direct connections using the built-in Postgres connection system
  3. Connection pooling for scalable connections

Data APIs#

Supabase provides auto-updating Data APIs. These are the easiest way to get started if you are managing data (fetching, inserting, updating). We provide several types of API to suit your preferences:

  • REST: interact with your database through a REST interface.
  • GraphQL: interact with your database through a GraphQL interface.
  • Realtime: listen to database changes over websockets.

Connection pooler#

Every Supabase project comes with a connection pooler for managing connections to your Postgres database.

A connection pooler is useful for managing a large number of temporary connections - for example, if you are using Prisma, Drizzle, Kysely, or anything deployed to a Serverless environment (AWS Lambdas or Edge Functions). Supabase's connection pooler also supports ipv4 out of the box.

You can find the connection pool config in the Database settings inside the dashboard:

  1. Go to the Settings section.
  2. Click Database.
  3. Under Connect to your database via connection pooling, copy your Connection string.

Direct connections#

You can use a direct connection to connect directly to your Postgres database. By default, this connection uses ipv6, which isn't supported by all network providers. If you need an ipv4 address, use the connection pooler instead.

You can find the direct connection string in the Database settings inside the dashboard:

  1. Go to the Settings section.
  2. Click Database.
  3. Under Connect to your database directly, copy your Connection string.

Choosing a connection method#

  • The Data APIs provide programmatic access and have built-in connection pooling. You can use these for all browser and application interactions. We recommend using these wherever possible.
  • A "connection pooler" is a tool which keeps connections "alive". You should use this for serverless functions and tools which disconnect from the database frequently, like Prisma, Drizzle, Kysely, etc. You should also use this if your network doesn't support ipv6.
  • A "direct connection" is Postgres' native connection system. You can use this for tools which are always alive, such as long-running server, as long as your network supports ipv6.

Why would you use a connection pool? Primarily because the way that Postgres handles connections isn't very scalable for a large number of temporary connections. You can use these simple questions to determine which connection method to use:

  • Are you on a network that doesn't support ipv6? Use the connection pooler.
  • Are you connecting to your database and then disconnecting immediately (e.g. a serverless environment)? Use the connection pooler.
  • Are you connecting to a database and maintaining a connection, and does your network support ipv6? If yes, use a direct connection.

Connecting with SSL#

You should connect to your database using SSL wherever possible, to prevent snooping and man-in-the-middle attacks.

You can obtain your connection info and Server root certificate from your application's dashboard:

Connection Info and Certificate.

Integrations#

Connecting with Drizzle#

Drizzle ORM is a TypeScript ORM for SQL databases designed with maximum type safety in mind. You can use their ORM to connect to your database.

1

Install

Install Drizzle and related dependencies.


_10
npm i drizzle-orm postgres
_10
npm i -D drizzle-kit

2

Create your models

Create a schema.ts file and define your models.


_10
import { pgTable, serial, text, varchar } from "drizzle-orm/pg-core";
_10
_10
export const users = pgTable('users', {
_10
id: serial('id').primaryKey(),
_10
fullName: text('full_name'),
_10
phone: varchar('phone', { length: 256 }),
_10
});

3

Connect

Connect to your database using the Connection Pooler for serverless environments, and the Direct Connection for long-running servers.


_11
import { drizzle } from 'drizzle-orm/postgres-js'
_11
import postgres from 'postgres'
_11
import { users } from './schema'
_11
_11
const connectionString = process.env.DATABASE_URL
_11
_11
// Disable prefetch as it is not supported for "Transaction" pool mode
_11
const client = postgres(connectionString, { prepare: false })
_11
const db = drizzle(client);
_11
_11
const allUsers = await db.select().from(users);

Connecting with pgAdmin#

pgAdmin is a GUI tool for managing Postgres databases. You can use it to connect to your database via SSL:

1

Register

Register a new Postgres server.

Register a new postgres server.

2

Name

Name your server.

Name Postgres Server.

3

Connect

Add the connection info. You can use the "Direct connection" config, which you can find in your Supabase dashboard.

Add Connection Info.

4

SSL

Navigate to the Parameters tab and select connection parameter as Root Certificate. Next navigate to the Root certificate input, it will open up a file-picker modal. Select the certificate you downloaded from your Supabase dashboard and save the server details. PgAdmin should now be able to connect to your Postgres via SSL.

Add Connection Info.

Connecting with psql#

psql is a command-line tool that comes with Postgres.

Assuming you've downloaded your SSL certificate to $HOME/Downloads/prod-supabase.cer, and your host address is db.ref.supabase.co you connect to your database via SSL:


_10
psql "sslmode=verify-full sslrootcert=$HOME/Downloads/prod-supabase.cer host=db.ref.supabase.co dbname=postgres user=postgres"

Connecting with Prisma#

Refer to our Prisma integration guide for more details.

Connecting with Postgres.js#

Postgres.js is a full-featured PostgreSQL client for Node.js and Deno.

1

Install

Install Postgres.js and related dependencies.


_10
npm i postgres

2

Connect

Create a db.js file with the connection details. Use the Connection Pooler for serverless environments, and the Direct Connection for long-running servers.


_10
// db.js
_10
import postgres from 'postgres'
_10
_10
const connectionString = process.env.DATABASE_URL
_10
const sql = postgres(connectionString)
_10
_10
export default sql

3

Execute commands

Use the connection to execute commands.


_11
import sql from './db.js'
_11
_11
async function getUsersOver(age) {
_11
const users = await sql`
_11
select name, age
_11
from users
_11
where age > ${ age }
_11
`
_11
// users = Result [{ name: "Walter", age: 80 }, { name: 'Murray', age: 68 }, ...]
_11
return users
_11
}

How connection pooling works#

A "connection pool" is a system (external to Postgres) which manages Postgres connections.

When a client makes a request, the pooler "allocates" an available connection to the client. When the client transaction or session is completed the connection is returned to the pool and is free to be used by another client.

Connection pooling

There are several pool modes, each handling connections differently:

Session#

When a new client connects, a connection is assigned to the client until it disconnects. Afterward, the connection is returned back to the pool.

All Postgres features can be used with this option.

Transaction#

This is the suggested option for serverless functions. A connection is assigned to the client for the duration of a transaction. Two consecutive transactions from the same client could be executed over two different connections. Some session-based Postgres features such as prepared statements are not available with this option.

Statement#

This is the most granular option. Connections are returned to the pool after every statement. Transactions with multiple statements are not allowed. This is best used when AUTOCOMMIT is in use.

Supavisor vs PgBouncer#

Supabase previously used PgBouncer for connection pooling. We have now deprecated PgBouncer in favor of Supavisor. Supavisor is available on all new and existing projects.

Supavisor is a new connection pooler by Supabase that runs on a high-availability cluster, segregated from your database. This means more resources are available for your database. No Application changes are required to switch from PgBouncer to Supavisor, you simply need to choose the new connection string from the "Connection Pooling" section on Database settings.

PgBouncer is now deprecated and is in the process of being removed from the platform, you can see full details and timelines of the deprecation on Github Discussions. Additionally, your Supabase database domain (db.projectref.supabase.co) will start resolving to an IPv6 address. No changes are required if your network supports IPv6. Otherwise, update your applications to use Supavisor which will continue to support IPv4 connections.

Read the full pgBouncer and IPv4 deprecation details.

© Supabase Inc—ContributingAuthor StyleguideOpen SourceSupaSquad