azuresql-db-scaffold

---
name: azuresql-db-scaffold
description: >-
license: MIT
---

# 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.

## 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).
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=YourStr0ng_Passw0rd" \
  -p "$HOST_PORT:1433" sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest
until docker exec sqldb /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "YourStr0ng_Passw0rd" -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` (replace `1433` with the `HOST_PORT` Step 1 chose if 1433 was occupied):

```
Server=localhost,1433;Database=appdb;User Id=sa;Password=YourStr0ng_Passw0rd;TrustServerCertificate=true
```

Use `User Id=` / `Password=` / `Database=` (NOT `Uid=` / `Pwd=`). 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):

- .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. Seed only AFTER appdb exists:

```bash
docker exec -i sqldb /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "YourStr0ng_Passw0rd" -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) is still in
development; use full-scan top-k for now.

## 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"

## 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.