Is there a best practice for resolving dependencies of SQL objects for build during development?

Question

I have a greenfield project involving a SQL database that is under development. There is no production data involved yet. There is a rather long list of specifications which I expect can be implemented with a collection of SQL tables, views, functions, and procedures. The overview of the inner loop of the development process is basically the following:

1. drop and recreate the database
2. create the tables, views, etc
3. import test data
4. run tests
5. make changes to code
6. repeat beginning at (1)

The development has already involved a number of refactorings. The results of these refactorings are good. However, they usually precipitate a period of dependency hell in step (2) during which I have to manually re-resolve the order of the files defining the objects are executed. That file order is currently hard-coded as a list of file names in a script. Each file has exactly one object defined in it.

The process of manually resolving dependencies works, but is labor-intensive. For C and .Net projects in the past I have used make and MSBuild to manage dependencies, respectively. I can almost imagine how to manage SQL dependencies with those tools, but I haven't seen it done and I'd rather not re-invent the wheel if there is a method that is already proven. A search of stackoverflow for makefile and MSBuild with SQL didn't produce any results that were obviously on-point.

Is there a best practice for resolving dependencies of SQL objects for build during development?

Answer 1

I can almost imagine how to manage SQL dependencies with [make and MSBuild]

For future reference, the old-school tool I'd use for this is the POSIX (and WSL) utility tsort (topological sort). A topological sort creates a full ordering from a set of partial orderings.

Assume you create a file like this one. Read the first line as "I need to build the table orders before the table orderitems". In this limited case, we're probably concerned with foreign key references, but our immediate concerns don't matter. Just the knowledge of "a before b".

table-orders    table-orderitems
table-products  table-orders
table-customers table-orders
table-inventory table-orders

The tokens are completely arbitrary. Use whatever makes sense to your use.

Run that file through tsort to compute a full ordering that's compatible with these partial orderings.

$ tsort my-sql-database.txt 
table-customers
table-inventory
table-products
table-orders
table-orderitems

If you build in this order, you'll satisfy all the partial orderings in the file.

Answer 2

The generic term you're looking for is migrations / migration scripts.

• A Database Project (as per David Browne) is one tool to help manage migrations.

• EF Core Migrations is another tool to manage revisions.

• Third party schema comparison tools like SQL Examiner can also be used to generate migration scripts, but typically require two databases to compare the differentials of when generating the scripts.

1. drop and recreate the database

This is probably the reason for your main struggle. Migrations are typically cumulative, to continually change the database to the latest version. They shouldn't be a complete revision of the original script resulting in a single perfect script from the inception of the database. The scripts to create the database to it's latest form should include any changes as additional alters to the schema.

Even if you start out with an object, say a View for example, that you later on realize you no longer need, you shouldn't change the original scripts to remove that View and update all its dependencies. Instead, an additional script should be created to DROP the View and alter any dependencies appropriately. So in the entirety of your schema scripts, for a specific version of the database, the View would get created and dropped, all in one deployment.

If you're running into issues finding out what those dependencies actually are, when a change is made, there are a few ways to find them. One way is in SSMS (SQL Server Management Studio), there's a tool to View Dependencies of an object, which gives you the recursive tree of dependencies for that object.