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                 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:

  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!