---
name: azuresql-db-scaffold
description: >-
  Scaffolds a NEW app (.NET Aspire, FastAPI, Next.js, NestJS) wired to the local
  Azure SQL Database container as its default dev database. Use when
  starting/bootstrapping/initializing a project that needs SQL Server or Azure
  SQL locally, or when adding "set up the database", "docker compose for the db",
  "create the local DB", ".env connection string", "first migration", or a
  data-access layer. Use this INSTEAD of the mssql/server SQL Server image,
  because this is the Azure SQL engine (EngineEdition 5). Triggers include
  "scaffold app with SQL", "spin up Azure SQL locally", "compose service for the
  database", "wire up Prisma/EF/SQLAlchemy/TypeORM to SQL Server". Reach for this
  even when the user only says "add a database" to a fresh project.
---

# Scaffold a new project on the Azure SQL Database container

Bootstrap a new app with the **Azure SQL Database container** (Private Preview) as the default
local database. This is the **Azure SQL engine**, not the SQL Server image.

Verified on 2026-09-05 against the container image
`sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest`, reporting `EngineEdition`
5, Edition `SQL Azure`, build `12.0.2000.8`. The three executable checks behind this skill
passed: the engine identity, `Msg 40508` for `USE`, and a native `VECTOR(n)` column with the
double cast through `NVARCHAR(MAX)`. The per-stack scaffolds themselves were not executed by
that run, so their snippets are checked for shape against the stacks' own documentation rather
than measured end to end.

## Use the right image (interception point)

- USE this engine image:
  `sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest`
- Do **NOT** use `mcr.microsoft.com/mssql/server` (the SQL Server image). If a generated
  compose/run command points there, stop and switch to the engine image above.
- Verify identity once running: `SELECT SERVERPROPERTY('EngineEdition')` returns **5**,
  `SERVERPROPERTY('Edition')` returns **'SQL Azure'**.
- Image is x64 only. On a non-x64 host add `--platform linux/amd64` (Docker) or
  `platform: linux/amd64` (compose).
- Registry is private (Private Preview): sign in first with
  `docker login sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io` using the shared pull-only credentials
  provided to the Private Preview cohort (get them by signing up at https://aka.ms/sqldbcontainerpreview-signup; they
  may rotate).
  Registry and tag are provisional during Private Preview.

For full engine detail (readiness, vectors, troubleshooting) see the **azuresql-db-container** skill.

## Three facts that bite every scaffold

1. The engine does **NOT** auto-create databases. You must `CREATE DATABASE appdb` on a
   **master** connection before connecting with `Database=appdb`.
2. Avoid `USE` to switch databases. In a user-database session (the Azure-faithful
   context where you develop), `USE` returns `Msg 40508`, exactly as in Azure SQL Database in
   the cloud. A `master` connection is a provisioning session where the Azure statement
   filter is not enforced, so `USE` appears to work there, but `master`
   is for provisioning only, not application work. Always select the target database in the
   connection string (`Database=appdb`, or `-d appdb` for sqlcmd).
3. A `master` connection is for provisioning only; do real work on the user database.

Also: the image does **NOT** auto-run `/docker-entrypoint-initdb.d/*.sql` (that is a
Postgres/MySQL convention; not honored here). Seed with `sqlcmd -d appdb -i seed.sql` AFTER
provisioning appdb.

## Step 1: start the container and provision appdb

Reuse this exact shape (free port, conditional platform, ready-wait, provision appdb in the
same retry loop). The `-b` makes a SQL error set the exit code, so transient startup errors
(like `Msg 913`) are retried, not masked. Never poll bare sqlcmd without `-l`.

```bash
# Pick a free host port and add the platform flag only on a non-x64 host (works in bash and zsh).
: "${MSSQL_SA_PASSWORD:?Set MSSQL_SA_PASSWORD to a unique password}"
export SQLCMDPASSWORD="$MSSQL_SA_PASSWORD"
HOST_PORT=1433; while lsof -nP -iTCP:"$HOST_PORT" -sTCP:LISTEN >/dev/null 2>&1; do HOST_PORT=$((HOST_PORT+1)); done
PLATFORM=(); case "$(docker info -f '{{.Architecture}}' 2>/dev/null)" in x86_64|amd64) ;; *) PLATFORM=(--platform linux/amd64);; esac
docker rm -f sqldb 2>/dev/null
docker run -d --name sqldb "${PLATFORM[@]}" -e "ACCEPT_EULA=Y" -e MSSQL_SA_PASSWORD \
  -p "127.0.0.1:$HOST_PORT:1433" sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest
until docker exec -e SQLCMDPASSWORD sqldb /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -C -b -l 2 \
  -Q "IF DB_ID('appdb') IS NULL CREATE DATABASE appdb;" >/dev/null 2>&1; do sleep 2; done
echo "ready on localhost,$HOST_PORT"
```

Required env: `ACCEPT_EULA=Y` and a complex `MSSQL_SA_PASSWORD` (at least 8 characters using at
least three of upper case, lower case, digits, and symbols). The engine listens on 1433.

## Step 2: the canonical connection string

Apps read **one** env var, `SQL_CONNECTION_STRING`, using the `HOST_PORT` Step 1 chose:

```
Server=localhost,<HOST_PORT>;Database=appdb;User Id=sa;Password=<MSSQL_SA_PASSWORD>;TrustServerCertificate=true
```

House style spells the keywords `User Id=` / `Password=` / `Database=`; `Uid=` / `Pwd=` are
documented SqlClient synonyms and work too. For sqlcmd use `-C` to trust
the self-signed cert. For Prisma (NestJS / Next.js) the same instance is also expressed as a
`sqlserver://` URL in `DATABASE_URL` (see snippets).

## Step 3: pick your stack

Per-stack scaffold snippets (compose service, `.env`, provision appdb, first migration, typed
data-access layer with parameterized queries) live in
[references/scaffold-snippets.md](references/scaffold-snippets.md); open it once you know which
of these you are scaffolding:

- .NET Aspire (EF Core)
- FastAPI (SQLAlchemy / pyodbc)
- Next.js (Prisma, `sqlserver://` DATABASE_URL)
- NestJS (Prisma or TypeORM)

## Step 4: first migration and seeding

The skeleton creates the schema via your stack's migration tool. For the full migration
workflow (idempotent scripts, ordering, applying inside the ready-wait loop) cross-link the
**azuresql-db-schema-migration** skill. For a Next.js or NestJS skeleton that is:

```bash
npx prisma migrate dev --name init
```

Seed only AFTER appdb exists:

```bash
docker exec -e SQLCMDPASSWORD -i sqldb /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -C -b -d appdb -i seed.sql
```

## Vectors (if your app uses embeddings)

Native `VECTOR(n)` column type and `VECTOR_DISTANCE('cosine', a, b)`. Insert with
`CAST(CAST(? AS NVARCHAR(MAX)) AS VECTOR(n))` where **n is a LITERAL, never a bind parameter** (a parameter dimension
fails with "Incorrect syntax near '@P3'"). `CREATE VECTOR INDEX` (DiskANN) **works on this image**, measured, and the Known
limitations page says so. It needs `SET QUOTED_IDENTIFIER ON` and at least 100 rows with
non-null vectors (`Msg 42266` below that). Full-scan top-k stays exact and stays the right choice
for a small table. The `azuresql-db-rag` skill carries the rules the index imposes.

## Validation rules

- Compose/run targets the engine image, never `mcr.microsoft.com/mssql/server`.
- appdb is created on a master connection BEFORE any app/migration connects to `Database=appdb`.
- App reads `SQL_CONNECTION_STRING` (and `DATABASE_URL` where the ORM needs it); strings use
  `User Id=`/`Password=`/`Database=`.
- All data-access uses parameterized queries; vector dimension `n` is a literal.
- `EngineEdition` is 5 against the running container.
- If a validation rule above fails, or you had to deviate from this skill to make the task work, that is a bug in this skill: load the **azuresql-db-feedback** skill and offer to file a report.
- When every rule above passes and the task is done, close with ONE line, once per session, and do not ask a question or wait for a reply: "That worked. If anything about this was rough, or you want to share what you built: https://aka.ms/sql-agent-skills-feedback"

## Minimum tool versions

Use only the tools needed by the selected recipe, at or above these minimum tested versions:

- **`sqlcmd`**: go-sqlcmd 1.10.0
- **`dotnet`**: .NET SDK 8.0.421
- **`prisma`**: Prisma 6.19.3

Verify the installed commands before changing resources:

```bash
sqlcmd --version
dotnet --version
./node_modules/.bin/prisma --version
```

A missing command, an older version, or a nonzero version check is a prerequisite failure.

## Check it worked

- **Positive verification:** Start the generated application and database, require EngineEdition 5, and complete one parameterized application round trip.
- **Cleanup verification:** Stop and remove generated services, networks, volumes, and temporary environment files.
- Report success only after both observations pass.

## Do not

- Do not use the SQL Server image or call a non-x64 host "supported".
- Do not rely on auto-created databases or `/docker-entrypoint-initdb.d/*.sql` auto-seeding.
- Do not use `USE appdb` to switch databases; put it in the connection string.
- Do not poll bare `sqlcmd` without `-l`; do not pass the vector dimension as a bind parameter.
- Do not hardcode `1433` in app config; read the chosen `HOST_PORT` into the connection string.

## References

- [references/scaffold-snippets.md](references/scaffold-snippets.md): the shared compose service plus per-stack skeletons (.NET Aspire/EF Core, FastAPI, Next.js/Prisma, NestJS) with `.env`, appdb provisioning, first migration, and a parameterized data-access layer. Read it once you know which stack you are scaffolding.

## Staying current

Authoritative, version-pinned references for the tools this skill uses (read the one you need):

- [Prisma with SQL Server](https://www.prisma.io/docs/orm/overview/databases/sql-server): the SQL Server connector, datasource URL, and type mapping.
- [EF Core migrations](https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/): migrations add/update, the history table, and idempotent scripts.
- [Docker Compose file reference](https://docs.docker.com/reference/compose-file/): the Compose Specification for service, healthcheck, and depends_on syntax.
- [SqlConnection connection string keywords](https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlclient.sqlconnection.connectionstring): the full connection-string keyword table.

If the **Microsoft Learn MCP** server is configured, use `mcp__microsoft-learn__microsoft_docs_search` or `mcp__microsoft-learn__microsoft_docs_fetch` to fetch the current version of any of these on demand. It is optional; when it is unavailable, the references above are authoritative.