Home

Database

Eternaltwin uses Postgres as its main database. It is the only backend that supports the full system: users, authentication, forum, OAuth, archives and jobs.

A partial SQLite backend also exists for a few stores, see SQLite.

Do you need a database?

No, not to get started. Each backend store is selected by the backend.store key (or a per-store *_store key) in the configuration file, and it accepts:

• Memory: keep everything in RAM. Eternaltwin starts without any database and loses all data on restart.
• Postgres: store the data in a Postgres database, configured in the [postgres] section.

The dev profile defaults to Memory, so yarn start works out of the box with no database at all. The production and test profiles default to Postgres. Set up Postgres when you need data to survive a restart, or when you work on SQL queries.

Requirements

Postgres 14 is the hard floor: the schema declares CREATE TYPE PERIOD AS RANGE and then uses the companion period_multirange type, which Postgres only creates automatically from version 14 on. The server page lists the version deployed in production.

The extensions are created by the schema scripts themselves (CREATE EXTENSION IF NOT EXISTS pgcrypto and btree_gist). Neither is a trusted extension, so the role used to create the schema must be a superuser.

Initialize the Postgres cluster

Skip this if your distribution already runs a Postgres cluster.

# Run as the Postgres user
initdb --locale "en_US.UTF-8" --encoding="UTF8" --pgdata="/var/lib/postgres/data/"

Example

[postgres@red ~]$ initdb --locale "en_US.UTF-8" --encoding="UTF8" --pgdata="/var/lib/postgres/data/"
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgres/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Europe/Paris
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgres/data/ -l logfile start

Create a dev DB superuser

# Run as the Postgres user
createuser --encrypted --interactive --pwprompt

Example

postgres@host $ createuser --encrypted --interactive --pwprompt
Enter name of role to add: eternaltwin.dev.main
Enter password for new role: dev
Enter it again: dev
Shall the new role be a superuser? (y/n) y

eternaltwin.dev.main and the password dev are the defaults of the dev profile, so using them means you have nothing to configure afterwards. Any other name works as long as you write it in the configuration file.

Create the database

Eternaltwin never creates the database itself: eternaltwin db sync connects to an existing database and only manages the schema inside it. Create the database first.

createdb --owner=<dbuser> <dbname>
psql <dbname>
ALTER SCHEMA public OWNER TO <dbuser>;

Example:

$ createdb --owner=eternaltwin.dev.main eternaltwin.dev
$ psql eternaltwin.dev
psql (17.2)
Type "help" for help.

eternaltwin.dev=# ALTER SCHEMA public OWNER TO "eternaltwin.dev.main";

Configure Eternaltwin

The configuration lives in eternaltwin.local.toml at the root of the repository (git-ignored; the search order also accepts eternaltwin.<profile>.local.toml, eternaltwin.toml and their .json variants).

[backend]
store = "Postgres"

[postgres]
host = "localhost"
port = 5432
name = "eternaltwin.dev"
user = "eternaltwin.dev.main"
password = "dev"

Keys of the [postgres] section:

Setting user also sets admin_user, and setting password also sets admin_password, unless you give them explicitly. Split them when the backend must run with a restricted role while migrations run as the superuser. In the production profile the defaults become eternaltwin.production, eternaltwin.production.main and eternaltwin.production.admin.

Create and update the schema

The schema is managed by the eternaltwin binary, exposed through three project tasks at the root of the repository:

yarn run db:sync   # upgrade the schema to the latest version
yarn run db:check  # print the current schema state
yarn run db:reset  # empty the database, then re-create the latest schema

They are thin wrappers around the CLI, which you can also call directly:

cargo run --bin eternaltwin -- db sync
cargo run --bin eternaltwin -- db check
cargo run --bin eternaltwin -- db reset

Use db:sync on a freshly created database to install the schema, and again after pulling changes that add migration scripts. db:reset destroys all the data, it is meant for development only.

There is no db:create task: creating the database is the createdb step above.

Schema scripts

The SQL lives in db/scripts (the crates/db_schema/scripts path is a symlink to it, and the files are embedded in the binary at build time):

• create/001.sql: the initial schema.
• upgrade/NNN-MMM.sql: one migration per version step. The chain currently runs up to 060-061.sql.
• drop.sql: drops the public schema and re-creates it with the required extensions. db:reset does the equivalent from the CLI.
• grant.sql.example: sample grants, if you want separate read-only and read-write roles next to the admin role.

Adding a migration means adding the next NNN-MMM.sql file: the version chain must stay unbroken from the empty database to the latest state, and a unit test in eternaltwin_db_schema fails if a step is missing.