One class for a table's row and the variants a database and a JSON API read and write, with repositories on any SQL client.
A row is rarely one shape. The database assigns its id, so an insert has none; a password
is written but never sent to a browser; a creation time is set once and not updated. A
model declares each field's variants in one class, and pyfect.sql.model builds a
repository on it. pyfect.schema.model follows Effect's Model, and
pyfect.sql.model its SqlModel.
Variants#
A model has six variants: select (a row read from the database), insert, update,
json (what a JSON API sends), json_create and json_update (what it receives). The
class is the select variant, a schema class; User.insert,
User.json and the others are struct schemas whose make fills constructor defaults.
A plainly annotated field is in every variant. Model.field(...) gives a field variants
of its own:
GeneratedByDb(schema): made by the database, inselectandjsononly.GeneratedByApp(schema): made by the application, never sent by a JSON client.Sensitive(schema): in the database variants only, never in JSON.FieldOption(schema): anOption, stored asNULL, optional in JSON.DateTimeInsert,DateTimeUpdateand theirFromDate/FromNumberforms: set to theClock's now on insert, or on every write.S.make_effect(User.insert)reads the fiber'sClock(so aTestClock);User.insert.make(...)runs on its own, so it reads the system clock, as Effect'smakeUnsafedoes.UuidV4Insert,UuidV7Insertand the...WithGenerateforms: a generated id.field_only([...]),field_except([...])andField(select=..., insert=..., ...): any other set of variants.
pyright cannot compute a variant's keys from the fields, so a model names them:
Model.Class[Insert, Update, Json, JsonCreate, JsonUpdate] takes a TypedDict per variant,
each Mapping[str, object] when left out. When the class is made, each TypedDict is
checked against the fields, and only a field with a constructor default may be
NotRequired; a mismatch is a TypeError, so a stale TypedDict fails at import. A field
the select variant leaves out is assigned as it is, without an annotation or Model.field:
extra = Model.Field(json=S.String).
Repositories#
sql_model.make_repository(Model, table_name=..., id_column=..., span_prefix=...) needs a
SqlClient and gives a typed Repository: insert takes the insert
variant and returns the row the database stored (RETURNING *, OUTPUT INSERTED.* on SQL
Server, a second SELECT on MySQL), update takes the update variant and returns the row
too, find_by_id fails with NoSuchElementException when there is no row,
and delete removes one. insert_void and update_void skip reading the row back. Each
call runs in a span named <span_prefix>.insert, <span_prefix>.findById and so on. With
soft_delete_column="deleted_at", delete sets that column to CURRENT_TIMESTAMP and
the reads skip rows where it is set.
id_column must be a field of both the select and the update variants: update
finds the row by it. GeneratedByDb leaves a field out of update, so a database-made id
is declared with field_only(["select", "update", "json"]), as below. pyright cannot check
a column name against a TypedDict's keys, so make_repository raises ValueError when
called with an id outside update, naming the keys update has.
from datetime import datetime
from typing import NotRequired, TypedDict
import pyfect as fx
from pyfect import schema as S
from pyfect.schema import model as Model
from pyfect.sql import SqlClient, sqlite
from pyfect.sql import model as sql_model
# Pyright cannot compute a variant's keys, so the model declares them.
class UserInsert(TypedDict):
name: str
password: str
created_at: NotRequired[datetime] # has a default: now
class UserUpdate(TypedDict):
id: int
name: str
password: str
class User(Model.Class[UserInsert, UserUpdate]):
# The database assigns the id: it is not inserted, and a JSON client never sends it.
id: int = Model.field(Model.field_only(["select", "update", "json"])(S.Int))
name: str
password: str = Model.field(Model.Sensitive(S.String)) # never in the JSON variants
created_at: datetime = Model.field(Model.DateTimeInsertFromNumber)
@fx.fn
def program():
sql = yield from SqlClient()
yield from sql(
"CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, password TEXT, created_at INTEGER)"
)
users = yield from sql_model.make_repository(
User, table_name="users", id_column="id", span_prefix="Users"
)
ada = yield from users.insert(User.insert.make({"name": "Ada", "password": "s3cret"}))
print(ada.id, ada.name)
yield from users.update(User.update.make({"id": ada.id, "name": "Ada L.", "password": "x"}))
print((yield from users.find_by_id(ada.id)).name)
print(sorted(User.json.fields)) # what a JSON API sends: no password
yield from users.delete(ada.id)
print((yield from users.find_by_id(ada.id).option()))
database = sqlite.layer(sqlite.SqliteClientConfig(filename=":memory:"))
fx.run_main(program().pipe(fx.provide(database)))As in Effect, update appends returning * itself rather than through the statement
compiler (on SQL Server it writes OUTPUT INSERTED.* before the where), so on ClickHouse it
fails with a syntax error: use update_void, then find_by_id if you need the row.
sql_model.make_resolvers(...) gives the same operations as
batching resolvers: insert, insert_void,
find_by_id and delete. R.request(resolvers.insert) (from pyfect.sql.resolver) makes a
function from one request to its effect, and the requests made together run as one
statement. The resolvers run on the SqlClient that make_resolvers ran with, whatever
client a request provides, so with a database per tenant make them per tenant (see
One resolver per tenant).
from typing import TypedDict
import pyfect as fx
from pyfect import schema as S
from pyfect.schema import model as Model
from pyfect.sql import SqlClient, sqlite
from pyfect.sql import model as sql_model
from pyfect.sql import resolver as R
class AccountInsert(TypedDict):
code: str
class AccountUpdate(AccountInsert):
id: int
class Account(Model.Class[AccountInsert, AccountUpdate]):
id: int = Model.field(Model.field_only(["select", "update", "json"])(S.Int))
code: str
@fx.fn
def program():
sql = yield from SqlClient()
yield from sql("CREATE TABLE accounts (id INTEGER PRIMARY KEY, code TEXT)")
resolvers = yield from sql_model.make_resolvers(
Account, table_name="accounts", id_column="id", span_prefix="Accounts"
)
# R.request turns a resolver into a function from one request to its effect.
insert = R.request(resolvers.insert)
find = R.request(resolvers.find_by_id)
# Requests made together run as one INSERT ... RETURNING *, each getting its row.
codes = ["cash", "bank", "sales"]
created = yield from fx.all(
*(insert(Account.insert.make({"code": c})) for c in codes), concurrency="unbounded"
)
print([(account.id, account.code) for account in created])
# And as one SELECT ... WHERE id IN (...); an id with no row fails its request alone.
found = yield from fx.all(find(3), find(1), concurrency="unbounded")
print([account.code for account in found])
print(type((yield from find(9).flip())).__name__)
database = sqlite.layer(sqlite.SqliteClientConfig(filename=":memory:"))
fx.run_main(program().pipe(fx.provide(database)))S.variant_schema.make(variants=..., default_variant=...) builds a toolkit like Model's
for any other set of variants, with Struct, Field, Union, extract and a Class
base.
Reference: pyfect.schema.model,
pyfect.schema.variant_schema,
pyfect.sql.model.