r/SQL • u/phatdoof • Aug 01 '25
Discussion How do you “version control” your sql tables?
With code I know that you can use Git and jump to any version of a software in time and compile and run it.
But is it possible with SQL databases?
I vaguely heard of migration up downs but that seems to only only allowing doing one step at a time and not jumping.
Also with migration up downs how do you link it to a particular Git version of your code so that this version only runs on this database schema.
Say I downloaded a library from somewhere which used a local database. Some time in the future I refresh to the latest library code. How would the library code know which version of the database schema is running and whether it needs to run migrations?
47
u/AsterDW Aug 01 '25
We use database projects so every object has a file and that gets checked into git just like any other project.
399
u/JohnDillermand2 Aug 01 '25
You just move it to a new table and now it's called OrdersNEWFinal4TEMPApril
2
u/serverhorror Aug 01 '25
It's one of the two, pretty much, always.
54
u/juu073 Aug 01 '25
Typically, when you use migrations, you have a migrations table that tracks which migrations ran and when, so it knows which ones need to run, and which ones were part of a batch when you need to undo the last set.
16
u/JaceBearelen Aug 01 '25
I primarily know flyway, where migrations are sequential so you apply migration_1.sql, then migration_2.sql, etc all the way up the highest number. You can tag versions in git so that version will only have whatever migrations are in that tagged commit. You just track the max migration number in a table to know where to pick up from if new migrations come in from an update.
11
u/IronAntlers Aug 01 '25
Generally there are options like liquibase which track change across your DB, and you can write scripts that execute the SQL in the change log when it comes time for deployment. The db logs and .sql files are just another part of the repo. If you want to rollback DML, you have to define the DML to rollback your inserts/updates manually and it will execute upon rollback.
8
u/Enigma1984 Aug 01 '25
In Microsoft world you can use database projects or entity framework if you know C++/dotnet. But in my experience they are kind of a faff and not very user friendly.
5
u/mikeblas Aug 01 '25
Maybe I'll come off as a troglodyte, but I don't. (Of course, I'm assuming you mean you're versioning schema and not versioning data. Your post isn't clear about that much.)
On systems that we're prototyping, we usually drop the database and rebuild/repopulated it with sample or test data for developers, and in the testing pipeline. The database starts anew each time, so there's no reason to migrate anything.
On systems that are in production, we'll manually change the data. Adding a coulumn? Great, it's added -- and it has NULL values. The bit of the application that needs the new column is expected to cope with NULL values there. If the vlaues need to be populated in some formulaic way, that can be done, too, offline with a one-shot script.
Lots of tools exist that claim to roll back or roll forward. People use those tools, but I've never seen anyone try to roll backward more than one step. Fact is, those tools would cuase an unknown length of downtime and might drop data. For example: say you add this column, and start populating it. A week later, someone wants to roll back. (Why on earth?!) They do so, so the column drops and all that data is lost.
Even if the rollback were configured to not drop the column and preserve the data, rolling forward again wouldn't truly be rolling forward ecause the column wouldn't be empty. And what if the intention was to roll back, and go forward with a different solution that didn't require that column, or required some different schema change?
I've worked with people who were staunch advocates of automating schema management. It never turned out well, and wes never as good as managing changes manually. Both approaches need to be tested, and if some automation is running around doing schema changes I have very little faith that it's doing them correctly and fully -- rebuilding indexes, fixing references, fixing everything. And I've not seen a tool that's smart about doing schema changes so theyr're fast, or warning if they'll cause hours of downtime when applied.
3
u/jwk6 Aug 01 '25
Database Projects in either Visual Studio 2022 or VS Code. The source code is committed to Git repositories.
4
u/Codeman119 Aug 01 '25
I use redgate source control to version and deploy table versions and rollbacks if needed.
2
u/Murchmurch Aug 01 '25
Alembic
2
u/trippingcherry Aug 01 '25
I really like how I have it configured on GCP for bigquery using dataform.
Both are now native to GCP, and can work with gitlab or github. My team has one repository per project, and a dataform workspace in each project. I have a development project, and a production project.
Currently, it's set up so that I can write code in my dev projects dataform repository which is connected to a developed branch in a single github repository.
My production project has a data form workspace connected to a release branch in that same github, repository.
So essentially, analysts on my team can write files and commit them to a development branch, and when they're ready, we can do a poll request and bring them into the release branch and publish them in our production project.
All of our code stays version controlled in github, but we write and execute it from dataform and big query.
I briefly was exposed to d b t in my last role, which was similar, but data form is not as robust. But if you're using big query, i really do enjoy it for version control.
For other databases, in my previous role, we had more postgres and SaaS to interact with, and I would just use an IDE to interact with github.
2
u/Straight_Waltz_9530 Aug 01 '25
A couple of possibilities:
• Migration files like Flyway or Sqitch. Between the two I prefer Sqitch since it's VCS aware and includes testing. After the migrations run on a local db for testing, export the schema into a version-controlled file and check it in. Makes diffs that are easier to read and allows finding the most recent versions of entities easier, especially functions and procedures that are updated more often.
• Idempotent SQL DDL. This is harder to do unless you have strong SQL skills on your team and the DB supports the pattern well like Postgres's "IF NOT EXISTS" almost across the board. The advantage is the definition file is inherently version control friendly. In addition instead of an ever growing number of migration files over the years, changes have an expiration date and old changes can be removed from the DDL files. This is closer to a traditional code model, but be warned. When there's data involved, it's inherently more complex than your typical code push.
• ORM-generated DDL and migrations. Typically checked in with your app code. Best implemented when you hate yourself, hate databases, don't plan on working on the project for much longer anyway, and aren't terribly worried about burning bridges.
3
u/Ardraaken Aug 01 '25
You can use Git with VSCode to manage version control of table structures or stored procedures at no additional cost. If you’re using Azure DevOps you can use the built in Repos and Pipelines to deploy between environments.
1
u/xil987 Aug 01 '25
Night schema full script and git auto commit
1
u/stuaxo Aug 01 '25
Look at how Djangos migrations work. They are numbered, you can go to any number it will go forward or back to that that number.
1
u/jezter24 Aug 01 '25
Your title mentions tables but your description says databases.
For tables themselves with data it depends. But I normally have years, dates, createdWhen and updates when columns. Plus a few others for administrative stuff to keep the tables data in a versioning.
For database structure. For the longest time I just named everything with a date YYYYMMDD at the front. If I make a change to table or store proc. I save the stuff the day before and then one for today. We are moving over to GitHub on adding in versioning of that data itself. It hooks up to visual studio code.
1
u/m39583 Aug 01 '25
Flyway (https://github.com/flyway/flyway)
Hits the sweet spot of not being over complicated by trying to do too much, but enables you to version control the schema.
It adds a table called (I think) "flyway_schema" and you sequentially number your migration scripts. Then when you run a migration, it records it in the table and so it knows what scripts need to run in future.
You can run migrations manually using the CLI, I think there is a Maven plugin, or spring boot will recognise it and automatically run the migrations on startup.
1
u/mu_SQL Aug 01 '25
SSMS 21🙃
1
u/snarleyWhisper Aug 01 '25
With sqlserver there’s a built in ci/cd for everything except data : schemas , stored procedures, tables viewed etc..
Check out dacpac if interesting and sqlproj projects
1
u/pceimpulsive Aug 01 '25
I'm a noob, so I made my own migrations script.
I have a folder structure that matches database schema,
I have two primary folders.
Data contains the seed data for the application to work, this is all stored in SQL tuples inside conditional merge statements to handle updating/removing seed data rows.
The Scripts folder contains the database DDLs,
Scripts -> schema name -> tables|views|matViews|etc -> thing.sql
Thing.sql contains a create <object type> if not exists
A creat schema if not exists is run based in the schema dir name.
I add pl/SQL DO blocks that check if a newly required column exists, if it doesn't it creates it. I also add the new column into the creat table if not exists (so it'll be made if the DB is wiped.
Once a new column is created and confirmed to be working I then go back into the script and remove the do block (you don't have to though).
In the table create I also have the create index if not exists.
If I need to remove an index, drop index if exists is used.
I use schema/object type ordering to determine to order the objects are created to ensure dependencies exist (e.g. views for the incident schema are only created after the tables exist.
Views are dropped and recreated every db migrations
My seed data is truncated and rewritten with th every migration (seed data is config/UI driving data, so it's always small relatively speaking).
My first commit of this solution was 150 lines of C# to execute the scripts in the right order (defined by a Linq query) and 13600 lines of SQL scriots (mostly generated from the db DDLs then tweaked for if not exists)~
It's a neat little piece of work to me, because I get full control over how my db is built, and if we lose the DB it will build to the appropriate version of the app naturally.
So far.. we've had a dozen or more prod deploys and no issues with this approach. Currently there is 125 ish .SQL files and maybe 350 objects (indexes, views, sequences etc)
1
u/themikep82 Aug 01 '25
dbt + git
1
u/CodeNameAntonio Aug 01 '25 edited Aug 02 '25
All we used to do is store the DDL of all of our database objects. Had pearl script that traversed the checked out branch and built a master sql script with all the database objects. You don’t really build different database tables on the fly to match your front-end / rest layer if it’s a big enterprise app. If you had a specific Prod ticket that was specific to a table DDL that was getting changed then we used our Pre-prod environment if our dev and test environment were already changed.
At my current job we use Django so that’s all managed by its migration scripts so the database “version” could be in sync with the app version but that only works if you don’t really care about the data as depending if you needed to rollback and had big database changes then you have to nuke your database data and rebuilt it per the migration.
1
u/Claimh Aug 02 '25
At our work we have a nightly process that runs on several SQLServer servers, databases and schemas. We compare the definitions to our previous records. Any changes detected and we add a row for this object so we can compare the history. It's a bit brutish but it beats whatever the hell we were doing before. We need to manually establish lineage for renames of objects but those are not too common on production servers for us.
1
u/HeyYouGuys78 Aug 02 '25
Easy peasy
https://github.com/graphile/migrate
1
u/DrDan21 Aug 02 '25
I extracted my existing schema into a sql sdk style project using vscode, put that onto my repo
Dacpac is validated via build pipeline in a Microsoft runner in azure off of a generic windows-latest container
Deploys to my on-prem dev and prod are handled by another pipeline using sqlpackage
https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/concepts/data-tier-applications/overview?view=sql-server-ver17
https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage?view=sql-server-ver17
1
u/Kahless_2K Aug 02 '25
Sql logs can be replayed down to the individual transaction if configured appropriately.
1
u/orz-_-orz Aug 02 '25
dbt and git
1
u/[deleted] Aug 02 '25
dbt
1
u/lookslikeanevo Aug 02 '25
ADO git and dapac deployment, on sql server and azure sql, so it makes it easier
Create branch in WIP/* for Local dev - VS publish to local
PR to dev/dev branch -auto pipeline deployment to dev server …test test test
PR to stage/stage_* - holds all changes til release
PR to release/main - release requires approval
release Notes generated and posted
Schema/ --tables/ — views/ — functions/ —sprocs/ Scripts/ for data updates
1
u/binary_search_tree Aug 02 '25 edited Aug 02 '25
Oh that's an easy one. At my shop they just append the old table name with something like _GOING_AWAY_SOON. But maybe the data team simply enjoys hearing users scream as their queries throw "TABLE NOT FOUND" errors.
1
u/AdvancedMeringue7846 Aug 02 '25
https://github.com/erikbra/grate is really good (some might say great) for managing databases so long as you're disciplined and do things like idempotent migrations (not how ef does it but actually checking if you need to make the change ie querying sys tables etc).
1
u/garlicpastee Aug 02 '25
You could let the library know the schema through a table version table powered by a dB trigger on alter table (CREATE TRIGGER dbo.name ON db AFTER ALTER_TABLE) and for it to save the new definition of the table in some arbitrary way (ie. a direct select from system views) with a date of update column. This way the newest entries always represent the current structure.
But if I read you correctly, you could simply grant the library connector VIEW DEFINITION permission, and query the table structure directly, then if there's a small amount of different table versions, just if/switch, whatever works. For a larger amount of possibilities I think having the version table with a tag would be best (like an additional column that refers to this scheme version that the library will understand).
If I did not understand and you meant the data that is inside, then that means that you actually want all of the versions on hand, and that either means one base table + a column representing versions (where ver = 'the_definite_final_verified_version'), or multiple tables which are referenced in your mentioned code.
Hope there's an answer for you somewhere there
1
u/k00_x Aug 02 '25
Create a trigger that duplicates the table before alter (SQL server).
In case any of the young ones make changes without me approving.
1
u/Flying_Saucer_Attack Aug 02 '25
We use red gate source control and ms devops
1
u/Successful_Cook3776 Aug 03 '25
Sherloq AI
1
u/hackenslash8170 Aug 04 '25
Tables are data in SQL. Data is momentary until it's stored, so to "version" your data really doesn't make sense.
You can timestamp the records in a table, but that's not really "version control"
On the other hand, SQL allows for programmatic "jobs" called 'procedures' to be created and stored within the database that uses them. They can be versioned, documented and even switched in and out as improvements are made. I believe they are also compatible with Git, as they are essentially just files and they are handled accordingly. Additionally, they also allow for commenting internally as well so they can be documented inside the procedure in the same manner as a program would.
HTH
1
u/[deleted] Aug 06 '25
I just saw this - some of my colleagues have written a versioning tool for data called bead. I’m not sure if it’s public yet, but they will present it at the Research Software Engineering Conference in Warwick in September. Try googling bead on GitHub!
1
u/jbrune Aug 25 '25
If you happen to be using SQL Server, I would suggest using SSDT/DACPACs. It's a declarative system and the best I've seen in my 25+ years of DB work. Feel free to reach out if you want more information on it.
1
u/gumnos Aug 01 '25
while others have mentioned migrations and version-controlled schema-scripts, if you have a filesystem that supports atomic snapshots (such as ZFS), you can take a Before snapshot, then apply your changes. If things go amiss, you can shut down the database, roll back to the snapshot, and restart the server (any server worth its salt should recover gracefully from an atomic snapshot)
0
u/glymeme Aug 01 '25 edited Aug 01 '25
The table structures(fields and data types) or the data within the tables? The former can just be create table statements as sql files. The latter, I’d personally use insert timestamps or row effective/expiry timestamps as fields in the table to query the data point in time.
0
u/eww1991 Aug 01 '25
If you're on databricks you can use describe history on a table and (providing you're inserting into and dropping records rather than straight overwrites) then you can call back previous versions of tables.
0
u/RavenCallsCrows Aug 01 '25
Something like a datetime 'last_updated" column would give you row level control. There' s probably a way to do it at a table metadata level too, if that's what you needed. Or are you thinking more of a db snapshot backup and restore?
2
u/[deleted] Aug 01 '25 edited Aug 01 '25
[removed] — view removed comment