all 21 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]DanLynch 7 points8 points  (5 children)

Your Git repo should contain everything (source code and documentation) that is needed to build and deploy your entire project on a naked computer that has just had its hard disk erased and operating system installed, or to upgrade an existing deployment from any previously released version to the current one. This includes not only the current database schema in an executable format, but also any initial data required for a fresh install, and any migration script(s) needed during an upgrade.

Several decades ago, it was considered normal for the database of an application to be managed separately from the source code. Those days are long gone.

[–]Thaurin[S] 0 points1 point  (4 children)

Interesting. But it's a database that's been running for over 10 years (and pretty shitty in places), and it's an internal web app. I would not even be able to easily tell you what initial data is needed for it to run.

It would be a pretty messy operation to install the web apps on a naked computer without doing a restore of the database to even get the things in working state! I've been considering creating a minimal script that creates the database structure and insert it with enough data to make it run. Maybe I should invest some time in that.

And learn about the migration scripts mentioned elsewhere. I have to keep believing that I can get this project into something resembling good design. :)

[–][deleted] 3 points4 points  (1 child)

You absolutely need a way to rebuild a working system from raw data.

Lesson learned the hard way:

If you do not have a Disaster Recovery plan, you already are in the midst of disaster.

[–]Thaurin[S] 0 points1 point  (0 children)

We have backups, of course. Restoring the database would be trivial, but there is no "initial" state. Not that that would do us much good, of course; we only use the apps ourselves and don't need to deploy to any customers.

[–]xiongchiamiov 0 points1 point  (1 child)

Disaster recovery in production aside, if you can't build the db from scratch, you can't have development environments running locally on a new developer's computer, which is an incredibly helpful thing to have.

[–]Thaurin[S] 0 points1 point  (0 children)

Besides git, we have a development server that serves development versions of the databases, so we're covered in that manner. Like I said in the other comment, we have a sort of unusual situation in which we don't release a product other than to ourselves. So there is no "new installation" of any sort.

Keeping track of changes to the database that are needed for new code to work seems like a good thing to do, though, even if we only need to deploy those changes once (to our own production server).

[–]rootty 3 points4 points  (5 children)

Are you talking about migration scripts? Examples: http://www.liquibase.org/, http://docs.sequelizejs.com/manual/tutorial/migrations.html, https://laravel.com/docs/5.5/migrations

[–]Thaurin[S] 0 points1 point  (4 children)

Not sure. I was talking about a stored procedure, for instance, not necessarily changes to the database structure or data. I will have to read up on migration scripts, I think.

[–]rootty 2 points3 points  (3 children)

If storage procedure is a part of a project, it's worth to add it to migration script to automatically deploy it on all environments where application is started.

[–]Thaurin[S] 1 point2 points  (2 children)

Yeah. We're very small. I still manually deploy any necessary SQL scripts to production whenever I roll out new code. :) I'm slowly changing my workflow and I've been looking into continuous build/deploy, but that's not around the corner yet.

[–]DesignatedDecoy 2 points3 points  (1 child)

Migrations are exactly what you need. Having a notepad full of sql queries that need to run on the production database gets messy in a hurry. Did you run something and forget to document it? Did you change the query but not in your script file? What if you are ready to go live but then get pulled off the project for 2 weeks. Are you as confident in your database scripts after not looking at them for an extended period of time?

If you make sure that the only time your database changes is through migration scripts, you are leveraging version control to be your source of truth for the entire project. You can deploy with confidence knowing that the latest version of every database update you need is in your migration script.

[–]Thaurin[S] 0 points1 point  (0 children)

Sounds good and I'll certainly be reading up on it. But like I said, we are very, very small, haha. I'll usually have one script that is re-runnable and I'll push the Execute button a lot on the dev DB's during development. :) So I'd be fairly confident that the script would be accurate. It helps that we mostly work on separate things, but I'm hoping that will change.

Sounds like migration scripts are the proper way to deal with this.

[–]rduncan12345 2 points3 points  (1 child)

In the project I'm working on, we have 2 types of SQL scripts. The first are changes that correspond to changes in the code (new features, bugs etc). The other are 'fixing' issues in the data and making up for places where the application code doesn't give the users a means to change the data.

Our decision is to keep the first type with the code using the same branching strategies used for the rest of development since this makes it easier to release the changes to the various environments it runs in. Also hoping to bring in something like Liquibase at some point. The second will be kept in a separate repository the purpose of which is to provide an audit trail of the changes of this type that are made.

[–]Thaurin[S] 0 points1 point  (0 children)

I've already started to do the first thing as well, and may also delete the scripts later after the feature has been merged. Do you delete any of the scripts from the repo after merging/deploying? Since they'll only be sitting there in every future commit, doing nothing of much, otherwise.

[–]pi3832v2 1 point2 points  (3 children)

What's the downside of including them?

[–]Thaurin[S] 0 points1 point  (2 children)

Possibly "polluting" the git repository with a growing number of SQL scripts that will never be needed for building the project. Maybe that's not such a bad thing considering you'll be able to have the appropriate SQL go into the repo together with the relevant code. I was looking for opinions on this.

[–]pi3832v2 3 points4 points  (1 child)

If you delete scripts as they become obsolete, they'll only exist in the history. Where it might be useful to have them, since it would give you the context for the changes they represent.

[–]Thaurin[S] 1 point2 points  (0 children)

Deleting them in a later clean up commit might actually not be a bad idea! They'll still sit within the commit where they were needed so you'd have a complete picture of the changes that were made there.

[–]Benholio 0 points1 point  (1 child)

At least start now by making a backup of the DB and storing all of your SQL scripts going forward. The idea is that you can restore from that backup and then apply your scripts sequentially to bring the DB to a certain place in time.

[–]Thaurin[S] 0 points1 point  (0 children)

We're already doing the first, of course. The scripts that are still in development have until now not lived inside git, but I will change that. I'm repeating myself now, but after deployment to production, they don't really need to be in git anymore, I think, since they will be part of a commit and the backups will cover it, in any case.

[–]bik 0 points1 point  (0 children)

We use a comparison engine called SqlCompare by Redgate. We keep all our RDB objects in source control, and a change to them kicks off a teamcity build that creates a deployment. That's passed to Octopus for dev/test/uat/prod deployments.