Flask

Flask connects to DB9 through SQLAlchemy and any standard PostgreSQL driver (psycopg2 or psycopg3). DB9 is PostgreSQL-compatible, so no special adapter is needed.

This guide shows how to set up a Flask app with DB9 using Flask-SQLAlchemy for model management and Flask-Migrate for schema migrations.

Prerequisites

• A DB9 database (create one)
• Python 3.10+
• Flask 3.0+

Create a DB9 Database

Terminal

db9 create --name flask-app

Get the connection string:

Terminal

db9 db status flask-app

Set the connection string as an environment variable:

.env

DATABASE_URL="postgresql://flask-app.admin:YOUR_PASSWORD@pg.db9.io:5433/postgres?sslmode=require"

Project Setup

Terminal

mkdir flask-db9 && cd flask-db9
python -m venv venv
source venv/bin/activate
pip install flask flask-sqlalchemy flask-migrate psycopg2-binary python-dotenv

Configure the App

app/__init__.py

import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
from dotenv import load_dotenv

load_dotenv()

db = SQLAlchemy()
migrate = Migrate()


def create_app():
    app = Flask(__name__)
    app.config["SQLALCHEMY_DATABASE_URI"] = os.environ["DATABASE_URL"]
    app.config["SQLALCHEMY_ENGINE_OPTIONS"] = {
        "pool_size": 5,
        "pool_recycle": 300,
    }

    db.init_app(app)
    migrate.init_app(app, db)

    from app import routes
    app.register_blueprint(routes.bp)

    return app

Define Models

app/models.py

from datetime import datetime, timezone
from app import db


class User(db.Model):
    __tablename__ = "users"

    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), unique=True, nullable=False)
    name = db.Column(db.String(100), nullable=False)
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))
    posts = db.relationship("Post", backref="author", lazy=True)


class Post(db.Model):
    __tablename__ = "posts"

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(500), nullable=False)
    content = db.Column(db.Text)
    published = db.Column(db.Boolean, default=False)
    author_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
    created_at = db.Column(db.DateTime, default=lambda: datetime.now(timezone.utc))

Run Migrations

Terminal

flask db init
flask db migrate -m "create users and posts"
flask db upgrade

Routes

app/routes.py

from flask import Blueprint, jsonify, request
from app import db
from app.models import User

bp = Blueprint("api", __name__, url_prefix="/api")


@bp.route("/users", methods=["GET"])
def list_users():
    users = User.query.order_by(User.created_at.desc()).all()
    return jsonify([
        {"id": u.id, "name": u.name, "email": u.email}
        for u in users
    ])


@bp.route("/users", methods=["POST"])
def create_user():
    data = request.get_json()
    user = User(name=data["name"], email=data["email"])
    db.session.add(user)
    db.session.commit()
    return jsonify({"id": user.id, "name": user.name, "email": user.email}), 201

Run the App

wsgi.py

from app import create_app

app = create_app()

Terminal

flask --app wsgi run --debug

Test with curl:

Terminal

curl -X POST http://localhost:5000/api/users \
  -H "Content-Type: application/json" \
  -d '{"name": "Alice", "email": "alice@example.com"}'

curl http://localhost:5000/api/users

Production Notes

• Server-side only: DB9 connections happen on the server. Never expose the connection string to client-side code.
• Connection pooling: Start with pool_size=5. DB9 handles per-tenant connection pooling on the server side.
• TLS required: Use sslmode=require in the connection string.
• Port 5433: DB9 uses port 5433, not the default PostgreSQL port 5432.
• WSGI server: Use Gunicorn or uWSGI in production instead of the Flask dev server.

Troubleshooting

ECONNREFUSED on port 5432

DB9 uses port 5433, not 5432. Verify your DATABASE_URL includes the correct port.

psycopg2 not found

Install psycopg2-binary for development or psycopg2 (with libpq) for production:

Terminal

pip install psycopg2-binary

Migration errors

If flask db upgrade fails with schema errors, check that your DATABASE_URL points to the correct DB9 database. DB9 supports standard PostgreSQL DDL, but some information_schema queries may differ. See the SQLAlchemy guide for details.

SSL connection errors

Ensure sslmode=require is in your connection string. If using psycopg2, the SSL parameters are passed through the connection string automatically.

Next Pages

• SQLAlchemy — full SQLAlchemy integration guide
• Connect — connection strings and authentication
• Production Checklist — deployment readiness