The complete example

Scene 01

The plan of the example

This lesson runs the course model from start to end, in one script. Every step uses an API of an earlier lesson. The script runs on an empty PostgreSQL server. The variable ASQUEEL_DSN names a database that does not exist yet.

Example & screen notes

Slide "From recipe to data". Numbered list:

Recipe: invoicing.py
Migrate
Insert reference data, a customer, products, an invoice and its rows
Read with relation paths and @rows.aggr
Update the invoice totals
Delete the invoice with its rows

Scene 02

Recipe and migration

The recipe is the course model of lesson 3, with one package, invc, and nine tables. Building AsqueelDb validates the model and does not connect. db dot migrate creates the database, the schema, the tables, the keys, the indexes and the foreign keys, as in lesson 30. A second plan is empty.

Example & screen notes

Code block; a side panel shows the table list of the recipe: region, state, customer_type, vat_type, customer, product, invoice, invoice_row, invoice_note.

import sys
from datetime import date
sys.path.insert(0, 'temp/course/course_model')
from invoicing import Invoicing
from asqueel import AsqueelDb, session

db = AsqueelDb(Invoicing)
db.migrate().empty            # False: database, schema, nine tables
db.migration_plan().empty     # True

Scene 03

Inserts

The session of lesson 27 frames the work. It sets the user and the work date in the environment of lesson 17. The inserts follow the order of the foreign keys: region, state, customer type, VAT type, customer, products, invoice, rows. The tables with a code key receive the key from the caller. customer, product, invoice and invoice row have an id key of 22 characters. The insert generates it, as in lesson 20, and writes it into the record. The next insert reads it from that record. The sys fields of lesson 25 fill the timestamp columns. One commit saves all ten rows together.

Example & screen notes

Code block, the session(...) line and db.commit() highlighted.

with session(db, user='course', workdate=date(2026, 10, 20)):
    db.table('invc.region').insert({'code': 'NE', 'name': 'North East'})
    db.table('invc.state').insert({'code': 'NSW', 'name': 'New South Wales', 'region_code': 'NE'})
    db.table('invc.customer_type').insert({'code': 'BUS', 'description': 'Business'})
    db.table('invc.vat_type').insert({'code': 'STD', 'description': 'Standard', 'vat_rate': 10})
    customer = db.table('invc.customer').insert({
        'account_name': 'Harbour Books', 'street_address': '1 Quay St', 'suburb': 'Sydney',
        'state': 'NSW', 'customer_type_code': 'BUS'})
    product = db.table('invc.product')
    pen = product.insert({'code': 'PEN', 'description': 'Pen', 'unit_price': 2.50,
                          'vat_type_code': 'STD'})
    pad = product.insert({'code': 'PAD', 'description': 'Notepad', 'unit_price': 4.00,
                          'vat_type_code': 'STD'})
    invoice = db.table('invc.invoice').insert({
        'inv_number': 'INV-001', 'customer_id': customer['id'], 'date': date(2026, 10, 1)})
    rows = db.table('invc.invoice_row')
    for item, quantity in ((pen, 10), (pad, 3)):
        rows.insert({'invoice_id': invoice['id'], 'product_id': item['id'],
                     'quantity': quantity, 'unit_price': item['unit_price'], 'vat_rate': 10})
    db.commit()
    customer['id']            # 'Z0G7qrqMak0CbKu4KQ8qKV': 22 characters

Scene 04

A reading query

One query reads the invoice with data from four tables. customer_name is the aliasColumn of lesson 8. The path at customer id, at state, name crosses two to-one relations, as in lesson 5. is_recent is a formula that reads env workdate, which the session set. at rows dot aggr counts the rows and sums their amounts, as in lesson 6. rows is the relation name declared on invoice row. The query adds the filter on underscore underscore del underscore ts by itself, because of sys fields. The commit closes the read transaction.

Example & screen notes

Code block, then the result row.

query = db.table('invc.invoice').query(
    columns='$inv_number, $customer_name, @customer_id.@state.name AS state_name, $is_recent, '
            '@rows.aggr(count() AS lines, sum($quantity * $unit_price) AS net)',
    where='$date >= :since', since=date(2026, 1, 1), order_by='$inv_number')
query.fetch()
db.commit()

{'inv_number': 'INV-001', 'customer_name': 'Harbour Books',
 'state_name': 'New South Wales', 'is_recent': True, 'lines': 2, 'net': Decimal('37.00')}

Scene 05

The SQL of the query

The to-one paths become two LEFT JOINs. Each aggregate becomes a correlated subquery, so the rows never multiply the invoices. The parameters since and env workdate stay parameters. The last condition is the filter that sys fields added.

Example & screen notes

The text of query.sqltext, joins and subqueries highlighted.

SELECT "t0"."inv_number" AS "inv_number",
"t1"."account_name" AS "customer_name",
"t2"."name" AS state_name,
( "t0"."date" >= %(env_workdate)s - INTERVAL '90 days' ) AS "is_recent",
 ( SELECT COUNT(*) AS "COUNT___"
 FROM "invc"."invc_invoice_row" AS "t0_t0"
WHERE ("t0_t0"."invoice_id"="t0"."id") )  AS "lines",
 ( SELECT SUM("t0_t0"."quantity" * "t0_t0"."unit_price") AS "SUM__quantity____unit_price_"
 FROM "invc"."invc_invoice_row" AS "t0_t0"
WHERE ("t0_t0"."invoice_id"="t0"."id") )  AS "net"
 FROM "invc"."invc_invoice" AS "t0"
       LEFT JOIN "invc"."invc_customer" AS "t1" ON (("t0"."customer_id")="t1"."id")
       LEFT JOIN "invc"."invc_state" AS "t2" ON (("t1"."state")="t2"."code")
WHERE ("t0"."date" >= %(since)s) AND ("t0"."__del_ts" IS NULL)
ORDER BY "t0"."inv_number"

Scene 06

An update with the old record

The update starts with a read that locks the row, as in lesson 19. fetchRecord returns the single row of the query. A second query computes the net amount from the rows. The new record is a copy of the old one with three totals changed. update receives the new record and the old record. update never reads the old record from the database: the caller passes it, as in lesson 20. The hooks and the field triggers of lessons 21 and 22 can compare the two records. The commit saves the totals and releases the lock.

Example & screen notes

Code block, for_update=True and old_record= highlighted; output below.

table = db.table('invc.invoice')
old_record = table.query(where='$inv_number = :number', number='INV-001',
                         for_update=True).fetchRecord()
net = table.query(columns='@rows.aggr(sum($quantity * $unit_price) AS net)',
                  where='$id = :id', id=old_record['id']).fetchRecord()['net']
record = dict(old_record)
record['total'] = net
record['vat_total'] = net * 10 / 100
record['gross_total'] = record['total'] + record['vat_total']
table.update(record, old_record=dict(old_record))
db.commit()

{'inv_number': 'INV-001', 'total': Decimal('37.00'),
 'vat_total': Decimal('3.70'), 'gross_total': Decimal('40.70')}

Scene 07

A delete with cascade

delete receives the invoice record. The relation of invoice row declares onDelete equal cascade. Before deleting the invoice, asqueel deletes its two rows, each through the full delete cycle, as in lesson 23. The cascade runs in Python, not in the database. Inside the same transaction, the rows are already gone. The commit makes the delete final. Leaving the session closes the connection.

Example & screen notes

Code block and output; a small diagram: box "invoice INV-001" with an arrow labelled "onDelete='cascade'" to two boxes "invoice_row".

table.delete(record)
db.table('invc.invoice_row').query().count()   # 0, before the commit
db.commit()
table.query().count()                          # 0

Scene 08

What each lesson contributed

The recipe and its declarations come from lessons 3 to 10. Layers and extended grammars come from lessons 11 to 13. Reading comes from lessons 14 to 19. The write cycle comes from lessons 20 to 26. Transactions, deferred callbacks and direct SQL come from lessons 27 to 29. Migrations, the command line and the host integration come from lessons 30 to 32.

Example & screen notes

Two-column table.

Step	Lessons
Recipe, keys, relations, aliases, formulas	3-10
Layers, extended grammar	11-13
Query, parameters, fetch, environment, policies, `for_update`	14-19
`insert`, `update`, `delete`, cycle, triggers, relation actions, `sys_fields`	20-26
Transactions, callbacks, direct SQL	27-29
Migration, command line, host	30-32

Scene 09

A second example

The repository contains a second example, examples slash two underscore schemas. It splits one database into two schemas, identity and sales, with one module per table. Each table binds its own table class with x underscore table underscore class. Its configure dot py is a configuration folder for the command line of lesson 31. Run from the repository root, its main module compiles two queries without connecting.

Example & screen notes

Folder tree of examples/two_schemas, then a terminal line.

examples/two_schemas/
├── configure.py
├── __main__.py
└── schemas/
    ├── identity/   schema.py, user.py, access.py
    └── sales/      schema.py, customer.py, product.py, invoice.py, invoice_logic.py, invoice_row.py

python -m examples.two_schemas

Scene 10

Recap

One recipe drives the migration, the queries and the writes. Every unit of work ends with an explicit commit or rollback. Relation paths, aggregates, old records and cascades all come from the declarations of the model. The next lesson compares asqueel with SQLAlchemy, Django and Peewee.

Example & screen notes

Slide "Recap". Bullets:

One recipe drives the migration, the queries and the writes
Every unit of work ends with an explicit commit() or rollback()
Relation paths, aggregates, old records and cascades all come from the model

Next: "Lesson 34 — asqueel, SQLAlchemy, Django and Peewee".