Variables
Scripts are preprocessed: {env}, {db}, your own variables, and $(sqlcmd) syntax.
Before a script reaches the database, bsm replaces variables in it:
SELECT * FROM [calc_{env}].[table1]; -- test: SELECT * FROM [calc_test].[table1];
SELECT * FROM [calc_$(env)].[table1]; -- the same, sqlcmd syntax
Syntax
| Write | Gets |
|---|---|
{name}, {group.name} | The variable's value |
{var.name} | A user variable, explicitly |
$(name) | The same variables, sqlcmd style |
{{name}} | A literal {name} |
-- bsm:preprocess off as the first line | The script untouched |
Only an identifier directly inside the braces is a variable. ODBC escapes ({fn NOW()}, {d '2026-01-01'}), JSON ('{"a":1}') and format strings ('{0}') are left alone. Names are case-insensitive.
Built-in variables
| Variable | Also | Value |
|---|---|---|
{env} | {env.name} | Environment name |
{env.description} | Environment description | |
{db} | {db.name} | Database name |
{db.host} | Server host | |
{db.port} | Server port | |
{change} | {change.name} | Current change or repeatable |
{change.kind} | deploy, revert, verify or repeatable | |
{change.hash} | The change's script hash | |
{bsm.provider} | Provider id (sqlserver) | |
{bsm.version} | Tool version | |
{bsm.schema} | Journal schema | |
{plan.head} | Plan head chain id | |
{deploy.user} | OS user running bsm * | |
{deploy.host} | Machine running bsm * | |
{deploy.utc} | Run start, ISO 8601 UTC * | |
{deploy.date} | Run start date, UTC * | |
{deploy.id} | Unique id of the run * |
* Per-run values: a script that uses them renders differently on every run, so it is not tracked by rendered hash. The names env, db, change, bsm, plan and deploy (and anything under them) are reserved.
Your variables
bsm.yamlvariables: # every environment
AppRole: app_reader
schemas: # groups become {schemas.calc}, {schemas.stage}
calc: calc_{env} # values may use other variables and built-ins
stage: stage_{env}
calcTable: "[{schemas.calc}].[table1]"
environments:
prod:
variables: # this environment only; wins over the shared ones
AppRole: app_reader_prod
Region: ${env:DEPLOY_REGION} # secrets and env vars work here too
Precedence, lowest to highest: shared variables, the environment's variables (including what it inherits through extends), then --var name=value on the command line. Circular references are errors.
YAML tip: in an inline map ({ a: b }), braces are YAML syntax, so quote values that contain variables: { calc: "calc_{env}" }. In block style (one key per line) no quotes are needed.
Unknown variables
An unknown {name} or $(name) is an error that names the script and line, so a typo never reaches the database. If your scripts contain many literal braces, set preprocessor.unknown: keep to leave unknown {name} tokens as they are (unknown built-ins such as {env.nmae} are still errors).
preprocessor:
unknown: error # error (default) | keep
sqlcmdSyntax: true # expand $(name) too (default)
bsm plan check also fails when a script uses a variable that some environment does not define, and names those environments.
Seeing the result
bsm vars -e prod # every variable: value, source, description
bsm render create-calc-schema -e prod # the exact SQL prod will receive
bsm render create-calc-schema -e prod --kind revert
bsm deploy -e prod --script review.sql # all pending changes, rendered, for review
Variables and certification
Raw scripts are hashed and certified, so the same change certifies identically in every environment. The rendered SQL is hashed separately per environment (see Hash certification). If a variable's value changes after a change was deployed, verify reports it and revert refuses unless you pass --allow-variable-drift.