Self-Hosted Postgres and Prisma

Learnings from setting up Postgres on a DigitalOcean droplet for Penultimate Guitar. This was done on trackstar-22, so should presumably work on any Ubuntu 22.04 instance.

  1. Install Postgres. This will add a postgres user account to the system. I set up another user account to interact with the database, in this case called pg-user.
  2. Using psql, create a database that will be used for your application.
  3. Whiler running pg-user, in psql, run \password and set a password for this account to interact with Postgres. This password should not use an @, as it will conflict with the connection string later.

  4. Grant the user account access to the database with the SQL command. 

    GRANT ALL PRIVILEGES ON DATABASE "my_db" to my_user;

  5. Modify /etc/postgresql/14/main/pg_hba.conf (or equivalent). Set the ADDRESS field for IPv4 connections to this:

    # IPv4 local connections:
host    all             all             0.0.0.0/0              scram-sha-256

  6. Modify  /etc/postgresql/14/main/postgresql.conf. Set listening_addresses to all:

    # - Connection Settings -
listen_addresses = '*'

  7. Restart Postgres systemctl restart postgres

  8. In the Prisma application, set up Prisma normally. The connection string in .env should be this format:

    postgresql://pg-user:passwd@example.com:5432/db?schema=public

  9. Push the schema designed in prisma/schema.prisma using the command npx prisma db push.

  10. If this succeed, Prisma can now effectively connect to the database!