PostgreSQL Setup¶

Overview¶

PostgreSQL can be used for both local development and production on Render. Unlike SQLite, however, PostgreSQL requires a running database server. This guide covers setup for both environments.

Note

Ensure you have PostgreSQL installed locally before proceeding with the local setup.

On Windows: Download the installer from PostgreSQL.org

On macOS: brew install postgresql@18

On Linux: sudo apt install postgresql

Local Development¶

Create the Database¶

Start the PostgreSQL service and create a database for the project:

# macOS (Homebrew)
brew services start postgresql@18

# Linux
sudo service postgresql start

Then open the PostgreSQL shell and create the database and user:

psql postgres

CREATE DATABASE your_db_name;
CREATE USER your_db_user WITH PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE your_db_name TO your_db_user;
\q

Configure Environment Variables¶

Set DATABASE_URL in your .env file:

DATABASE_URL=postgresql://your_db_user:your_password@localhost:5432/your_db_name

Run Migrations¶

flask db upgrade

Resetting the Local Database¶

To wipe and rebuild from scratch:

psql postgres -c "DROP DATABASE your_db_name;"
psql postgres -c "CREATE DATABASE your_db_name;"
psql postgres -c "GRANT ALL PRIVILEGES ON DATABASE your_db_name TO your_db_user;"

rm -rf migrations
flask db init
flask db migrate -m "initial migration"
flask db upgrade

Warning

This deletes all data. Never do this in production!

Viewing the Local Database¶

Use the flask shell to inspect data:

flask shell

from app.models import User
users = User.query.all()
for u in users:
    print(u.id, u.username, u.email, u.is_admin)

Or connect directly via psql:

psql postgresql://your_db_user:your_password@localhost:5432/your_db_name

\dt               -- list all tables
SELECT * FROM user;
\q                -- quit

Creating a PostgreSQL Database with Render¶

Create Render Postgres Instance¶

Log in to Render and go to your dashboard.
Click New → PostgreSQL.
Fill in the details:
- Name: choose a name (e.g. myapp-db)
- Region: match the region of your web service
- Plan: Free (or paid for persistence beyond 90 days)
Click Create Database.
Once provisioned, open the database page and copy the Internal Database URL - use this for services running within Render.

Use the External Database URL only for connecting from outside Render (e.g. from your local machine or a database GUI).

Configure Environment Variables on Render¶

In your Render Web Service settings:

Go to Environment → Environment Variables.
Add a new variable:
```
DATABASE_URL=<paste Internal Database URL here>
```
Note

Render’s PostgreSQL URL uses the postgres:// scheme. SQLAlchemy requires postgresql://. SignBridge automatically modifies the scheme if necessary.

Viewing the Production Database¶

To inspect data on Render, connect using the External Database URL from your local machine:

psql <External Database URL>

\dt
SELECT * FROM user;
\q

You can also use a GUI tool such as pgAdmin.

Warning

Never run destructive commands (DROP TABLE, DELETE FROM, etc.) against the production database without a backup.