asqueel, SQLAlchemy, Django and Peewee

This lesson compares asqueel with three Python libraries: SQLAlchemy ORM, Django and Peewee. Each comparison shows one task written in the other library and in asqueel. The other libraries support more than one style. The examples use their common style. The asqueel examples run on the course model, on PostgreSQL. The other examples assume the usual model classes for customers, invoices, invoice rows and invoice notes, with the same fields as the course model.

In SQLAlchemy ORM, work goes through a Session. Objects loaded through the Session enter its identity map: the same primary key gives the same Python object. asqueel has no Session and no identity map. A query returns rows, and each row is a NamedList with the selected columns. A row is a projection. It is not an object mapped to a table. Two fetches of the same record return two separate lists. In this example the asqueel query does not write the join. The alias column customer_name declares the path to the customer once, in the recipe.

With its default loading strategy, SQLAlchemy loads a related object when you first read the attribute. The read runs a second SELECT. asqueel does not load related objects. The row contains the key customer_id, and nothing more about the customer. Asking for a column that is not in the projection raises KeyError. To read the customer name you put it in the columns, with a relation path or an alias column. The join is then part of the same SELECT.

In SQLAlchemy, assigning an attribute marks the object as changed. The UPDATE runs later, at the flush. A flush happens at commit, and by default also before a query. asqueel has no unit of work and no flush. Changing a row changes only the Python object. The UPDATE runs inside the call to update, with its hooks. The record passes every column it contains, and old_record gives the key and the previous values to the hooks. Then db dot commit ends the transaction. Nothing else is written at commit. A related row is written only by its own table call, or by a relation action declared in the model.

Django runs in autocommit mode by default. Outside an atomic block, each query is committed when it runs, so save is durable at once. A Django model instance saves itself with save. In asqueel the table saves the record: you call update with the record and, optionally, the old record. asqueel has no autocommit mode. On PostgreSQL the first statement opens a transaction, and a read counts. The transaction stays open until db dot commit or db dot rollback. If you call closeConnection without a commit, the update is rolled back. On SQLite a read opens no transaction, but writes wait for commit in the same way.

In Django a nested atomic block creates a savepoint. An error inside it undoes only the inner block, and the outer work can still commit. asqueel has no nested transactions and no savepoints. In the example the invoice number INV-001 already exists, so the second insert fails with a unique violation. A failed SQL statement rolls back the whole transaction of that connection, so the customer insert is undone too. A Python error raised inside a write leaves the connection rollback-only: the next statement raises until you call db dot rollback. When you need an independent unit of work, use another named connection, which has its own transaction. Lesson 27 covers named connections. The method tempEnv is not a transaction block.

Peewee declares fields on Python model classes. Each query that needs the customer name writes the join and names the field of the other model. In asqueel the shared definition lives in the configuration recipe, not in model classes. The alias column customer_name declares the relation path once. Queries then use dollar customer_name in the columns, in the filter and in the ordering, as a column of the invoice. A relation path compiles to a LEFT JOIN, so an invoice without a customer still appears. Peewee's join is an inner join by default. The asqueel row is a NamedList, not a dictionary. Call dict on the row when you need a dictionary.

When a query joins two to-many relations, each invoice row is multiplied by its rows and by its notes. The Django documentation states that combining several aggregations in annotate gives wrong results for this reason. The same holds for a hand-written join with GROUP BY in SQLAlchemy or Peewee. In asqueel a to-many relation is aggregated only with the explicit aggr call. Each aggr call compiles to its own correlated subquery. It returns one value for each invoice and never multiplies the outer rows. A plain path through a to-many relation, such as COUNT of at rows dot id, is refused with UnsupportedFeatureError.

Django has save overrides and signals. SQLAlchemy has session and mapper events. In asqueel a table class declares hooks as methods, and the recipe names the class with x_table_class. Insert, update and delete each run their hooks in a fixed, documented order. Lesson 21 explains each step. Derived columns are part of the model too. An alias column and a formula column are declared in the recipe, and every query uses them by name. asqueel also lacks several things. It has no savepoints. The API is synchronous: there is no async interface. It supports two backends, PostgreSQL and SQLite. It provides no admin and no forms. Migrations compare the model with the live database; there are no migration files.

asqueel has no Session, no identity map, no flush and no lazy loading: rows are projections, and a write runs at the table call. It has no autocommit and no savepoints: you end each transaction with commit or rollback. Relation paths, alias and formula columns are part of the model, and to-many aggregation is explicit and never multiplies rows. This is the last lesson of the course. Lesson 33 contains the complete example to start from.