Thread: what database schema version management system to use?

Hi all,

I am searching for a proper database schema version management system.

My criteria are the following:
0) Open-source, supports postgresql
1) Uses psql to execute changesets (to have no problems with COPY,
transaction management or sophisticated DDL commands, and to benefit
from scripting)
2) Support repeatable migrations (SQL files that get applied every time
they are changed, it is useful for functions or views tracking).

Reasonable?

But unfortunately it looks like each of Liquibase, Flyway, SqlHawk,
MigrateDB, Schema-evolution-manager, Depesz's Versioning, Alembic and
Sqitch does not satisfy some of those, right?

What DB VCS do you use and how does it related with the criteria listed
above?
Do you have any idea what other systems to try?

Regards,
   Alexey

On 06/04/2016 13:55, Alexey Bashtanov wrote:
> Hi all,
>
> I am searching for a proper database schema version management system.
>
> My criteria are the following:
> 0) Open-source, supports postgresql
> 1) Uses psql to execute changesets (to have no problems with COPY, transaction management or sophisticated DDL
commands,and to benefit from scripting) 
> 2) Support repeatable migrations (SQL files that get applied every time they are changed, it is useful for functions
orviews tracking). 
>
> Reasonable?
>
> But unfortunately it looks like each of Liquibase, Flyway, SqlHawk, MigrateDB, Schema-evolution-manager, Depesz's
Versioning,Alembic and Sqitch does not satisfy some of those, right? 
>
> What DB VCS do you use and how does it related with the criteria listed above?
> Do you have any idea what other systems to try?

Maybe Git then interface with smth like teamcity to apply your changes. Honestly you are asking too much. The classic
problemis to find a tool that would translate DDL diffs into ALTER commands, if  
you want to store pure DDL CREATE statements. I have watched many presentations of people on the same boat as you, and
theyall implemented their own solutions. Good luck with your solution and keep  
us posted, many ppl might benefit from this.

>
> Regards,
>   Alexey
>
>


--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

On 04/06/2016 03:55 AM, Alexey Bashtanov wrote:
> Hi all,
>
> I am searching for a proper database schema version management system.
>
> My criteria are the following:
> 0) Open-source, supports postgresql
> 1) Uses psql to execute changesets (to have no problems with COPY,
> transaction management or sophisticated DDL commands, and to benefit
> from scripting)
> 2) Support repeatable migrations (SQL files that get applied every time
> they are changed, it is useful for functions or views tracking).
>
> Reasonable?
>
> But unfortunately it looks like each of Liquibase, Flyway, SqlHawk,
> MigrateDB, Schema-evolution-manager, Depesz's Versioning, Alembic and
> Sqitch does not satisfy some of those, right?

I use Sqitch and supports the above afaict. The only one I would have a
question about is 2) as I am not quite sure what you are getting at.

>
> What DB VCS do you use and how does it related with the criteria listed
> above?
> Do you have any idea what other systems to try?
>
> Regards,
>    Alexey
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Alexey Bashtanov schrieb am 06.04.2016 um 12:55:
> I am searching for a proper database schema version management system.
>
> My criteria are the following:
> 0) Open-source, supports postgresql
> 1) Uses psql to execute changesets (to have no problems with COPY, transaction management or sophisticated DDL
commands,and to benefit from scripting) 
> 2) Support repeatable migrations (SQL files that get applied every time they are changed, it is useful for functions
orviews tracking). 
>
> Reasonable?
>
> But unfortunately it looks like each of Liquibase, Flyway, SqlHawk, MigrateDB, Schema-evolution-manager,
> Depesz's Versioning, Alembic and Sqitch does not satisfy some of those, right?
>
> What DB VCS do you use and how does it related with the criteria listed above?
> Do you have any idea what other systems to try?

We are quite happy with Liquibase (using the XML format exclusively).

COPY FROM stdin isn't supported that is true, but so far we did not have the need for hat.
We don't do bulk imports in our schema migrations.

DDL commands can easily be run using the <sql> tag, but for e.g. stored functions or view definitions we usually use
<sqlFile>runOnChange="true" (that's your #2) and we only use a single statement per file so that we can also use
splitStatements=falseto avoid any problems with embedded semicolons (which in turn means that the included SQL file can
easilybe tested with psql or any other SQL client) 

We written our own implementation of the Liquibase "Database" class (actually only a slightly modified version of the
built-inone) that gets the quoting right for Liquibase & Postgres. Liquibase automatically quotes identifiers if they
areno completely written in lowercase which is a bit annoying.  

Thomas

On Wed, Apr 6, 2016 at 3:55 AM, Alexey Bashtanov <bashtanov@imap.cc> wrote:
> Hi all,
>
> I am searching for a proper database schema version management system.
>
> My criteria are the following:
> 0) Open-source, supports postgresql
> 1) Uses psql to execute changesets (to have no problems with COPY,
> transaction management or sophisticated DDL commands, and to benefit from
> scripting)
> 2) Support repeatable migrations (SQL files that get applied every time they
> are changed, it is useful for functions or views tracking).

If you are using COPY, then you must be loading data (not just
changing schemas).

But if you are loading data, how can you have it be repeatable?  How
would it know which data needs to be (or not be) loaded again?  Do you
want it do a three way comparison (The current patch it is trying to
apply, the most recent patch that had been applied, and the live
database) and resolve conflicts?

Cheers,

Jeff

On Wed, 6 Apr 2016 11:55:40 +0100
Alexey Bashtanov <bashtanov@imap.cc> wrote:

> Hi all,
>
> I am searching for a proper database schema version management system.
>
> My criteria are the following:
> 0) Open-source, supports postgresql
> 1) Uses psql to execute changesets (to have no problems with COPY,
> transaction management or sophisticated DDL commands, and to benefit
> from scripting)
> 2) Support repeatable migrations (SQL files that get applied every time
> they are changed, it is useful for functions or views tracking).
>
> Reasonable?
>
> But unfortunately it looks like each of Liquibase, Flyway, SqlHawk,
> MigrateDB, Schema-evolution-manager, Depesz's Versioning, Alembic and
> Sqitch does not satisfy some of those, right?
>
> What DB VCS do you use and how does it related with the criteria listed
> above?
> Do you have any idea what other systems to try?

http://dbsteward.org/

--
Bill Moran

On 4/6/2016 3:55 AM, Alexey Bashtanov wrote:
> I am searching for a proper database schema version management system.


At my $job we're old school.     our schemas are versioned. there's a
settings table with (setting TEXT, value TEXT) fields, a row in that is
('version', '1.0')  or whatever.

each new release of the schema is released as a .SQL file which builds
the full schema from scratch, and a .SQL file which updates the previous
version to the new version.     the full build and update .sql files are
kept in our source code control along with the rest of our software.
we're quite careful about how we modify our schema so it can be done
online, update the schema on the live database, then update and restart
the application/middleware.


--
john r pierce, recycling bits in santa cruz

John R Pierce wrote:
> On 4/6/2016 3:55 AM, Alexey Bashtanov wrote:
>> I am searching for a proper database schema version management system.
>
>
> At my $job we're old school.     our schemas are versioned. there's a
> settings table with (setting TEXT, value TEXT) fields, a row in that is
> ('version', '1.0')  or whatever.
>
> each new release of the schema is released as a .SQL file which builds
> the full schema from scratch, and a .SQL file which updates the previous
> version to the new version.     the full build and update .sql files are
> kept in our source code control along with the rest of our software.
> we're quite careful about how we modify our schema so it can be done
> online, update the schema on the live database, then update and restart
> the application/middleware.
>


I would be interested in knowing specifically how the ".SQL file which
updates the previous version to the new version" is generated. Is there
a tool that does that based on the difference between new and old? Or is
that update script coded by hand?

On Thu, Apr 07, 2016 at 06:21:10AM -0400, Berend Tober wrote:

> I would be interested in knowing specifically how the ".SQL file which
> updates the previous version to the new version" is generated. Is there a
> tool that does that based on the difference between new and old? Or is that
> update script coded by hand?

We (GNUmed) create it manually.

In fact, those scripts are a by-product of sane database
layout development. Starting from what is, developers write
scripts which modify the layout to what shall be.

It doesn't seem to be good practice to do
point-and-click-based "development" of databases.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On 4/7/2016 3:21 AM, Berend Tober wrote:
> John R Pierce wrote:
>> On 4/6/2016 3:55 AM, Alexey Bashtanov wrote:
>>> I am searching for a proper database schema version management system.
>>
>>
>> At my $job we're old school.     our schemas are versioned. there's a
>> settings table with (setting TEXT, value TEXT) fields, a row in that is
>> ('version', '1.0')  or whatever.
>>
>> each new release of the schema is released as a .SQL file which builds
>> the full schema from scratch, and a .SQL file which updates the previous
>> version to the new version.     the full build and update .sql files are
>> kept in our source code control along with the rest of our software.
>> we're quite careful about how we modify our schema so it can be done
>> online, update the schema on the live database, then update and restart
>> the application/middleware.
>>
>
>
> I would be interested in knowing specifically how the ".SQL file which
> updates the previous version to the new version" is generated. Is
> there a tool that does that based on the difference between new and
> old? Or is that update script coded by hand?


by hand, with a text editor of choice.



--
john r pierce, recycling bits in santa cruz

On Fri, Apr 08, 2016 at 09:09:22AM -0500, Merlin Moncure wrote:

> I rolled my own in bash.  It wasn't that difficult.  The basic tactic is to:
>
> *) separate .sql that can be re-applied (views, functions, scratch tables,
> etc)  from .sql that can't be re-applied (create table, index, deployment
> data changes etc).  I call the former 'soft' and latter 'hard' changes.
> *) keep each database tracked in its own folder in the tree and put all the
> soft stuff there.  I keep all the hard stuff in a folder, 'schema'.  I also
> ha ve a special library folder which tracks all databases
> *) redeploy 'soft' changes every release.  The bash script deploys files in
> mtime order after setting mtime to git commit time since git doesn't track
> mtime
> *) keep a tracking table in each database tracking deployed scripts

GNUmed does pretty much the same thing except we call it
"static" vs "dyamic" changes.

Also, with modern PostgreSQL versions (UPSERT, ON CONFLICT,
IF EXISTS) many items among "index, deployment data changes"
can be turned into soft (dynamic) changes.

We've never had a single bit of patient data get lost among
GNUmed database versions up to the current v21 (but of course
we are paranoid and check md5 sums of the schema before/after
upgrades and run automated data conversion sanity checks
after an upgrade).

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346