Datasette Apps: Host custom HTML applications inside Datasette
Did you know that > 70 % of data‑science projects stall because the insights never leave the notebook? Imagine turning every exploratory notebook into a share‑ready, interactive web app without leaving the familiar Datasette environment—and doing it with just a few lines of HTML, CSS, and JavaScript. In this article you’ll learn how to embed custom front‑ends directly inside Datasette, turning raw query results into polished, production‑ready dashboards for machine‑learning teams.What Datasette Apps Are & When to Use Them
Datasette apps are lightweight plug‑ins that serve custom HTML, CSS, and JavaScript alongside Datasette’s built‑in API. They’re perfect when you want to expose data‑first views—like model‑performance dashboards or data‑exploration portals—without spinning up a full Flask or Django stack. What I love about this approach is that the SQLite database stays at the core, so versioning, data integrity, and query performance are baked in. Sound familiar? You’ve probably seen a static report in a Jupyter notebook that you wish could be interactive. - Start simple: a bar chart of AUC scores - Scale up: a live inference widget that queries a SQLite view that loads a pickled scikit‑learn model - Manage growth: add authentication with datasette‑auth and publish to a static‑site host In my experience, the biggest win is cutting the “notebook‑to‑presentation” lag. Teams can just point a colleague at a URL and see real‑time insights.Setting Up the Environment
First, get the gear in place. I usually pull in a fresh virtual environment because that keeps things tidy. ```bash python -m venv venv source venv/bin/activate pip install datasette ``` Now, create a sample SQLite database. For illustration, let’s store a toy churn model. ```sql -- churn.db CREATE TABLE customers (id INTEGER PRIMARY KEY, age INTEGER, tenure INTEGER, churn INTEGER); INSERT INTO customers (age, tenure, churn) VALUES (25, 12, 0), (45, 24, 1), (35, 18, 0); ``` Add a `model_predictions` view that loads a pickled scikit‑learn model. Pickle the model first: ```python # train.py from sklearn.linear_model import LogisticRegression import pickle import sqlite3 X = [[25,12],[45,24],[35,18]] y = [0,1,0] clf = LogisticRegression().fit(X, y) conn = sqlite3.connect('churn.db') conn.execute(''' CREATE TABLE IF NOT EXISTS model_blob (id INTEGER PRIMARY KEY, blob BLOB) ''') conn.execute('INSERT INTO model_blob (blob) VALUES (?)', (pickle.dumps(clf),)) conn.commit() conn.close() ``` Run it and you’ll have a blob stored. Next, create a view that returns predictions. ```sql CREATE VIEW model_predictions AS SELECT c.id, c.age, c.tenure, CASE WHEN pickle.loads(b.blob).predict([[c.age, c.tenure]])[0] = 1 THEN 'Churn' ELSE 'Stay' END AS prediction FROM customers c JOIN model_blob b ON 1=1; ``` Now, add a `metadata.json` that registers an app. ```json { "app": { "title": "Churn Dashboard", "path": "/app", "index": "index.html", "static": "static" } } ``` The folder structure should look like this: ``` churn.db metadata.json index.html static/ style.css script.js ``` Run the server: ```bash datasette churn.db -p 8001 ``` Open `http://localhost:8001/churn/app` and you’ll see the front‑end load. That’s the barebones setup.Building the Front‑End
The front‑end is pure HTML/JS, so you can use any charting library you like. I’ll show a tiny example with Chart.js because it’s straightforward.Model Predictions
``` ```javascript // static/script.js fetch('/churn/model_predictions.json') .then(res => res.json()) .then(data => { const labels = data.map(row => `Cust ${row.id}`); const churnCounts = data.reduce((acc, row) => { if (row.prediction === 'Churn') acc.push(1); else acc.push(0); return acc; }, []); const ctx = document.getElementById('predChart').getContext('2d'); new Chart(ctx, { type: 'bar', data: { labels, datasets: [{ label: 'Churn', data: churnCounts, backgroundColor: 'rgba(255, 99, 132, 0.5)' }] }, options: { responsive: true } }); }) .catch(err => console.error(err)); ``` Now the page will pull data from the SQLite view and render it instantly. If you want to add more interactivity—say, filter by tenure—you simply add a new query endpoint and tweak the JS. But here’s the thing: you’re not stuck with vanilla JS. You could drop in React, Vue, or even a Jinja2 template if you’re comfortable with server‑side rendering. The key is that Datasette handles the data layer, so you can focus on the UI.Why It Matters
Speed to insight is a game‑changer. Teams that can view a live dashboard instead of digging through notebooks save hours. Governance is also a win. Because the database, the view, and the UI live in the same repo, audit trails are automatic. Cost‑effective scaling: just point your repo to Netlify or Vercel, and you get a static host that serves the app. No heavy web frameworks, no servers to maintain. In terms of performance, SQLite shines for read‑only analytics. If you hit millions of rows, the query planner is pretty good. For heavy inference, cache the results or push the model to a serverless function; the front‑end stays snappy because it runs in the browser.Actionable Takeaways & Next Steps
- **Checklist** - Install Datasette - Create your SQLite database and any model views - Add `metadata.json` with an `app` entry - Build `index.html` and accompanying assets - Test locally, then deploy to a static host - **Best‑practice tips** - Keep the UI minimal; the heavy lifting is the data. - Cache large queries using Datasette’s `cache=` option. - Store model artifacts in separate SQLite tables; keep them immutable. - **Where to go from here** - Add authentication with `datasette-auth` to protect sensitive data. - Use Jinja2 in `metadata.json` to server‑render templates. - Publish a public catalog of reusable Datasette apps on your organization’s website. What I think is the most exciting part is that you can iterate on the front‑end in seconds while the back‑end stays versioned in Git. That’s data science at its most productive.Frequently Asked Questions
How do I host a custom HTML dashboard inside Datasette?
Add an app entry to metadata.json that points to your index.html (or a folder). Datasette will serve the file at /‑/app and you can use its JSON API to pull data into the page.
Can I run scikit‑learn models directly from a Datasette app?
Yes. Store a pickled estimator in a SQLite BLOB column, expose a SQL view that loads the model with pickle.loads, and call that view via AJAX from your front‑end.
What are the performance limits of Datasette apps for ML dashboards?
Datasette is built on SQLite, so it handles millions of rows efficiently for read‑only queries. For heavy inference, cache results or off‑load to a serverless function; the UI remains snappy because the HTML/JS runs in the browser.
Is it possible to secure a Datasette app with login?
Datasette provides plug‑ins like datasette-auth and datasette-login. By adding them to your metadata.json, you can restrict the app to authenticated users or specific groups.
How does a Datasette app differ from a Flask or Streamlit app?
Datasette focuses on data‑first delivery: the database and API are baked in, and the front‑end is pure static assets. Flask/Streamlit require a separate Python server for routing and rendering, which adds deployment complexity.
Related reading: Original discussion
What do you think?
Have experience with this topic? Drop your thoughts in the comments - I read every single one and love hearing different perspectives!
Comments
Post a Comment