Lone Badger Tech

Moving from other tools

Convert a Flyway, DbUp, Entity Framework Core, grate (or RoundhousE), Sqitch or SQL Server Database Project (SSDT), then adopt the databases it already manages without running anything.

trek import flyway ./flyway-project -C db --dry-run   # see what it would do
trek import flyway ./flyway-project -C db             # write a new trek project in db/
trek plan check -C db
trek baseline --from flyway -C db -e prod             # once per existing database
trek deploy -C db -e prod                             # from now on

Import writes a new project (it refuses a folder that already has one) and changes no database. Baseline reads the old tool's journal table in each database, records the changes it already ran, and runs nothing; the old journal table stays until you drop it. It also records the repeatables the database already has with the same definition (on SQL Server), so the first deploy re-applies only the ones that differ. New, empty databases just run trek deploy.

FromImportAdopt existing databases
Flywaytrek import flywaytrek baseline --from flyway (checksums checked)
DbUptrek import dbuptrek baseline --from dbup
Entity Framework Coretrek import eftrek baseline --from ef
grate, RoundhousEtrek import gratetrek baseline --from grate or --from roundhouse (hashes checked)
Sqitchtrek import sqitchPostgreSQL: trek baseline --from sqitch (script hashes checked). Other engines: trek baseline --to <change>
SQL projects (SSDT)trek import sqlprojtrek baseline --from sqlproj (tables and module definitions checked)

Imports target SQL Server unless you pass --provider postgresql or --provider sqlite; EF Core migrations take the provider their SQL was written for, Sqitch projects for PostgreSQL (engine = pg) or SQLite become projects for that provider on their own, and Flyway's jdbc:postgresql and jdbc:sqlite URLs become environments for it.

Every import marks changes !notx when their scripts contain statements SQL Server refuses inside a transaction (the other tools often run without one), and lists everything it could not carry over.

Flyway

Point the import at the folder you run Flyway from. It reads flyway.toml or flyway.conf (also in conf/, or pass --config), takes migrations from flyway.locations (or sql/, src/main/resources/db/migration, or --location), and honours custom prefixes, separators, suffixes and placeholder syntax.

Flywaytrek
V1.2__Add_email.sqlChange add-email, in version order (1.10 after 1.9). The plan note flyway V1.2 checksum … links it to Flyway's history. Repeated descriptions get the version appended.
U1.2__Add_email.sql (undo)The change's revert.sql. Changes without one are !irreversible.
R__Customer_view.sqlrepeatable/Customer_view.sql (same order: by description)
V2__x.sql.conf with executeInTransaction=false!notx
${name} placeholders{name} variables. Values from [flyway.placeholders] go under variables:; values from [environments.x.flyway.placeholders] go to that environment. Names trek reserves (such as env) become {flyway.env}.
${flyway:database}, ${flyway:environment}{db}, {env}
${flyway:defaultSchema}, ${flyway:user}, ${flyway:table}{flyway.defaultSchema}, {flyway.user}, {flyway.table}, with values from the configuration
${flyway:timestamp}, ${flyway:filename}{deploy.utc} (ISO 8601 UTC), {change} (the change name)
flyway.url, [environments.x]Environments: host, port, instance, database, Windows or Entra authentication, TLS and the JDBC settings SqlClient shares. Passwords are never copied: ${env.DB_PASSWORD} becomes ${env:DB_PASSWORD}, and a literal password becomes a reference to FLYWAY_PASSWORD.
Callbacks (afterMigrate.sql …), baseline migrations (B…), Java migrationsNot imported; listed in the output

Baseline from Flyway's history

trek baseline --from flyway -e prod                         # reads dbo.flyway_schema_history
trek baseline --from flyway -e prod --journal app.flyway_history

Applied, undone and baselined versions are taken from the history in order. Baseline refuses, and says why, when:

  • a version Flyway applied has no change in the plan;
  • a change before the last applied one was never applied (Flyway's outOfOrder);
  • Flyway's recorded checksum differs from the imported script, meaning the database ran a different version of it. trek computes checksums exactly as Flyway does; --skip-checksums turns the check off.

Repeatable scripts whose latest run in Flyway's history has the checksum of the same script rendered for this environment are recorded too, so the first deploy only runs the ones that differ.

DbUp

trek import dbup ./src/App.Database/Scripts -C db --var schemaName=app --run-always "Always/**"
trek baseline --from dbup -C db -e prod                    # reads dbo.SchemaVersions

DbUp's scripts and options live in code, so tell the import what the code does:

DbUptrek import
Scripts embedded as resources (WithScriptsEmbeddedInAssembly)Default: script names are the folder path joined with dots (Orders.Script0003.sql), and the order is DbUp's: ordinal by name. Baseline matches them to the full resource names in the journal.
WithScriptsFromFileSystem--file-names: names are the file names
ScriptType.RunAlways scripts--run-always "glob": imported as repeatables, which run when they change rather than on every deploy
$name$ variables, WithVariable("name", value){name} variables; pass the values with --var name=value, or add them to trek.yaml afterwards (plan check fails until every variable has a value). --no-variables if the code calls WithVariablesDisabled().

DbUp has no down scripts, so every change is !irreversible. DbUp runs without a transaction unless configured to; trek runs each change in one, and trek plan check lists any statement that needs !notx.

Entity Framework Core

trek import ef ./src/Shop.Data -C db                    # runs dotnet ef in that project
trek import ef up.sql --down down.sql -C db              # or from scripts you wrote
trek baseline --from ef -C db -e prod                    # reads dbo.__EFMigrationsHistory

EF Core migrations are C#, so the import works from the SQL EF generates from them. Point it at the project that holds the migrations, and trek runs dotnet ef migrations script twice: once for every migration's Up(), and once from the last migration back to the start for every Down(). That builds the project, and needs the dotnet-ef tool and Microsoft.EntityFrameworkCore.Design in the project; pass --context when it has several DbContexts and --startup-project when another project configures it. To work from scripts instead, write them yourself (without --idempotent):

dotnet ef migrations script -o up.sql
dotnet ef migrations script <LastMigration> 0 -o down.sql
EF Coretrek
Migration 20261120131340_AddBlogUrlChange add-blog-url, in migration order, planned at the migration's timestamp. The plan note ef 20261120131340_AddBlogUrl links it to __EFMigrationsHistory.
Up() as SQLdeploy.sql, without EF's BEGIN TRANSACTION/COMMIT (trek runs each change in its own transaction) and without the __EFMigrationsHistory table and rows
Down() as SQLrevert.sql. Without a down script, changes are !irreversible.
SQLite table rebuilds (PRAGMA foreign_keys = 0 between EF's transactions)!notx, since the pragma does nothing inside a transaction
ProviderFrom the SQL: SQL Server, PostgreSQL (Npgsql) or SQLite; --provider overrides it

Tested with EF Core 10. Baseline reads __EFMigrationsHistory (in dbo, public or the SQLite file; --journal for another schema or table) and refuses when a migration EF applied has no change in the plan, or one before the last applied migration was skipped. Leave the history table until the application no longer calls Database.Migrate(), and add new changes with trek add from then on: trek add --from-diff can write them from a database you changed with dotnet ef database update.

grate and RoundhousE

trek import grate ./db -C trek-db --var AppRole=app_reader
trek baseline --from grate -C trek-db -e TEST                 # reads grate.ScriptsRun
trek baseline --from roundhouse -C trek-db -e TEST            # reads RoundhousE.ScriptsRun

Point the import at the folder you pass to grate as --sqlfilesdirectory. grate's folders run in a fixed order, and the import keeps it:

gratetrek
up/ (run once)One change per script, in grate's order (by path, ignoring case). The note grate up/<name> links it to ScriptsRun. grate has no down scripts, so changes are !irreversible.
runAfterCreateDatabase/, alterDatabase/Changes at the start of the plan: they run once on each new database
runBeforeUp/, runFirstAfterUp/, functions/, views/, sprocs/, triggers/, indexes/, runAfterOtherAnyTimeScripts/ (anytime)Repeatables under numbered folders that keep grate's folder order: repeatable/06-functions/, 07-views/, 08-sprocs/ …
beforeMigration/, permissions/, afterMigration/, everytime.* (every time)Repeatables, which trek runs when they change rather than on every deploy
*.ENV.* scriptsenvironments/<name>/ for each environment in the file name (the upper-case parts, or the names you pass with --environment); trek.yaml gets those environments
{{Token}}{Token} variables; values for --ut tokens come from --var. {{DatabaseName}}, {{ServerName}} and {{EnvironmentName}} become {db}, {db.host} and {env}; folder-name tokens get their values; connection strings are never written into scripts.
createDatabase/, dropDatabase/Not imported: trek does not create databases

Renamed folders: --folder up=ddl. If grate ran with --ignoredirectorynames or --disabletokenreplacement, pass --ignore-directory-names or --no-tokens.

grate records a hash of each script after replacing its tokens, so baseline --from grate renders every imported script with this environment's variables and compares: a mismatch means the database ran different text (or a token now has another value), and baseline stops. Repeatables and environment scripts grate already ran with the same text are recorded as applied, so they do not run again. runAfterCreateDatabase/ scripts may be missing from the journal: grate only runs them for databases it created.

Sqitch

trek import sqitch ./flipr -C trek-db
trek import sqitch ./flipr -C trek-db --engine pg      # use that engine's settings from sqitch.conf

Sqitch is closest to trek: a plan of named changes with deploy, revert and verify scripts. The import reads sqitch.conf (top_dir, plan_file, deploy/revert/verify dirs, extension, per-engine overrides) and keeps the plan's order, dependencies, tags, planner and timestamp.

  • A reworked change (the same name again after a tag) becomes <name>-v2, -v3 …; earlier versions take their scripts from <name>@<tag>.sql, as Sqitch keeps them. Dependencies such as users@v1.0.0 point at the right version.
  • Conflicts (!change) and dependencies on other Sqitch projects have no trek equivalent and are listed.
  • Scripts that manage their own transaction (BEGIN; … COMMIT;, as Sqitch's templates do) are !notx.
  • Scripts are copied as written. A Sqitch project for PostgreSQL becomes a PostgreSQL project; one for another engine needs its SQL translated for the provider you choose (trek plan check flags what does not parse).
trek baseline --from sqitch -e prod      # reads sqitch.changes in the same PostgreSQL database

On PostgreSQL, baseline reads Sqitch's registry (sqitch.changes), matches each deployed change by project, name and version (a reworked change is deployed once per version), and checks the SHA-1 Sqitch recorded for each deploy script against the imported one. Sqitch's registry schema stays until you drop it.

From SQL projects (SSDT)

trek import sqlproj ./Shop/Shop.sqlproj -C db --dry-run
trek import sqlproj ./Shop/Shop.sqlproj -C db
trek plan check -C db
trek baseline --from sqlproj -C db -e prod     # databases already published from the dacpac

A SQL project describes the database as it should be, and SSDT works out a script on each publish. trek needs ordered, reviewable steps, so the import gives every object its own piece and puts them in dependency order. It reads classic projects (<Build Include> items) and SDK-style Microsoft.Build.Sql projects (every .sql file except None, Content and Build Remove items, bin/ and obj/). It parses the scripts with ScriptDom and needs no Visual Studio, MSBuild or dacpac, so it runs the same on Windows, macOS and Linux; item paths written with \ or in the wrong case still resolve.

In the projectIn trek
Schemas, tables, types, sequences, synonyms, users, roles, partition functions and schemes, XML schema collections; indexes and constraints in files of their ownOne change each (changes/create-<schema>-<name>/, create-type-…, create-schema-…), with a generated revert.sql and [requires] on the changes it needs. Indexes, constraints, grants and extended properties in an object's file stay with that object.
Views, procedures, functions, triggersRepeatables (repeatable/views/dbo.vCustomer.sql and so on) with CREATE turned into CREATE OR ALTER, and -- trek:requires for the repeatables they use (a view on a view, a function in a view).
A function or view a change needs (a computed column or check constraint uses it, it has an index, a WITH SCHEMABINDING object uses it)A change instead: repeatables run after all changes, and CREATE OR ALTER would drop a view's indexes or fail on a schema-bound object. Edit it later with a new change.
Standalone GRANT, ALTER ROLE … ADD MEMBERRepeatables under permissions/: they are safe to run again.
Post-deployment scriptA repeatable under post-deploy/, with :r includes inlined. SSDT ran it on every publish, so it is already safe to re-run; trek runs it whenever it changes. Move data for one environment only into environments/<name>/.
Pre-deployment scriptNot imported, with a warning: trek has no step before the changes. Turn work that is still needed into a change.
SqlCmdVariablesVariables in trek.yaml with their default values; $(Name) keeps working and $(DatabaseName) becomes $(db). Put values from publish profiles under each environment.
Database referencesmaster and msdb references are dropped. References to other databases are listed: their objects are not managed or ordered here. Same-database (composite) references are listed too; import those projects as well.

Tables whose foreign keys reference each other cannot be created in any order, so the import moves one table's foreign keys into a later change, add-foreign-keys-<schema>-<table>, as SSDT's publish does. Other cycles keep project order and are reported. Procedures that call each other need no order: SQL Server resolves those names when they run.

Every change's note starts with sqlproj <file>:. For a database already published from the dacpac, trek baseline --from sqlproj checks that it has the project's tables, then records every imported change as applied. It also records the repeatables the database already has with exactly that definition, so the first deploy does not re-run them all; a view or procedure that differs (published from an older version, or edited by hand) is left out, and the next deploy applies it. The permission and post-deployment scripts are not recorded: the database may have been published with older versions of them, so the first deploy runs them once (SSDT ran them on every publish, so they are safe to run again). After that, stop publishing the dacpac to that database.

Reverting an imported project goes back as far as its tables and types, but not past a schema that still holds views, procedures or functions: those are repeatables, which a revert leaves in place. trek revert --all therefore stops at the first create-schema-… change; drop the repeatables' objects first if you really need an empty database.

Environments at different versions

A SQL project describes one state, but production is often behind staging, and staging behind development. Build the plan from the oldest environment forward, so each step is a change the older environments will deploy later:

  1. Import the project at the release tag production runs, not the current branch. Baseline's table check only finds missing tables, not missing columns, so a newer project would pass it and be wrong.
  2. Baseline production, then confirm it matches the plan exactly: the dry run must report no schema differences.
  3. For each newer environment, oldest first: baseline it (--from sqlproj for the first, --to the previous environment's change after that), then capture what it has beyond the plan with trek add <name> --from-diff. The new change is recorded there as applied.
git -C Shop worktree add ../Shop-prod release-1.4      # the project as production runs it
trek import sqlproj ../Shop-prod/Shop.sqlproj -C db
trek baseline --from sqlproj -C db -e prod
trek add check --from-diff --dry-run -C db -e prod      # must say: no schema differences

trek baseline --from sqlproj -C db -e staging
trek add release-1.5 --from-diff -C db -e staging       # staging's lead over prod, recorded there as applied

trek baseline --to release-1.5 -C db -e dev
trek add release-1.6 --from-diff -C db -e dev

Every baseline also records the repeatables whose definition in that database already matches the script, and lists the ones that differ. Those run on the next deploy (on SQL Server; other providers re-run them all). That makes the last step safe: the plan's views and procedures are production's versions, so update the repeatable files to the newest definitions, for example by importing the current project into a scratch folder and copying its repeatable/ folder over. Each environment's next trek deploy then runs the changes it lacks and only the repeatables that differ from what it has.

Before relying on it:

  • Generated changes cover schema only. Data fixes an environment received (reference data, backfills) are not in them; add those as changes yourself.
  • A renamed column or table can come through as a drop and an add. Review every generated change, and turn those into renames (sp_rename) before another environment deploys them.
  • The environments must form a straight line: each has everything the older ones have. A hotfix made only in production shows up in staging's change as something to undo. Bring it into the newer environments first, or edit the generated change.
  • Try the whole sequence on copies of the databases first, and compare each with trek diff-schema --against afterwards.

Text that must stay literal

None of these tools expands {name} or $(name), but trek does. The import escapes {name} to {{name}}, and if any script contains $(name) text it sets preprocessor.sqlcmdSyntax: false in trek.yaml.

Before you baseline

  • Write the reverts you want now: a change's hash includes its revert script, so adding one later means trek repair in every database.
  • Review the generated environments and fill in what the output asks for (tenant ids, variables without values).
  • Check the result: deploy the project to an empty database and compare it with one the old tool built, using trek diff-schema -e new --against old. Only the old tool's journal table should differ.
  • Scripts the old tool ran every time (DbUp run-always, grate everytime folders, Flyway callbacks) now run only when they change. If one must run on every deploy, make it idempotent and keep it in a change you add per release, or run it from your pipeline.