backends.md

Backend configuration and types

Protocols and construction

Backend	Protocol	Source options
SQLite	db+sqlite	Required database, or path / url alias.
PostgreSQL	db+postgresql, db+postgres	dsn, url, or connection fields.
MySQL	db+mysql	dsn, url, or connection fields.
DuckDB	db+duckdb	database=":memory:" or existing connection. Requires the duckdb extra.
SQLAlchemy	db+sqlalchemy	url or existing engine. Requires SQLAlchemy and a backend driver.
ODBC	db+odbc	Required connection_string; optional user, password, batch_size=65535. Requires the odbc extra and an OS driver.

SQLite accepts a path, :memory:, or SQLite URL. Network backends accept host, port, database, user, and password. PostgreSQL accepts sslmode; MySQL accepts ssl_mode and charset. Both accept min_connections and max_connections. An explicit DSN or URL takes precedence over connection fields. fsspec config sections use the backend protocol name; explicit constructor arguments take precedence.

Parameters

Raw query(sql, params=None) uses driver-specific placeholders:

Backend	Placeholder
SQLite	?
PostgreSQL	$1, $2, ...
MySQL	?

SQLAlchemy uses its driver's SQL parameter conventions; DuckDB uses ?. ODBC parameters are passed to its driver. The bridge accepts scalar parameters of None, bool, int, float, str, and bytes.

SQLite mapping

SQLite values are converted into Arrow arrays with a small affinity mapper. Query results use declared column metadata plus observed non-null runtime value types, so NULL-first and mixed numeric expression columns can still produce numeric Arrow arrays.

SQLite value/type	Arrow type
boolean-like	bool
integer-like	int64
real/float/double	float64
blob/binary	binary
everything else	utf8

For writes, Arrow Null columns bind SQLite NULL. Temporal arrays are bound as integer epoch values. When a query produces mixed SQLite runtime types in one expression column, cast explicitly in SQL for predictable Arrow output.

PostgreSQL and MySQL mapping

The sqlx backends convert common database scalar types into Arrow:

Backend	Database type family	Arrow type
PostgreSQL	BOOL	bool
PostgreSQL	INT2, INT4, INT8, OID	int64
PostgreSQL	FLOAT4, FLOAT8	float64
PostgreSQL	BYTEA	binary
PostgreSQL	TEXT, VARCHAR, CHAR, BPCHAR	utf8
MySQL	BOOLEAN	bool
MySQL	signed and unsigned integer families	int64
MySQL	FLOAT, DOUBLE	float64
MySQL	BINARY, VARBINARY, *BLOB	binary
MySQL	CHAR, VARCHAR, *TEXT, JSON, enums	utf8

Additional native output mappings are:

Backend	Database type	Arrow type
PostgreSQL	DATE	date32
PostgreSQL	TIME	time64[us]
PostgreSQL	TIMESTAMP, TIMESTAMPTZ	timestamp[us] without timezone metadata
PostgreSQL	NUMERIC, UUID, JSON, JSONB	utf8 containing the decoded value's text representation
MySQL	DATE	date32
MySQL	DATETIME, TIMESTAMP	timestamp[us] without timezone metadata
MySQL	DECIMAL, JSON	utf8 containing the decoded value's text representation

PostgreSQL NAME also maps to UTF-8. MySQL YEAR and BIT follow the integer mapping; SET follows the string mapping. Unsigned MySQL integers that do not fit in Arrow int64 return an error. Non-null output types absent from these mappings raise an unsupported-type error; explicit SQL casts can select a supported type.

Metadata and writes

SQLite introspection uses PRAGMA database_list, sqlite_master, table_info, index_list, index_info, and foreign_key_list. Schema listings do not count every relation; relation lookups inspect the requested relation.

PostgreSQL uses information_schema and pg_catalog. Root listings exclude pg_catalog, information_schema, and toast schemas. MySQL uses information_schema and excludes information_schema, mysql, performance_schema, and sys from root listings. MySQL database names are schema directories.

Native PostgreSQL overwrite uses TRUNCATE TABLE; MySQL and SQLite use DELETE FROM in the insertion transaction. Python adapter atomicity depends on the adapter. ODBC lists no indexes and does not provide portable view definitions.

Native queries expose RecordBatchStream through the Rust Database trait. The current sqlx backends construct Arrow arrays from rows. ArrowExtraction records the extraction strategy without changing path semantics.