The MiniDataAPI is a persistence API specification that designed to be small and relatively easy to implement across a wide range of datastores. While early implementations have been SQL-based, the specification can be quickly implemented in key/value stores, document databases, and more.
TODO: Write why we're doing this, in a really interesting way! __
Work in Progress
The MiniData API spec is a work in progress, subject to change. While the majority of design is complete, expect there could be breaking changes.
We connect or construct the database by passing in a string connecting to the database endpoint or a filepath representing the database’s location. While this example is for a SQLite running in memory, other databases such as PostgreSQL, Redis, MongoDB, would instead use a URI pointing at the database’s filepath or endpoint.
db = database(':memory:')__
We use a create() method attached to Database object (db in our example) to create the tables. Depending on the database type, this method can include transforms - the ability to modify the tables.
class User: name:str; email: str; year_started:int
users = db.create(User, pk='name')
users __
<Table user (name, email, year_started)>
From the table objects we can extract a Dataclass version of our tables. Usually this is given an singular uppercase version of our table name, which in this case is User.
UserDC = users.dataclass()__
If no pk is provided, id is assumed to be the primary key.
class Todo: id: int; title: str; detail: str; status: str; name: str
todos = db.create(Todo)
todos __
<Table todo (id, title, detail, status, name)>
Let’s extract the dataclass for Todo.
TodoDC = todos.dataclass()__
The MiniData API spec supports compound primary keys, where more than one column is used to identify records.
class Publication: authors: str; year: int; title: str
publications = db.create(Publication, pk=('authors', 'year'))
PublicationDC = publications.dataclass()__
Add a new record to the database. We want to support as many types as possible, for now we have tests for Python classes, dataclasses, and dicts. Returns an instance of the new record.
Here’s how to add a record using a Python class.
users.insert(User(name='Braden', email='[email protected]', year_started=2018))__
User(name='Braden', email='[email protected]', year_started=2018)
Let’s add another user, this time via a dataclass.
users.insert(UserDC(name='Alma', email='[email protected]', year_started=2019))__
User(name='Alma', email='[email protected]', year_started=2019)
And now Charlie gets added via a Python dict.
users.insert({'name': 'Charlie', 'email': '[email protected]', 'year_started': 2018})__
User(name='Charlie', email='[email protected]', year_started=2018)
And now all three methods for TODOs
todos.insert(Todo(title='Write MiniDataAPI spec', status='open', name='Braden'))
todos.insert(TodoDC(title='Implement SSE in FastHTML', status='open', name='Alma'))
todo = todos.insert(dict(title='Launch FastHTML', status='closed', name='Charlie'))
todo __
Todo(id=3, title='Launch FastHTML', detail=None, status='closed', name='Charlie')
Let’s do the same with the Publications table.
publications.insert(Publication(authors='Alma', year=2019, title='FastHTML'))
publications.insert(PublicationDC(authors='Alma', year=2030, title='FastHTML and beyond'))
publication= publications.insert((dict(authors='Alma', year=2035, title='FastHTML, the early years')))
publication __
Publication(authors='Alma', year=2035, title='FastHTML, the early years')
Get a single record by entering a primary key into a table object within square brackets. Let’s see if we can find Alma.
users['Alma']__
User(name='Alma', email='[email protected]', year_started=2019)
If no record is found, a NotFoundError error is raised. Here we look for David, who hasn’t yet been added to our users table.
try: users['David']
except NotFoundError: print(f'User not found')__
User not found
Here’s a demonstration of a ticket search, demonstrating how this works with non-string primary keys.
todos[1]__
Todo(id=1, title='Write MiniDataAPI spec', detail=None, status='open', name='Braden')
Compound primary keys can be supplied in lists or tuples, in the order they were defined. In this case it is the authors and year columns.
Here’s a query by compound primary key done with a list:
publications[['Alma', 2019]]__
Publication(authors='Alma', year=2019, title='FastHTML')
Here’s the same query done with a tuple:
publications[('Alma', 2030)]__
Publication(authors='Alma', year=2030, title='FastHTML and beyond')
Get zero to many records by entering values with parentheses searches. If nothing is in the parentheses, then everything is returned.
users()__
[User(name='Braden', email='[email protected]', year_started=2018),
User(name='Alma', email='[email protected]', year_started=2019),
User(name='Charlie', email='[email protected]', year_started=2018)]
We can order the results.
users(order_by='name')__
[User(name='Alma', email='[email protected]', year_started=2019),
User(name='Braden', email='[email protected]', year_started=2018),
User(name='Charlie', email='[email protected]', year_started=2018)]
We can filter on the results:
users(where="name='Alma'")__
[User(name='Alma', email='[email protected]', year_started=2019)]
We can also query on compound Primary keys, either individually or in combination.
publications(where="year='2019'")__
[Publication(authors='Alma', year=2019, title='FastHTML')]
publications(where="authors='Alma' and year=2019")__
[Publication(authors='Alma', year=2019, title='FastHTML')]
Update an existing record of the database. Must accept Python dict, dataclasses, and standard classes. Uses the primary key for identifying the record to be changed. Returns an instance of the updated record.
Here’s with a normal Python class:
users.update(User(name='Alma', year_started=2099, email='[email protected]'))__
User(name='Alma', email='[email protected]', year_started=2099)
Now with a dataclass, sending Alma further in time:
users.update(UserDC(name='Alma', year_started=2199, email="[email protected]"))__
User(name='Alma', email='[email protected]', year_started=2199)
If the primary key doesn’t match a record, raise a NoteFoundError.
John hasn’t started with us yet so doesn’t get the chance yet to travel in time.
try: users.update(User(name='John', year_started=2024, email='[email protected]'))
except NotFoundError: print('User not found')__
User not found
Delete a record of the database. Uses the primary key for identifying the record to be removed. Returns a table object.
Charlie decides to not travel in time. He exits our little group.
users.delete('Charlie')__
<Table user (name, email, year_started)>
If the primary key value can’t be found, raises a NotFoundError.
In John’s case, he isn’t time travelling with us yet so can’t be removed.
try: users.delete('John')
except NotFoundError: print('User not found')__
User not found
Deleting records with compound primary keys requires providing the entire key.
publications.delete(['Alma' , 2035])__
<Table publication (authors, year, title)>
Attempting to delete a record with a compound primary without all of those keys throws a NotFoundError with a string that provides information as to why the deletion failed.
try: publications.delete('Alma')
except NotFoundError as e: print(str(e))__
Need 2 pk
The xtra action adds a filter to queries and DDL statements. This makes it easier to limit users (or other objects) access to only things for which they have permission.
For example, if we query all our records below, you can see todos for everyone.
todos()__
[Todo(id=1, title='Write MiniDataAPI spec', detail=None, status='open', name='Braden'),
Todo(id=2, title='Implement SSE in FastHTML', detail=None, status='open', name='Alma'),
Todo(id=3, title='Launch FastHTML', detail=None, status='closed', name='Charlie')]
Now we use .xtra to constrain results just to Charlie.
todos.xtra(name='Charlie')__
Now if we loop over all the records again, only those assigned to Charlie will be displayed.
todos()__
[Todo(id=3, title='Launch FastHTML', detail=None, status='closed', name='Charlie')]
Is Alma in the Users table? Or, to be technically precise, is the item with the specified primary key value in this table?
'Alma' in users __
True
Poor John never joined the users table, we show that below:
'John' in users __
False
Also works with compound primary keys, as shown below. You’ll note that the operation can be done with either a list or tuple.
['Alma', 2019] in publications __
True
And now for a False result, where John has no publications.
('John', 1967) in publications __
False
- fastlite - The original implementation, only for Sqlite
- fastsql - An SQL database agnostic implementation based on the excellent SQLAlchemy library.