MYSQL

PROFILES.YML — A COMPLETE WORKING EXAMPLE

The common case: TCP, username and password. Drop this in ~/.dbt/profiles.yml (yes, .dbt — DVT reads the same file dbt does, on purpose) and you have a working MySQL connection:

my_project:
  target: mysql_dev
  outputs:
    mysql_dev:
      type: mysql
      host: db.internal.example.com
      port: 3306                # optional — 3306 is the driver default
      user: analytics
      password: "{{ env_var('MYSQL_PASSWORD') }}"
      schema: analytics_db      # on MySQL, the schema IS the database
      threads: 4

Notice what's not there: no database: key. On MySQL the schema and the database are the same namespace, so schema: alone names where DVT builds — the section below explains the rule and what happens if you add database: anyway. Then let DVT do the installing and the checking:

$ dvt sync     # reads the profile, installs the MySQL adapter + driver
$ dvt debug    # proves the connection actually works, both layers

dvt sync sees type: mysql and installs the adapter and its driver (mysql-connector-python) into the one DVT environment — nothing to pick or pin yourself.

OTHER WAYS TO CONNECT

Unix socket — for a MySQL server on the same machine, skip TCP entirely and point at the socket file. Leave host out: if a host is present, it wins and the socket is ignored.

    mysql_local:
      type: mysql
      unix_socket: /var/run/mysqld/mysqld.sock
      user: analytics
      password: "{{ env_var('MYSQL_PASSWORD') }}"
      schema: analytics_db

Canonical spelling— the same TCP profile written with the adapter's own field names, exactly equivalent to the first example:

    mysql_dev:
      type: mysql
      server: db.internal.example.com
      username: analytics
      password: "{{ env_var('MYSQL_PASSWORD') }}"
      schema: analytics_db

MySQL 8 auth plugins — caching_sha2_password, the MySQL 8 default, works out of the box; no extra profile keys needed. Older accounts on mysql_native_password work the same way.

TLS client certificates (mutual TLS) — if your server authenticates clients with certificates, point the profile at the key material. The same three fields feed both lanes, and paths may start with ~ — DVT expands it. Ordinary server-side TLS still negotiates on its own when the server offers it.

    mysql_secure:
      type: mysql
      host: db.internal.example.com
      user: analytics
      password: "{{ env_var('MYSQL_PASSWORD') }}"
      schema: analytics_db
      ssl_ca: ~/.mysql/ca.pem
      ssl_cert: ~/.mysql/client-cert.pem
      ssl_key: ~/.mysql/client-key.pem

ONE NAMESPACE: SCHEMA IS THE DATABASE

MySQL has no separate schema layer — CREATE SCHEMA is literally a synonym for CREATE DATABASE. DVT's profile mirrors that: schema: names the database you build in, and database: may be omitted (recommended) or set to exactly the same value. Anything else is refused before a single query runs:

    schema: analytics_db
    database: other_db
On MySQL, database must be omitted or have the same value as schema.

The same rule reaches your sources: declare them with schema: only. A source that sets database: to a different value — legal on every other engine — is a hard parse error on the MySQL family. Reading across two databases on one server needs no config at all: other_db.some_table is ordinary MySQL SQL.

PURE-PYTHON DRIVER BY DEFAULT (USE_PURE)

DVT connects to MySQL with the driver's pure-Python implementation — use_pure: trueis the default, and it's a deliberate one. The driver's C extension has a crash class where a failed connect can segfault, and a segfault kills the whole interpreter: no traceback, no retry, nothing to catch. The pure implementation turns every driver failure back into an ordinary error DVT can report and handle.

The usual objection — speed — doesn't apply here. DVT moves bulk data through its own data-movement layer, so this connection carries only DDL, catalog reads and small statements. If you still want the C extension, it's one line:

    mysql_dev:
      type: mysql
      # ...
      use_pure: false     # opt back into the C extension

INCREMENTAL MODELS — NO MERGE, AND THAT'S FINE

MySQL has no MERGEstatement, so the adapter doesn't pretend to have one. An incremental model with a unique_key runs as delete+insert: rows matching incoming keys are deleted, then the new rows are inserted. Without a unique_key it's a plain append. There is no incremental_strategyto choose — the key decides. The adapter doesn't police the config either: its materialization never reads incremental_strategy, so a strategy written on a MySQL model is silently ignored rather than refused. There is no error text for a wrong one, because nothing looks at it.

{{ config(
    materialized='incremental',
    unique_key='order_id'      -- present: delete+insert; absent: append
) }}

select order_id, status, updated_at
from {{ source('app_db', 'orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

The federated lane answers differently, and the difference is worth stating. When MySQL is the target of an f_incremental model, DVT computes the model and lands the rows through its data-movement layer, so the strategy selects a load mode instead of compiling into MySQL SQL — and there it does mean something. Three names are understood: append, merge and delete+insert, with merge and delete+insert resolving to the same key-based upsert on unique_key. insert_overwrite and microbatch are refused by name with DVT025— "has no Sling equivalent — f_incremental supports append, merge and delete+insert". Either upsert without a unique_key warns and falls back to a full refresh rather than failing, and append without a watermark_column does the same.

MySQL is also one of the engines admitted to the keyless-watermark set — the measured list is postgres, redshift, mysql, duckdb, sqlite, sqlserver, oracle, clickhouse and trino — so an f_incremental carrying only a watermark_column and no key runs against a MySQL target instead of being refused with DVT026. Note the list is by engine name: MariaDB and mysql5 are separate types and are not on it. And --full-refresh rebuilds the table safely — the old table is renamed to a backup, the new one is built, and the backup is dropped last.

QUOTING, CASE, AND OTHER ENGINE MANNERS

MySQL quotes identifiers with backticks — and by default treats "double quotes"as string literals, not identifiers. DVT's federation layer knows this and renders MySQL-dialect SQL when it pushes work here; keep your own identifiers lower-case and unquoted and you'll never think about it. Table-name case sensitivity follows the server's filesystem (lower_case_table_names), which is one more reason lower-case names are the safe habit.

Catalog support is full: the adapter reads information_schema, so dvt docs generate, dvt metadata, profiling, and dvt generate-sources all see your columns, types and constraints.

USING IT LIVE: MYSQL AS A FEDERATION SOURCE

The same profile entry doubles as a live connection. Name it from a source's meta.connectionand that source's tables can join anything else your project reaches — the federated model below mixes MySQL orders with a Postgres customer table, and DVT does the moving:

# models/sources.yml
sources:
  - name: app_db
    meta:
      connection: mysql_dev     # the profiles.yml output above
    schema: analytics_db
    tables:
      - name: orders

-- models/marts/orders_enriched.sql
{{ config(materialized='f_table') }}

select o.order_id, o.status, c.segment
from {{ source('app_db', 'orders') }} o
join {{ source('pg_crm', 'customers') }} c
  on o.customer_id = c.id

MOVING DATA IN AND OUT

When a federated model reads from or writes to MySQL, DVT's data-movement layer carries the rows and stages loads through a temp table — no external staging area, no extra privileges beyond ordinary read/write. Seeds work the same way: dvt seed --target mysql_dev loads files straight in.

LOCAL DEV IN ONE LINE

Need a scratch MySQL to try this page against? Docker has you:

$ docker run -d --name dvt-mysql \
    -e MYSQL_ROOT_PASSWORD=devpass -e MYSQL_DATABASE=analytics_db \
    -p 3306:3306 mysql:8.4

Point the profile at host: 127.0.0.1, user: root, schema: analytics_db, and dvt debug away.

REFERENCE — EVERY PROFILE FIELD

Everything the type: mysql profile entry reads. Aliases land on the same field — write either spelling, not both.