Database Reference
This reference collects database compatibility requirements, connection options, SQL type mappings, and function-registration settings. For an end-to-end example, use the DuckDB, PostgreSQL, MySQL, or Redis guide.
Client applications communicate with a server over its protocol. Server extensions load into the database process and have additional ABI and privilege requirements.
Compatibility
Choose SDKs that match your Codon installation and deployment platform. Server extensions also need to match the database they will be loaded into.
| Integration | Database requirement | Runtime dependency |
|---|---|---|
| DuckDB | Embedded SDK includes 1.4.3; extensions use the stable C extension API | Codon runtime; no separate embedded server |
| PostgreSQL | Server functions target PostgreSQL 16+ and must match its major version | Client applications use libpq |
| MySQL | Oracle MySQL 8.0+; MariaDB is not supported | Client applications use libmysqlclient |
| Redis | Redis 7.2+ | Client SDK includes hiredis; TLS builds include OpenSSL |
Native validation has covered macOS ARM64 with Codon 0.20.3, DuckDB 1.4.3/1.4.4, PostgreSQL 18.6, MySQL 8.4.11, and Redis 7.2.7. Linux is a build target but still requires completed native deployment validation. Windows is not supported.
MySQL
mysql.connect() and mysql.Connection() accept the same keyword arguments.
TCP connections verify the server certificate and hostname by default. Explicit
Unix sockets use local filesystem permissions instead of TLS.
| Option | Default | Purpose |
|---|---|---|
host, port | "127.0.0.1", 3306 | Server address |
user, password, database | "" | Credentials and database |
unix_socket | "" | Local socket path |
connect_timeout | 5 | Connection deadline in seconds |
read_timeout, write_timeout | 0 | I/O deadlines; zero uses the client-library default |
sslmode | "" | Defaults to verify_identity for TCP and disabled for Unix sockets |
sslca | "" | Trusted CA certificate |
sslcert, sslkey | "" | Client certificate and private key |
The connection starts in UTC with utf8mb4 encoding and autocommit enabled.
server_version, ssl_cipher, and in_transaction expose its current state.
verify_ca checks the certificate chain without checking the hostname.
required encrypts but does not authenticate the server; disabled permits
plaintext. Use these weaker policies only when your deployment explicitly needs
them.
Results expose columns, types, flags, rowcount, lastrowid, and
warning_count. For writes, rowcount counts changed rows rather than matched
rows. DatabaseError carries errno and sqlstate; ConnectionError indicates
authentication or transport failure. SQL warnings remain warnings, according to
the server's SQL mode.
Function Options
@mysql.function(name="sql_name", max_length=65535) sets the SQL name and maximum
string-result size in bytes. Names must be lowercase ASCII identifiers and must
not collide with runtime symbols. Larger string results need a larger explicit
limit.
Functions need fixed, typed arguments and one supported return type. The builder supports up to 100 arguments and 256 functions per library. It accepts Python-compatible source; put Codon-only syntax in imported helper modules.
MySQL UDF integers are signed and range-checked; fractional and overflowing inputs fail. REAL inputs follow MySQL's numeric coercion, but non-finite inputs and results are rejected. A runtime failure sets MySQL's UDF error flag, which can make the remaining rows of the expression NULL as well.
PostgreSQL
postgres.connect(conninfo="", **options) accepts a libpq connection string or
URI. Nonempty keyword options override that string; remaining settings come
from libpq's environment and defaults.
| Option | Default | Purpose |
|---|---|---|
host, dbname, user, password | "" | Connection settings |
port | 0 | Zero preserves libpq's configured port |
connect_timeout | 5 | Connection deadline in seconds |
sslmode | "" | Libpq's policy, normally prefer |
sslrootcert | "" | Trusted CA certificate |
sslcert, sslkey | "" | Client certificate and private key |
application_name | "codon" | Name shown in server activity |
For remote connections, use sslmode="verify-full" with a trusted CA. Libpq's
default prefer can fall back to plaintext and does not authenticate the server.
set_statement_timeout(milliseconds) limits query execution on the server;
the connection timeout does not limit how long a query can run.
Results expose columns, types (PostgreSQL OIDs), command, and rowcount.
DatabaseError.sqlstate identifies server errors. A SQL error inside a
transaction requires rollback before further work. ConnectionError means the
connection can no longer be used.
Function Options
@postgres.function(name="score", volatility="immutable", cost=20) sets the SQL
name and planner properties. Defaults are the Codon function's name, volatile,
and a cost of 100. Use immutable only for a result determined entirely by its
arguments; PostgreSQL may evaluate it in advance. stable promises consistency
within a statement. Generated functions are parallel-unsafe.
Functions support booleans, signed 16/32/64-bit integers, floats, UTF-8 strings,
bytes, and their optional forms. An uncaught Codon exception becomes PostgreSQL
SQLSTATE 38000. The current API provides scalar functions, not triggers,
table functions, aggregates, or access to the current SQL connection.
SQL Values
PostgreSQL and MySQL clients read rows into a tuple type supplied to a fetch method. Its field count must match the query. The common choices are:
| Codon type | Use for |
|---|---|
int, fixed-width integers | Integer columns; conversions check the range |
float, float32 | Approximate numeric values |
str | Text, or the textual representation of an unsupported SQL type |
bytes | Binary data |
Optional[T] | A nullable column of type T |
date, time, datetime | Representable date/time values |
json.JSON, typed lists/dictionaries/tuples | JSON-typed SQL results |
Keep exact decimals and very large numbers as text, using a SQL cast when
writing them back. Converting through floating point can lose precision. Use
SQL expressions to turn special database types into an appropriate supported
representation, such as ST_AsBinary for spatial values or array_to_json
for PostgreSQL arrays.
Codon's datetime types do not carry a time zone. PostgreSQL zoned values should
be read as text or explicitly normalized in SQL, for example with
AT TIME ZONE 'UTC'. MySQL connections start in UTC, so TIMESTAMP reads follow
that convention while DATETIME remains a wall-clock value. Negative or multi-day
MySQL TIME values need timedelta or text. Invalid and out-of-range dates raise
conversion errors.
Fetching Rows
Both clients provide fetchone(Row), fetchmany(Row, size=2048), rows(Row),
batches(Row, size=2048), and fetchall(Row). fetchone() returns None at
the end; the other fetching methods return the remaining rows or batches.
query() buffers its result. Iterating that result in batches only bounds the
conversion work, not the original buffer. Use cursor() when the result is too
large to load at once, and keep the connection open until the cursor is closed.
Client Scope
These are synchronous clients without connection pooling or automatic retries. Use one connection per concurrent worker. PostgreSQL offers COPY for bulk transfer; MySQL does not currently expose multiple result sets or a stored procedure API. Neither client is an ORM.
DuckDB
duckdb.connect(path=":memory:", threads=1) opens a database. File-backed
connections share a database instance; in-memory connections are independent.
query(sql, stream=False) returns a result and execute(sql) discards it.
The embedded API accepts SQL strings and does not support bound query parameters.
Function registration supports scalar and vectorized UDFs with explicit argument and return types. Aggregate functions, table functions, and automatic signature inference are not supported.
DuckDB results use fetchmany, batches, rows, and fetchall with an explicit
tuple type. The requested type must match the SQL type, so a SUM(BIGINT) result
needs i128 for DuckDB's HUGEINT, not int.
DuckDB Types
In addition to numbers, strings, bytes, and optional values, the integration supports nested lists, positional tuples as STRUCTs, and dictionaries as MAPs. Map keys cannot be optional.
| Codon type | DuckDB type |
|---|---|
i8 / i16 / i32 / int | TINYINT / SMALLINT / INTEGER / BIGINT |
u8 / u16 / u32 / u64 | Corresponding unsigned types |
i128, u128 | HUGEINT, UHUGEINT |
float32, float | FLOAT, DOUBLE |
duckdb.Decimal[p, s] | DECIMAL(p,s) with an exact scaled i128 coefficient |
date, time, datetime | DATE, TIME, TIMESTAMP |
duckdb.TimestampTZ | Zoned timestamp; .utc is a UTC datetime |
duckdb.TimeTZ | Zoned time; .time and .offset_seconds |
duckdb.Interval | Calendar interval; months, days, and microseconds |
Decimal precision is 1 to 38, and scale is between zero and precision. Temporal
values must fit Codon's range and microsecond precision. An interval containing
calendar months cannot be converted to a fixed-duration timedelta.
Vector API
Vectorized callbacks receive read-only input vectors and one writable output. Vectors are valid only during the callback; values copied out of them can be retained.
| Operation | Purpose |
|---|---|
len(vector) | Rows in this callback |
vector.is_valid(index) | Check for SQL NULL |
vector[index] | Read a typed value |
output[index] = value | Write a result |
output.set_null(index) | Write SQL NULL |
Redis
Redis() connects to 127.0.0.1:6379, database 0, using RESP2. Select RESP3
with protocol=3. Convenience methods normalize either protocol to native Codon
values.
| Option | Default | Purpose |
|---|---|---|
host, port, db | "127.0.0.1", 6379, 0 | Server and database |
username, password | "" | ACL credentials |
unix_socket_path | "" | Local socket instead of TCP |
socket_connect_timeout | 5.0 | Connection timeout in seconds |
socket_timeout | 5.0 | I/O timeout; zero allows an indefinite wait |
ssl | False | Enable verified TLS |
ssl_ca_certs | "" | CA bundle; empty uses the configured trust store |
ssl_certfile, ssl_keyfile | "" | Client certificate and key |
ssl_server_hostname | "" | Verified hostname; empty uses host |
Keys and values accept bytes, UTF-8 strings, integers, or floats. Reads return
bytes unless you request a type. Missing values use Optional[T]; the API does
not confuse a missing key with an empty string or zero.
execute_command("COMMAND", *args) and execute(iterable) return a ClientReply
for commands without a convenience method. Use it in a with block, then read
through text(), bytes(), int(), or as_type(T). Child replies depend on their
root reply; copied values do not.
Redis Module Commands
module.command(name, flags="readonly", arity=-1, keys=(0, 0, 0), key_access="auto")
registers a handler taking (context, args). arity counts arguments without the
command name. The keys tuple gives first key, last key, and step, counted from
one; a negative last position counts backward from the end.
Declare every key the handler accesses, including through nested commands.
key_access can be read, write, or readwrite; automatic access follows the
command flags. A read/modify/write operation needs read/write permission.
Handlers may return native scalars, optional values, lists, tuples, or a command
reply. Status and Error represent protocol status and error responses.
Exceptions become Redis errors but do not undo writes that already happened.
Module contexts and replies cannot be retained beyond the invocation.