MySQL Adapter

Generate MySQL SQL from your model.

What the adapter emits

  • Identifiers in `backticks`; bare column names in expressions are lowercased.
  • No FULL JOIN. When a query blends measures from two facts, the per-fact subqueries are combined with a LEFT JOIN instead of the FULL OUTER JOIN other dialects get. Groups that exist only on the right-hand fact are not returned.
  • MySQL has no DATE_TRUNC, so grain truncation is built from MySQL’s own date functions; parts with YEAR(x), MONTH(x), DAYOFWEEK(x).
  • CTEs for the per-fact subqueries and window functions where a decorator needs them; both require MySQL 8.0 or later.

Configuration

mysql_db:
  adapter: mysql
  name: MySQL Database
  tier: warm
  host: db.example.com
  port: 3306
  database: analytics
  username: analyst
  # password: never here; zsql deploy strips it if present

Connection fields

The server never connects to MySQL: it has no database driver in it, and the only keys the deployed model keeps are adapter, tier and the name. Your application runs the statement it gets back, with its own credentials.

zsql on this machine does connect, read-only, for zsql catalog, zsql sample and zsql run. It takes username and a password; --schema on a catalog read names the database. TLS is not wired up for this driver yet. Secrets go in .zsql or ZSQL_DS_<KEY>_<FIELD>, never in datasources.yml; zsql deploy strips password from the file if present. See Connecting to a warehouse.

Notes

  • Expressions are MySQL SQL. group_concat, if() and date_format are fine in expression.sql.
  • Blends are left joins. Put the fact whose groups must all appear first in the spec’s projections, or model a conformed dimension table so each fact’s measure can be served from a path that reaches it.

Next Steps