Skip to main content
Version: 4.x

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.

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:

NamePlaceholderDescription
HostHOSTIP address/domain of your database server, e.g. localhost
PortPORTPort on which your database server is running, e.g. 5432
UserUSERName of your database user, e.g. postgres
PasswordPASSWORDpassword of your database user
DatabaseDATABASEName 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: