skillZs
★ LIVE SKILL TAGS ★
>>> LIVE SKILLS INDEX <<<
* OPEN SOURCE *
NO LOGIN, NO TRACKING
※ REAL INSTALL DATA ※
← back to all skills
ariga/atlas67 installs

atlas-mssql

SQL Server specifics for Atlas (also Azure SQL and MSSQL): login, Azure SQL sign-in with Microsoft Entra ID (fedauth, no password), dev database and URL scope (mode=schema vs mode=database, the database query parameter), compatibility level and collation, system-generated constraint names, clustered index defaults and datetime2 precision in HCL, GO batches and the delimiter directive, transactions and statements that cannot run in one, identity, computed and temporal columns, alias and table types, changes planned as a drop and an add, what Atlas leaves out (DENY, synonyms, row-level security, sparse columns), database roles, users and grants, schema and migration tests, and custom lint rules. Use with the atlas skill whenever Atlas manages a SQL Server or Azure SQL database.

How do I install this agent skill?

npx skills add https://github.com/ariga/atlas --skill atlas-mssql
view source ↗

Is this agent skill safe to install?

  • Gen Agent Trust Hubpass

    This skill provides comprehensive instructions for managing SQL Server databases using Atlas. It is safe for use as it involves standard documentation, legitimate command execution for database management, and references to trusted vendor domains.

  • Socketpass

    No alerts

  • Snykpass

    Risk: LOW · No issues

What does this agent skill do?

Atlas for SQL Server

What is specific to SQL Server when Atlas manages the schema. The atlas skill covers the workflows and commands; this skill covers the rules, edge cases, and answers that differ on SQL Server. Behavior is described for SQL Server 2022; where a compatibility level changes it, the text says so. Azure SQL Database follows the same rules, and Fabric Data Warehouse has a guide of its own (https://atlasgo.io/guides/azure-fabric).

Files

This skill is SKILL.md plus seven reference files under references/, each served at https://atlasgo.io/skills/atlas-mssql/references/<name>. Read a reference when the task needs it:

FileRead it for
references/dev-database.mdConnection URLs, mode=schema and mode=database, Docker dev URLs, atlas tool docker, the docker block (collate, baseline), your own dev database, compatibility level and collation, Azure SQL and its Microsoft Entra ID sign-in
references/security.mdDatabase roles and users, users for logins, fixed database roles, GRANT and REVOKE, what is not managed (DENY, logins, row-level security)
references/objects.mdConstraint names, identity and sequences, column changes, alias and table types, functions, procedures, triggers and views, clustered indexes, temporal, memory-optimized and partitioned tables, what Atlas does not manage
references/hcl.mdWriting the schema in HCL: the NOT NULL default, defaults and their names, types (-1 for MAX, datetime2(7)), nonclustered, routines, sequences and types, seed data
references/migrations.mdLint on SQL Server, transactions, GO and the delimiter directive, txmode none, lock-safe changes, the revisions table and the lock, baselines
references/testing.mdTesting every change: T-SQL assertions, schema tests, permission tests with as, migration tests with seeded rows
references/custom-rules.mdWriting a custom lint rule in the HCL rule language when no analyzer covers a check: what SQL Server objects expose, and tested rules

Requires

The atlas skill, installed next to this one (../atlas/SKILL.md). If it is missing, download https://atlasgo.io/skills/atlas/SKILL.md to ../atlas/SKILL.md and each reference file listed in its Files section to ../atlas/references/<name>.

Rules

  1. Check the login first with atlas whoami. The SQL Server driver needs a login for every command: logged out, Atlas aborts with The sqlserver driver is available only to Atlas Pro users or login is required to use the 'sqlserver' driver. If it shows no login, ask the user to run atlas login; a user without an account can start a free trial with it.
  2. Write URLs as sqlserver://user:pass@host:1433?database=app&mode=schema, and pick the scope from what the schema holds (references/dev-database.md):
    • The database goes in ?database=. A database in the URL path is read as an instance name, and Atlas silently connects to the login's default database, usually master.
    • Schema scope, mode=schema, the default: Atlas manages the connecting user's default schema (dbo) and writes unqualified statements ([users]). Database scope, mode=database: Atlas covers every schema in that database, plus roles, users, and partition functions, and qualifies each statement ([dbo].[users]).
    • At database scope, a schema the desired state does not declare is planned for DROP SCHEMA, after its tables. Limit the run with schemas or --schema.
    • The dev URL takes the target's scope: docker://sqlserver/2022-latest/dev?mode=schema, or ?mode=database.
  3. The dev database runs the target's major version, compatibility level, and collation. A case-sensitive target needs a case-sensitive dev database: set collate in a docker block. Create what the schema references but does not manage in the block's baseline. Never point dev at a real environment's database.
  4. Name every primary key, check, and default in a SQL schema with CONSTRAINT <name>, and give every HCL check block a label. SQL Server names unnamed constraints itself with a random suffix (PK__users__3213E83F6692D103), so every plan renames the primary key or drops and re-adds the check or default. Keep the generated names inspection writes for existing constraints (references/objects.md).
  5. HCL differs from T-SQL in three places (references/hcl.md):
    • A column without null = true is NOT NULL, and so is a type_alias.
    • An index or unique block without nonclustered = true is clustered. Write nonclustered = true on every secondary index and unique constraint, or the plan fails with Cannot create more than one clustered index.
    • A bare datetime2, time, or datetimeoffset has precision 0. Write datetime2(7). Write MAX as -1: nvarchar(-1).
  6. Migration files are T-SQL, not sqlcmd scripts (references/migrations.md):
    • End every CREATE PROCEDURE, FUNCTION, TRIGGER, and VIEW with GO, or its body swallows the next statement.
    • Each statement runs on its own, so a DECLAREd variable is gone in the next one. Start a file that uses variables with -- atlas:delimiter \nGO and an empty line, so only GO splits it.
    • :setvar, :r, and $(var) fail.
  7. Atlas runs each migration file in a transaction, and a file that fails is rolled back completely. Never write BEGIN TRANSACTION or COMMIT in it. Statements that cannot run in a transaction, such as ALTER DATABASE, full-text indexes, and RESUMABLE index builds, go in a file of their own that starts with -- atlas:txmode none.
  8. Some changes are planned as a drop and an add, which loses data; lint reports them only as DS103 (references/objects.md):
    • An identity's seed or increment, or making an existing column an identity. To restart numbering, run DBCC CHECKIDENT ('dbo.t', RESEED, n) in a migration file and leave the schema's seed alone.
    • A computed column's expression.
    • A partition function's input type or range side drops and recreates every table on it. Write these by hand, and test them with seeded rows (Rule 11).
  9. Atlas leaves out DENY, database-level permissions, logins, synonyms, security policies, dynamic data masking, Always Encrypted, SPARSE columns, data compression, and filegroups. A statement for them in a SQL schema is dropped without an error, and no drift is reported. Apply them in hand-written migration files (references/objects.md).
  10. Managing roles and users needs mode=database on both URLs and mode { roles = true }; grants need permissions = true (references/security.md). Leave the fixed roles (db_datareader, ...) out of HCL and write membership in them in SQL. A user with login_name needs the login on the dev server. Set drop_user and drop_role in the skip diff policy if other teams own principals in the database.
  11. Test every change before reporting it done, as a habit. Run atlas schema validate, add a test "schema" case for each function, procedure, trigger, view, computed column, or check you add or change, and a test "migrate" case with seeded rows for each migration that moves, converts, or narrows data. Cast decimal, money, and uniqueidentifier values to text in test queries, and write asserts as SELECT IIF(<condition>, 1, 0). Run them with atlas schema test and atlas migrate test (references/testing.md).
  12. SQL Server has no lint analyzers of its own, and lint does not report a narrower type that truncates rows. When no analyzer covers a check, write a custom lint rule in the HCL rule language, and do the same for a convention the user states, such as an index on every foreign key or SCHEMABINDING on every view (references/custom-rules.md).

Quick Answers

QuestionAnswer
A local SQL Server database to apply migrations toatlas tool docker --url "docker://sqlserver/2022-latest/myapp" --name my-db prints its URL; atlas tool docker kill --name my-db stops it (references/dev-database.md)
The sqlserver driver is available only to Atlas Pro users, or login is required to use the 'sqlserver' driverAsk the user to run atlas login (Rule 1)
Inspection prints an empty schema "dbo" {} for a database that has tablesThe database is in the URL path; move it to ?database= (Rule 2)
Connect to Azure SQL without a password (Microsoft Entra ID, a managed identity, a CI service principal)azuresql://<instance>.database.windows.net?fedauth=ActiveDirectoryDefault&database=<name>: the driver gets the token from the Azure credentials where Atlas runs, with no data source (references/dev-database.md, Azure SQL and Fabric)
cannot diff a schema "dbo" with a database connection, or the reverseGive the URL and the dev URL the same mode (references/dev-database.md)
cannot use HCL with more than 1 schema when dev-url is limited to schema "dbo"Use mode=database on both URLs (references/dev-database.md)
schema connection mode requires a default schema to be set for the userGive the user a DEFAULT_SCHEMA, or use mode=database (references/dev-database.md)
A plan drops other schemas (DROP SCHEMA)Database scope covers every schema: limit it with schemas or --schema (references/dev-database.md)
Every plan renames a primary key (sp_rename), or drops and re-adds a check or defaultName the constraint (Rule 4)
Cannot create more than one clustered index on table, or Cannot add more than one clustered index for constraints on tableAdd nonclustered = true to the HCL index or unique block (Rule 5)
datetime2 values lost their fractional secondsThe HCL type is a bare datetime2: write datetime2(7) (references/hcl.md)
There is no type named "MAX"Write nvarchar(-1) or sql("nvarchar(max)") (references/hcl.md)
A column became NOT NULL without asking for itHCL columns are NOT NULL unless they set null = true (references/hcl.md)
Incorrect syntax near the keyword 'VIEW' (or another keyword) after a CREATE PROCEDUREAdd GO after the procedure (Rule 6)
Must declare the scalar variable "@x"Start the file with -- atlas:delimiter \nGO (references/migrations.md)
Incorrect syntax near ':'sqlcmd syntax such as :setvar: use plain T-SQL (references/migrations.md)
ALTER DATABASE statement not allowed within multi-statement transactionMove it to a file with -- atlas:txmode none (Rule 7)
Could not find stored procedure 'COMMIT'Remove BEGIN TRANSACTION and COMMIT from the file (Rule 7)
String or binary data would be truncatedA narrower column type: fix the rows first, and add a migration test (references/objects.md)
Incorrect syntax near the keyword 'RESTRICT'SQL Server has no RESTRICT: use NO_ACTION (references/hcl.md)
A plan drops and re-adds an identity or computed column (DS103)Data loss: write it by hand, or reseed with DBCC CHECKIDENT (Rule 8)
Cannot drop type 'x' because it is being referenced by objectA column uses the alias type: write the change by hand (references/objects.md)
cannot set value for generated column "id" in a data blockIdentity columns cannot be seeded from HCL; seed them from SQL (references/objects.md)
connected database is not cleandev points at a database that is not empty (references/dev-database.md)
already exists or There is already an object named on the dev database, on a later runA user, login, or unmanaged object stayed on a dev database you provide: use a Docker dev database (references/dev-database.md)
There is already an object named while loading a composite_schemaThe object is defined in two of its sources, such as a shared base and an environment folder: keep one (https://atlasgo.io/guides/environment-specific-objects)
Invalid object name 'X' while loading the schemaIdentifier case on a case-sensitive dev database, or an object missing from the baseline (references/dev-database.md)
User, group, or role 'db_...' already exists in the current databaseRemove the fixed-role role blocks from the HCL (references/security.md)
unknown attribute external in block of type roleSQL Server roles have no external (references/security.md)
'x' is not a valid login or you do not have permissionCreate the login on the dev server, in the baseline (references/security.md)
A DENY, synonym, security policy, or SPARSE column is missing from the planAtlas does not manage it: write it in a migration file (Rule 9)
acquiring database lock: sql/schema: lock is held by other sessionAnother deployment holds the database's lock: wait, or raise --lock-timeout (references/migrations.md)
We couldn't find a revision table in the connected schema after changing modeSet --revisions-schema to the schema that holds the table (references/migrations.md)
Test output shows 0x... for numbers or GUIDsCast the column to varchar in the test query (references/testing.md)
template attribute is supported only for postgres dev-databasesSQL Server has no template databases: remove template (references/testing.md)
Lint does not flag a narrower column typeAdd a migration test with seeded rows, and a custom rule if it is a convention (Rule 12)

Documentation

Add the canonical catalog link to the repository README so users can inspect current installs and available audits. The publishing guide covers the complete discovery path.

<a href="https://skillzs.dev/skills/ariga/atlas/atlas-mssql">View atlas-mssql on skillZs</a>