How to Use PostgreSQL with Vercel for Backend Data

Using PostgreSQL with Vercel

Vercel provides managed hosting solutions optimized for frontend applications, and it supports PostgreSQL databases through its managed integrations. You can easily connect PostgreSQL to a Vercel-hosted application, allowing for efficient database management and seamless integration with backend functions.

Description

Vercel doesn’t host databases directly, but it provides integrations with providers like Supabase, PlanetScale, and Neon, where PostgreSQL databases can be hosted and connected to Vercel projects. These integrations enable developers to use Vercel's serverless functions alongside PostgreSQL databases, creating full-stack applications with Vercel as the frontend and PostgreSQL as the backend data storage.

Connecting a PostgreSQL Database to a Vercel Project

To connect a PostgreSQL database, you can follow these steps, which demonstrate how to use a PostgreSQL database hosted on a provider like Supabase or Neon with Vercel.

Step-by-Step Guide:

1. Create a PostgreSQL Database with an External Provider:

• Go to a PostgreSQL hosting provider (e.g., Supabase, Neon).
• Set up a new PostgreSQL database and note the connection credentials (host, port, database name, user, and password).

2. Connect PostgreSQL to Vercel as an Environment Variable:

Code:

# Example of setting up the DATABASE_URL environment variable in Vercel
vercel env add DATABASE_URL postgres://username:password@host:port/database

Explanation:

• Replace username, password, host, port, and database with your actual PostgreSQL database credentials.
• The DATABASE_URL environment variable will be accessible in your Vercel app.

3. Use PostgreSQL in your Vercel Project:

In a Node.js environment on Vercel, use a PostgreSQL client library, like pg, to connect to the database.

Code:

// Import the pg library
const { Pool } = require('pg');

// Initialize the pool with the environment variable
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: { rejectUnauthorized: false } // Important for cloud-hosted databases
});

// Define a function to get data
async function getData() {
  const client = await pool.connect();
  try {
    const res = await client.query('SELECT * FROM users'); // Example query
    console.log(res.rows); // Log the query result
  } finally {
    client.release();
  }
}

getData().catch(console.error);

Explanation:

• The code initializes a Pool connection using the pg library and the DATABASE_URL environment variable.
• The getData function queries a sample users table in PostgreSQL and logs the result to the console.
• This example demonstrates how to query PostgreSQL data from within a Vercel project.

Deploying on Vercel:

• Push your code to a repository (e.g., GitHub).
• Link the repository to Vercel, and deploy your application. Vercel will automatically connect to your PostgreSQL database using the DATABASE_URL you defined in the environment variables.

Example Query in PostgreSQL with Vercel

You can run different types of queries in your Vercel backend by modifying the SQL inside the getData function. Here’s another example:

Code:

// Query to insert a new record into the "users" table
async function addUser(name, email) {
  const client = await pool.connect();
  try {
    const res = await client.query('INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *', [name, email]);
    console.log('User added:', res.rows[0]);
  } finally {
    client.release();
  }
}
addUser(‘Keahi Julius’, '[email protected]').catch(console.error);

Explanation:

• This example inserts a new user into the users table, demonstrating how to write to the PostgreSQL database from Vercel.

Notes:

• SSL Configuration: Most cloud PostgreSQL providers require SSL for connections. Be sure to enable SSL in the connection configuration, as shown in the examples above.
• Error Handling: Add appropriate error handling for production environments, as network or configuration issues can interrupt connections.

All PostgreSQL Questions, Answers, and Code Snippets Collection.