Typed tables.Typed queries.Typed results.

TypeDAL adds class-based models and a typed Query Builder to PyDAL. You define tables with Python type hints. TypeDAL works with PyDAL's supported databases, including SQLite and PostgreSQL.

uv pip install typedal

Define a table

Define columns with type annotations. For example, age: int | None allows the age to be NULL, while author: Author references an Author row.

TypedField[...] lets your editor autocomplete column methods such as User.name.like() and lets your type checker check those calls. On a row, user.name has the type str.

You can also write name: str. Row attributes have the same types with either annotation style, but mypy and pyright treat User.name as a str and report errors for column methods such as .like().

Both styles are valid. You can mix them freely within one class.
@db.define()
class User(TypedTable):
name: TypedField[str]
age: TypedField[int | None]
name: str
age: int | None
user = User.where(name="Alice").first_or_fail()User
user.namestr
User.where(User.name.like("A%"))QueryBuilder[User]checker flags this
User.orderby(~User.age)QueryBuilder[User]checker flags this

mypy and pyright report the types shown here. Both annotation styles produce the same columns and query behavior at runtime.

mypy and pyright report errors for .like() on str and ~ on int | None. These type errors do not prevent the queries from running.

Build a query

Use where to filter rows, orderby to sort them and join to load related rows. collect() returns TypedRows[User], while first() returns User | None.

Change the underlined options to see the return type and SQL. The async versions return the same types when awaited.

rows = (
User.where()
.orderby()
.
)

TypedRows[User]

Returns all matching users. Iterate over the rows or get their names with .column("name").

Show the SQL

        

Load related rows

A field typed as another model defines a relationship. Join it by name and read the related row as an attribute.

Joins can follow nested relationships: here, Author has roles: list[Role]. Use relationship(...) to define reverse or one-to-one relationships, or a many-to-many relationship.

@db.define()
class Post(TypedTable):
title: str
author: Author
posts = Post.join().collect()TypedRows[Post]
for post in posts:
post.author.namestr

Generate migrations

Generate and run SQL migrations from your models. You can also generate TypeScript interfaces from those classes for your frontend.

Store settings under [tool.typedal] in pyproject.toml, or let typedal setup write them.

models.py

@db.define()
class User(TypedTable):
name: str
age: int | None

typedal typescript.generate

interface User {
id: number
name: string
age: number | null
}
typedal migrations.generateSQL via pydal2sql
typedal migrations.runapplied via edwh-migrate

The command line tools come with optional extras; see Extras for which one you need.

Extras

The base package has everything on this page except the command line tools and framework integration. Those come as optional extras, so you only install what you use.

InstallAdds
Models, the Query Builder, relationships, caching, memoization, async and upsert.
The typedal command: setup, the cache commands, and migrations.generate with pydal2sql.
Everything in cli, plus edwh-migrate for migrations.run and migrations.fake.
Everything in cli, plus typescript.generate and as_typescript().
py4web, for typedal.for_py4web and its typed Auth tables.
All of the above, plus Pydantic for PydanticMixin.

Other features

Query caching
Cache a query with .cache(ttl=60).
Function memoization
db.memoize(fn, *args) tracks the rows a function reads and recomputes its result when one changes.
Upsert
User.upsert({"email": ...}, name=...) inserts or updates by a unique key.
Mixins
Inherit timestamp or slug fields. Mixins also provide Pydantic support, and you can write your own.
Enums and permissions
Use enums as field types. Set read and write permissions per table or per query.
Lazy loading policy
Control what happens when you access a relationship you did not join, including warnings or refusing to load it.

Use with existing PyDAL code

You can use existing PyDAL tables alongside TypeDAL models, including Auth tables in py4web and web2py. You can convert one table at a time.

Before

db.define_table("person", Field("name"), Field("age", "integer"))
rows = db(db.person.age > 18).select()

After

@db.define()
class Person(TypedTable):
name: str
age: int | None
rows = Person.where(Person.age > 18).collect()TypedRows[Person]

More pydal tools

Related projects around PyDAL. typedal migrations.generate uses pydal2sql to write the SQL.

  • pydal2sql★ 3

    Convert pydal define_tables to SQL using pydal's CREATE/ALTER logic

    last push Sep 2026
  • pydal2sql-core★ 3

    CLI-agnostic pydal2sql code; Convert pydal define_tables to SQL using pydal's CREATE TABLE logic.

    last push Nov 2026
  • edwh-pydal2sql-plugin

    Integration of pydal2sql in the edwh tool

    last push Oct 2026
  • pydal-psycopg3

    Experimental psycopg3 driver for pydal

    last push Sep 2026
  • pydal-adapter-limbo

    Custom pyDAL adapter for limbo (SQLite rewrite)

    last push Sep 2026
  • pydal-legacy-cgi

    Archive of my pydal fork to fix deprecated cgi import

    archived

Read the manual

  1. 1Getting started
  2. 2Defining tables
  3. 3Building queries
  4. 4Relationships
  5. 5py4web and web2py
  6. 6Migrations
  7. 7Configuration
  8. 8Mixins
  9. 9Function memoization
  10. 10Advanced APIs
  11. 11Async