External Database Setup

If you are using your own PostgreSQL instance (e.g., AWS RDS, Azure Database, CloudNativePG, or a local installation) instead of the provided Docker Compose setup, follow these steps to ensure the database is correctly configured.

!WARNINGCommunity Supported Only External and managed database configurations are not officially tested or supported by the core application maintainers. Use this guide at your own risk.

1. Database and Users

The application uses two database roles:

  1. SPARKY_FITNESS_DB_USER: Used for migrations and schema management.
  2. SPARKY_FITNESS_APP_DB_USER: Used by the application components with restricted permissions.

!IMPORTANT The application automatically creates the App User (SPARKY_FITNESS_APP_DB_USER) and grants it all necessary permissions on first startup. The DB User (SPARKY_FITNESS_DB_USER) must have CREATEROLE to do this.

Option A — Standard Setup (SparkyFitness creates the SPARKY_FITNESS_APP_DB_USER User automatically)

Run the following as a database superuser:

-- 1. Create the Database
CREATE DATABASE sparkyfitness_db;

-- 2. Create the DB Owner user (referenced in .env as SPARKY_FITNESS_DB_USER)
CREATE USER sparky_admin WITH PASSWORD 'your_secure_password';

-- 3. Grant database ownership so sparky_admin can create schemas and tables
ALTER DATABASE sparkyfitness_db OWNER TO sparky_admin;

-- 4. Grant Role Creation privilege
-- Required so the app can automatically create SPARKY_FITNESS_APP_DB_USER
-- on first startup (see utils/dbMigrations.ts)
ALTER USER sparky_admin CREATEROLE;

-- 5. PostgreSQL 15+ only: the default public schema permissions changed in PG 15.
-- Explicitly grant create rights to the owner:
GRANT ALL ON SCHEMA public TO sparky_admin;

Option B — Reduced Privilege Setup (you create the SPARKY_FITNESS_APP_DB_USER User manually)

If your environment does not allow CREATEROLE (e.g., strict managed databases), you can pre-create the App User yourself. The application checks whether the role already exists before attempting to create it — if it finds it, CREATE ROLE is skipped entirely and CREATEROLE is not required.

-- 1. Create the Database
CREATE DATABASE sparkyfitness_db;

-- 2. Create the DB Owner user (referenced in .env as SPARKY_FITNESS_DB_USER)
CREATE USER sparky_admin WITH PASSWORD 'your_secure_password';

-- 3. Grant database ownership
ALTER DATABASE sparkyfitness_db OWNER TO sparky_admin;

-- 4. PostgreSQL 15+ only
GRANT ALL ON SCHEMA public TO sparky_admin;

-- 5. Pre-create the App User (referenced in .env as SPARKY_FITNESS_APP_DB_USER)
--    The app detects this role already exists and skips CREATE ROLE,
--    so sparky_admin does NOT need CREATEROLE.
CREATE USER sparky_app WITH PASSWORD 'another_secure_password';

Set both users in your .env:

SPARKY_FITNESS_DB_USER=sparky_admin
SPARKY_FITNESS_DB_PASSWORD=your_secure_password
SPARKY_FITNESS_APP_DB_USER=sparky_app
SPARKY_FITNESS_APP_DB_PASSWORD=another_secure_password

!NOTE With Option B, sparky_admin still needs database ownership to run migrations (create tables, schemas, functions, indexes). The only privilege that is no longer required is CREATEROLE.

2. PostgreSQL Extensions

!NOTENo extensions required. As of v0.17.0 release, SparkyFitness no longer depends on uuid-ossp, pgcrypto, or pg_stat_statements.

  • UUID generation uses the built-in gen_random_uuid() (PostgreSQL 13+), which requires no extension.
  • Encryption is handled entirely in application code (AES-256-GCM via Node.js crypto), not via database functions.

If you are upgrading an existing installation that has these extensions installed, the 20260618000000_remove_superuser_extensions.sql migration will attempt to remove them automatically. Non-superuser environments will skip the DROP EXTENSION step gracefully — the extensions are unused and harmless if left in place.

3. Row Level Security (RLS)

The sparky_admin user must be the owner of the tables to enable and manage RLS policies. Setting ALTER DATABASE ... OWNER TO sparky_admin ensures that any tables created during migrations are automatically owned by sparky_admin.

4. Environment Configuration

In your .env file, ensure you point to your external database:

SPARKY_FITNESS_DB_HOST=your-db-host
SPARKY_FITNESS_DB_NAME=sparkyfitness_db
SPARKY_FITNESS_DB_USER=sparky_admin
SPARKY_FITNESS_DB_PASSWORD=your_secure_password
SPARKY_FITNESS_DB_PORT=5432

# App user — created automatically (Option A) or pre-created by you (Option B)
SPARKY_FITNESS_APP_DB_USER=sparky_app
SPARKY_FITNESS_APP_DB_PASSWORD=another_secure_password

!IMPORTANTSPARKY_FITNESS_APP_DB_USER and SPARKY_FITNESS_APP_DB_PASSWORD are always required in the .env file — the app uses them for the live query connection pool regardless of which setup option you chose.

  • Option A: the app creates this role automatically using these values.
  • Option B: the role already exists; the password in .env must match the one set when you created it manually.

5. Security Hardening (applies only to Option A)

The CREATEROLE privilege is only required during the initial installation or when a future update needs to create a new specialized database role.

Revoking CREATEROLE

Once the application has successfully started for the first time and you see the log Successfully created role, you can revoke this privilege:

ALTER USER sparky_admin NOCREATEROLE;

!CAUTIONPotential Issues with NOCREATEROLE If you revoke this privilege, future application updates that require creating new database roles will fail. If you encounter a "Permission Denied" error during a future upgrade, temporarily re-grant CREATEROLE or manually create the required role as a superuser, then revoke it again.

Table of Contents