Metadata-Version: 2.4
Name: iceaxe_sqlite
Version: 0.1.0
Summary: A modern, fast SQLite ORM for Python.
Author-email: Pierce Freeman <pierce@freeman.vc>
Requires-Python: >=3.11
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: click>=8
Requires-Dist: pydantic<3,>=2
Requires-Dist: rich>=13
Dynamic: license-file

# iceaxe-sqlite

![Python Version](https://img.shields.io/python/required-version-toml?tomlFilePath=https%3A%2F%2Fraw.githubusercontent.com%2Fpiercefreeman%2Ficeaxe-sqlite%2Frefs%2Fheads%2Fmain%2Fpyproject.toml) [![Test status](https://github.com/piercefreeman/iceaxe-sqlite/actions/workflows/test.yml/badge.svg)](https://github.com/piercefreeman/iceaxe-sqlite/actions)

`iceaxe-sqlite` is a SQLite-focused rewrite of the original [iceaxe](https://github.com/piercefreeman/iceaxe), which is built around Postgres. The model, query, and data-access conventions from Iceaxe are still extremely effective, so this project keeps those patterns while retargeting them to SQLite.

That split is intentional. Rather than chasing a backend-agnostic API like Peewee or SQLAlchemy, `iceaxe-sqlite` stays opinionated about the datastore underneath it. The goal is for the ORM surface area to match SQLite itself, so the abstractions stay simple, predictable, and honest about what the database can do.

If you already like the way Iceaxe models tables and fetches data, this package gives you those same conventions in a form designed specifically for SQLite.

Goals are also similar:

- 🏎️ **Performance**: We want to exceed or match the fastest ORMs in Python and stay close to raw SQLite query performance.
- 📝 **Typehinting**: Everything should be typehinted with expected types. Declare your data as you
expect in Python and it should bidirectionally sync to the database.
- 🪶 **SQLite first**: Use a small async SQLite backend with no external database server.
- ⚡ **Common things are easy, rare things are possible**: 99% of the SQL queries we write are
vanilla SELECT/INSERT/UPDATEs. These should be natively supported by your ORM. If you're writing _really_
complex queries, these are better done by hand so you can see exactly what SQL will be run.

The original Iceaxe conventions are used in production at several companies. `iceaxe-sqlite` is an independent project. It's compatible with the [Mountaineer](https://github.com/piercefreeman/mountaineer) ecosystem, but you can use it in whatever
project and web framework you're using.

For comprehensive documentation, visit [https://iceaxe.sh](https://iceaxe.sh).

## Installation

Install with `uv`:

```bash
uv add iceaxe-sqlite
```

Otherwise install with pip:

```bash
pip install iceaxe-sqlite
```

## Usage

Define your models as a `TableBase` subclass:

```python
from iceaxe_sqlite import TableBase

class Person(TableBase):
    id: int
    name: str
    age: int
```

TableBase is a subclass of Pydantic's `BaseModel`, so you get all of the validation and Field customization
out of the box. We provide our own `Field` constructor that adds database-specific configuration. For instance, to make the
`id` field a primary key / auto-incrementing you can do:

```python
from iceaxe_sqlite import Field

class Person(TableBase):
    id: int = Field(primary_key=True)
    name: str
    age: int
```

Okay now you have a model. How do you interact with it?

Databases are based on a few core primitives to insert data, update it, and fetch it out again.
To do so you'll need a _database connection_. The `DBConnection` is the core class for all ORM actions against the database.

```python
from iceaxe_sqlite import DBConnection, connect

conn = DBConnection(await connect("app.db"))
```

The Person class currently just lives in memory. To back it with a full
database table, we can run raw SQL or run a migration to add it:

```python
await conn.conn.execute(
    """
    CREATE TABLE IF NOT EXISTS person (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age INT NOT NULL
    )
    """
)
```

### Magic Migrations

For local development or side projects, you can use `magic_migrate` to automatically sync your database schema with your models:

```python
await conn.magic_migrate("my_project")
```

This will:
1. Compare your current database schema against your model definitions
2. Generate a migration file if changes are detected
3. Apply all pending migrations

The migration files are written to your package's `migrations/` folder, giving you a history of schema changes.

**Recommended workflow for production:**

While `magic_migrate` is convenient for rapid local iteration, we recommend a more controlled approach before merging to production:

1. Iterate freely during development using `magic_migrate`
2. Before merging, reset your database to the production schema state
3. Run `uv run migrate generate` once to generate a single, clean migration file
4. Commit this migration file with your PR

This ensures your production migrations are clean and reviewable, while still giving you the speed of automatic migrations during development.

### Inserting Data

Instantiate object classes as you normally do:

```python
people = [
    Person(name="Alice", age=30),
    Person(name="Bob", age=40),
    Person(name="Charlie", age=50),
]
await conn.insert(people)

print(people[0].id) # 1
print(people[1].id) # 2
```

Because we're using an auto-incrementing primary key, the `id` field will be populated after the insert.
Iceaxe will automatically update the object in place with the newly assigned value.

### Updating data

Now that we have these lovely people, let's modify them.

```python
person = people[0]
person.name = "Blice"
```

Right now, we have a Python object that's out of state with the database. But that's often okay. We can inspect it
and further write logic - it's fully decoupled from the database.

```python
def ensure_b_letter(person: Person):
    if person.name[0].lower() != "b":
        raise ValueError("Name must start with 'B'")

ensure_b_letter(person)
```

To sync the values back to the database, we can call `update`:

```python
await conn.update([person])
```

If we were to query the database directly, we see that the name has been updated:

```
id | name  | age
----+-------+-----
  1 | Blice |  31
  2 | Bob   |  40
  3 | Charlie | 50
```

But no other fields have been touched. This lets a potentially concurrent process
modify `Alice`'s record - say, updating the age to 31. By the time we update the data, we'll
change the name but nothing else. Under the hood we do this by tracking the fields that
have been modified in-memory and creating a targeted UPDATE to modify only those values.

### Selecting data

To select data, we can use a `QueryBuilder`. For a shortcut to `select` query functions,
you can also just import select directly. This method takes the desired value parameters
and returns a list of the desired objects.

```python
from iceaxe_sqlite import select

query = select(Person).where(Person.name == "Blice", Person.age > 25)
results = await conn.exec(query)
```

If we inspect the typing of `results`, we see that it's a `list[Person]` objects. This matches
the typehint of the `select` function. You can also target columns directly:

```python
query = select((Person.id, Person.name)).where(Person.age > 25)
results = await conn.exec(query)
```

This will return a list of tuples, where each tuple is the id and name of the person: `list[tuple[int, str]]`.

We support most of the common SQL operations. Just like the results, these are typehinted
to their proper types as well. Static typecheckers and your IDE will throw an error if you try to compare
a string column to an integer, for instance. A more complex example of a query:

```python
query = select((
    Person.id,
    FavoriteColor,
)).join(
    FavoriteColor,
    Person.id == FavoriteColor.person_id,
).where(
    Person.age > 25,
    Person.name == "Blice",
).order_by(
    Person.age.desc(),
).limit(10)
results = await conn.exec(query)
```

As expected this will deliver results - and typehint - as a `list[tuple[int, FavoriteColor]]`

## Production

The underlying SQLite connection wrapped by `conn` stays alive while the `DBConnection` is in memory. For web apps, create one connection per process or use your framework lifecycle hooks to open and close it deliberately. SQLite serializes writes, so high-write deployments should plan around short transactions and WAL mode when appropriate.

## Benchmarking

We have basic benchmarking tests in the `__tests__/benchmarks` directory. To run them, you'll need to execute the pytest suite:

```bash
uv run pytest -m integration_tests
```

Current benchmarking as of October 11 2024 is:

|                   | raw SQLite | iceaxe-sqlite | external overhead                             |   |
|-------------------|-------------|--------|-----------------------------------------------|---|
| TableBase columns | 0.098s      | 0.093s |                                               |   |
| TableBase full    | 0.164s      | 1.345s | 10%: dict construction | 90%: pydantic overhead |   |

## Development

If you update your Cython implementation during development, you'll need to re-compile the Cython code. This can be done with
a simple uv sync.

```bash
uv sync
```
