Database

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. Connection pooling for scalable connections
  3. Direct connections using the built-in Postgres connection system
  4. Using one of the many Client Libraries

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

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.

Every Supabase project comes with a connection pooler for managing connections to your database. The pooler provides 2 important services:

  1. It manages connections for applications that connect and disconnect from the database frequently. For example, serverless functions and ORMs such as Prisma, Drizzle, and Kysely often make and drop connections to the database. If they connected directly each time, they would quickly exhaust your database server's memory. To connect to your database efficiently with such tools, you need a pooler.
  2. It provides an IPv4 endpoint to connect to your database, which you can use if your network provider doesn't support IPv6. This is unlike the direct database connection, which resolves to an IPv6 address, unless you've enabled the IPv4 add-on.

Transaction mode is recommended if you are connecting from serverless environments. A connection is assigned to the client for the duration of a transaction. Two consecutive transactions from the same client can be executed over two different connections. Some session-based Postgres features such as prepared statements are not available with this option.

Session mode is similar to connecting to your database directly. There is full support for prepared statements in this mode. When a new client connects, a connection is assigned to the client until it disconnects. You might run into pooler connection limits since the connection is held till the client disconnects.

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 Connection string, make sure Use connection pooling is checked. Copy the URI.

Pre-requisites

The connection pooler on Supabase projects currently uses the pgbouncer Postgres role to make an initial connection to the postgres database. If these permissions are revoked, you will not be able to use the pooler until the requisite permissions have been granted.

Direct connections

You can also use a direct connection to connect directly to your Postgres database. By default, you can connect to it via IPv6, which isn't supported by all networks. If you need an IPv4 address, use the connection pooler instead or enable the IPv4 add-on.

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 Connection string, make sure Use connection pooling is unchecked. Copy the URI.

Choosing a connection method

  • Are you performing operations supported by the Data APIs? We recommend using the APIs.
  • Are you connecting to your database and then disconnecting immediately (for example, are you running your code in a serverless environment)? Use the connection pooler in transaction mode.
  • Are you on a network that doesn't support IPv6? Use the connection pooler. (If you need a long-lived connection, for example for prepared statements, use session mode.)
  • Are you connecting to your database and maintaining a connection, and does your network support IPv6? 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.

schema.ts

_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.

In your Database Settings, make sure Use connection pooler is checked, then copy the URI and save it as the DATABASE_URL environment variable. Remember to replace the password placeholder with your actual database password.

db.ts

_10
import 'dotenv/config'
_10
_10
import { drizzle } from 'drizzle-orm/postgres-js'
_10
import postgres from 'postgres'
_10
_10
const connectionString = process.env.DATABASE_URL
_10
_10
// Disable prefetch as it is not supported for "Transaction" pool mode
_10
export const client = postgres(connectionString, { prepare: false })
_10
export const db = drizzle(client);

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.

2

Name

Name your server.

Name Postgres Server.

3

Connect

Add the connection info. Go to your Database Settings. Make sure Use connection pooling is enabled. Switch the connection mode to Session and copy your connection parameters. Fill in your Database password that you made when creating your project (It can be reset in Database Settings above if you don't have it).

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 Database Settings 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.

Download your SSL certificate to /path/to/prod-supabase.cer.

Find your connection settings. Go to your Database Settings and make sure Use connection pooling is checked. Change the connection mode to Session, and copy the parameters into the connection string:


_10
psql "sslmode=verify-full sslrootcert=/path/to/prod-supabase.cer host=[CLOUD_PROVIDER]-0-[REGION].pooler.supabase.com dbname=postgres user=postgres.[PROJECT_REF]"

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.

To get your connection details, go to your Database Settings. Make sure Use connection pooling is enabled. Choose Transaction Mode if you're on a platform with transient connections, such as a serverless function, and Session Mode if you have a long-lived connection. Copy the URI and save it as the environment variable DATABASE_URL.


_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
}

Troubleshooting Supavisor

Connection pooler logs are found here. The following are common errors and their solutions:

  • Max client connections reached This error happens when the number of connections to Supavisor is more than the allowed limit of your compute add-on. Upgrade the database to a higher compute add-on to increase the number of Supavisor connections.

  • Connection failed {:error, :eaddrnotavail} to 'db.xxx.supabase.co':5432 Supavisor cannot connect to the customer database. This is usually caused if the target database is unable to respond.

  • Connection failed {:error, :nxdomain} to 'db.xxx.supabase.co':5432 Supavisor cannot connect to the customer database. This is usually caused if the target database is unable to respond.

  • Connection closed when state was authentication This error happens when either the database doesn’t exist or if the user doesn't have the right credentials.

  • Subscribe error: {:error, :worker_not_found} This log event is emitted when the client tries to connect to the database, but Supavisor does not have the necessary information to route the connection. Try reconnecting to the database as it can take some time for the project information to propagate to Supavisor.

  • Subscribe error: {:error, {:badrpc, {:error, {:erpc, :timeout}}}} This is a timeout error when the communication between different Supavisor nodes takes longer than expected. Try reconnecting to the database.

  • Terminating with reason :client_termination when state was :busy This error happens when the client terminates the connection before the connection with the database is completed.

Finding your database hostname

Your database’s hostname is crucial for establishing a direct connection. It resolves to the underlying IP address of your database. To find your hostname, navigate to your Database Settings.

Note that the pooler (connection pooler) has a different IP than your database. To reveal the database host and direct connection string, hide the pooler connection string.

Example hostname: db.<PROJECT_REF>.supabase.co

Managing your IP address

To determine your current IP address, you can use an IP address lookup website or the terminal command:


_10
nslookup db.<PROJECT_REF>.supabase.co

This command queries the domain name servers to find the IP address of the given hostname.

Example IPv6 Address: 2a05:d014:1c06:5f0c:d7a9:8616:bee2:30df

IPv6 address

Upon project creation, a static IPv6 address is assigned. This address might change when:

  • A project is paused or resumed.
  • The database is upgraded.

IPv4 address

Opting for the static IPv4 add-on provides a more stable connection address. The IPv4 address remains constant unless the project is paused or resumed.

Unlike the IPv6 address, upgrading your database doesn't affect the IPv4 address.