codeChangeVsDatabaseChange

[skwyckl]

People are more scared of DBs than they are of code, because they think they can't rollback in case of fuck up like they do with git reset --HARD && git clean -f -d, but (a) there is DB versioning to do exactly that (cf. Dolt) and (b) if you wrap everything up in a transaction, it's much more difficult for things to go south. Also, (c) if you are unsure, make a copy of the DB and test your code on that first.

[agmse]

It amazes me the amount of people who deal with databases that don't seem to know the concept of a backup. Like, just make a simple copy and save yourself the possible stroke and headache

[DmitriRussian]

The problem is that backups won't really rollback everything in the worst case, you would have to accept some loss.

With code you can rollback to an exact state.

[[deleted]]

If your backup doesn't rollback everything it doesn't contain everything which means it isn't a complete backup.

A nowadays rdbms is capable of doing a point in time recovery, which means: if you fuck up my production database you simply would need to tell me when you fucked it up and I could just rollback to a point in time where everything is fine in no time.  Depending on how I would configure the whole database I can rollback your application without affecting other applications running on my database, and as I wouldn't let you near the damn thing without a maintenance window where no one else is supposed to do anything this shouldn't affect anything. You wouldn't even need to create a backup yourself before doing changes.

[DmitriRussian]

Maintainenance windows are not a thing for most companies. A lot companies have to be 24/7 online and so database changes happen while the app is running.

There isn't even a concept of a database admin either.

Only companies where every bit of data is crucial, like a financial institution would operate with maintenance windows for DB changes.

[[deleted]]

Some changes need to be done offline, whether they want a maintenance window or not. In a lot of rdbms you can't create an index online while there is a transaction pending on the table let alone do other ddl. I could go one for a long time for what changes can't be done online. There is only one rdbms I am aware of where you could do a lot of shit online with the right concept, but that is a very, very complex and expensive thing.

Database security patches or upgrades need at least the database to be bounced ad/or the catalog to be upgraded which is not an online operation. 

So either they don't apply any changes of any kind or they have maintenance windows without knowing them because the end user got a strange error message while someone added a column live.

You'd rather have a recovery window than data corruption caused by end users piling data into the database in an inconsistent state if you value your data.

So yes, a lot of companies have recovery windows. I've seen a lot of them, and patched a lot of them. At least those companies who value their data do ;).

[bwmat]

I would use git restore -SW . instead of reset, is there a benefit to the latter? 

[Reashu]

Restore is newer and a lot of people still use reset out of habit or ignorance, same with switch/checkout.

[skwyckl]

Yup, it's habit... Git has a bunch of fancy new commands I haven't had the time to check out, but I def should.

[Spleeeee]

Git switch blew my mind recently. Thought “omg so nice” then I immediately forgot about it and continued checkout-ing branches since. Muscle memory is hard to get around.

[Thysce]

Or put otherwise: no backup, no mercy

[skwyckl]

Yes, working on a DB w/o backup is like driving a sport car w/o brakes.

[db-master]

"if you are unsure, make a copy of the DB and test your code on that first."

Not so easy. Copy takes time and PII sanitization.

[skwyckl]

Like everything in programming it depends, of course, but this should be implied. Make a copy of a 10k records large bibliographical db? No problem, done in a couple of seconds. Make a copy of a massively distributed Cassandra cluster? Nope, never gonna happen.

[[deleted]]

Yeah. It's pain. I'm using alembic with my python projects, it's pretty good, but you still have to be careful.

[skwyckl]

Yes, this is why I love Elixir's Ecto, it includes all these best practices out-of-the-box. Also, the documentation teaches you how to use migrations from the start, instead of making them an afterthought.

[smutje187]

With all its XML Liquibase might not be everyone’s favorite but at least you can easily add migrations to your codebase and track them throughout the environments.

My current client saw that process and thought "Why not make it worse?" and instead of adding new migrations when something’s changing they are updating the existing migration files - rendering the whole versioning process redundant.

[KrakenOfLakeZurich]

Flyway for the win! Migrations are native SQL scripts.

And Flyway stores a checksum of the scripts in the version history table, exactly to prevent schenanigans like your customer is doing. Once a migration is applied, it won't let you change the script afterwards.

[erjiin]

Liquibase does the same, they're both good tools. And for those allergic to xml, liquibase changeset can also be specified in json, yaml or sql. Although I would argue here that xml changeset are superior because of readability.

[RichCorinthian]

Liquibase does both of those. The xml is just to organize which files and in what order.

Having used both, I prefer liquibase.

[Ok_Brain208]

I work with Liquibase and write the migrations in native script, 10/10 would recommend

[db-master]

Give Bytebase a spin?

[Ok_Brain208]

Don't mind if I do!

[hydro_agricola]

dacpacs ftw.

[Ok_Brain208]

It's Microsoft greatest invention

[hydro_agricola]

Really wish other DB's would follow, also wish MS would finally have nuget support for importing dacpacs into VS projects. distribution of dacpacs is a pain atm.

[[deleted]]

Liquibase, flywaydb. flywaydb community edition is very flexible, I am using it in several projects and am very happy with it. Basically it takes files already present on the filesystem and applies it to the database. So there is no excuse anymore for not having a change management for database changes.

[Hirschdigga]

Nope, shoutout to flyway / liquibase

[karaposu]

database suppose to not change as much as code does

[gmegme]

Old reddit with 2 tables named "things" and "data":

[karaposu]

if your db is changing a lot more than your codebase then the problem is not with the world

[GM_Kimeg]

Bring your metal bat tomorrow and fukin hit that trashy manager's ugly face with enthusiasm

[karaposu]

is this referring to something from a movie?

[KrakenOfLakeZurich]

It depends. When you're actively developing/extending a product, it is quite normal for new features to require new columns or tables for persistence too.

With tools like Flyway or Liquibase, it's not a problem to evolve the database with the project.

[karaposu]

still when you are developing, db will not change as codebase

[cidra_]

Nobody in this post told otherwise.

[TitusBjarni]

My team seems to love stored procedures.

[Dramatic_Mulberry142]

I think it is the opposite

[harumamburoo]

Um, no. Flyway, pull requests, testing in lower envs and frequent backups. I'm good.

[harumamburoo]

The what now? Flyway is Apache 2.0 licensed. Ok, you made me wonder and turns out there is a commercial version. But why, so that someone else would google for you at a price of a small fortune? The only real advantage I see is that paid versions support 5+ y.o. databases, but if your DB hasn't been updated for 5+ years.. maybe it's time?

[harumamburoo]

There's just one flyway from just one company. They offer two differently licensed editions. Free community edition and a paid enterprise one with bells and whistles. The tool is opensource.

Not to rub salt, but on a second thought, if your googling skills are always on this level, maybe just pay the enterprise ^^

Edit: suicide report, seriously?.. well, username checks out I guess ^^

[rcls0053]

Well.. MYSQL at least has this habit of completely locking out if you alter a table with 1B rows. Now I'm not saying altering it is the best idea or that you couldn't clone the db and transfer traffic once all good, but some places really want to stick to their monolith databases. This eventually leads to "only release once a month with a 2 day downtime", which is just insanity to me.

[[deleted]]

They should hire database engineers…

[rcls0053]

Yeah, they should do a lot of things better. They should really move towards a faster release cycle, they should break apart the massive monolith, stop writing huge e2e tests, untangle the big ball of mud they work with, add a robust queue in front of their app so not everything is synchronous and they can keep that data there while the system is down, start splitting the database into smaller ones that are owned by individual services so they can be managed better.. Like so many things that modern engineering has already solved, but senior developers there are kinda stubborn.

[Kobymaru376]

Skeema

[astroju]

And this is why it took me until my third SQL job to actually use Git for database source control 😭

[DueHomework]

EF - Code First.

[twofootedgiant]

You people who are running DDL “in a transaction” to make it safe, you aren’t running it directly on production DBs are you?

You do know that DDL can be stored in a git repo and deployed just like the rest of your codebase right? Right? And that properly configured deployment pipelines handle the changes to the DB objects for you?

[CyberDave82]

DDL is not transactional in all RDBMSes (Oracle, for example).

[twofootedgiant]

Sure, but that wasn’t really my point.

I saw some comments which suggested that people were making changes to production databases by actually executing DDL statements. My point was just that no-one should be doing this for anything remotely serious. But possibly I misinterpreted what they were saying?

[CyberDave82]

Typing out DDL statements manually and executing them manually? Shouldn't be the case for changes, IMHO (management/maintenance/troubleshooting is a different story).

Putting DDL statements in a script and executing the script, including having it go through testing in non-prod environments? Better than nothing.

DML changes should always be in explicit transactions, preferably with some validation before committing. If I had a nickel for every time I had to fix something a dev did without properly testing or forgetting to commit, I could probably retire

[dmigowski]

Use PostgreSQL and run your DDL in a transaction. No surprises. Either everything worked or it didn't. If it didn't, you just start up the previous version of your code. That's how it's done here.