A CRUD app needs somewhere durable to keep records while people add, edit, and delete them. PostgreSQL is a relational database suited to those frequent, small changes and to keeping data consistent during concurrent operations. Unlike an analytics-oriented warehouse such as BigQuery, it fits an app that works with individual records. section 8.2.3
To start locally:
- Install PostgreSQL and keep the superuser password you choose during setup. section 8.2.3
- Open its psql shell, create an app user and database, then connect to that database. section 8.2.3
- Use SQL to create tables. Column types constrain values; a primary key identifies each row, and a foreign key connects records across tables. section 8.2.4
A small app database
A writing app can store usernames in a users table and entries in a haikus table. Each entry has an automatically assigned ID, a creation timestamp, text, and an author that references a username. In psql, INSERT adds entries; SELECT reads them, including filtered matches; UPDATE changes one; and DELETE removes one. A VARCHAR length limit rejects values that are too long, while a primary key prevents duplicate usernames. section 8.2.4
Once manual queries work, connect the app through Streamlit’s st.connection. Install SQLAlchemy and psycopg2-binary, and provide a PostgreSQL connection URL in .streamlit/secrets.toml. A reusable Database class can execute SQL with parameters, commit writes, and roll them back if an error occurs. A first check is to SELECT the entries and display the returned rows with st.write. section 8.2.5
Listing 8.1 The Database class
import streamlit as st
from sqlalchemy import text
class Database:
def __init__(self):
self.conn = st.connection("haikuconn", type="sql")
def execute_query(self, query, params={}, write=False):
with self.conn.session as session:
try:
result = session.execute(text(query), params)
if write:
session.commit()
return result.fetchall()
except Exception:
if write:
session.rollback()
raise
The reusable class shows the practical difference between reading rows and committing a write, including rollback when a write fails. section 8.2.5
For deployment on Streamlit Community Cloud, use an external PostgreSQL server rather than the local installation. A managed Neon database supplies a production connection string; create the tables there again with the same CREATE TABLE commands used locally, and keep the connection string safe. section 8.6.1
