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:
- checks that the database has exactly a prefix of the plan deployed, in order (otherwise the comparison would include unrelated differences);
- 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;
- compares the two catalogs and writes
changes/<name>/deploy.sql(shadow → your database) andrevert.sql(back again), with a summary and warnings at the top; - appends the change to
bsm.plan, marks it!notxif needed, and records it as applied in your database, which already has it (--no-recordto skip); - drops the shadow database (
--keep-shadowto 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
| Objects | Changes |
|---|---|
| Schemas, tables, columns | Create 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, constraints | Primary 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, triggers | CREATE OR ALTER, in dependency order; dropped in reverse order |
| Types, sequences, synonyms | Alias and table types, sequences (increment, limits, cycle, cache), synonyms |
| System-versioned tables | New temporal tables with their history table and retention; retention changes |
| Partitioning | Partition functions and schemes; boundaries added (SPLIT) and removed (MERGE); tables and indexes on a scheme |
On PostgreSQL
| Objects | Changes |
|---|---|
| Schemas, tables, columns | Create 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, constraints | Primary keys, unique, exclusion, check and foreign keys; indexes (partial, expression, included columns, on partitioned tables); statistics objects |
| Views, functions, triggers | Views 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 rest | Enums (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 privileges | COMMENT 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).
-- 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 NULLcolumn without a default to a table with rows; making a columnNOT NULLwhile 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_renamereally was renamed. - Values of this environment's variables in the SQL, such as
reporting_localfor{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.