Migration Script Best Practices
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.