The system has three database options to choose from. This document talks about them from the perspective of local development, only.

1. Sqlite, the default development database
2. PostgreSQL, the preferred production database
3. LocalFilesystem, a fake database make from storing JSON files in a directory.

SQLite is so simple to use that you don't have to do anything, and you'll have a working database. You can learn how to use SQLite online very easily. The important thing to know about SQLite is that everything is stored in one file locally on your filesystem. In fact, it's stored in your repo's directory in the file ./db/game.db (./db is in .gitignore for this purpose.)

Pros/cons

• So simple to set up, it works even if you don't do anything

• Beacuse of how Heroku works, if your instanceisn't touched for a few hours, the database disappears. I mean completely deleted, never to return.
• It's not clear whether Heroku's version of SQLite supports the JSON1 extension https://www.sqlite.org/json1.html which would make it easy to change data in a running database. That said, once an instance starts, the entire game is kept in memory only.
• Cannot query the database from your local computer.

PostgreSQL is the preferred database for running on production.

• Free on Heroku for development instances
• Preserves data even if your server shuts down or restarts.

• Takes some work to set up.

• Supports querying JSON out of the box.
• Can run queries from your local computer using heroku pg:psql

First, useful links in an pinch: Forgot password, delete all data and list all databases

(This assumes you're using Ubuntu, but you can find the document for your operating system anywhere.) I followed these instructions

1. Make sure PostgreSQL is installed and up to date:

$ sudo apt update
$ sudo apt install postgresql postgresql-contrib

2. Create a user with a username, and give it admin priveleges (unless you are concerned about that, I suppose.)

$ sudo -u postgres createuser --interactive

Output
Enter name of role to add: sammy
Shall the new role be a superuser? (y/n) y

3. Give that new user a password

$ sudo -u postgres psql

postgres=# \du
postgres=# alter user sammy with password 'apassword';

4. Create the database.

sudo -u postgres createdb terraforming-mars

5. Then update your .env file to indicate you want to connect to that postgresql instance.

# .env file
POSTGRES_HOST=postgresql://sammy:apassword@localhost/terraforming-mars

Also useful:

$ sudo -u postgres psql -U postgres
psql (12.10 (Ubuntu 12.10-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# \list
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

If you need access to this database from a command-line, read https://devcenter.heroku.com/articles/heroku-postgresql#pg-credentials for instructions on fetching credentials (hint $ heroku pg:credentials:url --app your-app-name)

LocalFilesystem is a bunch of files stored in ./db/files/ (and is also therefore ignored by git) and stores files in readable JSON format. This allows you to edit games on the fly. (Though you will have to stop the server, edit the game file, and then restart the server.) It also stores game history in ./db/files/history/ which means you can compare past versions by running diff or perform undos by hand by copying files from the history directory, replacing the top-level entry.

Pros/cons

• Very easy to set up.
• Very easy to change data, just find the file you're interested in and edit it. (And then restart your server.)
• Very easy to perform an arbitrary rollback. Just copy the old version from the history directory to the files directory.

• Not fully compliant with the database API.
• Not for production use.