Skip to main content

Database Setup

Prime SaaS uses PostgreSQL as its primary database, accessed via Knex.js for query building and migrations.

1. Install PostgreSQL​

macOS (Homebrew):

brew install postgresql@18
brew services start postgresql@18

Ubuntu/Debian:

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql

Docker:

docker run -d \
  --name primesaas-db \
  -e POSTGRES_USER=primesaas \
  -e POSTGRES_PASSWORD=yourpassword \
  -e POSTGRES_DB=primesaas \
  -p 5432:5432 \
  postgres:18

2. Create a database​

psql -U postgres
CREATE USER primesaas WITH PASSWORD 'yourpassword';
CREATE DATABASE primesaas OWNER primesaas;
GRANT ALL PRIVILEGES ON DATABASE primesaas TO primesaas;
\q

3. Configure environment variables​

In demo-server/.env:

DB_HOST=localhost
DB_PORT=5432
DB_USER=primesaas
DB_PASSWORD=yourpassword
DB_NAME=primesaas

# Connection pool (optional, defaults shown)
DB_POOL_MIN=2
DB_POOL_MAX=10

4. Run migrations​

From the demo-server/ directory:

npm run migrate

This runs all pending Knex migrations from demo-server/migrations/. The migration files create all required tables:

  • accounts — tenant organizations with billing info
  • users — individual user records with 2FA and OAuth fields
  • plans — pricing plan catalog
  • app_usage — per-account usage tracking (API calls, scans)
  • subscriptions — subscription lifecycle records
  • payments — payment history
  • api_keys — user-generated API keys
  • and more...

Rollback​

To roll back the last migration batch:

npx knex migrate:rollback --knexfile knexfile.js

Connection config​

The database config is in demo-server/knexfile.js. It reads from your environment and sets up connection pooling automatically. No changes are needed unless you're using a non-standard PostgreSQL setup.