Metadata-Version: 2.5
Name: sustained
Version: 2.27.0
Summary: A Python ORM, SQL query builder, and schema migration tool, inspired by Objection.js and Knex
Project-URL: Homepage, https://sustained.tbmh.org
Project-URL: Documentation, https://sustained.tbmh.org
Project-URL: Changelog, https://sustained.tbmh.org/changelog
Project-URL: Source, https://github.com/wetherc/sustained
Project-URL: Issues, https://github.com/wetherc/sustained/issues
Author-email: Christopher Wetherill <git@tbmh.org>
License-Expression: MIT
License-File: LICENSE
Keywords: aiosqlite,asyncpg,athena,database migrations,duckdb,knex,mariadb,migration tool,mssql,mysql,objection.js,orm,postgresql,presto,query builder,schema migrations,sql,sql server,sqlite
Classifier: Development Status :: 5 - Production/Stable
Classifier: Framework :: AsyncIO
Classifier: Intended Audience :: Developers
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Programming Language :: Python :: 3.9
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Programming Language :: SQL
Classifier: Topic :: Database
Classifier: Topic :: Database :: Front-Ends
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Classifier: Typing :: Typed
Requires-Python: >=3.9
Description-Content-Type: text/markdown

# Sustained.py

Sustained is a Python query builder, lightweight ORM, and schema migration tool, originally inspired by [Objection.js](https://vincit.github.io/objection.js/).

You describe your tables in one set of model classes, and Sustained uses those classes both to build and run queries and to keep the schema in step.

The syntax will look familiar if you have worked with Objection, Kysely, or even Knex before:

```python
adults = User.query().where(User.c.age >= 18).orderBy('name').run()
```

## Managing queries through Sustained

With Sustained, you can:

- **Build SQL programmatically.** Selects, aggregates, window functions, `CASE` expressions, every join type, CTEs (including recursive), unions, `INTERSECT` and `EXCEPT`, and subqueries in `SELECT`, `FROM`, `WHERE`, and `JOIN` clauses.
- **Target seven dialects.** ANSI (default), PostgreSQL, MySQL and MariaDB, MSSQL, Presto, AWS Athena, and DuckDB. Quoting, placeholders, upsert syntax, `LIMIT`/`OFFSET` spelling, and function names all follow the dialect. Unsupported features raise `DialectError` at build time instead of failing in the database. Migrating queries between dialects is a one-line change.
- **Execute queries safely.** Every statement runs parameterized against any DB-API 2.0 connection or a `ConnectionPool`. Transactions nest through savepoints, and `update()` and `delete()` refuse to run without a `WHERE` clause.
- **Write data.** `insert()`, `update()`, `delete()`, upserts through `onConflict()`, `INSERT ... SELECT`, `CREATE TABLE AS`, and `RETURNING`.
- **Hydrate results.** Rows become model instances, plain dicts, `pandas` DataFrames, or `pyarrow` Tables. Relations eager load with `withGraphFetched()`. A type checker reads `Show.query().run()` as `List[Show]`.
- **Run queries async.** The same queries run through driver adapters, including `asyncpg` and `aiosqlite`, with `await query.arun()`. `AsyncConnectionPool` pools those adapters, so concurrent queries do not queue behind one connection.

## Schema management with Sustained

Sustained also manages schema changes. It generates migrations from your models, tests each change before it runs, and rolls a migration back when you ask. [Schema and Migrations](https://sustained.tbmh.org/schema) describes these features in detail.

With Sustained, schema migrations are:

- **Generated from your models.** `Migrator.up(models=[...])` diffs the live database against your models, generates the migration, records it, and applies it. If you run it again after a model change, it applies only the difference. `down()` rolls it back.
- **Rehearsed before they land.** `sustained rehearse` applies every pending migration, runs the downgrade steps to test the revert plan, and rolls the whole thing back. If a migration fails to run or fails to reverse, the rehearsal reports it before the migration reaches the real schema. A config module can send the rehearsal to a scratch database instead.
- **Planned in one screen.** `sustained plan` shows your pending migrations, outstanding problems that `validate` would report, and any gap between your models and the database's current state.
- **Read for their impact on a live database.** `sustained impact` reports, for each statement a run would apply on PostgreSQL, MySQL, MariaDB, SQL Server, SQLite, or DuckDB, the tables it locks, whether reads or writes wait, whether it scans, rebuilds an index, or rewrites the table, and how long the lock is held. It flags a lock held until commit across a later backfill, and names the safer form, such as `CREATE INDEX CONCURRENTLY` or `NOT VALID` followed by `VALIDATE CONSTRAINT`. [Statement impact](https://sustained.tbmh.org/impact) lists the rules.
- **Verified before every run.** Sustained keeps a per-database tracking table that records a sequence number, a SHA-256 checksum, an apply timestamp, execution time, and a success flag per migration. `validate` refuses a run when a migration was edited after it ran, arrives out of order, or left a failed attempt behind. After manual corrections, `repair` deletes failed runs from the tracking table and updates script checksums.
- **Gated by custom safeguards.** A guard is a built-in rule such as `no_drops()`, `index_must_be_concurrent()`, or `max_statements(n)`, or a function you write. Guards read every statement a run would apply and block the deployment when a rule fails.
- **Safe by default.** Drops need an explicit `allow_drops=True`, renames need explicit hints, and `NOT NULL` changes need a `default` or `backfill`, so destructive changes never run by default.
- **Written your way.** Migrations can be Python `Migration` objects, `<id>.up.sql` and `<id>.down.sql` files with `${placeholders}`, or `<id>.repeat.sql` files for views and seed data, which re-run whenever their contents change.
- **Ready for deploys.** The `sustained` console script runs `plan`, `impact`, `status`, `rehearse`, `migrate`, `down`, `validate`, `repair`, `script`, and `baseline`, with exit codes for pipelines and `before_migrate`, `after_migrate`, and `on_error` callbacks around a run. Concurrent deploys queue on an advisory lock. `baseline` adopts a database whose schema already matches the migrations. `script('up')` renders the SQL for a DBA instead of running it. `AsyncMigrator` does all of this on an async adapter.


## Installation

```bash
python3 -m pip install sustained
```

## Usage

```python
from sustained import Model, RelationType

class Person(Model):
    tableName = 'persons'

class Animal(Model):
    tableName = 'animals'
    relationMappings = {
        'owner': {
            'relation': RelationType.BelongsToOneRelation,
            'modelClass': Person,
            'join': {
                'from': 'animals.ownerId',
                'to': 'persons.id'
            }
        }
    }

# Build a query
query = Animal.query().select('animals.name', 'persons.name').leftOuterJoinRelated('owner')

print(query)
# SELECT animals.name, persons.name
# FROM animals
# LEFT OUTER JOIN persons
#   ON animals.ownerId = persons.id


# Execute against any DB-API 2.0 connection
import sqlite3

conn = sqlite3.connect('app.db')
Animal.bind(conn)

# Parameterized execution with model hydration
animals = Animal.query().where('species', '=', 'dog').orderBy('name').run()

# Or take the SQL and parameters and execute them yourself
sql, params = Animal.query().where('species', '=', 'dog').to_sql()
# sql:    "SELECT * FROM animals WHERE species = ?"
# params: ('dog',)
```

Models define their own schema, so a column change is a migration:

```python
from sustained.migrations import Migrator
from sustained.schema import Integer, String, Text

class User(Model):
    tableName = 'users'
    tableColumns = {
        'id': Integer(primary_key=True, autoincrement=True),
        'email': String(120, unique=True, nullable=False),
    }

migrator = Migrator(conn, [])
migrator.up(models=[User])         # creates the users table

User.tableColumns['bio'] = Text()
migrator.plan([User])              # the migration the next run would generate
migrator.up(models=[User])         # adds only the bio column
migrator.down()                    # rolls it back
```

From the shell, a config module names the connection, the migrations directory, and the models:

```python
# sustained_config.py
import sqlite3

def get_connection():
    return sqlite3.connect('app.db')

migrations_dir = 'migrations'
models = [User]
```

```console
$ sustained plan        # pending migrations, validation problems, model drift
$ sustained rehearse    # run it all, forwards and back, then roll back
$ sustained migrate     # apply it for real
$ sustained down        # --steps N (0 or more) or --to ID
```

See [Schema and Migrations](https://sustained.tbmh.org/schema) for SQL file migrations, repeatables, checksum validation, `baseline`, and the Athena rules.

## Documentation

The [documentation](https://sustained.tbmh.org/) includes:

- [Getting Started](https://sustained.tbmh.org/getting-started) builds a working application in one sitting, against SQLite from the standard library.
- [Recipes](https://sustained.tbmh.org/recipes) pairs a common task with the code that does it.
- The guides cover one area each: [models](https://sustained.tbmh.org/models), [queries](https://sustained.tbmh.org/queries), [dialects and drivers](https://sustained.tbmh.org/dialects), [filtering](https://sustained.tbmh.org/filtering), [grouping](https://sustained.tbmh.org/grouping), [relations and joins](https://sustained.tbmh.org/relations), [execution, pooling, and async](https://sustained.tbmh.org/executing), and [schema and migrations](https://sustained.tbmh.org/schema) at length.
- The [API reference](https://sustained.tbmh.org/reference/) gives every public name its signature, return type, and the conditions that raise.

The [support policy](https://sustained.tbmh.org/support) lists the supported databases and Python versions and states the deprecation policy. The [changelog](https://sustained.tbmh.org/changelog) lists released versions.

## Development

To install from source:

```bash
git clone https://github.com/wetherc/sustained.git
cd sustained
python3 -m pip install -e .
```

This project uses `pre-commit` to format code, lint, type check, and run the test suite before each commit:

```bash
pip install pre-commit
pre-commit install
```
