Up:: Programming
Managing sql in git
Why in GIT
Version control is as important in sql as it is in code. The changes are easily tracked, and versioning is also possible. Rollbacks will also be available should something be breaking and a rollback would resolve the issue. It also creates a place outside of the database that can be trusted as the source of truth for all procs related to any process on the project. This means that any changes on the database that is not in the repo can be overwritten as it is not the responsibility of the person updating the proc to make sure it is up to date to start.
Basic Concepts
- 1 File - 1 Stored Procedure/Function/Structure
- Repo is the Source of truth.
- All stored procedures, functions, triggers and structure changes should be worked on from the repo, NOT the database.
Folder Structure
|-- Repo
|-- Structure
|-- Data
|-- Common
|-- Processes...
|-- Misc
Structure
All structure related changes and updates should be in this folder with a date and time prefix to a short description of the change (YYYYMMDD_HHmmSS_ChangeDescription.sql), ie:
20230101_142424_AlterTBL_TR_RPR_ProblemdetailsToAddBooleanCheckColumn.sql
The reasoning for this it gives you a searchable filename as well as the order in which changes are brought into the database, which gives you the ability to restore your database from your code should the ability to restore from backup not be possible.
Data
This folder is for the standard data sets to be included, especially the lookup data that should be present in the database, alongside any database updates. The file name again should be in the format YYYYMMDD_HHmmSS_UpdateDescription.sql
20230228_075722_UpdateRPRStatuses.sql
Common
This folder is for items that are for use across multiple processes and cannot be grouped with just one process.
Processes..
This is just a way to say that each process should have its own folder to group related sql item together that is not structure or data (Stored procedures, functions, triggers)
Misc
This folder is for items that may not be used in the system but that you deem worthy of being included in the repo.
Basic Workflow
sequenceDiagram Developer->>Repo: Gets latest sql from repo Developer->>Repo: Creates a branch Developer->>Repo: Commits changes Developer->>Repo: Creates pull request Developer->>Repo: Deploy to relevant environment
flowchart TD; A[Get the latest sql from repo]-->B[Create branch] B-->C[Do work on branch] C-->D[Create pull request] D-->E[Deploy to relevant environments]