Lone Badger Tech

Moving from other tools

Convert a Flyway, DbUp, grate (or RoundhousE) or Sqitch project, then adopt the databases it already manages without running anything.

bsm import flyway ./flyway-project -C db --dry-run   # see what it would do
bsm import flyway ./flyway-project -C db             # write a new bsm project in db/
bsm plan check -C db
bsm baseline --from flyway -C db -e prod             # once per existing database
bsm 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. New, empty databases just run bsm deploy.

FromImportAdopt existing databases
Flywaybsm import flywaybsm baseline --from flyway (checksums checked)
DbUpbsm import dbupbsm baseline --from dbup
grate, RoundhousEbsm import gratebsm baseline --from grate or --from roundhouse (hashes checked)
Sqitchbsm import sqitchPostgreSQL: bsm baseline --from sqitch (script hashes checked). Other engines: bsm baseline --to <change>

Imports target SQL Server unless you pass --provider postgresql; Sqitch projects for PostgreSQL (engine = pg) become PostgreSQL projects on their own, and Flyway's jdbc:postgresql URLs become PostgreSQL environments.

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.

Flywaybsm
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 bsm 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

bsm baseline --from flyway -e prod                         # reads dbo.flyway_schema_history
bsm 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. bsm 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

bsm import dbup ./src/App.Database/Scripts -C db --var schemaName=app --run-always "Always/**"
bsm 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:

DbUpbsm 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 bsm.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; bsm runs each change in one, and bsm plan check lists any statement that needs !notx.

grate and RoundhousE

bsm import grate ./db -C bsm-db --var AppRole=app_reader
bsm baseline --from grate -C bsm-db -e TEST                 # reads grate.ScriptsRun
bsm baseline --from roundhouse -C bsm-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:

gratebsm
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 bsm 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); bsm.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: bsm 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

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

Sqitch is closest to bsm: 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 bsm 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 (bsm plan check flags what does not parse).
bsm 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.

Text that must stay literal

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

Before you baseline

  • Write the reverts you want now: a change's hash includes its revert script, so adding one later means bsm 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 bsm 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.