SQL compilation for asyncpg
The native PostgreSQL driver executes SQL on asyncpg. This page describes how the driver builds that SQL with Django's query compiler without a database connection, how Django's output differs from what asyncpg accepts, and what SQLAlchemy Core provides as an alternative.
Design
The native PostgreSQL driver builds its SQL with DjangoCompiler. It:
- turns an adorm operation into a Django query;
- compiles it on a database wrapper that never connects;
- converts placeholders and parameters for asyncpg;
- applies Django's result converters to the rows asyncpg returns.
Lookups, functions, custom fields and their converters are Django's own. The asyncpg extra installs Django and psycopg 3 along with asyncpg.
SQLAlchemy Core can build the SQL for applications without Django models. It needs table metadata and its own translation of every adorm construct; adorm does not provide such a builder.
compiler = DjangoCompiler("default", server_version=int(await conn.fetchval("SHOW server_version_num")))
statement = compiler.compile(Club.objects.filter(name__icontains="chess").values("id", "name"))
rows = statement.rows(await conn.fetch(statement.sql, *statement.params))Django's compiler
queryset.query.get_compiler(using).as_sql() returns (sql, params). It builds the SQL from the database wrapper's ops, features and vendor, none of which opens a connection, except in these cases:
| Construct | Why it connects |
|---|---|
AnyValue, JSONObject, JSONArray, UUID7 | features.is_postgresql_16 / _18 read pg_version, a cached property that opens a temporary connection to ask the server |
select_for_update() | the compiler calls get_autocommit() to refuse locking outside a transaction, and that ensures a connection |
SearchHeadline with options | the options are quoted with psycopg's mogrify(), which needs a connection |
an update that sets fields of a multi-table inheritance parent (related_updates) | pre_sql_setup() selects the affected keys with execute_sql() on connections[alias]; the parent tables' updates run only in execute_sql() |
executing (execute_sql(), results_iter() without results) | runs the statement |
A value stored on the wrapper (wrapper.__dict__["pg_version"]) replaces the pg_version query. The server version then determines the SQL: JSONObject compiles to JSON_OBJECT(... RETURNING JSONB) from PostgreSQL 16 and to JSONB_BUILD_OBJECT(...) before; AnyValue raises Django's NotSupportedError before 16.
DjangoCompiler:
- stores the server version it is given;
- answers
get_autocommit()withFalseand checksselect_for_update()against the caller'sin_transaction; - refuses the parent-field update and
SearchHeadlinewith options.
The native PostgreSQL driver runs the parent-field update itself: it selects the keys as pre_sql_setup() does, then compiles one update per table filtered by those keys.
get_compiler(using) takes the wrapper from django.db.connections, which is thread-local: each thread has its own wrapper and its own cached pg_version. get_compiler(connection=wrapper) takes any wrapper, including one built from a DATABASES entry with load_backend(engine).DatabaseWrapper(settings_dict, alias) and never connected. Neither get_compiler() nor as_sql() is async_unsafe in Django, so compiling inside a running event loop works; only a connection attempt raises SynchronousOnlyOperation there. Compiling only fills the wrapper's cached properties, with the same values in every thread, so one wrapper serves concurrent threads.
as_sql() of a select, update, delete or aggregate query returns one statement. An insert returns a list of statements, one per batch, and one statement for a single row on PostgreSQL. With the compiler's returning_fields set, an insert or update ends with RETURNING. Django's insert reads the primary key and the values the database sets that way; Django 6 also reads the values of expressions a save writes. DjangoCompiler.compile_insert() and compile(..., returning=...) set returning_fields; DjangoStatement.returned() applies the fields' converters to the returned rows.
query = InsertQuery(Tag)
query.insert_values([Tag._meta.get_field("name")], [Tag(name="night")])
statement = compiler.compile_insert(query, [Tag._meta.pk])
statement.sql # 'INSERT INTO "library_tag" ("name") VALUES ($1) RETURNING "library_tag"."id"'Queries without a statement
Django executes some queries without SQL:
- A filter that can match nothing (
pk__in=[]) raisesEmptyResultSetwhile compiling, andexecute_sql()returns no rows. Withelide_empty=Falsea select compiles to... WHERE 0 = 1, which also returns the correct aggregate values over no rows.DjangoCompilercompiles this way. - An update or delete with such a filter raises
EmptyResultSetregardless, and an update without values compiles to an empty string.DjangoCompilerreturns an empty statement for both (DjangoStatement.empty); it affects no rows and is not executed. aggregate()over such a query returns each aggregate's value over no rows without SQL. The compiled aggregation has an empty statement and that result.
Aggregation
Django's aggregate() decides inside Query.get_aggregation() whether the aggregates run directly on the query or over it as a subquery (a slice, distinct(), a values().annotate() grouping or an aggregate annotation). It builds that statement, executes it on connections[using] and converts the row. No public Django method returns the statement without executing it.
DjangoCompiler.compile_aggregate()runsget_aggregation()on its own wrapper, registered under its own alias in the calling thread'sconnectionsfor the duration of the call. The wrapper's cursor raises with the statement instead of executing it.- After asyncpg fetches the row,
DjangoAggregation.result()runsget_aggregation()again with a cursor that returns that row, so Django's converters and names shape the result.
Both runs are synchronous and leave neither the alias nor the cursor in any thread or task.
aggregation = compiler.compile_aggregate(Book.objects.order_by("-pages")[:2], {"pages": Sum("pages")})
aggregation.statement.sql # 'SELECT SUM("__col1") FROM (SELECT ... LIMIT 2) subquery'
aggregation.result(await conn.fetchrow(aggregation.statement.sql, *aggregation.statement.params))Placeholders
Django's compiler emits the PEP 249 format paramstyle: %s for a parameter, %% for a literal percent sign (its pattern escaping for contains with an expression produces REPLACE(..., E'%%', E'\\%%')). Values are never inlined: Value("a%s") stays a parameter. LIMIT and OFFSET are inlined as integer literals. psycopg interprets % everywhere in the statement, including inside quoted literals and identifiers.
asyncpg accepts only numbered placeholders, $1 … $n. numbered_placeholders() applies psycopg's rule: %s becomes the next $n, %% becomes %, anything else after % is an error.
UPPER("library_book"."title"::text) LIKE UPPER(%s) AND "library_book"."pages" >= %s
UPPER("library_book"."title"::text) LIKE UPPER($1) AND "library_book"."pages" >= $2::int4Parameter types: psycopg and asyncpg
Parameter binding:
- psycopg with client-side binding (Django's default) interpolates each value into the statement as a literal. Numbers are untyped literals: an integer is
int4,int8ornumericby size, a finite float isnumeric, infinity and NaN are'Infinity'::float8. Other values carry a cast ('2024-01-01'::date,'{1,2}'::int2[]); astris an untyped literal. - psycopg with server-side binding (
OPTIONS["server_side_binding"]) sends each parameter with the type OID of its Python type: an integer as the smallest ofint2,int4,int8,numeric, a float asfloat8, astrasunknown. - asyncpg prepares the statement and lets the server infer each parameter's type from the context. It then requires a Python value of exactly that type's encoder. Without a typing context (
SELECT $1) the server inferstext.
Django prepares parameters for psycopg: integer field values wrapped in psycopg's Int2, Int4 and Int8, JSON values in Jsonb, binary values in Binary, IP addresses as ipaddress objects, ranges as psycopg's Range. On asyncpg this fails in two cases:
| Case | asyncpg's error |
|---|---|
a parameter without a typing context: Exists() compiles to SELECT %s AS "a" with Int4(1), and Value(1), Value(True), Value(datetime) in a select list | invalid input for query argument $1: Int4(1) (expected str, got Int4) |
| a psycopg wrapper asyncpg has no encoder for | expected str, got Jsonb; a bytes-like object is required, not 'Binary' |
bind_param() unwraps each parameter and casts its placeholder to the type it has under psycopg in the alias's binding mode ($1::int4, $2::jsonb with the JSON text); the server resolves the statement as under psycopg. A str stays uncast. Where the context already types the parameter, the cast changes nothing. Where it does not, the binding mode determines the result:
Value(30000) + Value(30000)isint4arithmetic with client-side binding and overflowsint2with server-side binding;Value(0.1) + Value(0.2)isnumeric0.3with client-side binding andfloat80.30000000000000004with server-side binding.
Naive datetimes: psycopg sends one as timestamp, which the server converts to timestamptz in the session's time zone; asyncpg's timestamptz encoder reads it as UTC. With the cast to timestamp the session's time zone applies, as under psycopg.
asyncpg sends at most 32767 parameters with one statement and raises InterfaceError above that. DjangoCompiler refuses a statement with more and sets its wrapper's features.max_query_params to that limit. On Django 6 compiler.connection.ops.bulk_batch_size(fields, objs) returns batch sizes within it; Django 5.2's PostgreSQL backend does not read it.
Results
For most fields asyncpg returns the same Python types as psycopg under Django (Decimal, timedelta, bytes, lists for arrays, aware datetime for timestamptz). json / jsonb columns are text on both: Django's psycopg setup loads JSON as text and decodes it in JSONField.from_db_value(). compiler.results_iter(results=[rows]), which applies the fields' converters to rows fetched elsewhere, decodes asyncpg's text the same way.
Differences in what asyncpg returns:
| Column | Django on psycopg | asyncpg |
|---|---|---|
inet, cidr | str (GenericIPAddressField has no converter) | ipaddress objects |
uuid | uuid.UUID | asyncpg.pgproto.pgproto.UUID, a subclass that compares equal |
columns added for ordering (distinct() with order_by() on an expression) or by extra(select=) | dropped by execute_sql(), which trims rows to col_count | returned |
DjangoStatement.rows() applies the converters and removes these differences. It trims each row to col_count when the compiler has extra select columns, turns ipaddress values into text and asyncpg's UUIDs into uuid.UUID, also inside arrays.
Django's psycopg loader relabels every timestamptz result with the connection's time zone (UTC under USE_TZ) without converting it; the result is correct only while the session's time zone is the one Django set. asyncpg returns the correct instant in any session time zone.
SQLAlchemy Core
SQLAlchemy's asyncpg dialect compiles a Core statement without an engine or a connection (statement.compile(dialect=asyncpg.dialect())). Its paramstyle is numeric_dollar. It renders a cast for every parameter from the column or literal type ($1::INTEGER, $2::JSONB, $3::TIMESTAMP WITH TIME ZONE), as bind_param() does. The compiled object lists parameters by name in positiontup. Types with a bind processor (JSON) need it applied before the value goes to raw asyncpg, as SQLAlchemy does when it executes the statement.
compiled = update(book).where(book.c.isbn == "x").values(pages=book.c.pages + 1).compile(dialect=asyncpg.dialect())
str(compiled)
# 'UPDATE library_book SET pages=(library_book.pages + $1::INTEGER) WHERE library_book.isbn = $2::VARCHAR'
[compiled.params[name] for name in compiled.positiontup] # [1, 'x']SQLAlchemy needs Table metadata with column types, which adorm's model schema can supply, and its own translation of every adorm lookup, function and aggregate. Django's custom lookups, fields and converters are not available through it.