Connecting to your database
Explore the options for connecting to your Postgres database.
Supabase provides several options for programmatically connecting to your Postgres database:
- Programmatic access using the Data APIs
- Connection pooling for scalable connections
- Direct connections using the built-in Postgres connection system
Direct database connections will start resolving to an IPv6 address. Starting from January 26, 2024, projects will be gradually migrated to this behavior. If you connect to your database using the REST or GraphQL endpoints, this doesn't affect you. If you use the database connection string, see the GitHub discussion on how to prepare for IPv6.
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:
- 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.
- 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.
The connection pooler is available in 2 modes: Transaction
and Session
.
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:
- Go to the
Settings
section. - Click
Database
. - Under
Connection string
, make sureUse 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:
- Go to the
Settings
section. - Click
Database
. - Under
Connection string
, make sureUse 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:
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.
Install
Install Drizzle and related dependencies.
_10npm i drizzle-orm postgres_10npm i -D drizzle-kit
Create your models
Create a schema.ts
file and define your models.
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.
Connecting with pgAdmin
pgAdmin
is a GUI tool for managing Postgres databases. You can use it to connect to your database via SSL:
Register
Register a new Postgres server.
Name
Name your server.
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).
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.
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:
_10psql "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.
Install
Install Postgres.js and related dependencies.
_10npm i postgres
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_10import postgres from 'postgres'_10_10const connectionString = process.env.DATABASE_URL_10const sql = postgres(connectionString)_10_10export default sql
Execute commands
Use the connection to execute commands.
_11import sql from './db.js'_11_11async 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:
_10nslookup 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.