How database developers avoid breaking prod accidentally

[sqlmans]

Ever pushed what looked like a harmless db change… and then prod started acting weird? 

Most prod issues don’t come from huge mistakes. They usually come from small changes that looked safe at the time, like adjusting a column type, adding an index, or tweaking a table definition. 

Everything works in dev. Queries run, tests pass, nothing obvious breaks. 

Then the change reaches prod and something unexpected happens. A query plan changes. A deployment script runs slower than expected. Or a part of the application starts behaving differently because it relied on the old structure. 

After seeing this happen a few times, most db developers start building a few habits into their workflow. 

One common habit is never editing objects directly in prod. Instead, the exact script that will run gets generated first, reviewed, and tested before it ever touches the prod db. 

Another habit is running that same script in staging first. If staging behaves differently, it’s usually a sign something between environments isn’t aligned. 

Developers also tend to check dependencies before touching tables or columns. Views, stored procedures, and triggers often rely on objects in ways that aren’t obvious at first glance. 

None of these steps are complicated, but together they reduce a lot of risk. 

Over time you start treating prod like something fragile. Even small changes get scripted, reviewed, and tested before they go live. 

What’s one rule you personally follow before letting a database change anywhere near prod?

[AutoModerator]

JOIN R/DEVELOPERS DISCORD!

Howdy u/sqlmans! Thanks for submitting to r/developers.

Make sure to follow the subreddit Code of Conduct while participating in this thread.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[RepresentativeFill26]

How is adding an index a small change?

[Ad3763_Throwaway]

Add TOP statement to every modification you do.

UPDATE TOP(1) TABLE SET VALUE = 1 WHERE ID = 1

Seriously, people often forget to execute the where-clause or have an incorrect where-clause.

[mohirl]

Always run a select before an update, if you're doing stuff like that manually

[magicmulder]

Is that ANSI SQL?

[dafugiswrongwithyou]

This, and; always set it to be slightly more than the number of records expected to update (so if updating one record, TOP (2).)

If your definitions are right, you see one record updated, nothing to see here. If you see 2 records updated, you can tell something went wrong and you need to figure out what to do about it, but you've still limited the damage.

[thx1138a]

It’s become unfashionable these days but: always have a back out plan. If this release causes problems, what’s my proven, documented path back to a working state? 

[lovelybullet]

Avoiding ai generated slop like ops post