Type mapping

What your MySQL TINYINT(1) / ENUM / DECIMAL / JSON / temporal types become on Postgres (and vice versa), and on SQLite / D1 — the cross-engine translation policies.

sluice never translates one dialect straight to another. Every column type maps source-dialect → typed IR → target-dialect: source-specific knowledge lives in readers, target-specific knowledge in writers, and the IR is the only shared contract. That's why the four-direction matrix needs four readers and four writers, not twelve pairwise tables. This page is the operator-facing summary of those policies; the canonical, always-current source is docs/type-mapping.md and the runtime value contract in docs/value-types.md.

Core vs extension types #

The IR type system is a two-tier hierarchy, and the tier decides what happens on an engine that lacks a type:

• Core types — integers, decimal, float, boolean, char/varchar/text, binary/blob, date/time/datetime/timestamp, JSON — are the types every relational engine has in some form. Every engine reads and writes them; they are the lingua franca.
• Extension types — ENUM, SET, UUID, arrays, PostGIS geometry, and the Postgres network types (inet/cidr/macaddr) — are types only some engines support natively. Each engine's own reader/writer type dispatch decides what it handles; an engine that lacks one either applies a documented degradation (e.g. Postgres array → MySQL JSON) or refuses loudly. Postgres extension types (hstore, citext, pgvector, PostGIS) are opt-in via --enable-pg-extension EXT and refuse loudly at schema-read if the flag is absent (SLUICE-E-SCHEMA-EXTENSION-NOT-ENABLED) — never silently dropped.

Adding a new engine never amends the core; it provides the reader/writer code, and its own type dispatch IS the declaration — a missing arm refuses loudly rather than degrading silently. The orchestrator's capability questions are strategy questions (which bulk-load method, which CDC transport, which schema scope). And since v0.119.0 the target engine is asked to dry-run its own column-type emit before any data moves, so an unrenderable column is refused up front — naming the table, column and type — instead of surfacing mid-run from the target's CREATE TABLE.

MySQL ↔ Postgres #

The most-travelled direction. Notable rows below; the full table is in the canonical doc.

Decimal values past the target scale: DECIMAL-SCALE-EXCEEDED (v0.156.4) #

An unconstrained Postgres numeric lands on a MySQL-family target as DECIMAL(65,30), MySQL's widest fixed-point form: 35 integer digits and 30 fractional digits. Past 35 integer digits MySQL refuses the write itself (Error 1264). Past 30 fractional digits it does not: MySQL and MariaDB round the value and report only Note 1265, which even strict sql_mode accepts. The bulk copy (LOAD DATA and the batched INSERT path) was always loud, because it refuses a strict-mode write whose warning list is non-empty; the CDC applier never read that list. So through v0.156.3, sync (log-based or postgres-trigger), chain restore, sync from-backup and the added-column backfill stored such values rounded, at exit 0 — measured on MySQL 8.0.46 and MariaDB 11.4: 0.1234567890123456789012345678901 landed as 0.123456789012345678901234567890, and -0.99999999999999999999999999999999 as -1.000000000000000000000000000000. A DECIMAL column's literal DEFAULT past the scale was stored rounded the same way on CREATE TABLE and on a forwarded ADD COLUMN.

Since v0.156.4 sluice refuses any DECIMAL value, and any DECIMAL column's literal DEFAULT, whose significant fractional digits exceed the target column's scale — before the driver sees it, with SLUICE-E-VALUE-UNREPRESENTABLE marked DECIMAL-SCALE-EXCEEDED, naming the column and the digits lost. It applies to a constrained DECIMAL(p,s) column too, and to a float column retyped to a narrower DECIMAL with --type-override. Trailing zeros past the scale lose nothing and are not refused (1.5000…0 lands as 1.5). To proceed, round the value on the source if the digits are not needed, or --type-override the column to a text type to carry every digit. numeric[] values land in a JSON column as strings and were always exact.

Non-ASCII text in MySQL-family DEFAULTs and expressions #

Row values never pass through the catalog, so they were never affected by what follows. Schema text does: sluice builds the target's DEFAULTs, generated columns, CHECK constraints and functional indexes from the source catalog, and both MySQL's and MariaDB's information_schema render some non-ASCII text unfaithfully. sluice recovers it; where it cannot recover it faithfully, it refuses the schema read, naming the object, rather than guessing.

• A literal DEFAULT beyond the Basic Multilingual Plane (v0.156.1). Both servers store COLUMN_DEFAULT in utf8mb3, so VARCHAR(20) DEFAULT '😀x' reads back as ?x. A character default that reads back with a ? is re-read — through a DEFAULT() probe on MySQL and MariaDB, from SHOW CREATE TABLE on PlanetScale and Vitess — and must agree with the catalog text, or the read refuses; a genuine ? default stays ?. Refused rather than guessed: such a default on a non-UTF-8 column charset on PlanetScale/Vitess, and an ENUM/SET default whose label holds such a character.
• Expressions — an expression DEFAULT (…), a generated column, a CHECK, a functional index (v0.156.2, Bug 288). MySQL (and PlanetScale, Vitess) stores each string literal of an expression in the encoding its charset introducer names and renders every stored byte as a separate character, so DEFAULT ('é') read back as _utf8mb4'é' and, through v0.156.1, é landed on the target as é and 中 as 中 — on migrate, sync cold start, restore and a forwarded ADD COLUMN, to MySQL and Postgres targets alike. sluice now undoes the widening and decodes each literal by its introducer: utf8mb4, utf8mb3 and binary bytes as UTF-8, latin1 bytes as their latin1 characters. It refuses a latin1 byte in 0x80–0x9F (MySQL's latin1 is cp1252 there — 0x80 is €), a literal in any other charset (gbk, …), and a non-ASCII literal with no introducer. The decoded expression must also agree with that object's own SHOW CREATE TABLE line, or the read refuses. MariaDB's information_schema is faithful for the BMP but writes one ? per byte for a character beyond it (an emoji became ????, on every LTS line from 10.11 to 12.3); sluice takes the SHOW CREATE TABLE text the ?s match, searched only within the object's own clause, and refuses on no match or an ambiguous one.

Columns in a non-UTF-8 character set: the change stream (v0.156.3) #

A legacy schema's CHAR/VARCHAR/TEXT columns are often declared latin1, cp1251, sjis, gbk, utf16 or another non-UTF-8 charset. They map to the same IR string type as a utf8mb4 column, and the value is always carried as UTF-8. The bulk copy (migrate, a sync cold start, a backup full) never sees the stored bytes: it reads over a utf8mb4 connection, so the server converts every value first. The change stream does see them — the binlog row image (MySQL, MariaDB) and the VStream row event (PlanetScale / Vitess) both carry a character column's value as the bytes stored in its own charset. Since v0.156.3 sluice converts those bytes by the column's charset, so sync, backup stream and backup incremental carry what the bulk copy carries: every 8-bit charset, latin1 as MySQL's cp1252 variant, the Japanese, Korean and GB charsets, the UTF-16/UTF-32/UCS-2 family, and swe7 (not ASCII-compatible: { is ä). Each conversion table is graded against the server's own CONVERT(… USING utf8mb4) over its whole code space on MySQL 8.0 and MariaDB 11.4. Keys and before-images are converted too, so a row updated or deleted by a non-ASCII text key is found on the target. A PlanetScale/Vitess column with a _bin collation (latin1_bin, utf8mb4_bin, …) arrives typed VARBINARY/BLOB/BINARY and is decoded as text by its collation; the declared column type decides, so a true binary column stays bytes.

What cannot be converted faithfully refuses with CHARSET-NOT-DECODABLE, naming the table, column and charset (the value is not printed):

• a non-ASCII big5 value — no available table matches MySQL's big5 (the common one disagrees in 267 places);
• on PlanetScale / Vitess, any non-ASCII value in a gbk, big5, tis620 or gb18030 column — vttablet sends those with collation 0, so the stream cannot tell the charset (the binlog lanes convert gbk, tis620 and gb18030 normally);
• the tis620 bytes MySQL itself converts to U+FFFD;
• on PlanetScale / Vitess, a non-UTF-8 ENUM/SET cell that is a member under both readings. vttablet sends such a cell as UTF-8 label text in CDC but as its stored bytes in the COPY phase, with identical field metadata, so sluice resolves each cell against the column's own labels; it refuses only when two labels are each other's mis-encoding (a latin1 ENUM('é','é')). Rename one label, or exclude the table. The index-0 '' is a member of every ENUM.

Pure-ASCII values in those columns stream normally. To proceed past the refusal, convert the column to utf8mb4 on the source and re-snapshot the table (sync … --restart-from-scratch), or exclude the table. Do not convert and resume: the stream replays history written in the old charset.

A charset change while the stream is behind it. A replay of history recorded before an ALTER … CHARACTER SET (a warm resume after downtime, a lagging stream, a cold-start handoff whose copy spanned the ALTER) carries bytes in the old charset.

• MySQL 8, and MariaDB under binlog_row_metadata=MINIMAL or FULL: each row event's TABLE_MAP records the collation the value was written in (MySQL 8 writes it under its default MINIMAL), and sluice decodes by it, so the replay is exact. Only a collation ID neither the source's collation table nor sluice can name refuses, with SLUICE-E-CDC-SCHEMA-REPLAY-MISMATCH.
• MariaDB under its default binlog_row_metadata=NO_LOG, and PlanetScale / Vitess, record no written charset (vttablet reports a replayed row's current collation). Rows are decoded by the current charset, and each table with non-UTF-8 text columns logs CHARSET-HISTORY-UNRECORDED once. When the stream reaches an ALTER that sets a non-UTF-8 charset explicitly (CONVERT TO CHARACTER SET, or MODIFY/CHANGE with a CHARACTER SET or COLLATE), sluice compares the charset and collation it sets with the shape it has been decoding the table by; a match means the stream is replaying, and it refuses with SLUICE-E-CDC-SCHEMA-REPLAY-MISMATCH. The rows replayed before that ALTER were already emitted, so the remedy depends on the command: under sync, re-snapshot the table (--restart-from-scratch); for backup stream / backup incremental, take a fresh full backup. A backup window that decoded rows by the new charset and ended before the ALTER is refused before it commits (CHARSET-HISTORY-UNRECORDED, SLUICE-E-CDC-SCHEMA-REPLAY-MISMATCH, take a fresh full backup). A change into utf8mb4/utf8mb3/utf8 is not guarded, so routine UTF-8 DDL never refuses a live stream; its replay carries the old bytes as v0.156.2 carried every value.

SQLite & Cloudflare D1 #

SQLite (and D1, which is SQLite over HTTP) is the one engine whose value storage isn't pinned by its column declaration — a column has a type affinity, and each stored value carries its own storage class. sluice resolves an IR type from the declared type in a load-bearing order: declared temporal / bool spellings win first, affinity second.

As a migrate target, SQLite emits the declared type its reader reads back to the same IR type. The one load-bearing wrinkle: an ir.Decimal is stored with TEXT affinity (the exact decimal string), not NUMERIC — NUMERIC affinity would coerce 19.99 to the binary float 19.989999999999998 and silently corrupt money (Bug 162); it reads back as text (a documented downgrade). Anything SQLite has no faithful storage for — geometry, inet/cidr/macaddr, bit, interval, array, domain — is refused loudly at emit time, never coerced to a silently-wrong column. D1 is not a write target: emit a SQLite .db (--target-driver sqlite) and wrangler d1 import it.

Column-fidelity markers you will see in the log #

Some schema facts have no spelling on the target, or have one that only some paths can use. sluice never drops one silently: each carries a grep-stable marker on a WARN line — or, where carrying on would land a wrong value, on the refusal that stops the run — so you can search a run's logs for it.

Per-column overrides #

The default policies cover the common case; override per column in YAML (mappings:) or on the CLI. Overrides are typed against the IR, not dialect syntax:

• --type-override TABLE.COLUMN=TYPE — force a target column type (repeatable). The override rewrites the IR type the reader decodes with, so e.g. =smallint on a TINYINT(1) reads the cell as an integer end-to-end.
• --enable-pg-extension EXT — opt into a Postgres extension type (hstore, citext, vector, PostGIS) so its columns pass through instead of refusing.
• YAML mappings: entries take table:, column:, target_type:, and free-form target_type_options: (e.g. binary: true) — the file form of --type-override. Zero-date policy is the process-global --zero-date error|null|epoch flag (per-sync via the sync run fleet config's zero-date key), not a per-column mapping.

Run sluice schema preview first to see the exact target DDL sluice would emit, including every widening/narrowing advisory and any untranslatable-expression refusal — before touching the target.