MySQL / MariaDB

import { Aside } from ‘@astrojs/starlight/components’;

One mysql adapter targets both MySQL 8 and MariaDB 11 — the same project runs against either. SQLAnvil generates portable MySQL-dialect SQL; engine-specific features ride through operations.

MySQL support landed in `@sqlanvil/core` **1.5.0** (the `mysql: {}` block, COMMENT metadata, and materialized-view emulation arrived in 1.6.0). Pin `sqlanvilCoreVersion: 1.6.0` or newer.

Connection

Non-secret settings go in workflow_settings.yaml (committed); the connection — including the password — goes in .df-credentials.json (gitignored):

workflow_settings.yaml

warehouse: mysql
defaultDataset: analytics            # the MySQL DATABASE your models build into
defaultAssertionDataset: sqlanvil_assertions
sqlanvilCoreVersion: 1.6.0

// .df-credentials.json  (gitignored) — MysqlConnection
{
  "host": "localhost",
  "port": 3306,
  "database": "analytics",
  "user": "root",
  "password": "your-password",
  "sslMode": "disable"
}

MySQL has no schema-vs-database split — `defaultDataset` is the **database**, and the credentials have **no `defaultSchema`** field. `sslMode` is `disable` (local) or `require` (managed/TLS).

Try it locally:

docker run --rm -e MYSQL_ROOT_PASSWORD=password -e MYSQL_DATABASE=analytics -p 3306:3306 -d mysql:8
# or MariaDB: ... -p 3307:3306 -d mariadb:11

Tables

-- definitions/orders.sqlx
config { type: "table" }
SELECT order_id, customer_id, total FROM ${ref("raw_orders")}

Generates a drop + CREATE TABLE … AS SELECT. Identifiers compile to two-part backticks — `analytics`.`orders`.

Incremental tables (upserts)

config { type: "incremental", uniqueKey: ["order_id"] }
SELECT order_id, customer_id, total FROM ${ref("raw_orders")}
${ when(incremental(), `WHERE updated_at > (SELECT MAX(updated_at) FROM ${self()})`) }

uniqueKey compiles to INSERT … ON DUPLICATE KEY UPDATE, and the adapter auto-creates the matching unique index on the first/--full-refresh build — you don’t add your own PK.

MySQL-specific config (mysql: block)

Declare secondary indexes and table options:

config {
  type: "table",
  mysql: {
    engine: "InnoDB",
    charset: "utf8mb4",
    collation: "utf8mb4_unicode_ci",
    indexes: [
      { name: "ix_customer", columns: ["customer_id"], unique: false }
    ]
  }
}
SELECT ...

Field	Type	Description
engine	string	Storage engine → ENGINE= (e.g. InnoDB)
charset	string	Default character set → DEFAULT CHARSET=
collation	string	Default collation → COLLATE=
rowFormat	string	Row format → ROW_FORMAT= (e.g. DYNAMIC, COMPRESSED) (≥ 1.19.0)
indexes[].name	string	Index name (derived if omitted)
indexes[].columns	string[]	Indexed columns; a column may carry a MySQL prefix length — "body(50)" → `body`(50) (required to index TEXT/BLOB) (≥ 1.19.0)
indexes[].unique	boolean	Create a UNIQUE index
indexes[].type	string	"fulltext" or "spatial" (≥ 1.19.0); mutually exclusive with unique

No where/include/opclass (those are Postgres-only). A SPATIAL index requires a NOT NULL SRID-attributed geometry column — CTAS-created columns are nullable, so spatial normally needs an ALTER TABLE … MODIFY in post_operations first.

Partitioning

Native MySQL/MariaDB partitioning via mysql: { partition: {...} } (≥ 1.11.0):

// RANGE (explicit child partitions)
config { type: "table", mysql: { partition: {
  kind: "RANGE", expression: "id",
  partitions: [
    { name: "p0",   values: "values less than (1000)" },
    { name: "pmax", values: "values less than maxvalue" }
  ]
}}}

// HASH / KEY (partition count)
config { type: "table", mysql: { partition: { kind: "HASH", expression: "id", count: 4 } } }

kind is RANGE/LIST/HASH/KEY. RANGE/LIST take explicit partitions[] (the values body is emitted verbatim — values less than (...) / values in (...), use MAXVALUE for a catch-all); HASH/KEY take count. MySQL requires every column in the partition expression to be in every UNIQUE/PRIMARY key, so a partitioned incremental’s uniqueKey must include the partition column(s). RANGE COLUMNS / LIST COLUMNS aren’t supported yet — use raw DDL in operations.

Comments

description: and columns: apply as real table/column comments, read back from information_schema:

config {
  type: "table",
  description: "Cleaned orders.",
  columns: { order_id: "primary key", total: "order total in cents" }
}
SELECT ...

Comments apply to tables and incremental tables only — MySQL views can't carry comments.

Materialized views

MySQL/MariaDB have no native materialized views, so materialized: true is emulated as a refreshed table snapshot — rebuilt (drop + CREATE TABLE … AS SELECT) on every run, honoring the mysql: {} block:

config { type: "view", materialized: true, mysql: { indexes: [{ columns: ["id"], unique: true }] } }
SELECT id, count(*) AS n FROM ${ref("events")} GROUP BY id

It reads back as a regular table; refresh = re-run. (The mysql: {} block on a type: "view" config compiles through since 1.19.0 — earlier versions silently ignored it there.)

Procedures, functions, triggers

Use type: "operations". Statements are separated by ---, never ;, so a CREATE PROCEDURE … BEGIN … END body is one statement (its internal ; survive) — don’t use DELIMITER (it’s a client-only directive and will fail).

`sqlanvil introspect` works for MySQL/MariaDB (≥ `1.11.0`): point it at a `platform: mysql` connection to scaffold a `declaration` from an existing table's columns/types/comments. Since **1.18.0**, a Postgres/Supabase project can also **read MySQL data at run time**: MySQL sources are runner-extract only (no FDW — the rows are materialized into a plain table each run). See [Foreign sources](/docs/guides/foreign-wrappers/).

Configuration reference

workflow_settings.yaml (committed):

Field	Required	Description
warehouse	✓	mysql
defaultDataset	✓	Default database for actions
defaultAssertionDataset		Database for generated assertions
sqlanvilCoreVersion	✓	Pin @sqlanvil/core (≥ 1.6.0 for the mysql: block)

.df-credentials.json (gitignored — the MysqlConnection):

Field	Required	Description
host	✓	Database host
port	✓	Database port (default: 3306)
database	✓	Database name
user	✓	Database user
password	✓	Password
sslMode		disable or require

Troubleshooting

Symptom	Likely cause	Fix
Could not connect to MySQL … Access denied	Wrong user/password	Check .df-credentials.json; for the local container the user is root, password password.
Could not connect … ECONNREFUSED	Wrong host/port, or DB not up yet	Confirm the container is running and the port (3306 MySQL, 3307 MariaDB); wait for it to accept connections.
Unexpected property "defaultSchema"	Postgres-shaped creds on a mysql project	MySQL .df-credentials.json has no defaultSchema — use host/port/database/user/password/sslMode.
Unsupported warehouse "mysql"	sqlanvilCoreVersion pinned too low	MySQL warehouse support landed in core 1.5.0; remove the pin or set it ≥ 1.5.0.
TLS / HANDSHAKE_NO_SSL_SUPPORT	sslMode: require against a server without TLS	Use "sslMode": "disable" for local/non-TLS servers.

Notes & limits

- Backtick-quote identifiers in raw DDL (`` `col` ``), never double-quote. - The `mysql: {}` block covers engine/charset/collation/`rowFormat`, plain/unique/`FULLTEXT`/`SPATIAL`/prefix indexes, and partitioning; anything beyond that (e.g. `AUTO_INCREMENT` seeds, tablespace clauses) is raw DDL in `operations`. - A **mysql warehouse** can't read cross-warehouse named connections (the bridge targets Postgres/Supabase). MySQL **as a source** for a Postgres/Supabase warehouse works since 1.18.0 (runner-extract) — see [Foreign sources](/docs/guides/foreign-wrappers/).