Controlling who commits code to a given database schema in Git with Azure DevOps

One of most the fun things about working as an Advocate at Redgate is getting to help clients determine their preferred workflow for database DevOps.

Teams often have unique requirements and are using different combinations of tooling, so figuring out the best way to accomplish what they need typically involves leveraging what I already know, collaborating with my coworkers and the client to generate ideas, researching and prototyping solutions, and then getting feedback from everyone.

This week, I’ve been thinking about a scenario which may come into play when an organization is moving away from a monolith architecture toward a microservices architecture

One pattern which can enable this move is to at first increase the use of schemas within the existing large legacy database, and begin to document and understand dependencies between different parts of the codebase. Over time, the goal is to reduce dependencies and to begin to move portions of the database out from the large legacy database and into their own data stores. The schemas help clarify ‘ownership’ of code with minimal initial impact to performance and no need for initial data movement.

Part of starting to clarify ‘ownership’ of code in schemas is to limit who commits code to objects in a given schema

In other words, if team Fruitbats owns a schema which exists for the Fruitbats service, members of the Cheeseweasel team shouldn’t just go making changes to those objects. Instead, they need to talk to the Fruitbats team and discover what the best way is to accomplish what they need. At the same time, it’s valuable for team members to be able to see the code for all objects to support the existing legacy codebase.

I thought about a couple of different options in thinking through this, including things like using multiple projects, using a branching strategy, or figuring out some way to run checks in pre-commit hooks with Git.

Here’s what I would do

After consideration, the approach which I think is the simplest, but which has the most benefits, is to keep all objects in the database in a single project and use Pull Requests (PRs) with automatic reviewers to ensure the right changes are being made.

This is simple to set up with a combination of Azure DevOps and Redgate’s SQL Source Control tooling, because SQL Source Control uses an object’s schema name as part of the file name.

Here’s an overview of the setup:

• A branch policy protects master and requires a PR to complete a merge
• Automatic reviewers are set up
  • Establish a user or group with ownership over each schema, and set up a rule so that if any files in that schema are changed in a PR, the group or user is added to the PR. It can’t pass until they review it (or someone with permission to override makes it so).
  • Since SQL Source Control places the schema name at the beginning of file names, the filter for each schema can be as simple as something like: */fruitbats.*
• Automation can further automatically run a build of code and run tests against the code when the PR is created, which can do things like ensure that new cross-schema dependencies are not introduced

I like this approach because while it ensures that the teams owning a schema will always be in the loop on a change, it is still flexible: there may be times when it is desirable to have a single person contribute changes to multiple schemas. This allows that without the need for permissions to be reconfigured, and it even gives a place for the owners of all those schemas to have visibility and input on the change early in the process.

In other words, this was simpler than I thought

The approach here can be a simple modification to an implementation of the Azure DevOps Release Flow branching strategy I wrote about last week – it’s a way to use feature branches with automatic reviewers. While there are certainly other ways to solve this problem, this workflow fits many teams and is also quite simple to implement and maintain.