Relational ORM

Overview

Both relational plugins, sqlite and sql, hand out the same ORM from conn.get_orm(). It has three layers, each useful on its own:

• query builders: orm.select(table, *columns), orm.update(table, values) and orm.delete(table) return chainable queries: .where(...) links AND together, criteria objects compose OR groups, and a terminal (fetch(), execute(), count()) runs exactly one statement.
• quick forms: insert(table, dict) for the write with nothing to filter, and tables().
• model gateways: orm.table(factory, table, ...) maps rows onto your objects through a factory function.

The ORM is Scriptling code that runs host-side in both plugin modes, so chained builder calls do not cross the plugin boundary. Each connection supplies its dialect: backtick quoting on MySQL/MariaDB, double quotes and $n placeholders on PostgreSQL, and SQLite syntax for SQLite. ORMs from different connections do not interfere.

The example is backend-neutral: pass it a connection created by either scriptling.sqlite.connect(...) or scriptling.sql.connect(...).

def high_scores(conn):
    orm = conn.get_orm()

    (orm.create_table("people")
     .column("id", "integer", primary_key=True, autoincrement=True)
     .column("name", "text")
     .column("score", "real")
     .column("active", "integer", default=1)
     .if_not_exists()
     .execute())

    orm.insert("people", {"name": "ada", "score": 9.5})
    return (orm.select("people", "name", "score")
            .where("score", ">=", 8.0)
            .order_by("score", desc=True)
            .fetch())

The table builder renders the auto-increment primary key per backend, so the function works with SQLite, MySQL/MariaDB, or PostgreSQL connections; the caller is still responsible for importing the selected plugin and supplying its file path or DSN.

Transactions

ORM terminal operations use the connection’s autocommit API. Each fetch(), count(), insert(), execute(), save(), or delete() call is independent, and the ORM exposes no transaction handle. It cannot group several calls into one atomic transaction.

Query Builders

orm.select(table, *columns), orm.update(table, values) and orm.delete(table) each return a chainable query; every method returns the same query so calls chain; the terminal (fetch(), execute(), count()) assembles the SQL, binds the parameters and runs exactly one statement.

# select: filter, order, page, then fetch() (or one(), iterate(), count())
rows = (orm.select("people", "name")
        .where("active", "=", 1)                                    # flat AND
        .where("score", ">=", 8.0)
        .where(orm.any_of(orm.eq("name", "ada"),                    # OR group
                          orm.eq("name", "grace")))
        .order_by("score", desc=True)
        .limit(10)
        .fetch())

# count: the same filters, SELECT count(*) underneath
high = orm.select("people").where("score", ">=", 8.0).count()

# update: the same filters, execute() returns rows_affected
result = (orm.update("people", {"score": 0.0})
          .where("score", "<", 5.0)
          .execute())                       # -> {"rows_affected": 2}

# delete: the same filters
orm.delete("people").where("active", "=", 0).execute()

Conditions

.where(...) accepts either shape:

• .where(column, op, value): flat condition; op is whitelisted (= != <> < <= > >= like).
• .where(criterion): a criterion from the constructors below, including groups.

Criteria are plain values: build them anywhere, pass them around, combine them:

Groups nest arbitrarily. (a OR b) AND (a OR c):

rows = (orm.select("people", "name")
        .where(orm.any_of(orm.eq("a", 1), orm.eq("b", 2)))
        .where(orm.any_of(orm.eq("a", 1), orm.eq("c", 3)))
        .fetch())

Every value binds as a parameter and every identifier is quoted and validated against a safe character set: conditions are generated, never interpolated.

Methods

where and where_sql work the same on all three builders; the rest belong to the select query.

On postgres the renumbering leaves quoted literals, quoted identifiers, comments, and the jsonb ?|/?& operators alone. A bare jsonb ? is indistinguishable from a placeholder on this path; jsonb_exists(data, 'k') is the function form that works. | order_by(column, desc=False) | order; repeatable; select | | limit(n) / offset(n) | paging; select | | fetch() | run and return all rows as a list of dicts; select | | iterate() | stream rows from the query; compiled-in relational builds read lazily, while external plugins fetch one row per call across the plugin boundary | | one() | first row or None; select | | count() | run as SELECT count(*) and return an int; select | | execute() | run the UPDATE/DELETE and return {"rows_affected": int}; update and delete |

Quick Forms

Blanket updates and deletes are refused: .execute() without any where raises, so if you genuinely want every row, say so explicitly with conn.execute("delete from t").

Table Builder

orm.create_table(table) returns a builder with the same shape as the query builder; .execute() runs the DDL. Identifiers are quoted and validated like everywhere else, and the documented integer auto-incrementing primary-key shape renders per backend: AUTOINCREMENT on SQLite, AUTO_INCREMENT on MySQL/MariaDB, and SERIAL on PostgreSQL. Portability is limited to this documented generated subset; raw DDL, type names, constraints, indexes, collations, and backend-specific features still differ.

(orm.create_table("people")
 .column("id", "integer", primary_key=True, autoincrement=True)
 .column("name", "text", nullable=False, unique=True)
 .column("score", "real", default=0.0)
 .if_not_exists()
 .execute())

orm.drop_table("people")     # DROP TABLE IF EXISTS, same everywhere

Column types pass through as-is: integer, text and real work on all three backends; anything backend-specific (for example, serial or jsonb) is yours to spell. default accepts integers, finite floats, strings, and booleans. None means no DEFAULT clause; non-finite floats (NaN, positive infinity, or negative infinity) are rejected with ValueError. Indexes and foreign keys stay with raw conn.execute: DDL beyond tables is where the backends genuinely disagree.

Model Gateways

orm.table(factory, table, pk="id") returns a gateway bound to the connection. The factory is a plain function that turns a row dict into your object (a dict, an instance of your class: anything with the columns as attributes or keys):

def make_person(id=None, name=None, score=None, active=None):
    return Person(id, name, score, active)     # or just return a dict

# no columns: the gateway writes every column the table has
people = orm.table(make_person, "people", pk="id")

p = people.get(1)          # factory(row) or None
people.save(p)             # update by pk
people.insert(make_person(name="kurt", score=6.0))
people.delete(p)           # by instance or raw pk
people.count()
people.select("name").where("active", "=", 0).fetch()   # full builder

# columns=[...]: the same table through a narrower gateway
names = orm.table(make_person, "people", pk="id", columns=["id", "name"])
n = names.get(1)
n.name = "ada lovelace"
names.save(n)              # writes name only; score and active stay untouched

# per-call columns: narrow one save or insert without a second gateway
people.save(p, columns=["score"])
people.insert(make_person(name="kurt", score=6.0), columns=["name", "score"])

columns is optional. Without it the gateway writes every column the table has: the column list is read from the schema once per get_orm() and cached, so adding or removing a column in the table needs no script change. Pass columns=["id", "name"] to manage a subset (a wide table, or columns you deliberately do not want writes to touch); the list then applies to insert and save only, and costs no schema lookup. That list is the gateway’s default write shape — save(p, columns=[...]) and insert(p, columns=[...]) narrow it for a single call.

Two write semantics worth knowing: insert skips None values, so unset fields take their schema defaults and the primary key auto-assigns (an object with everything unset inserts a pure-defaults row); save writes every managed column, including None (that is how you clear one). An explicit columns= list on either call is explicit: None in a listed column is written as NULL, which is the way to override a schema default on insert through the gateway (the kit form orm.insert(table, {"cleared_at": None}) does the same).

See Also

• sqlite and sql: the connections that hand out ORMs
• Database Libraries: all four backends, two API shapes