@vercel/postgres

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:

• sql (recommended)
• db
• createClient()

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};`;

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.

Here's how we process queries sent with the sql template tag to prevent SQL injections for you:

1. The SDK extracts your parameters and adds them to an array
2. 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
3. Your Postgres server sanitizes your parameters and inserts them into your query
4. 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.