Local Postgres Setup

RedwoodJS uses a SQLite database by default. While SQLite makes local development easy, you're likely going to want to run the same database you use in production locally at some point. And since the odds of that database being Postgres are high, here's how to set up Postgres.

Install Postgres

Mac

If you're on a Mac, we recommend using Homebrew:

brew install postgresql@14

Install Postgres? I've messed up my Postgres installation so many times, I wish I could just uninstall everything and start over!

We've been there before. For those of you on a Mac, this video is a great resource on how to wipe the various Postgres installs off your machine so you can get back to a blank slate. Obviously, warning! This resource will teach you how to wipe the various Postgres installs off your machine. Please only do it if you know you can!

Windows and Other Platforms

If you're using another platform, see Prisma's Data Guide for detailed instructions on how to get up and running.

Alternatives to local PostgreSQL Installation

Local Prisma Postgres

For local development, you can use local Prisma Postgres which runs a PostgreSQL-compatible database locally. This eliminates the need to install and manage PostgreSQL locally while maintaining full compatibility with production PostgreSQL databases.

First, update your Prisma schema to use PostgreSQL as the provider:

api/db/schema.prisma

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

Start the local Prisma Postgres server:

npx prisma dev

The server will start and display connection options. Press t to get the TCP connection URL for standard PostgreSQL connections, or press h if you're planning to use Prisma Postgres in production (which requires the Prisma Client extension).

If you're using any other provider for PostgreSQL, use the TCP connection URL in your .env file:

DATABASE_URL="postgresql://localhost:54322/main"

Keep the server running while performing migrations and using the database for local development.

Temporary Prisma Postgres database

For quick testing or prototyping, Prisma Postgres offers temporary production-ready databases that require no setup or accounts. Use npx create-db to create a database that's automatically deleted after 24 hours:

npx create-db@latest

This provides both Prisma ORM-optimized and standard PostgreSQL connection strings. You can also claim the database to make it permanent if needed.

Creating a database

If everything went well, then Postgres should be running and you should have a few commands at your disposal (namely, psql, createdb, and dropdb).

Check that Postgres is running with brew services (the $(whoami) bit in the code block below is just where your username should appear):

$ brew services
Name Status User Plist
postgresql started $(whoami) /Users/$(whoami)/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

If it's not started, start it with:

brew services start postgresql

Great. Now let's try running the PostgresQL interactive terminal, psql:

$ psql

You'll probably get an error like:

psql: error: FATAL: database $(whoami) does not exist

This is because psql tries to log you into a database of the same name as your user. But if you just installed Postgres, odds are that database doesn't exist.

Luckily it's super easy to create one using another of the commands you got, createdb:

$ createdb $(whoami)

Now try:

$ psql
psql (13.1)
Type "help" for help.

$(whoami)=#

If it worked, you should see a prompt like the one above—your username followed by =#. You're in the PostgreSQL interactive terminal! While we won't get into psql, here's a few the commands you should know:

• \q — quit (super important!)
• \l — list databases
• \? — get a list of commands

If you'd rather not follow any of the advice here and create another Postgres user instead of a Postgres database, follow this.

Update the Prisma Schema

Tell Prisma to use a Postgres database instead of SQLite by updating the provider attribute in your schema.prisma file:

api/db/schema.prisma

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

Note: If you run into a "PrismaClientInitializationError" then you may need to regenerate the prisma client using: yarn rw prisma generate

Connect to Postgres

Add a DATABASE_URL to your .env file with the URL of the database you'd like to use locally. The following example uses redwoodblog_dev for the database. It also has postgres setup as a superuser for ease of use.

DATABASE_URL="postgresql://postgres@localhost:5432/redwoodblog_dev?connection_limit=1"

Note the connection_limit parameter. This is recommended by Prisma when working with relational databases in a Serverless context. You should also append this parameter to your production DATABASE_URL when configuring your deployments.

Local Test DB

You should also set up a test database similarly by adding TEST_DATABASE_URL to your .env file.

TEST_DATABASE_URL="postgresql://postgres@localhost:5432/redwoodblog_test?connection_limit=1"

Note: local postgres server will need manual start/stop -- this is not handled automatically by RW CLI in a manner similar to sqlite

Base URL and path

Here is an example of the structure of the base URL and the path using placeholder values in uppercase letters:

postgresql://USER:PASSWORD@HOST:PORT/DATABASE

The following components make up the base URL of your database, they are always required:

Name	Placeholder	Description
Host	HOST	IP address/domain of your database server, e.g. localhost
Port	PORT	Port on which your database server is running, e.g. 5432
User	USER	Name of your database user, e.g. postgres
Password	PASSWORD	password of your database user
Database	DATABASE	Name of the database you want to use, e.g. redwoodblog_dev

Migrations

Migrations are snapshots of your DB structure, which, when applied, manage the structure of both your local development DB and your production DB.

To create and apply a migration to the Postgres database specified in your .env, run the migrate command. (Did this return an error? If so, see "Migrate from SQLite..." below.):

yarn redwood prisma migrate dev

Migrate from SQLite to Postgres

If you've already created migrations using SQLite, e.g. you have a migrations directory at api/db/migrations, follow this two-step process.

1. Remove existing migrations

For Linux and Mac OS From your project root directory, run either command corresponding to your OS.

rm -rf api/db/migrations

For Windows OS

rmdir /s api\db\migrations

Note: depending on your project configuration, your migrations may instead be located in api/prisma/migrations

2. Create a new migration

Run this command to create and apply a new migration to your local Postgres DB:

yarn redwood prisma migrate dev

DB Management Tools

Here are our recommendations in case you need a tool to manage your databases:

• TablePlus (Mac, Windows)
• Beekeeper Studio (Linux, Mac, Windows - Open Source)