Data types and migration portability

Version stamp

Applies to: renvor 0.0.0 · framework source 7d0816a · MSRV 1.94.0 · documentation set pre-release

This stamp is a single shared partial (docs/_stamp.mdx) imported by every prose page and the API reference. It binds this documentation snapshot to the immutable framework commit it describes. The framework remains unpublished and no release compatibility promise applies.

Nothing is published

None of the crates below are on crates.io. This page documents contracts that are implemented and tested, not software you can install.

All four persistence rows — direct SQLx and SeaORM, each on PostgreSQL and MySQL — are first-class. That means your schema has to be one both engines agree about. This page is how to write one.

The rules are contract C-16, and every rule there is attached to a test that runs against both engines on every verification. This page is the working guide to applying them.

The reasoning — what else was considered for each of the seven topics, and what the choice costs — is decision record ADR-0023. Read it when a rule below looks arbitrary: three of the seven differences cannot be removed by any adapter, and the record says which and why.

The short version

Lowercase identifiers under 63 bytes · DATETIME(6)/TIMESTAMP(6) storing UTC · one unique key per upsert target · order by a non-nullable unique key · jsonb and JSON · one schema change per migration.

Choosing column types

You want	PostgreSQL	MySQL	Watch for
Identity	BIGINT / BIGSERIAL	BIGINT / AUTO_INCREMENT	—
Short text	VARCHAR(n)	VARCHAR(n)	MySQL counts characters; index prefix limits apply
Long text	TEXT	TEXT / LONGTEXT	MySQL TEXT caps at 65 535 bytes
Instant	TIMESTAMPTZ	DATETIME(6)	see below
Boolean	BOOLEAN	BOOLEAN (alias of TINYINT(1))	MySQL returns 0/1
Exact decimal	NUMERIC(p,s)	DECIMAL(p,s)	never FLOAT for money
Document	JSONB	JSON	not PostgreSQL JSON — see below
Binary	BYTEA	BLOB / LONGBLOB	MySQL BLOB caps at 65 535 bytes

Timestamps are the trap

Two independent problems, and both bite silently.

Precision. A column written as TIMESTAMP or DATETIME with no precision keeps microseconds on PostgreSQL and whole seconds on MySQL. Nothing errors; the fractional part is simply gone.

-- Wrong: the same DDL means two different things
created_at DATETIME NOT NULL

-- Right: the precision is stated, so both engines store the same thing
created_at DATETIME(6) NOT NULL

Range. MySQL's TIMESTAMP type ends at 2038-01-19. A subscription that renews in 2039 is rejected outright:

ERROR 1292 (22007): Incorrect datetime value: '2039-01-01 00:00:00'

Use DATETIME(6) on MySQL and store UTC. MySQL's TIMESTAMP also converts on read using the session time zone, so two sessions reading one row can see two different values.

JSON: pick the normalising type

MySQL has one JSON type and it normalises — keys sorted, duplicate keys resolved last-wins, whitespace discarded. PostgreSQL has two, and only jsonb does the same:

input       {"b":1,"a":2,"a":3}

jsonb  ->   {"a": 3, "b": 1}      MySQL JSON ->  {"a": 3, "b": 1}      identical
json   ->   {"b":1,"a":2,"a":3}                                        PostgreSQL only

Use JSONB and JSON. If you genuinely need the bytes you were sent, store them in a text column and be explicit about it — PostgreSQL's json type has no MySQL equivalent.

JSON: four things the two types do not agree about

JSONB and JSON agree about far more than they disagree about, but "the same document round-trips the same way" is not true in general, and an application that assumes it will meet one of these:

{"z":"\u0000"}         jsonb  REFUSES it      MySQL JSON  stores it
{"e":1E2}              jsonb  100             MySQL JSON  100.0
{"n":1.50}             jsonb  1.50            MySQL JSON  1.5
{"n":0.123456789012345678901}
                       jsonb  exact           MySQL JSON  0.12345678901234568   <- rounded

The first is PostgreSQL's: its text type cannot hold a NUL, so jsonb rejects the escape outright. The last is the one to worry about — MySQL keeps non-integer JSON numbers as doubles, so it is data loss, not formatting. Plain-notation integers within the signed 64-bit range are the measured portable integer subset, including values past 2^53.

Stay inside the portable subset: objects, arrays, strings without U+0000, true/false/ null, and plain-notation integers from -9_223_372_036_854_775_808 through 9_223_372_036_854_775_807. Validate anything that might fall outside it before you store it, or keep the original text in a column of its own and say that is what it is.

And do not depend on what you sent coming back byte-for-byte. Both engines discard whitespace, sort keys and resolve duplicates. What is portable is the value.

Writing migrations

One schema change per migration

PostgreSQL runs DDL inside your transaction, so a failed migration leaves nothing behind. MySQL does not — DDL forces an implicit commit, so every statement before the failure is already permanent and no rollback can reach it.

BEGIN; CREATE TABLE t (...); ROLLBACK;

PostgreSQL   table is gone
MySQL        table is still there

A migration with one statement has no partial state to be in. Write one, and never rely on rollback to undo an earlier step.

If a MySQL migration fails partway, the next run is refused

This is the reason the rule above is a rule and not a preference. Renvor does not resume a half-applied migration, and neither does SQLx:

run #1   CREATE TABLE a;  CREATE TABLE b;  -- b fails
         -> table a is committed. _sqlx_migrations holds the version with success = FALSE.

run #2   -> MigrationDirty, before a single statement is sent.
run #3   -> MigrationDirty.  Retrying is a loop, not a recovery.

No sqlx migrate command clears that row — run, revert and override skip all check for a dirty version first and refuse. Recovery is yours to do, in this order:

1. Look at what committed: the version in _sqlx_migrations, the statements in the file before the failure, and which of those objects the catalogue actually has.
2. Back up, then bring the schema to one of two states: the migration fully applied, or fully undone. Not somewhere in between — SQLx will validate against the checksum already in that row.
3. Match the ledger to the schema you just made:
   • fully applied → UPDATE _sqlx_migrations SET success = TRUE WHERE version = <version>
   • fully undone → DELETE FROM _sqlx_migrations WHERE version = <version>
4. Run the migration again.

Do step 3 only after step 2. Editing the ledger to get a stuck deploy moving, before you know which state the schema is in, trades a stopped deploy for a schema the framework is wrong about — and the next migration will be written against that wrong belief.

PostgreSQL never gets here: the ledger row and your statements share one transaction, so a failure removes both and the next run starts over on its own.

Identifiers

Lowercase, at most 63 bytes, no quoting required. The limit is PostgreSQL's, and it is the dangerous one: past 63 bytes PostgreSQL truncates and succeeds, creating the table under a name you did not write, while MySQL refuses at 65 characters. The refusal is the better outcome.

CREATE TABLE rv_pt_aaaa…(65 chars)

PostgreSQL   NOTICE: identifier will be truncated   -> created as 63 characters
MySQL        ERROR 1059: Identifier name is too long

Upserts target exactly one unique key

MySQL's ON DUPLICATE KEY UPDATE cannot be scoped to a particular key. On a table with a second unique constraint, this happens:

-- table w(id PRIMARY KEY, tag UNIQUE, v), already holding (1, 'x', 1)
INSERT INTO w VALUES (2, 'x', 9) ...   -- scoped to id

PostgreSQL   ERROR: duplicate key value violates unique constraint "w_tag_key"
MySQL        succeeds — and updates row id = 1, which the statement never named

So: upsert against the primary key of a table that has no other unique constraint. And do not read insert-versus-update out of the affected-row count — MySQL reports 2 for an update and 0 when the values were already correct.

Reading and paging

NULLs sort to opposite ends

	ORDER BY v ASC
PostgreSQL	1, 2, NULL
MySQL	NULL, 1, 2

Page on a non-nullable unique key. A cursor over a nullable column resumes from a different row on each engine — a wrong answer, not a slow one. If a nullable column has to participate, put it before the unique tiebreaker and state its NULL placement explicitly.

Do not depend on the default isolation level

PostgreSQL defaults to READ COMMITTED, MySQL to REPEATABLE READ. With a transaction open and one read taken, a second read of the same table sees another session's commit on PostgreSQL and does not on MySQL.

Renvor exposes no isolation-level setter, so a read-modify-write that must be atomic has to say so in SQL — lock the row you read, or write a condition that fails if it changed since.

A schema that satisfies all of it

CREATE TABLE account (
  id          BIGINT       NOT NULL PRIMARY KEY,
  email       VARCHAR(320) NOT NULL UNIQUE,
  profile     JSON         NOT NULL,        -- JSONB on PostgreSQL
  created_at  DATETIME(6)  NOT NULL,        -- TIMESTAMPTZ on PostgreSQL; UTC either way
  balance     DECIMAL(19,4) NOT NULL
);

email is a second unique key, so account is not a portable upsert target. That is a real constraint, not an oversight: insert and handle the UniqueViolation, which both engines and both adapters report identically.

How this is kept true

Each rule above is one assertion in renvor_testkit::portability, compiled once and executed against both engines through both adapters on every verification run. xtask requires all four rows to report in, so a row that stopped running fails the build rather than going quiet.

The JSON boundary is a table of documents — the portable ones and the four excluded ones — with the answer each engine actually gave, so an engine that changes its mind about an exclusion fails the build just as loudly as one that breaks a guarantee. The dirty-ledger behaviour above has its own assertion in renvor-sqlx/tests/migration.rs, which builds a migration designed to fail and checks what the run after it does.

When an engine changes its behaviour, that suite fails and this page is what gets corrected.