SQL, NoSQL, and Why I Started Kanta DB
SQL is still the default for a reason…
If I were building a conventional Python service today, I would probably start with PostgreSQL, SQLAlchemy and Alembic. PostgreSQL gives me real UUIDs, JSONB, arrays, transactions, constraints and excellent indexing. SQLAlchemy maps most of that cleanly into Python. Alembic keeps schema changes explicit and versioned.
That gets quite far before anything starts to annoy me.
… but the model has edges
A database schema and a Python data structure are not quite the same thing.
With SQLAlchemy I can define typed models, use uuid.UUID directly against PostgreSQL UUID columns, map JSONB to Python containers, and keep most routine conversions out of my own code. That is considerably better than treating every database value as a string or manually assembling SQL.
Still, the ORM model becomes a special kind of object. It carries columns, relationships, session behavior and persistence rules. If the rest of the program wants plain application structures, I either let database concerns spread outward or add another conversion layer.
The problem gets more visible when the schema changes.
Adding a field to a Python structure feels trivial. Adding a column to persisted data means changing the model and creating a migration. More involved changes need data transformations and compatibility decisions. Alembic handles this sensibly, but I still have to maintain a history of structural changes just so old rows can become new rows.
That is not a flaw in PostgreSQL. The database has committed to a schema, so changing it has consequences.
History creates another layer. If I want to know who changed a value, when they changed it, or what the record looked like last Tuesday, I need to model that. I can add audit tables, triggers, timestamp columns or an event-sourcing layer. PostgreSQL can support all of these quite well.
But the normal row still represents what exists now. The history remains something I build around it.
Alternatives to SQL
MongoDB removes some friction
MongoDB moves the shape of the data much closer to the shape I use in the program.
I can store nested documents directly, add fields without altering a table, and let older and newer records coexist while the application understands both. That often makes schema evolution less ceremonial. Instead of migrating the whole database first, I can sometimes upgrade old documents when I read or modify them.
The schema still exists. My code still expects certain fields with certain meanings. MongoDB simply gives me more freedom about when I enforce that agreement.
Its change streams also get much closer to something I care about. I could subscribe to changes and react when documents change, rather than polling or inventing a notification layer next to the database.
That already makes MongoDB interesting for synchronized applications.
I would still have to decide how a client catches up after disconnecting, how much of a changed document to send, how to authorize subscriptions, and how to relate application state to the database’s stream of changes. MongoDB gives me the raw material, not the whole synchronization model.
Redis gives me excellent pieces
Redis is wonderfully practical.
If I need a cache, queue, counter, sorted set, distributed lock or ephemeral shared state, Redis usually has a compact answer.
Of course when my data comes in JSON or other structured format, it is also completely up to me to break it down to those Redis primitives, or just dumps in as a whole ignoring all the fine tools.
Pushing and pulling
Let me know when anyone touches my data
Firebase starts from synchronization
Firebase takes a more direct route. Its databases treat live client updates as part of the product.
I can attach a listener to data and have the client receive changes as they happen. Offline behavior and reconnection also belong to the same system instead of appearing later as a WebSocket project.
That is attractive.
We’ve got all the changes
MongoDB has the strong built-in answer on this. Change Streams let me watch a collection, database, or whole deployment and receive inserts, updates, deletes, etc. Updates normally include the changed fields, and each event carries a resume token, so I can reconnect and continue from where I left off as long as the oplog still contains that point.
PostgreSQL can also expose actual database changes through logical decoding and replication. Redis Change Streams offer a similar solution in that realm.
Change-data-capture systems build very capable pipelines on top of that, but then we need to parse SQL statements or Redis commands and keep track of the state ourselves.
Publish and subscribe
On PostgreSQL and Redis also offer oldskool message channels.
I could use LISTEN/NOTIFY or PUB/SUB and emit a notification from the same transaction that modifies the data. That avoids some of the ugliness of an unrelated message bus.
But I still have to create and receive notification messages to decide what to read and what to send. And it comes with race conditions between the actual change and the notification.
For my problem it felt like starting quite far below the abstraction I actually wanted.
I did not merely want to know that state had changed.
I wanted the change itself, and the state before and after.
Maybe I should make my own?
Having used all of the above, and always finding the ORM creep over my codebase becoming unbearable, I finally began working on an idea I had quietly developed for years and years.
To make my own database. You know, something they always tell you to not even try.
Let the log to be the database
That became the starting point for Kanta.
Instead of storing the latest state as the primary record and adding history around it, I wanted to store changes.
An object starts with empty state. Every later operation records only what changed, together with a timestamp and whatever other metadata belongs to that change.
The current state comes from applying the log. Now history no longer needs its own schema. Queries no longer need to find the most recent timestamp because they work on the state object at any given moment.
One structure all the way through
Python adds a temptation to simplify things: msgspec gives me typed, compact data structures with very fast serialization. Similar to dataclasses or Pydantic, it handles nested structures and common native types such as UUIDs, enums and datetimes without turning the objects into ORM entities. Defining your data structures becomes this simple:
struct Data(msgspec.Struct):
servername: str
users: dict[UUID, User]
That means I can use the same kind of object throughout the program. I can serialize it. I can send it over the network. I can persist its changes. I can reconstruct it on the other side.
I do not need one class for my application, another for SQLAlchemy, another schema for serialization, and little conversion functions mediating between all of them.
It’s alive
I hacked together a simple JSONL logger, one change per line, with a jsondiff change record on it. It wasn’t a binary format like I might have preferred, but it is something dead simple to edit and debug.
Reads are simply reads from Python variables, much faster than Redis or any external database!
Writes needed additional thinking. Rather than tell the database what to change, we would edit the state and the database would persist a change record.
with kanta.transaction(action="new_user"):
data.users[uuid7()] = User(...)
Notice there is no async with or await in there although we are working on async Python. The transaction is immediate and synchronous. You don’t require any locks or synchronization around it. The states before and after is stored and compared to produce the change diff. In case of error the previous state is restored, rolling back anything already done in that transaction.
The changes are persisted to disk by a background thread, on an append-only file that is easily repaired in case of any corruption due to power loss or crashes.
That was roughly the point where Kanta stopped looking like a database experiment and started looking like a coherent model to build on.
Why not try it on production?
After trialling with my own projects, I quickly wired it on a more serious application which had heavier data and many users. This answered my fears about possible performance issues, because after all we were in fact using JSON for a database, and in a logdb structure that others to my knowledge had not since the early ages of computing.
Sure enough, replaying large changesets on application startup became slow over time, so I added full snapshot lines to avoid the long replays from initial revision. After that the performance has exceeded all my needs.
But the main point is not performance, it is simplicity. By taking the assumption that our app can live in a single worker process and maintain the full state in its memory, we remove most of the problems that come with the typical database.
Due to log structure, rewinding history comes for free. I have even undone a series transactions in the middle of history — to rescue a user who had deleted part of the project and then done further changes after.
This website is also built on Kanta (Pagerite CMS).
Migrations
The single-process model removes a great deal of machinery, but it does not remove the fact that software changes.
Nobody ever liked migrations. They are the burden of making any maintenance changes over data. Adding another SQL migration or another Mongo legacy fallback and update branch is just too much trouble, so you rather avoid those changes.
Here again msgspec does much of the heavy lifting. If we want to add or remove a field, simply put the new field on the data structures with a default value, or remove any old field. It will silently migrate to new format. If the format being loaded does not match the structures, we get an error saying what and where is wrong.
Cool and simple, but not sufficient for a database.
Every now and then we want to rename a field, change data types or outright restructure the whole data, possibly fetching new outside data while at that (I’ve done that). This requires an actual migration function that knows what it is doing.
def migrate_v1(d: dict) -> None:
"""Rename counter to total"""
d["total"] = d["counter"]
The format is simple: database revision number comes directly from the function name. The function manipulates plain dict format so that we don’t need to maintain msgspec.Structs of older versions. Docstring gives the logged description of what was done.
To avoid littering the rest of our program with these, the migrations can be put on their own Python module, that we simply refer to with Python module path when defining the database:
kanta = Kanta("foo.kantadb", migrations="foo.migrations")
The changeset metadata and snapshots contain the version number they represent, and we apply all found migrate functions upwards from that version, and to complete the migration do the msgspec conversion in any case. If any of that resulted changes, we log and store the migrations done, and snapshot after.
Oldest migration functions can also be removed, when such versions no longer need to be supported, keeping this whole migration system manageable.
On disk format
The default format remains deliberately unsophisticated: JSON records, one per line. Msgspec encodes binary and other types into it.
Before publishing, I wanted to address a critique I would certainly myself give about the lack of binary support, by implementing that as an option.
While with JSONL we can seek for newlines from end of file to find a snapshot, the binary data itself could contain any markers and even its own sub databases that could confuse the parser.
We can read from the start of file for a fully deterministic approach, but if the database were large we’d prefer to seek near the end. I also wanted to maintain it append only, so we couldn’t simply write at start the snapshot offset.
The solution is two-fold:
- Unpredictable random nonce as syncword
- Blake3 hash for integrity verification
The change payloads themselves are currently encoded with MessagePack, which is readily supported in msgspec.
I would like to replace that with something closer to Protocol Buffers, not storing field names and types at all, taking advantage of that msgspec already knows the structure and types of my structs, and as such should be able to derive a compact binary representation from that same information.
That would keep the property I care about most: one definition of the data structure, rather than a Python model plus an ORM model plus a serialization schema.
Tooling
Kanta also has a small CLI for inspecting databases directly. It can replay a database, inspect selected ranges of its history, load the application’s actual data types and run migrations when needed, and dump the resulting state as JSON. I have found this particularly useful for treating the log as something I can examine and reason about directly, rather than as an opaque persistence file. A good tool also beats treating it as a text file.
The logs shown are similar to those printed runtime whenever any transaction is made.
The database is only half of the state
Keeping the authoritative state in memory makes another thing difficult to ignore: most interactive applications already keep another copy somewhere else.
The browser has one too.
Once the database itself records an ordered stream of changes, using that same stream for synchronization becomes an obvious next step.
Synchronizing stores over WebSocket
The FastAPI app sends changes over a WebSocket and applies incoming changes back to the database.
On each client, we maintain a shadow copy, that is the last seen server state, and the client’s current working state in its native Vue Pinia store. Or Svelte or React equivalents.
The store is the connecting link that offers reactivity all the way from database to the user interface and back.
The client may work offline or concurrently with other clients, and the changes are be merged when they land on the server, using a three way merge with automatic resolution. Combined with overwrite and validation/reject — we definitely don’t want merge conflicts to require manual git style solution.
I currently have this machinery embedded in applications rather than packaged as part of Kanta. The next step is to extract it into a separate Kanta-compatible synchronization module, with adapters also for the Svelte and React equivalents.
That is probably another article.
Give it a spin
Kanta grew out of wanting less machinery between application state, persistence, history and synchronization. The result is deliberately opinionated, geared mainly for FastAPI and other asyncio Python stacks.
It will not replace PostgreSQL, MongoDB or Redis, nor is that the goal. For the kinds of application that fit this model, though, it has made the database feel much less like a separate subsystem.
uv add kanta
Add to your project using uv or read more at git.zi.fi.
Demo.py
import asyncio
import sys
from datetime import UTC, datetime, timedelta
from pathlib import Path
import msgspec
from kanta import Kanta
from kanta.callbacks import DictPre
from kanta.logging import configure_logging
filename = Path(__file__).with_name("demo.kantadb")
# For demonstration purposes, we use "original v0" and "modified v1" in this same script
# Normally your app would only have the latest supported data model
class Data(msgspec.Struct): # type: ignore - intentionally redefined later
users: dict[str, dict] = {}
counter: int = 0
kanta_v0 = Kanta(filename, Data())
@kanta_v0.bootstrap
def bootstrap(data: Data) -> None:
"""Create the initial admin user."""
data.users["userid001"] = {"name": "Alice", "role": "admin"}
# Redefinition to simulate new version
class Data(msgspec.Struct):
users: dict[str, dict] = {}
total: int = 0 # Replaces old counter field
lang: str = "en" # New field
def migrate_v1(d: dict) -> None:
"""Rename counter to total"""
d["total"] = d["counter"]
kanta_v1 = Kanta(filename, Data(), migrations=sys.modules[__name__])
@kanta_v1.logfmt
def resolve_user(value: str, path: str, previous: DictPre) -> str | None:
"""Resolve user ids to names from the database state itself."""
if path != "$user" and not path.startswith("users."):
return None
return previous.get("users", {}).get(value, {}).get("name")
async def main() -> None:
filename.unlink(missing_ok=True)
print("Database creation with v0 schema and basic transactions:\n")
# Open and close automatically; you can also `await kanta.open()` instead
async with kanta_v0 as kanta:
with kanta.transaction(action="create", user="userid001") as data:
data.users["userid002"] = {"name": "Bob", "role": "user"}
with kanta.transaction(action="update", user="userid001") as data:
data.users["userid002"]["role"] = "editor"
data.counter = 1
# Display-only extra string, appended after the action.
with kanta.transaction(
action="export", user="userid002", extra="extra info"
) as data:
data.counter = 2
print("\nA new data model, migrations and logfmt pretty names:\n")
async with kanta_v1 as kanta:
with kanta.transaction(
action="update", user="userid002", extra=filename.name
) as data:
data.total += 1
try:
with kanta.transaction(action="reset", user="userid001") as data:
data.total = 99
raise ValueError("simulated failure")
except ValueError:
print(
f"\nReset rolled back: {data.total=} (we can always read data without tx)\n"
)
with kanta.transaction(action="delete", user="userid002") as data:
del data.users["userid001"]
if __name__ == "__main__":
configure_logging(debug=True)
asyncio.run(main())