Beginner's Guide to Vercel Postgres

Beginner's Guide to Vercel Postgres

Table of Contents

  1. Introduction
  2. Prerequisites
  3. Creating a Vercel Postgres Database
  4. Setting Up Environment Variables
  5. Using Vercel Postgres SDK
  6. Working with Vercel Postgres and ORMs
  7. Managing Data
  8. Security
  9. Vercel Postgres Limits and Pricing
  10. Conclusion

Introduction

This beginner's guide will help you understand and get started with Vercel Postgres, a serverless SQL database designed to integrate with Vercel Functions and your frontend framework. We will cover the creation,management, and usage of Vercel Postgres databases, along with some best practices.

Prerequisites Before you begin

Please ensure you have the following:

An existing Vercel Project

  • Install the Vercel Postgres package

    npm i @vercel/postgres

  • Install or update to the latest version of Vercel CLI

    npm i -g vercel@latest

Creating a Vercel Postgres Database

Follow these steps to create a Postgres database for your project:

  1. Navigate to your Vercel Project and select the Storage tab.
  2. Click the Connect Database button.
  3. Under the Create New tab, select Postgres and click Continue.
  4. In the dialog, enter a Store Name (e.g. pets_postgres_db), select a region, and click Create.

Your new database will be created in the specified region.

Setting Up Environment Variables

After creating your Vercel Postgres database, you need to set up environment variables for your local project. Run the following command:

vercel env pull .env.development.local

This will pull the latest environment variables, including the API URL and credentials for your Postgres database.

Using Vercel Postgres SDK

The Vercel Postgres SDK provides an efficient way to interact with your Postgres database. Import the SDK in your project:

import { db } from "@vercel/postgres";

Now you can use the db object to connect to your database and perform various operations like creating tables, inserting data, and querying data.

For example, to create a table called Pets:

await db.sql`CREATE TABLE Pets ( Name varchar(255), Owner varchar(255) );`;

To insert data into the Pets table:

await db.sql`INSERT INTO Pets (Name, Owner) VALUES ('Fiona', 'Lucy');`;

To query data from the Pets table:

const pets = await db.sql`SELECT * FROM Pets;`;

Working with Vercel Postgres and ORMs

You can use Vercel Postgres with popular Object Relational Mapping (ORM) tools like Kysely or Prisma. To do so, install the ORM of your choice and configure it to use the credentials provided in the environment variables.

For example, with Prisma, you would update your prisma/schema.prisma file to include:

datasource db {
  provider = "postgresql"
  url      = env("POSTGRES_PRISMA_URL")
}

This will configure Prisma to use the Vercel Postgres credentials.

Managing Data

To manage your data, you can use the Vercel Postgres SDK or an ORM of your choice. This can include creating, updating, deleting, and querying data.

For example, to update all pets' names in the Pets table:

await db.sql`UPDATE Pets SET Name = 'NewName' WHERE Name = 'Fiona';`;

To delete data from the Pets table:

await db.sql`DELETE FROM Pets WHERE Name = 'NewName';`;

Security

Always use parameterized queries, an ORM, or a 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 simple queries to parameterized queries for you.

Do not use the Vercel Postgres SDK in public-facing clients such as browsers, as this will expose your database URL and grant write access to unauthorized users.

Vercel Postgres Limits and Pricing

Vercel Postgres has limitations related to account plan limits, SOC 2 Type 2 compliance, and more. For details on pricing and limitations, refer to the official Vercel Postgres limits and pricing documentation.

Conclusion

This guide provided an introduction to Vercel Postgres, including creating a database, setting up environment variables, using the Vercel Postgres SDK, working with ORMs, managing data, and understanding security, limits, and pricing. With this knowledge, you can now confidently use Vercel Postgres in your projects for efficient and secure data management.

This guide was created using chatgpt and official Vercel documentation for the purpose of having a small guide on how to use Vercel Postgres. Its for me to learn how to combine complex documentation into a simple guide. I hope you enjoyed it!