Introduction

🦋 Xata is a serverless data platform built on PostgreSQL. It offers additional features like full-text search, file attachments, and built-in integrations, simplifying application development and management.

This guide explains how to securely access an Xata instance from Streamlit Cloud ☁️. It uses st.connection 🔌, XataClient and Streamlit's Secrets management.

Create a Xata Database

If you are new to Xata, you can take a look at my article on Getting Started with Xata.

Here is an example table (pets) that I will be using for this demo. You can modify the table as you wish.

Add XATA API and DB URL to your local app secrets

Your local Streamlit app will read secrets 🔒 from a file .streamlit/secrets.toml in your app's root directory. Create this file if it doesn't exist yet and add the database information as shown below:

XATA_API_KEY="REDACTED_API_KEY"
XATA_DATABASE_URL="REDACTED_DB_URL"

The Xata API key can be created in the account settings and do store it somewhere safe. If for some reason you lose your API KEY, you can always create it again in the settings. The DB URL can be found in your database settings.

Installation

To install the Xata SDK for Python enter the following command:

pip install xata

The xata package is a standalone SDK library that features XataClient. By installing the package, you can import the SDK into your Python project and start building 🏗️ on top of Xata.

Add Xata to your requirements file

Add the Xata package to your requirements.txt file, preferably pinning its version (replace x.x.x with the version you want installed):

# requirements.txt
xata==x.x.x

Write your Streamlit app

Copy the code below to your Streamlit app and run it 🏃‍♂️. Make sure to adapt the name of your database and table.

# streamlit_app.py

import streamlit as st
from xata.client import XataClient


# Initialize connection.
# Uses st.cache_resource to only run once.
@st.cache_resource
def init_connection():
    return XataClient(api_key=st.secrets["XATA_API_KEY"], db_url=st.secrets["XATA_DATABASE_URL"])

client = init_connection()

# Pull data from the table.
# Uses st.cache_data to only rerun when the query changes or after 10 min.
@st.cache_data(ttl=600)
def get_data():

    data = client.data().query("pets", {
        "columns": []
    })
    items = data["records"]
    items = list(items)
    return items

items = get_data()

# Print results.
for item in items:
    st.write(f"{item['Name']} has a :{item['Pet']}:")

Notice the usage of st.cache_data above? Omitting it would cause Streamlit to execute the query with each app rerun, such as during a widget interaction. However, with st.cache_data, the query executes only when there's a change in the query or after a 10-minute interval (controlled by ttl). Be cautious: if your database updates more frequently, adjust the ttl or consider removing caching to ensure viewers consistently access the latest data. For further insights, refer to the Caching section.

If everything went smooth, your app would look like this:

Copy your app secrets to the cloud

Since the secrets.toml file mentioned above isn't included in GitHub commits, you'll need to independently provide its contents to your deployed app (on Streamlit Community Cloud). Navigate to the app dashboard and access the app's seetings. Select "Edit Secrets" and paste the content of secrets.toml into the designated text area. Further details on managing secrets can be found in the Secrets management documentation.

Conclusion

In conclusion, connecting Streamlit to Xata opens up a world of possibilities for data scientists, developers, and businesses alike. By seamlessly integrating these two powerful platforms, users can leverage Streamlit's intuitive interface and Xata's robust data management capabilities to create dynamic and interactive applications that deliver valuable insights.

Thanks for the read :)