pyDAL and the database abstraction layer that stays close to SQL

What pyDAL is, and what it deliberately is not

pyDAL is a pure-Python database abstraction layer that generates SQL, or the appropriate query objects for NoSQL backends, in real time using the dialect of the configured back end, so you write Python instead of SQL and the same application runs unchanged against many databases.

The most important sentence in the documentation is the second one. pyDAL is intentionally not an Object-Relational Mapper. Most ORMs, and the readme names Django ORM and SQLAlchemy as the examples, map tables to Python classes and rows to instances of those classes. pyDAL instead treats rows as plain Python dictionaries, with attribute access for convenience, and keeps the API close to SQL.

The consequences of that choice are stated as four results. You get a small, predictable surface, and if you know SQL you will be productive in minutes. There are no declarative class hierarchies, no metaclass magic and no two-phase schema bootstrap. The same query DSL runs against roughly fifteen backends, so you swap the connection string and the app keeps working. And rows are just dicts, which makes them easy to serialize, easy to pass around and easy to inspect.

That last point is the one most often appreciated in practice. A row that is a dict can go straight into a JSON response, into a cache value, or into a log line, with no conversion step and no lazy-loading proxy in the way.

The history is in the project metadata. pyDAL was originally part of the web2py frameworks but is now an independent project, authored by Massimo Di Pierro, and it is published on PyPI as pydal under a BSD-3-Clause licence.

The primary language is Python, the default branch is master, the last push was on 2026-10-11, and the repository is not archived. The recent release tags are older than the recent commits, with v20200321.1 from 2020 and versions 19.04 and 19.02 in 2019, so the release cadence is irregular and the tags are not a reliable picture of the current state.

A first example, and the mapping to SQL

The example in the readme is short enough that it shows the whole model, and the driver ships with Python so it runs with no setup.

from pydal import DAL, Field

db = DAL("sqlite://storage.db")
db.define_table("thing", Field("name"))

db.thing.insert(name="Chair")
db.thing.insert(name="Table")

for row in db(db.thing.name.startswith("C")).select():
    print(row.id, row.name)
# 1 Chair

db.commit()

Every line of that example maps directly to a SQL operation, and the readme spells out the correspondence: define_table becomes CREATE TABLE, insert becomes INSERT, the call to db builds a WHERE clause, and .select() runs the query and returns rows.

That db(...) call is the piece worth understanding. It is not a method on a table object; it is the way you construct a query set, by passing conditions built from the field objects. db.thing.name is a field reference, and .startswith("C") on it produces a condition, and the set of those conditions becomes the WHERE clause. The chaining style is what makes the DSL read like SQL without being SQL, and the fact that it produces a dict in the end rather than an object is the same design decision as the rest of the library.

The commit at the end is explicit rather than automatic, and that is deliberate. Transactions with explicit commit and rollback are listed among the core features, and a data layer that commits implicitly is a data layer you cannot reason about in a multi-step operation.

The install step is the other thing worth noting:

pip install pyDAL

The only hard dependency is Python 3.7 or newer, and the readme notes that the SQLite driver ships with Python so the first example runs out of the box. For other backends you install the appropriate Python driver, psycopg2, pymysql or pymongo among the examples, and pyDAL picks it up automatically. There is no adapter to write.

The DAL connection and its options

The DAL object is the connection, and it takes a connection string, also called a uri. The documented examples cover the common backends: sqlite://storage.sqlite or sqlite:memory for SQLite, postgres://user:pass@localhost/test, mysql://user:pass@localhost/test with a set_encoding=utf8mb4 parameter, mssql4://user:pass@localhost/test for MSSQL 2012 or later, oracle://user/pass@test, and mongodb://user:pass@localhost/test.

Two constructor behaviours are worth knowing because they are not obvious. You can pass None to build a dry DAL that generates SQL without connecting, which is useful for tooling and for tests that assert on generated SQL rather than running it. And you can pass do_connect=False to defer the connection until it is needed.

The commonly used parameters are a short list with real consequences. pool_size is the number of pooled connections with a default of 0, and it is ignored by SQLite. folder is where migration metadata is written, and the readme tells you to set this explicitly when using pyDAL standalone with SQLite, which is a warning worth heeding because the default is convenient for the web2py case and wrong for a standalone one. migrate is the global default for whether table changes generate ALTER TABLE statements, defaulting to True. check_reserved is a list of backend names to validate identifiers against, for example postgres and mssql, which is how you catch a column name that is a reserved word in one dialect and not another.

The Table object is not instantiated directly; it is defined through the DAL:

db.define_table("person", Field("name"), Field("age", "integer"))

and it is also reachable as db.person. Every table automatically gets an auto-increment integer primary key called id, unless you explicitly opt out with the primarykey argument.

The Table options are where the awkward real-world cases live. The format argument is a record representation used for foreign-key display, either format="%(name)s" or a lambda taking the row. The rname argument is the real SQL name when the table is known by a different identifier in the database, for example a legacy name or a schema-qualified name like "app1.dbo.legacy_table", which is the case that breaks most table-oriented migrations. And redefine=True allows redefining an existing table, triggering a migration if the schema differs.

Fields, types and the query DSL

The Field constructor is the column definition, and the readme shows the shape:

Field("name", "string", length=80, default="anonymous", required=True)

The default type is string, and the available types are given in a table. The readme truncates that table, so the full list of types and their notes is worth reading in the documentation rather than guessing at, because this is precisely where dialect differences appear. A field type that behaves one way on SQLite and another on Oracle is the kind of thing you want to know about before you design the schema rather than after.

The query DSL is broader than the examples suggest. It covers comparisons, logical operators, string matching, regex, aggregates, date and time accessors, IN, CASE, COALESCE and substring expressions. On top of that there are inner joins, left outer joins and cross joins, with an .on() syntax or implicit cross-table queries, and subqueries usable in IN clauses, as join sources or as inline expressions. Common Table Expressions are supported, including recursive ones, which is a rarer thing to find in a small abstraction layer.

The parameter binding is type-safe and dialect-aware, with placeholders such as ?, $1 and %s selected per dialect. Lazy iteration for large result sets comes through iterselect, so a query that returns a million rows does not have to become a list of a million dicts.

Two conveniences round it out. There is built-in CSV import and export, per table or for the whole database. And there is a natural-language QueryBuilder that turns a string like 'name starts with C and age >= 18' into a real query, which is a demonstration of the DSL's expressiveness rather than something you would put in production.

The API is described as being built from seven core objects, and the readme is explicit that once you have seen them once, the rest of the API is just method calls. That is a small surface by design, and it is the reason the same queries can be translated to fifteen backends.

The supported backends, and what the range implies

The backend list is where the abstraction shows its edges, and it is worth reading as a list of requirements rather than a list of features.

The relational backends are SQLite through the built-in sqlite3 driver, PostgreSQL through psycopg2 or pg8000, MySQL through pymysql or MySQLdb, MSSQL through pyodbc, Oracle through cx_Oracle, FireBird through kinterbasdb, fdb or pyodbc, DB2 through pyodbc, Informix through informixdb, Ingres through ingresdbi, Sybase and SAP through the Sybase and sapdb drivers, and Teradata through pyodbc. Snowflake is there through snowflake-connector-python.

Then the two that make the list unusual. MongoDB is supported through pymongo, and so is Google Firestore through google-cloud-firestore. IMAP is supported through the built-in imaplib.

MongoDB and Firestore are why the readme mentions query objects for NoSQL backends rather than only SQL. A relational dialect and a document store have no common query language, so the layer that works across both is not translating SQL but translating a small query model into whatever the backend understands. That is a much harder design problem than generating dialect-specific SQL, and it explains why the surface is kept to seven objects and why the rows are dicts rather than objects. A dict is something both a SQL result set and a MongoDB document can be.

IMAP is the outlier that proves the abstraction is about a data access shape rather than about SQL specifically. A mailbox is a set of messages with folders, and exposing it through the same table and query model is either a neat unification or a curiosity, depending on your taste.

The practical consequence for an adopter is that the abstraction is only as good as the least common denominator it can express. A query that is natural on PostgreSQL may not be expressible on Firestore, and the surface is deliberately narrow enough that most of the work happens in the application rather than in the query layer.

The state of the project and the limits of the approach

Three things are worth knowing before you commit.

The first is the version story. The pyproject.toml requires Python 3.10 or newer, while the readme says the only hard dependency is Python 3.7 or newer, and the release tags stop at v20200321.1 from 2020 even though the repository was pushed to as recently as 2026-10-11. The package version is dynamic, read from pydal.__init__.__version__, so the installed version comes from the code rather than from the manifest.

The second is the type-checking story, and the Makefile explains it honestly. The typecheck target runs mypy against a specific list of modules, the AST, the AST translator, the driver and the compilers, and the comment says the rest of pydal uses metaclasses and dynamic attributes that mypy cannot follow, so the check is scoped to the modules that should stay clean.

That is an unusual and revealing admission. It means the library's public surface relies on runtime attribute lookup, which is the same mechanism that makes the multi-backend DSL work, but it also means a type checker will not help you with most of the API. The Makefile also shows the toolchain in use, with uv for environment and task management, ruff for linting and formatting, and unittest for the test suite, and the build target depends on the checks passing.

The third is the ORM question, and the project has already answered it. pyDAL will not give you the type safety of a modern ORM, because rows are dicts and the field objects are dynamic. What it gives you is a query surface that behaves the same way on fifteen backends, and for a web application that has to run against a customer's database rather than yours, that is the more valuable property.

The alternative worth comparing against is SQLAlchemy, which can do the same multi-backend job through its Core layer and adds an ORM on top. The difference in approach is that SQLAlchemy starts from an expression model you build up in Python, while pyDAL starts from a dictionary and a DAL, and the second is closer to what a PHP or Python web framework of the web2py era looked like. A team coming from an ORM will find pyDAL unfamiliar in a good way, and a team coming from web2py or py4web will find it familiar.

Editorial conclusion

pyDAL is a good fit for a project that needs to run the same application against several database backends, including a NoSQL store, without adopting an ORM's class-mapping model, because the query DSL is close to SQL and the same code runs on SQLite, PostgreSQL, MySQL, Oracle, MongoDB, Firestore and IMAP. It is a poor fit if you want the type-safety and static checking of a modern ORM, since rows are dicts and the API leans on dynamic attributes, and a poor fit for a project that has settled on one database. Before adopting, check the field types against the backends you plan to use, since the type table is where dialect differences surface, and read the DAL constructor options, because the folder and migrate settings decide where migration metadata is written and whether table changes are applied automatically.

Frequently asked questions

Official sources

1. Issues
2. License: BSD-3-Clause
3. README
4. Releases
5. web2py/pydal on GitHub