Keep your queries up to date
This page says what to commit to git, and what to do after a schema change or migration, a merge conflict, and in CI.
What to commit
- Commit your
.sqlfiles, their sidecars (*.sql.json),sqlsource.jsonanddotnet-tools.json. Every build reads the sidecars, and a copy of the project without them reports SQLSRC241 for each query. - Do not commit your
.envfile, since it holds your password. - Never edit a sidecar by hand. The tool writes it.
Add this line to the .gitattributes file at the root of your repository:
# .gitattributes
*.sql.json linguist-generated=true
GitHub then collapses the sidecars in the diff of a pull request.
The build describes for you
If the folder of your project has a .env file, dotnet build describes your queries before it compiles:
- If you change a query, the next build describes it again.
- If your database rejects a query, the build shows an error at that query.
Commit the sidecars that the build writes. A build with no connection does not describe. It reports SQLSRC244 for a query that changed since it was described. A .env file at the root of your repository does not count: see Give the tool your connection.
After a schema change
No build sees a change to your schema, such as a migration. After a migration, describe every query again. Then commit the sidecars that changed:
$ dotnet sqlsource describe --force
postgres (postgres): 1 described, 0 skipped, 0 failed
$ git add '*.sql.json'
$ git commit -m "Describe the queries again after the migration"
--force describes each query again, whether or not it changed. The number in the line is the count of your project's queries, so yours can differ. If the change broke a query, such as one that reads a column you dropped, the tool reports what your database said.
A merge conflict in a sidecar
If git reports a conflict in a sidecar:
- Bring your database to the schema of the merged code, such as by running the migrations of the other branch.
- Leave the conflict in the sidecar.
- Run
dotnet sqlsource describe. It describes each query of the file again and writes the whole sidecar. - Commit what it writes.
Until then, the build reports SQLSRC237 for the sidecar.
In CI
A build in CI never describes. It reads the committed sidecars. So a query that someone changed and did not describe fails the build in CI with SQLSRC244.
The build cannot see a sidecar that a schema change made wrong. To find that too, add a step to your CI job that describes against a database with your schema:
$ dotnet sqlsource describe --check
--check changes no file. It fails with SQLSRC219 when a sidecar is missing or out of date.
If it does not work
- SQLSRC244: a query changed after it was described. Build with your
.envfile in place, or rundotnet sqlsource describe. Then commit the sidecar. - SQLSRC237: a sidecar holds a merge conflict. Run
dotnet sqlsource describe. - A build does not describe: the
.envfile is not in the folder of your project.
What you have now
The sidecars in git, a build that describes what you change, and a check in CI.
To describe each query as you save it, see Describe queries as you save (watch). For a whole CI job, see Check the sidecars in CI.
Rules: sidecars in git, sidecar files, running in the build, after a schema change, describing again, check in CI.