Supabase provides several options for programmatically connecting to your Postgres database:
- HTTP connections using the API.
- Direct connections using Postgres' standard connection system.
- Connection pooling using PgBouncer.
A "direct connection" is when a connection is made to the database using Postgres' native connection implementation.
A "connection pool" is a system (external to Postgres) which manage connections, rather than Postgres' native system.
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 connecting to a database and maintaining a connection? If yes, use a direct connection.
- Are you connecting to your database and then disconnecting immediately (e.g. a serverless environment)? If yes, use a connection pool.
Supabase provides an auto-updating API. This is the easiest way to get started if you are managing data (fetching, inserting, updating).
You cannot manage the database schema via the API (for security reasons). To do that you can use the dashboard or connect directly to your database.
You can find the API URL and Keys inside the Dashboard.
Every Supabase project provides a full Postgres database. You can connect to the database using any tool which supports Postgres.
Connection pools are useful for managing a large number of temporary connections. For example, if you are using Prisma deployed to a Serverless environment.
A "connection pool" is a system (external to Postgres) which manages connections, rather than PostgreSQL's native system. Supabase uses PgBouncer for connection pooling.
When a client makes a request, PgBouncer "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.
Pool Mode determines how PgBouncer handles a connection.
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 PostgreSQL features can be used with this option.
This is the suggested option for serverless functions. A connection is only 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 PostgreSQL features such as prepared statements are not available with this option. A comprehensive list of incompatible features can be found here.
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.