-   [](https://platform.exaloop.io/)
-   Database Reference

# 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](https://platform.exaloop.io/db/duckdb/), [PostgreSQL](https://platform.exaloop.io/db/postgres/), [MySQL](https://platform.exaloop.io/db/mysql/), or [Redis](https://platform.exaloop.io/db/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.

Source: https://platform.exaloop.io/db/reference/