# Connect Streamlit to Prisma Postgres

Source: https://docs.streamlit.io/develop/tutorials/databases/prisma-postgres

## Introduction

This guide explains how to securely access a [Prisma Postgres](https://www.prisma.io/postgres) database from Streamlit. Prisma Postgres is a managed PostgreSQL database. The guide uses [`st.connection`](https://docs.streamlit.io/develop/api-reference/connections/st.connection), Streamlit's [Secrets management](https://docs.streamlit.io/develop/concepts/connections/secrets-management), and the PostgreSQL driver. You will create a small table of people and their pets, then display the results in your app.

### Prerequisites

- The following packages must be installed in your Python environment:

  ```txt
  streamlit>=1.28
  psycopg2-binary>=2.9.6
  sqlalchemy>=2.0.0
  ```

- You need either [Node.js](https://nodejs.org/) to create a temporary database from your terminal, or a [Prisma Console](https://console.prisma.io) account to create a permanent one.
- You should have a basic understanding of [`st.connection`](https://docs.streamlit.io/develop/api-reference/connections/st.connection) and [Secrets management](https://docs.streamlit.io/develop/concepts/connections/secrets-management).

## Create a Prisma Postgres database

If you already have a Prisma Postgres database that you want to use, you can [skip to the next step](#add-the-connection-string-to-your-local-app-secrets).

1. Create a database in one of two ways:
   - To get a temporary database without an account, run the following command in your terminal:

     ```bash
     npx create-db@latest
     ```

     The command prints a connection string and a claim link. The database is deleted after 24 hours unless you claim it with that link.

   - To create a permanent database, sign in to the [Prisma Console](https://console.prisma.io), create a project with a Prisma Postgres database, and generate a connection string as described in [Connecting to your database](https://www.prisma.io/docs/postgres/database/connecting-to-your-database).

1. Copy your connection string. It should look similar to this:

   ```bash
   postgres://xxxxxxxx:xxxxxxxxxxxx@db.prisma.io:5432/postgres?sslmode=require
   ```

1. Connect to your database with [`psql`](https://www.postgresql.org/docs/current/app-psql.html) or another SQL client, using the connection string from the previous step.

   ```bash
   psql "postgres://xxxxxxxx:xxxxxxxxxxxx@db.prisma.io:5432/postgres?sslmode=require"
   ```

1. Run the following statements to create a table with some example values:

   ```sql
   CREATE TABLE mytable (
       id SERIAL PRIMARY KEY,
       name VARCHAR(80),
       pet VARCHAR(80)
   );

   INSERT INTO mytable (name, pet)
   VALUES ('Mary', 'dog'), ('John', 'cat'), ('Robert', 'bird');
   ```

## Add the connection string to your local app secrets

1. If you do not already have a `.streamlit/secrets.toml` file in your app's root directory, create an empty secrets file.
1. Add your connection string to your app's `.streamlit/secrets.toml` file with two changes: replace the `postgres://` scheme with `postgresql+psycopg2://`, and replace the `db.prisma.io` host with `pooled.db.prisma.io`.

   ```toml
   # .streamlit/secrets.toml

   [connections.prisma_postgres]
   url = "postgresql+psycopg2://xxxxxxxx:xxxxxxxxxxxx@pooled.db.prisma.io:5432/postgres?sslmode=require"
   ```

   `st.connection` passes this URL to SQLAlchemy, which doesn't accept the `postgres://` scheme. Naming the driver in the scheme tells SQLAlchemy to use `psycopg2` from your requirements.

   Every Prisma Postgres database has a direct host (`db.prisma.io`) and a pooled host (`pooled.db.prisma.io`) that share the same credentials. The pooled host routes your app's queries through a connection pooler, so use it for your Streamlit app. Use the direct host for administrative tools like `psql`. Keep `sslmode=require` in the URL because Prisma Postgres only accepts encrypted connections.

   > **Important**
   >
   > Add this file to `.gitignore` and don't commit it to your GitHub repo!

## Write your Streamlit app

1. Copy the code below to your Streamlit app and save it.

   ```python
   # streamlit_app.py

   import streamlit as st

   # Initialize connection.
   conn = st.connection("prisma_postgres", type="sql")

   # Perform query.
   df = conn.query("SELECT * FROM mytable ORDER BY id;", ttl="10m")

   # Print results.
   for row in df.itertuples():
       st.write(f"{row.name} has a :{row.pet}:")
   ```

   The `st.connection` object above handles secrets retrieval, setup, query caching and retries.

   By default, `query()` results are cached without expiring. Setting the `ttl` parameter to `"10m"` ensures the query result is cached for no longer than 10 minutes. You can also set `ttl=0` to disable caching. Learn more in [Caching](https://docs.streamlit.io/develop/concepts/architecture/caching).

1. Run your Streamlit app.

   ```bash
   streamlit run streamlit_app.py
   ```

   If everything worked out (and you used the example table we created above), your app should look like this:

   ![Finished app screenshot](https://docs.streamlit.io/images/databases/streamlit-app.png)

## Connecting to a Prisma Postgres database from Community Cloud

This tutorial assumes a local Streamlit app, but you can also connect to a Prisma Postgres database from apps hosted on Community Cloud. The additional steps are:

- Add a [`requirements.txt`](https://docs.streamlit.io/deploy/streamlit-community-cloud/deploy-your-app/app-dependencies) file to your repo. Include all the packages listed in [Prerequisites](#prerequisites) and any other dependencies.
- [Add your secrets](https://docs.streamlit.io/deploy/streamlit-community-cloud/deploy-your-app/secrets-management#how-to-use-secrets-management) to your app in Community Cloud.
