1

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?

3
  • 1
    Have you checked out the Database Project in Visual Studio, or the sqlpackage utility it uses for schema diffs and deployments? learn.microsoft.com/en-us/sql/tools/sqlpackage/… Feb 25, 2023 at 18:14
  • @DavidBrowne-Microsoft I’ve been trying to understand how SqlPackage would help in this case. SqlPackage seems to be able to capture the configuration including dependencies of an existing database into a file. That file can then be used to deploy the database elsewhere. I don’t yet see how it would help manage dependencies defined in code before the database has been created for the first time. Am I missing something here?
    – alx9r
    Feb 26, 2023 at 0:06
  • 1
    @alx9r, VS database projects are built entirely from source code. A project includes the complete data model and can be used for iterative local sandbox development and testing by recreating the sandbox db each iteration. The project build and deploy process handles the dependency ugliness. Once local development and testing are complete, migration scripts can be created using the tool of your choice, including VS schema compare or sqlpackage. This method basically a hybrid of state-based and migration script approaches.
    – Dan Guzman
    Feb 26, 2023 at 13:06

1 Answer 1

0

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.

2
  • I'm puzzled by this answer as my question isn't about migrating a database. Rather, my question is about creating a new database from source code. There's no production data yet, so no data to migrate. So I'm not sure how to interpret this answer. Do you mean that a database migration tool would resolve dependencies of SQL objects defined in source code? Do you mean that instead of source code being the authoritative definition during development, an output from a migration tool should be the authoritative artifact instead? Or do you mean something else?
    – alx9r
    Feb 26, 2023 at 14:53
  • 1
    @alx9r The term migrations in this context means schema migrations. It doesn't literally mean to migrate from one database system to another, or even one database to another (necessarily), but rather to migrate one implementation / version of your schema to the next version as you make changes to your design. "I have to manually re-resolve the order of the files defining the objects are executed" - That's you manually migrating your schema to a new version. I'll admit, the first time I heard the term, it confused me for a few minutes too, but it's just a synonym for revision control.
    – J.D.
    Feb 26, 2023 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.