Lone Badger Tech

Converting between databases

Move a live database between SQL Server, PostgreSQL, MySQL, MariaDB and SQLite, in any direction: its schema becomes a new trek project for the provider you choose, then its rows are copied and checked.

trek convert schema -C db -e prod --to postgresql --out ../db-pg   # read prod's catalog, write a PostgreSQL project
# read ../db-pg/CONVERSION.md, set the connection in ../db-pg/trek.yaml
trek plan check -C ../db-pg
trek deploy -C ../db-pg                                       # create the schema in PostgreSQL
trek convert data -C db -e prod --target ../db-pg             # copy every row, compare counts
trek verify -C ../db-pg

The source is an environment of the project in -C, or any database: --from-provider sqlserver --connection-string "...". --to names the target provider and is always required: any supported database converts to any other (for example trek convert schema --from-provider sqlite --connection-string "Data Source=app.db" --to postgresql --out db). Conversion reads the database itself (its catalog), not the project's scripts, so it also works for databases trek has never managed.

What is converted

ObjectHow
Schemas, sequences, tables, columns, defaults, computed columns, primary keys, unique and check constraintsChanges convert-schemas, convert-types, convert-sequences, convert-tables, each with a revert. Types are mapped (table below); defaults and checks are translated.
Foreign keys, indexes (including filtered and included columns)Changes convert-foreign-keys and convert-indexes, after the tables. On PostgreSQL, foreign keys are DEFERRABLE so data can be loaded in any order.
Enums and domains (PostgreSQL), alias types (SQL Server)An enum becomes nvarchar with a CHECK (... IN (...)); domains and alias types map to each other, and a domain's checks are added to each column that uses it.
Views, functions, procedures, triggersTranslated statement by statement into repeatables (repeatable/views, functions, procedures, triggers) with -- trek:requires lines so they are created in dependency order. Functions that defaults or checks call are created by a change before the tables.
Comments (COMMENT ON, MS_Description)Carried over.

Partitioning, system versioning, Always Encrypted, masks, table types, synonyms, exclusion constraints, aggregates, and routines in languages other than SQL and PL/pgSQL are listed under "Not converted" in CONVERSION.md.

CONVERSION.md and REVIEW markers

Tables, keys and indexes convert mechanically. Code is translated as far as it can be done faithfully; everything that needs a person (a construct with no counterpart, a behaviour that differs, a guess) is marked /* REVIEW trek convert: ... */ in the script, listed at the top of the file, and collected in CONVERSION.md with the type mappings and every renamed identifier. Nothing is dropped silently: an object that cannot be translated at all is written with its original text commented out.

Names

Converting to PostgreSQL lowercases identifiers by default, so existing unquoted queries keep working (PostgreSQL lowercases them too): dbo.CustomerOrder.FirstName becomes public.customerorder.firstname. --names snake gives public.customer_order.first_name; --names keep keeps the exact case (then every reference must be double-quoted). Converting to SQL Server keeps names as they are. dbo and public map to each other. Two names that would collide after mapping stop the conversion.

Behaviour differences handled

DifferenceWhat the conversion does
SQL Server compares text case-insensitively (_CI_ collations)PostgreSQL text columns get the nondeterministic collation trek_ci, so equality, ordering and unique keys stay case-insensitive; translated LIKE uses ILIKE. --case-sensitive turns this off.
T-SQL has no boolean valuesbit and boolean map to each other; flag = 1 becomes flag = true, and a PostgreSQL condition used as a value becomes CASE WHEN ... THEN 1 ELSE 0 END.
Unique constraints and NULLSQL Server allows one NULL in a unique column, PostgreSQL many. To SQL Server, a unique constraint on nullable columns becomes a filtered unique index; to PostgreSQL it is flagged (add NULLS NOT DISTINCT to keep SQL Server's rule).
SQL Server triggers work on whole statements (inserted, deleted)To PostgreSQL: statement-level triggers with transition tables named inserted and deleted. To SQL Server: statement triggers map directly; row triggers loop over inserted/deleted, and a BEFORE trigger's changes to NEW are written back by primary key.
PostgreSQL functions may change data; SQL Server functions may notSuch functions become procedures (callers use EXEC; a scalar result becomes an OUTPUT parameter @result).
SCOPE_IDENTITY(), RETURNING, OUTPUTINSERT ...; SET @id = SCOPE_IDENTITY() becomes INSERT ... RETURNING id INTO v_id; RETURNING becomes OUTPUT ... INTO a table variable.
Dates and intervalsDATEADD/DATEDIFF/DATEPART and PostgreSQL interval arithmetic, date_trunc, extract and common to_char/CONVERT formats are translated.

Type mapping

SQL ServerPostgreSQL
int, bigint, smallint, tinyintinteger, bigint, smallint, smallint with a 0–255 check
bitboolean
decimal(p,s), moneynumeric(p,s), numeric(19,4) (numeric without precision becomes decimal(38,10))
nvarchar(n), nvarchar(max), nchar(n)varchar(n), text, char(n)
datetime2(p), datetime, datetimeoffset(p)timestamp(p), timestamp(3), timestamptz(p) (at most 6 fractional digits; the original offset is not kept)
uniqueidentifier, varbinary, xmluuid, bytea, xml
nvarchar(max) with ISJSONjson, jsonb, and arrays (stored as JSON arrays)
IDENTITY(s,i)GENERATED BY DEFAULT AS IDENTITY

SQLite

SQLite is a different shape of database, so converting to or from it changes more than the dialect:

TopicWhat the conversion does
SchemasSQLite has none. Tables in the source's default schema keep their names; others get the schema as a prefix (sales.Customer becomes sales_customer). From SQLite, everything lands in dbo or public.
TypesTo SQLite, columns keep a descriptive declared type (VARCHAR(50), DATETIME, BOOLEAN, NUMERIC(12,2)) whose affinity stores the values; dates are ISO 8601 text (UTC for time-zone-aware values), booleans 0/1 with a check, JSON with json_valid. Decimals with fractions become REAL in SQLite: keep money in integer cents if exactness matters. From SQLite, declared types are read with SQLite's affinity rules; a column with no declared type becomes text.
KeysINTEGER PRIMARY KEY (the rowid) and identities map to each other (AUTOINCREMENT, so numbers are never reused). Foreign keys are part of CREATE TABLE in SQLite. COLLATE NOCASE maps to the case-insensitive collations on the other side.
Sequences, functions, proceduresSQLite has none. Sequences and the defaults that use them are dropped and flagged; routines are kept commented out. A function whose body is a single expression is written in place where views use it.
TriggersSQLite triggers are row-level, one event each, and hold only SQL. To SQLite: PL/pgSQL and T-SQL logic is flattened (IF branches become WHERE guards, RAISE becomes RAISE(ABORT, ...), a BEFORE trigger's changes to NEW become an UPDATE of the row, statement triggers run per row over that row). From SQLite: each trigger becomes a PL/pgSQL trigger function, or a SQL Server trigger over inserted/deleted.
ViewsSQLite functions (ifnull, instr, group_concat, strftime, datetime('now', '-30 days'), iif ...) and PostgreSQL and T-SQL ones are mapped to each other. SQL Server and SQLite convert by way of PostgreSQL's syntax, so both directions use the same, tested mappings.
SQLite versionsThe converted project runs on SQLite 3.26 and later, the default sqlite.minVersion (SQLite versions), so syntax SQLite learned later is written the older way: -> and ->> become json_extract, NULLS LAST an IS NULL sort key, an aggregate's FILTER (WHERE ...) a CASE inside it, RIGHT JOIN a LEFT JOIN, and UPDATE ... FROM in a trigger correlated subqueries. What has no older form (generated columns, FULL JOIN, ORDER BY inside an aggregate) is kept or left out with a REVIEW marker naming the SQLite version it needs.

MySQL

MySQL 8.4 and 9.7 convert to and from every other provider. MySQL and SQL Server or SQLite convert by way of PostgreSQL's syntax, like SQLite does, so every direction uses the same, tested mappings.

TopicWhat the conversion does
SchemasA MySQL schema is a database, and the converted project works inside the environment's database. As with SQLite, tables in the source's default schema keep their names and others get the schema as a prefix (sales.Customer becomes sales_Customer). From MySQL, everything lands in dbo or public.
TypesTo MySQL: boolean and bit become TINYINT(1), text without a length LONGTEXT (VARCHAR(255) in keys and indexes, which cannot hold LONGTEXT), uuid CHAR(36), PostgreSQL enums ENUM(...), arrays and jsonb JSON, time-zone-aware timestamps DATETIME in UTC. From MySQL: TINYINT(1) becomes a boolean, unsigned integers the next larger signed type, ENUM and SET text with a check of the allowed values, YEAR smallint, JSON jsonb or checked JSON text.
Keys and identitiesIdentities and AUTO_INCREMENT map to each other, continuing after the copied rows. MySQL has no sequences: they are left out and flagged. Every table is InnoDB with utf8mb4; a case-insensitive source gets utf8mb4_0900_as_ci, a case-sensitive one utf8mb4_0900_as_cs. MySQL has no filtered indexes or INCLUDE columns: a filter is dropped (a filtered unique index is left out) and flagged. An expression index over text is cast to CHAR(255), since MySQL cannot index TEXT.
ViewsMySQL functions and operators (IFNULL, IF, CONCAT, GROUP_CONCAT, DATE_FORMAT, DATE_ADD and INTERVAL, TIMESTAMPDIFF, JSON_EXTRACT and ->>, <=>, LIMIT a, b ...) and their PostgreSQL, T-SQL and SQLite counterparts are mapped to each other. LIKE and REGEXP ignore case in MySQL's usual collations, so they become ILIKE and ~*; GROUP_CONCAT's 1024-byte limit is flagged.
Functions, procedures, triggersNot translated yet, in either direction: they are written commented out with a REVIEW marker, and a view that calls one of them is commented out too, until it is rewritten.

MariaDB

MariaDB 10.11, 11.4, 11.8 and 12.3 convert to and from every other provider, MySQL included. Everything in the MySQL section above applies, with these differences:

TopicWhat the conversion does
TypesTo MariaDB: uuid and uniqueidentifier become UUID, PostgreSQL inet INET6, JSON stays JSON (which MariaDB stores as checked LONGTEXT). From MariaDB: UUID becomes uuid or uniqueidentifier, INET4/INET6 inet or text, and a JSON column is recognized by its json_valid check.
SequencesMariaDB has them: sequences convert both ways, and nextval/NEXT VALUE FOR defaults become NEXTVAL(seq). To MySQL, which has none, they are flagged.
CollationsTables use utf8mb4_uca1400_as_ci or utf8mb4_uca1400_as_cs, MariaDB's own Unicode collations.
IndexesMariaDB has no expression indexes: each expression becomes a VIRTUAL generated column, indexed, and flagged (check the column's guessed type).
ViewsMariaDB has no ->/->> operators or REGEXP_LIKE: JSON paths use JSON_EXTRACT/JSON_VALUE, and ~/~* become REGEXP with an inline (?-i)/(?i) flag, so case sensitivity does not depend on the collation.
MySQL and MariaDBConverting between them keeps column types as they are (ENUM, SET, unsigned integers) and translates views and defaults by way of PostgreSQL's syntax, like every other pair.

Copying the data

trek convert data copies every table, parents before children, with the same name mapping convert schema used (it reads convert.json in the converted project). Into SQL Server it uses bulk copy (identity values kept, triggers not fired, constraints checked afterwards); into PostgreSQL, COPY in one transaction with foreign keys deferred and user triggers off; into SQLite, prepared inserts in one transaction with foreign keys deferred and the tables' triggers set aside until the commit; into MySQL and MariaDB, multi-row inserts in one transaction with foreign key checks off, and every foreign key checked by query before the commit (neither can turn triggers off, so the target's triggers fire for each row). PostgreSQL, SQLite, MySQL and MariaDB loads leave nothing behind when they fail. Identities and sequences are moved past the copied values, and row counts are compared at the end (exit code 1 when they differ).

The target tables must be empty; --truncate empties them first. --tables "sales.*" copies only some. Run it after deploying the converted project, while the source is not being written to.

A SQLite target with a relative database: could be two files: one relative to the folder you run convert data in, one relative to the converted project (where it is if you deployed from that folder). trek uses the one that exists, and stops if both or neither do rather than load or create the wrong file.