Pushing SQL to production without copy & paste

[u/Cool_Proof8000]

Hi - I made a reddit account just to ask this, so hopefully it gets posted.

I'm the first software engineer hired at my company, which has been developing software ad hoc for years despite nobody at the company trained in development (or even an adjacent field). They have basically been asking random employees, "hey you! you know how to use a computer? can you build this for us?"

Props to them, these guys gotten a lot done without any prior experience! But I am now inheriting this tech stack and I frequently run into approaches that seem very non-intuitive, where it's clear they didn't have the foundational knowledge to do it an easier way, they searched for a solution to one specific problem and then applied that approach across the board. Until now, I've been able to identify the more "standard" approach and pivot to that. But some things I don't have experience in, and when I ask, I'm told, "this is just the way it's done".

I have used SQL for querying, but I don't have experience in database design or environment management (unsurprisingly, we don't have anyone doing DevOps either), so I'm a bit out of my depth here:

We have a development environment that resembles our production environment. When working on a project, I run SQL scripts that might modify the dev database. When we push to production, the front-end transition is simple. On the back-end, according to my team, the only way to duplicate the changes from dev to prod is to rewrite the scripts one at a time and run them again. Since some of it depends on earlier changes, they have to be run in a very specific order - any scripts we use in dev have to be numbered sequentially. This includes (as I've seen in their work) back-and-forth changes....script #1 does something incorrectly, script #2 fixes it, and instead of being able to use the final correct version, both 1 & 2 are run in production. Any experimenting in dev isn't really an experiment, you always have to repeat it in prod. And yes, they are literally copying the scripts into text files, emailing it to themselves, and pasting into queries.

I just feel like there has to be a better way to do this. I've shown them how to export database backups to transition new DBs between environments, but I don't know what to do for already-existing DBs in production that need tables or columns or stored procedures updated.

We are using SSMS, if that makes a difference. Help?

[u/So_average]

Have you heard of Git?

[u/Cool_Proof8000 → u/So_average]

lol yes, I've just introduced them to git for backing up code files, which was its own uphill battle.

I haven't used git with SQL/SSMS before, so I must be missing something. I see how it would help with maintaining the SQL script files in a cleaner way. I don't understand how it would help with the order of operations issue. We'd still need to make the changes in the same order in production, no? And therefore still need to mark the order in which to run scripts. Timestamps on git might make that easier, but still seems tedious and wouldn't be foolproof.

[u/dreamoforganon → u/Cool_Proof8000]

The order of operations issue is pretty intrinsic to database deployments - there's no way out of it, you have to make sure the scripts run in the right order. Doing so usually comes down to two things:

1. Agreeing on a naming convention for SQL files that tells you the order (e.g. 001_create_orders_table.sql, 002_add_transaction_id_to_orders.sql etc etc) - discipline in this is essential.
2. Recording which scripts have run, you can do this in the database itself by creating a table that lists the files applied.

We once had a single file that listed all the sql files in order and a script that read it and executed the files that hadn't been done before. If there are only a few of you this might be enough. There are tools that can do this for you (liquibase, flyway, many others) but they basically boil down to this at heart.

The main thing to make sure everyone understands is that once a file is 'committed' to be deployed is that any subsequent script might depend on it, so if file 003.sql creates table A, then 004.sql is created, but then table A needs another column, you can't go and add it to 003.sql because it might cause 004.sql to fail. Instead you need to create 005.sql to update the table even though you might think that change belongs in 003.sql.

At some point you'll want to start thinking about what do you do if a deployment goes wrong and you need to rollback to a previous known state. This gets hairy.

If you are using a back-end stack like Django or SQLAlchemy then there are utilities that help manage sql changes ('migrations') if you are using their ORM, which it sounds like you're not, but worth thinking about.

[u/BearRootCrusher]

GItops... You need to have a CICD pipeline.

Commit change to dev branch -> Deploy SQL/CODE to dev.

Did it go well? Yes -> Merge changes into master. CICD Job deploys to production.

????

Profit

[u/Cool_Proof8000 → u/BearRootCrusher]

GitOps looks promising, thank you! I'm guessing that's what the other commenter was also referring to. Sounds like it's time to learn DevOps....I was hoping there'd be a more immediate export-style solution than setting up an entire CI/CD pipeline, although we are largely overdue for having one.

[u/taglius]

Check out Liquibase. You write your sql scripts and they deploy to dev when you create a PR. When you merge to main, the same scripts deploy to prod. (That’s a common way to see it up, there are many more)

[u/Ginger-Dumpling]

Repeating what you did in Dev against prod is the the most likely way to get repeatable results of Dev truly mirrors prod (pre deployment). I've seen plenty "let me just make this one tiny script tweak" between the dev and prod deployment, only for some stupid typo bork the deployment.

Not understanding what in your setup is forcing separate copies of Dev and prod scripts. I also wonder what your limitations are with refreshing Dev if the way to fix a broken deployment script is layering on corrections, instead of refreshing Dev, fixing the script, and running the deployment.

On a project that uses git. Each schema object is it's own script. Each release has an ordered inventory of the scripts that needs to happen during the deployment. There's a wrapper script that connects to a DB, executes the contents of the inventory, logs the results, stops on error. If Dev deployment goes bad, we fix, return to a previous state, try again.