In a previous post, I discussed using grate for migration-based database development. I presented some advantages, including increased confidence when publishing database changes and the simplicity that is afforded by being able to break changes into multiple steps. However, SSDT Database Projects offer project compilation, making it quick to—for example—find out if your intended change to a table breaks a view. This is something that's not available when using grate by itself.
Is there a way to get the best of both worlds? Let's explore.
Desired setup
To get the benefits from using grate, we need to be using its migration scripts as the source of truth. So the workflow we're after is:
- Create migration scripts, possibly generated using the database project.
- Apply migration scripts in local dev environment.
- Update the database project to reflect the new schema, so that it can be used for creating change scripts in future.
Ideally, we want a way of scripting step 3 so that it is automatically applied after step 2. If we have to do it manually, there's a risk of forgetting to update the database project, which could mean later accidentally generating scripts that revert a previous change.
Generating a change script
Generating a change script by comparing a database project to a connected database is possible using sqlpackage. To do this, first the database project needs to be built to generate a DACPAC file, then the sqlpackage script action needs to be called with the DACPAC file as the source and the database as the target.
Applying the change scripts
This is as simple as running grate. See the previous post for details.
Creating a database project from a database
Using Azure Data Studio, it's possible to create a database project from an existing database. It's also possible to update the project from Azure Data Studio using a right-click.
No scripting of database project creation from a database
I've looked for a way to script database project creation (or updates), so that the end result is a database project that reflects the schema of an existing database. After a fairly extensive search, including asking on StackOverflow, I've not found anything to suggest that this is possible. This is disappointing, as it makes the workflow more manual and therefore more error-prone.
Summary
The point we've arrived at is rather dissatisfying. It seems that we can use grate and SSDT together, but the workflow for doing so is a bit clunky and introduces some risks. However, it's not clear cut whether this is a dealbreaker. In a project with a lot of views, stored procedures and functions, the value provided by project compilation is pretty significant, and likely to be worth the clunky workflow. When the project is mostly table definitions, not so much.
There are however, a few other potential avenues worth exploring:
- Perhaps grate could be used by itself most of the time, and there could be occasional manual imports to check that the project compiles.
- The code for the SQL Database Projects extension for Azure Data Studio is available on GitHub, and the project license allows you to use and modify it. Maybe it's possible to extract or reverse engineer something scriptable 🤔
To my tastes, as it stands, standalone grate looks like the most appealing option. Take that with a pinch of salt though—I've not yet used it in production! I'm sure there'll be others who prefer sticking to only SSDT database projects, or are happy with the workflow I've described above. Whatever the outcome, I hope you've enjoyed this post 🙂
