Lone Badger Tech

Generating changes

Make schema edits in your own database with any tool, then let bsm write the change.

bsm add customer-phone --from-diff                 # compare 'local' with the plan, write the change
bsm add customer-phone --from-diff --dry-run       # print the deploy script, write nothing
bsm add customer-phone --from-diff --shadow-env docker -n "phone numbers"

Change your development database however you like (SSMS, Azure Data Studio, pgAdmin, DBeaver, a table designer, a script), then run bsm add <name> --from-diff. bsm:

  1. checks that the database has exactly a prefix of the plan deployed, in order (otherwise the comparison would include unrelated differences);
  2. creates a temporary shadow database on the same server and deploys the plan to it, as far as your database has it, with your environment's variables and keys;
  3. compares the two catalogs and writes changes/<name>/deploy.sql (shadow → your database) and revert.sql (back again), with a summary and warnings at the top;
  4. appends the change to bsm.plan, marks it !notx if needed, and records it as applied in your database, which already has it (--no-record to skip);
  5. drops the shadow database (--keep-shadow to look at it).

Everything is read from the database's own catalog with the same driver bsm already uses (no pg_dump, no SqlPackage): nothing else to install, on any OS. SQL Server and PostgreSQL both generate changes.

changes/customer-phone/deploy.sql
-- Deploy customer-phone: generated by Badger SQL Migrate from 'local'. Review before committing.
--   + column dbo.Customer.Phone varchar(30)
--   + index dbo.Customer.IX_Customer_Phone

ALTER TABLE [dbo].[Customer] ADD [Phone] varchar(30) NULL;
GO

CREATE NONCLUSTERED INDEX [IX_Customer_Phone] ON [dbo].[Customer] ([Phone]) WHERE ([Phone] IS NOT NULL);
GO

What is scripted

ObjectsChanges
Schemas, tables, columnsCreate and drop; add, drop and alter columns (type, length, nullability, collation, masking); defaults; a column replaced by one with the same definition becomes a rename (sp_rename)
Keys, indexes, constraintsPrimary keys, unique constraints, indexes (filtered, included columns, descending, columnstore, compression, partitioned), foreign keys (actions, NOCHECK), check constraints. Indexes and constraints on a column whose type changes are dropped and re-created around the change.
Views, procedures, functions, triggersCREATE OR ALTER, in dependency order; dropped in reverse order
Types, sequences, synonymsAlias and table types, sequences (increment, limits, cycle, cache), synonyms
System-versioned tablesNew temporal tables with their history table and retention; retention changes
PartitioningPartition functions and schemes; boundaries added (SPLIT) and removed (MERGE); tables and indexes on a scheme

On PostgreSQL

ObjectsChanges
Schemas, tables, columnsCreate and drop (including partitioned tables, partitions, inheritance, unlogged and foreign tables); add, drop and alter columns (type with USING, collation, nullability, defaults, identity and its options, storage); a column replaced by one with the same definition becomes RENAME COLUMN; partitions attached and detached; row-level security, replica identity, storage parameters and tablespaces
Keys, indexes, constraintsPrimary keys, unique, exclusion, check and foreign keys; indexes (partial, expression, included columns, on partitioned tables); statistics objects
Views, functions, triggersViews and materialized views (with their indexes), functions, procedures, aggregates, operators, casts, triggers, event triggers, rules and policies, in pg_depend order. A function whose signature stays the same is changed with CREATE OR REPLACE; otherwise it is dropped and created again together with the views, indexes, constraints and triggers that use it.
Types and the restEnums (new values with ADD VALUE), domains (defaults, NOT NULL, constraints), composite types (attributes), range types, sequences, collations, extensions (UPDATE TO), text search configurations and dictionaries, publications, foreign data wrappers, servers and user mappings
Comments and privilegesCOMMENT ON, GRANT/REVOKE (tables, columns, sequences, functions, schemas, types) and default privileges, written again for objects the script re-creates

Views, triggers, policies and rules on a table whose columns are dropped or retyped are dropped first and re-created after, because PostgreSQL refuses the ALTER while they exist. The script starts with SET LOCAL check_function_bodies = off so function bodies may refer to objects created later in it. PostgreSQL-specific warnings: removing or reordering enum values and changing a domain's base type while columns use them, changing how a table is partitioned, changing a generated column's expression, foreign table options, ordered-set aggregates and subscriptions (their connection holds a password).

changes/orders/deploy.sql
-- Deploy orders: generated by Badger SQL Migrate from 'local'. Review before committing.
--   + type public.status
--   + column public.customer.email character varying(200)
--   + table public.orders

CREATE TYPE public.status AS ENUM ('new', 'done');

ALTER TABLE public.customer ADD COLUMN email character varying(200);

CREATE TABLE public.orders (
    id bigint NOT NULL,
    customer_id integer NOT NULL,
    status public.status DEFAULT 'new'::public.status NOT NULL);

ALTER TABLE public.orders ADD CONSTRAINT orders_pkey PRIMARY KEY (id);

ALTER TABLE public.orders ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES public.customer(id);

Constraints SQL Server named itself (DF__Order__Total__3B75D760) have a different name on every server, so the script finds them at deploy time instead of hard-coding a name.

Warnings instead of guesses

Some edits cannot be scripted safely from two catalogs. They are listed at the top of deploy.sql and in the output, and left for you to write:

  • Data loss: dropped tables and columns, narrowed types. The output says WITH DATA LOSS.
  • Adding a NOT NULL column without a default to a table with rows; making a column NOT NULL while rows hold NULL.
  • Changes that need a table rebuild: identity, sparse, ROWGUIDCOL or encryption settings, column order, moving to another filegroup or partition scheme.
  • Turning system versioning on or off; changing a partition function's type or direction; XML, spatial and full-text indexes.
  • A rename is only a guess: check that a column scripted with sp_rename really was renamed.
  • Values of this environment's variables in the SQL, such as reporting_local for {schemas.reporting}: replace them with the variable if they differ per environment.

Repeatables and other tools' tables

Views, procedures, functions and triggers (on PostgreSQL: views, materialized views, functions and procedures) that a repeatable script defines are maintained in that script, so they are never put in a change: the output tells you to edit the repeatable. The bsm journal, and other tools' journals (flyway_schema_history, SchemaVersions, grate's and RoundhousE's schemas, __EFMigrationsHistory) are left out; add your own with --exclude audit.*.

The shadow database

It is created next to your database with CREATE DATABASE and dropped afterwards, so the login needs that right. On Azure SQL Database a new database takes a minute and is billed while it exists: use --shadow-env to build it on another server, such as a local SQL Server or PostgreSQL container. On PostgreSQL the shadow is created through the server's postgres database, so the role needs CREATEDB. The shadow uses your environment's variables and Always Encrypted keys, so it matches your database except for your edits.

Comparing any two databases

bsm diff-schema -e test --against prod                    # what differs
bsm diff-schema -e test --against prod --script sync.sql   # SQL that turns test into prod

The same scripter writes a one-off script between two live databases, for example to bring a drifted database back in line. It is not a change: review it, and run it yourself or turn it into one.