
Ask HN: How do you do database version control, deployments, etc? - rsweeney21
I&#x27;m working on a side project and I want to deploy the application to production as soon as possible, but I&#x27;m dreading having to deal with deploying schema changes, testing database deployments and everything else. In the past I&#x27;ve rolled my own primitive deployment scripts and database versioning scheme, but I&#x27;m wondering if there is a more robust solution out there. Is there a solid CD or CI solution out there for databases?
======
aregsarkissian
There are various database agnostic schema migration tools that can help.
There are rails, django and laravel migrations or alembic if you use
python/sqlalchemy. There also pure sql based migration systems such as flyway.
There is a great pluralsight course on proper way to do migrations using
flyway. Most of it applies to migrations with any migration tool. You might be
able to check it out with the free trial. But basically you want to do all
your add columns and add tables before deploying a new version of code. Just
to make sure the old version still runs. Then after deploying the new version
of code. at some point you can delete the unused columns and tables. I also
like to have one database object per migration file. That way the cost of each
migration is smaller and if the migration fails you don't have to fix a
partial migration since each migration operation is atomic.

~~~
w4tson
I really like flyway. It’s lightweight and the best thing I can say about it
is that I rarely have to bother with it. It just works.

I too find it useful to keep the migrations small and succinct. For Oracle do
migrations where the DDL cannot be rolledback I choose to separate these from
straight data manipulation.

A downside to the project that I see is that although it’s quite mature the
author is very very slow to engage with open source modifications.

I’d still make it my first choice because of its simplicity.

------
zer00eyz
I have never found a tool better than "by hand"

Simply put the DB isn't code, and a DB changes are in another category when
looking at the risks involved. Deploying to your own MYSQL cluster vs AWS vs
Postgres vs oracle are all very different mindsets, tools and requirements.

Can you get away with a tool in the short term, probably - and it would be one
specific to the language you work in. Pick something your side project is
written in, or something your interested in learning if your willing to go
higher risk.

~~~
place1
How does this scale once you have multiple developers/teams and an automated
CI/CD process in place? Could you elaborate on how to manage this manual step
(potentially for multiple environments) when an automated deployment depends
on them being done?

~~~
acutesoftware
If the database changes are substantial enough to break the applications, I
would have a new schema setup to have enough time to test carefully and then
when ready, point the application to the new schema.

For small changes, we use scripts 'INSTALL_[SCHEMA].sql' and
'ROLLBACK_[SCHEMA].sql' which do everything needed including backups /
restores of any tables needed.

This is tested in the DEV/QA environments as needed then run in Prod right
before deploying the application changes.

We haven't had high availability requirements and can get away with a
scheduled 3 minute outage but this works for us.

The beauty is that if something goes wrong, the rollback script gets us out of
trouble.

~~~
no-s
_The beauty is that if something goes wrong, the rollback script gets us out
of trouble._

ah, would it be that were true. Suppose you need to roll back until after the
next install? I defy you to set up a reliable test plan that will handle the
usual series of casual database tweaking popular with modern devs. The time
spent developing and assuring the quality of the rollback will be mostly
wasted energy when you're faced with rolling back a change which the next N
changes are dependent on.

(more) seriously any sane scheme like yours seems to run into trouble when
people want to add columns to some base table. Thusly I think "Alter Table"
should be a banned operation for devs in any live system. But it's great job
security for a DBA (speaking as a DBA, btw).

------
shoo
Flywaydb does schema versioning and migrations and is pretty simple. Each
migration is a single SQL script. It doesn't support rollbacks as the authors
claim from experience that rollback scripts tend to be needed rarely, and if
needed they tend not to work.

Flywaydb doesn't cover db testing.

------
bryan11
I built a system for MySQL a while ago so database patches (data fix, schema
change, etc) are applied with code deploys. All database patches are written
to be repeatable, which means they contain logic that checks if they've been
run already. They also have release and sequence ID so they're tracked and may
be run out of sequence if needed. Once everyone got accustomed to the system,
it's worked well for several years now.

------
slipwalker
Flyway, Liquibase or Nextep ( [https://github.com/christophefondacci/nextep-
designer/wiki](https://github.com/christophefondacci/nextep-designer/wiki) )
depending on which client, which project...

------
imhoguy
Ruby on Rails migrations do best for me for over decade. If you don't work
with RoR project then you can also try [https://github.com/thuss/standalone-
migrations](https://github.com/thuss/standalone-migrations) which is "Rails
migrations in non-Rails (and non Ruby) project".

------
daleholborow
Check out dbup (in c# land) for a strategy and tools , and check the
migrations into your repo along with your side.

------
twunde
For a side-project you're probably going to want to use the migrations logic
of whatever ORM/language you're using, but alternatively there are db devops
tools like dbmaestro or datical, although those are typically targeted at
enterprise companies

------
nathanken
You could try Liquibase.

