Using a Database

Using a Database#

App development often involves the use of a database. FastHTML supports whatever database you want to use, but is currently configured to use SQLite by default. This tutorial will briefly cover how to use a SQLite database with FastHTML.

Note that, at the time of writing, database support and opinionated workflows are largely undocumented, so the material here is drawn from a variety of examples and personal experience.

What do we use a database for?#

Databases in web applications can serve a variety of purposes. They can be used to store user data, application data, or any other data that needs to be persisted between requests.

You can create a database by passing a db_file argument to the fast_app function: if you do so, fast_app will return database tables and dataclasses. Thus functionality is marked as “experimental” so we will stick to setting up databases separately for now. See this example for more details.

By default, FastHTML uses SQLite and examples use the fastlite library to create and manage the database. FastLite supports the MiniDataAPI spec.

FastLite only supports SQLite. FastSQL, also by answer.ai, supports a wider range of databases. You can also just use e.g. SQLAlchemy or any other database library; there does not appear to be an inherent requirement to use FastLite.

FastLite Syntax#

Let’s briefly go over the FastLite syntax. For more, again, see the MiniDataAPI spec and the FastLite documentation.

Create a database and table#

In the next cell, we will:

  1. Create a SQLite database

  2. Create a table called “users”

  3. Create a dataclass for type hinting

See the comments for more details.

from fasthtml.common import *

# creates the SQLite database in the data/example.db file
db = database('data/example.db')

# make a table called "users"
users = db.t.users

# create the table if it doesn't exist
if users not in db.t:
    # create the table with the following columns:
    # - id: int
    # - name: str
    # - email: str
    # - pk: 'id' (primary key)
    users.create(id=int, name=str, email=str, pk='id')

# create a dataclass for type hinting
User = users.dataclass()

Add data to the table#

Next, we will create a few example users and add them to the database

users.insert(User(name="John Doe", email="john@doe.com"))
users.insert(User(name="Jane Doe", email="jane@doe.com"))
users.insert(User(name="Dan L", email="dan@doe.com"))

Users(id=3, name='Dan L', email='dan@doe.com')

Retrieve data from the table#

We can see the contents of the table by calling the table object:

users()

[Users(id=1, name='John Doe', email='john@doe.com'),
 Users(id=2, name='Jane Doe', email='jane@doe.com'),
 Users(id=3, name='Dan L', email='dan@doe.com')]

And we can retrieve a specific entry using the primary key in brackets:

users[2]

Users(id=2, name='Jane Doe', email='jane@doe.com')

Deleting and Updating Data#

# Delete a user
users.delete(2)

# Update a user
users.update({'id': 3, 'name': 'David L.'})

# View the updated table
users()

[Users(id=1, name='John Doe', email='john@doe.com'),
 Users(id=3, name='David L.', email='dan@doe.com')]

This covers the basics of using a database with FastHTML. See the FastLite documentation for more details. You should familiarize yourself with at least the following methods:

  • create

  • transform

  • transform_sql

  • update

  • insert

  • upsert

  • lookup

Run arbitrary SQL queries#

You can run arbitrary SQL queries and get back the results using the db.q method:

db.q("SELECT * FROM users;")

[{'id': 1, 'name': 'John Doe', 'email': 'john@doe.com'},
 {'id': 2, 'name': 'Jane Doe', 'email': 'jane@doe.com'},
 {'id': 3, 'name': 'Dan L', 'email': 'dan@doe.com'}]

Rendering Tables with Custom Components#

https://docs.fastht.ml/tutorials/e2e.html#drawing-rooms

It can be useful to specify how we want tables/their corresponding dataclasses to be rendered. We can do this by patching the dataclass to add an __ft__ method.

This is one of the clearest benefits of using a FastLite/MiniDataAPI-based approach to database management. It may not be as straightforward to control the rendering of data in a more custom database management system.

@patch
def __ft__(self:User):
    return Div(f"Name: {self.name}, Email: {self.email}")

Now, when we refer to a user in the context of a FastHTML component, it will be rendered using our custom component:

Html(users[1])

<html>
  <div>Name: John Doe, Email: john@doe.com</div>
</html>

We could also define a render function and pass it to fast_app as shown here. The approach in that example would allow us to initialize a database with a “default” table, dataclass, and render function. For now, I prefer the separate approach as it enables greater clarity and flexibility.

Summary#

This quick introduction to using databases with FastHTML has equipped you with the basics of using SQLlite databases via FastLite in your FastHTML app.

Contents