Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 38 ยท Data Connections

SQL Databases and st.connection

Connect Streamlit to SQL databases with st.connection and SQLAlchemy. Run safe parameterized queries, cache results with ttl and test the logic with SQLite.

Real apps rarely live on CSV files alone. Orders, users and measurements sit in a database, and a Streamlit page is a convenient front end for querying them. The good news: Streamlit adds very little new to learn. You write SQL, you get a DataFrame, and you show it. What is new is how to connect once, cache results, keep secrets out of your code, and avoid SQL injection, because your script reruns on every click.

This lesson uses the built-in st.connection API, shows the plain SQLAlchemy and DB-API alternatives, and tests all the database logic with SQLite, which Python includes. If you want to practice SQL itself, the SQL tutorial covers it from the start.

Streamlit does not run on this site, so app code is read-only. The runnable examples use Python's sqlite3 module in memory. Their SQL and the parameter handling are exactly what you would run against a real database.

st.connection

st.connection creates and manages a connection for you. It is cached with cache_resource internally, so reruns reuse it. The SQL flavor is type="sql":

Python
import streamlit as st

conn = st.connection("sales_db", type="sql")          # settings from secrets.toml
df = conn.query("SELECT * FROM orders ORDER BY created_at DESC LIMIT 50", ttl=300)
st.dataframe(df)

The first argument is a name. Streamlit looks for matching settings under [connections.sales_db] in .streamlit/secrets.toml:

TOML
[connections.sales_db]
dialect = "postgresql"
host = "db.example.com"
port = 5432
database = "shop"
username = "app_reader"
password = "kept-out-of-git"

You can also give a single url instead. For a local SQLite file:

TOML
[connections.local]
url = "sqlite:///data/shop.db"

The .streamlit/secrets.toml file must never be committed to git (see APIs and Secrets). A driver package for your database must be installed: psycopg2-binary for PostgreSQL, pymysql for MySQL, nothing extra for SQLite.

conn.query and ttl

conn.query(sql, ttl=...) runs the SQL and returns a DataFrame, and caches the result. ttl is how long (in seconds or a string like "10m") the cached answer is considered fresh. This is cache_data behavior built in, so repeated reruns do not hit the database. ttl=0 disables caching; leaving it out caches until the app restarts or you clear it. Choose it to match how fast the data changes.

Parameters, never string formatting

The most important rule: never build SQL by pasting user input into a string. Use query parameters:

Python
name = st.text_input("Customer")
df = conn.query(
    "SELECT * FROM orders WHERE customer = :name",
    params={"name": name},
    ttl=60,
)

The :name placeholder is filled by the driver, which treats the value as data, never as SQL. With an f-string, a user typing x' OR '1'='1 would change your query. That attack is SQL injection, and it is the most common database vulnerability in small apps.

Running real SQL in the browser (stand-in)

SQLite uses ? placeholders (or :name). The next TryIt builds a small database, runs a parameterized query and shows exactly why string pasting is dangerous.

Python Parameterized queries versus string pasting

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The pasted version leaks the whole table to the attack string; the parameterized version treats it as an odd customer name and returns nothing.

Aggregations for charts

Let the database do the heavy lifting and bring back only what the page needs. A chart of revenue per customer should not download every order:

Python
df = conn.query(
    """
    SELECT customer, SUM(total) AS revenue
    FROM orders
    GROUP BY customer
    ORDER BY revenue DESC
    """,
    ttl="10m",
)
st.bar_chart(df, x="customer", y="revenue")

Select only needed columns, filter and aggregate in SQL, and add a LIMIT when showing raw rows. A page that loads 5 million rows into pandas on each click will be slow no matter how fast the server is.

Writing: conn.session

conn.query is for reading. To write, use the connection's SQLAlchemy session and commit:

Python
from sqlalchemy import text

with conn.session as session:
    session.execute(
        text("INSERT INTO orders (customer, total) VALUES (:c, :t)"),
        {"c": customer, "t": total},
    )
    session.commit()
st.cache_data.clear()        # so cached queries do not show stale rows

After a write, clear the cache or use a short ttl, otherwise conn.query returns old results. Many apps also give the database user only the permissions it needs: a read-only account for dashboards.

Without st.connection

You can use any Python database library. Wrap the connection in st.cache_resource so it is created once:

Python
import sqlite3
import pandas as pd
import streamlit as st

@st.cache_resource
def get_connection():
    return sqlite3.connect("data/shop.db", check_same_thread=False)

@st.cache_data(ttl=300)
def top_customers(limit: int) -> pd.DataFrame:
    return pd.read_sql_query(
        "SELECT customer, SUM(total) AS revenue FROM orders "
        "GROUP BY customer ORDER BY revenue DESC LIMIT ?",
        get_connection(), params=(limit,))

st.dataframe(top_customers(st.slider("How many?", 1, 20, 5)))

check_same_thread=False is needed for SQLite because Streamlit runs scripts in threads. For PostgreSQL or MySQL, prefer a connection pool (SQLAlchemy's engine) in cache_resource; a single raw connection shared by many users can be closed by the server and then fail.

Testing the logic outside Streamlit

Keep SQL in plain functions that take a connection and return a DataFrame. They are easy to test without the UI. Here a function builds a summary, and the check prints the result you can assert on.

Python A testable query function

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Common mistakes

  • Building SQL with f-strings (injection).
  • Committing secrets.toml or passwords to git.
  • Loading whole tables into pandas instead of aggregating in SQL.
  • Forgetting ttl, so the database is queried on every rerun, or setting it too high and showing stale data after writes.
  • A single shared connection that is never recreated after the database closes it.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why use params={"name": name} instead of an f-string in a query?

    Parameters are sent separately from the SQL text, so user input can never change the structure of the query.
  2. 2. What does ttl do in conn.query(sql, ttl=300)?

    ttl is the time to live of the cached result. After it expires the next call queries the database again.
  3. 3. Where should database passwords live in a Streamlit project?

    secrets.toml (or the host's secrets setting) keeps credentials out of source control. Read them via st.secrets or st.connection.
  4. 4. After inserting a row, a cached conn.query still shows the old data. What fixes it?

    The cached result is valid until ttl expires. Clearing the cache after a write forces a fresh query.

Summary

  • st.connection(name, type="sql") creates a cached connection from secrets.toml; conn.query(sql, ttl=...) returns a cached DataFrame.
  • Always pass user values with parameters, never by pasting them into SQL.
  • Aggregate and filter in the database; load only what the page shows.
  • Write through conn.session, then clear caches so reads are fresh.
  • Keep SQL in plain functions so they can be tested outside the UI.
  • Keep credentials in secrets, not in code.

Next: APIs and Secrets