@vercel/postgres
View the API reference for the Vercel Postgres SDK.Vercel Postgres is available on Hobby and Pro plans
The @vercel/postgres
SDK provides an efficient way to interact with your Postgres database. It is 100% compatible with the node-postgres
library. You can use the following options:
Construct SQL queries with the sql
template literal tag. This function translates your query into a native Postgres parameterized query to help prevent SQL injections. It is recommended for one-off queries.
The sql
function automatically creates a pooled database connection for you and connects to the database specified in the process.env.POSTGRES_URL
Environment Variable.
import { sql } from '@vercel/postgres';
const likes = 100;
const { rows, fields } =
await sql`SELECT * FROM posts WHERE likes > ${likes} LIMIT 5;`;
When querying databases with a large number of rows, you may want to paginate your responses to prevent resource attacks. You can do this by using the LIMIT
and OFFSET
clauses in your query.
import { sql } from '@vercel/postgres';
const pageSize = 10; // Number of records per page
const pageNumber = 1; // Specific page number
const offset = (pageNumber - 1) * pageSize;
// Fetching records for the specific page number returning 10 records per page
const { rows, fields } =
await sql`SELECT * FROM posts WHERE likes > ${likes} LIMIT ${pageSize} OFFSET ${offset};`;
Query | Description |
---|---|
CREATE TABLE posts (likes INT); | Create a new table in the database |
SELECT * FROM posts; | Get all posts in the database |
SELECT * FROM posts WHERE likes > ${likes}; | Get all posts with more than 100 likes |
INSERT INTO posts (likes) VALUES (${likes}); | Insert a new post into the database |
UPDATE posts SET likes = 200; | Update all posts to have 200 likes |
DELETE FROM posts; | Delete all posts in the database |
You can't call sql
like a regular function. Attempting to do so will throw an error. This is a security measure to prevent the possibility of SQL injections.
You can run database queries in our built-in data browser. Go to your Postgres database in the dashboard and click on the Data tab.
Here's how we process queries sent with the sql
template tag to prevent SQL injections for you:
- The SDK extracts your parameters and adds them to an array
- The SDK sends the query string and the array of parameters to your PostgreSQL server. This is called a parameterized query, and it's a common pattern in modern JavaScript SQL libraries
- Your Postgres server sanitizes your parameters and inserts them into your query
- Your query is finally executed
At every step of this process, until the end, your query is not possible to execute. Only when the parameters are substituted back in by the server can the query execute, and by then the parameters have been sanitized.
You can create a client to connect to your Postgres database (pooled) using the db
method. Vercel will automatically manage connections to your database for you.
import { db } from '@vercel/postgres';
const client = await db.connect();
await client.sql`SELECT 1`;
Creating a client is preferred over the sql
helper if you need to make multiple queries or want to run transactions, as sql
will connect for every query.
If you want to connect to the non-default database using an Environment Variable, you can use the createPool()
method with the connectionString
parameter. The pool returned from createPool
is an instance of the Pool
class from node-postgres
.
Individual clients can be created, connected, and disconnected for each query. This method is less efficient than using db
and should only be used when a single client is required.
import { createClient } from '@vercel/postgres';
async function queryPosts() {
const client = createClient();
await client.connect();
try {
const likes = 100;
const { rows, fields } =
await client.sql`SELECT * FROM posts WHERE likes > ${likes};`;
} finally {
await client.end();
}
}
You can optionally pass createClient
a connectionString
parameter to connect to a specific database. If no connectionString
is provided, the SDK will use the default process.env.POSTGRES_URL_NON_POOLING
Environment Variable.
The client returned from createClient
is an instance of the Client
class from node-postgres
.
When using createClient()
, you must close the connection at the end of your queries. You can do so by using client.end()
.
import { createClient } from '@vercel/postgres';
const client = createClient();
await client.connect();
try {
await client.sql`SELECT 1`;
} finally {
await client.end();
}
You can use Vercel Postgres with many popular ORMs. In general, we recommend using one of our recommended ORMs, or a client you prefer, to access your Postgres database.
Learn more about using an ORM with Vercel Postgres.
@vercel/postgres
supports all valid PostgreSQL 15 queries. See Postgres compatibility for more information.
Do not use @vercel/postgres
in public-facing clients such as the browser. This will expose your database URL, granting write access to those who uses it. We recommend using this SDK only on the server.
Always use parameterized queries, an ORM, or query builder when creating queries with user-defined inputs to minimize the likelihood of SQL injection attacks. The sql
function in the @vercel/postgres
package translates raw queries to parameterized queries for you.
Was this helpful?