Suppose we use SQL stored procedures in our project. We write them directly in MS SQL Sterver Managament Studio.

Imagine a situation: we changed the code, then we corrected some procedure, or added a new one. Everything is clear here with the code, we store it on the git server and it’s not a problem to trace its evolution, but what to do with stored procedures and how to link their evolution with the code for which they are written?

  • We somehow had no problem with git with dozens of objects, where the software was sent and at the same time it was necessary to change the structure of the database. A small patcher was made, which simply executed all the sql code from the update files, in the order of their numbers. the last patch number sent to the database was recorded in the database itself, thus showing its version. The next time the patcher took only files that have not yet been executed on the database. thus, git should keep the files with the latest version of all procedures and separate patch files that bring the database structure to the desired state - Mike
  • For each release, store a script with updates of the database + scripts with the current state of objects - Denis Rubashkin
  • 2
    Add xg for git "pre-commit", which will drop all the procedures into a separate file. I even found an option, but for MySQL: gist.github.com/mithereal/13d94f5a00f4741b6342 - Total Pusher

3 answers 3

If the question was about how to store the source code of the database, then one of the options is in your decision to create a database project ( http://prntscr.com/js8arv ). True, there is a template only for MS Sql, I don’t know if there is the same for other DBMS to work in VS. You can fill out a project from a ready-made database by comparing schemas like this ( http://prntscr.com/js8d7v ) In fact, you upload yourself a complete diagram of your database as a set of files, each of which is a single object (table, procedure and etc.). You can keep this project in your solution. All changes will be committed to Git. Further development can be carried out in VS and then also through the comparison of the schemes to update the database, or using some other software (then after the changes do not forget to also update the project in VS through the comparison of the schemes). But I repeat, this is all good if the base is under your control. And if you hand over the development to the customer, then you need to provide a mechanism for updating the database when updating the application (see the answer about the update mechanism)

  • Good decision thanks! - Vasya Milovidov
  • You are welcome. Use and do not deny yourself anything :) - Cat Matroskin

For MS SQL, as well as for many other RDBMSs, in the evening you can write a utility that unloads the SQL script for creating all stored / view / triggers into one large or a bunch of separate files. They can be kommitit in git together with source codes.

You can approach the problem administratively and simply oblige before a commit to unload scripts for modified procedures using the same Managament Studio.

In order not to forget what exactly to upload, you can create DML triggers that will log what and when each programmer has changed.

    If the combat base is somewhere not under your control, then you will have to develop a mechanism for updating it. I once had developed the following mechanism for desktop applications:

    1. When connecting to the database, the application compares the current version of the database (stored in the database, updated by the update script) with the required version of the database (stored in the application, updated by the application developer if necessary).
    2. If the database version is outdated, the application offers updated.
    3. Next, a utility is launched that updates the database to the required version (in fact, it runs the update script).
    • Do you write procedures in the studio where in the manager? Is there some version control for them? - Vasya Milovidov