Skip to main content

Migration Script Best Practices

· 2 min read
Dubnium
SQL Server migration tooling by Booolean

Reliable database deployments start with migration scripts that are safe to review, test, and run through Dubnium.

Add idempotency guards​

Check whether an object exists before creating, altering, or dropping it.

IF NOT EXISTS (
SELECT 1 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '{{SCHEMA_NAME}}' AND TABLE_NAME = 'Users'
)
BEGIN
CREATE TABLE [{{SCHEMA_NAME}}].[Users] (
[Id] INT NOT NULL PRIMARY KEY,
[Name] NVARCHAR(200) NOT NULL
);
END

Guards make local testing and recovery from interrupted deployments more predictable.

Keep scripts compatible with direct execution​

Dubnium sends migration script content directly to SQL Server. Do not add GO batch separators because GO is a client command, not Transact-SQL.

For statements that must start a batch, execute the DDL through dynamic SQL:

EXEC(N'
CREATE OR ALTER PROCEDURE [dbo].[GetUsers]
AS
BEGIN
SELECT [Id], [Name]
FROM [dbo].[Users];
END
');

Use variables for environment-specific values​

Declare values in dubnium.yml and reference them with {{VARIABLE_NAME}}:

Variables:
SCHEMA_NAME: 'dbo'
ENV_LABEL: 'Production'
PRINT 'Deploying to {{ENV_LABEL}}';

Choose the right script type​

Use Migration/Platform/ for infrastructure, Migration/Generated/ for generated SQL, and Migration/Application/ for feature work. When versions match, Dubnium orders them Platform, Generated, then Application.

dbm new --platform Create Core Schema
dbm new --generated Refresh Generated Module
dbm new Add Orders Table

Verify before deployment​

Run dbm verify to check registration, database connectivity, required folders, migration filenames, and duplicate sequence numbers before using a deployment command.