Welcome Guest ( Log In | Register )

Outline · [ Standard ] · Linear+

 How do you version control database schema?

views
     
TSLord Tiki Mick
post May 29 2019, 03:38 PM, updated 5y ago

Regular
******
Senior Member
1,018 posts

Joined: Jul 2012
Currently at my company the way we do it is to have the original schema and patch files. The patch files alter the original schema and every installation requires the patch files to be executed. How does everyone else does it?
Fork
post Jun 15 2019, 10:49 AM

Getting Started
**
Junior Member
150 posts

Joined: Sep 2010
I use migration. In my company, we use migration to create/modify tables with code instead of using SQL script.

Then we use git to manage the version control, i.e. branch 1.0.0 has create users table, 1.0.1 modify users table by adding token column. Then further on we add more tables like roles and permission which is in 1.1.0 and so on.

But of course we are developing web applications, so the migration files we created are also following the version so that the running code is compatible.

This post has been edited by Fork: Jun 15 2019, 11:01 AM
ngaisteve1
post Jun 17 2019, 09:09 AM

Software Engineer
*******
Senior Member
6,779 posts

Joined: Dec 2005
From: Kuala Lumpur


QUOTE(Fork @ Jun 15 2019, 11:49 AM)
I use migration. In my company, we use migration to create/modify tables with code instead of using SQL script.

Then we use git to manage the version control, i.e. branch 1.0.0 has create users table, 1.0.1 modify users table by adding token column. Then further on we add more tables like roles and permission which is in 1.1.0 and so on.

But of course we are developing web applications, so the migration files we created are also following the version so that the running code is compatible.
*
Entity Framework Migration or FluentMigrator?
RNM
post Jun 17 2019, 10:20 AM

Getting Started
**
Junior Member
187 posts

Joined: Jul 2017
QUOTE(Lord Tiki Mick @ May 29 2019, 03:38 PM)
Currently at my company the way we do it is to have the original schema and patch files. The patch files alter the original schema and every installation requires the patch files to be executed. How does everyone else does it?
*
Lot of grey area there. What stack you use? For PHP with framework mostly comes with migrate tool (code instead of sql files). same for java but limited anyway nope for old project. unless pump - export import the sql script manually.

Just to share my experience. I have some project using cvs revision with old plain stack. I created "deployer tool" to patch script (mostly java and perl) and the db (oracle). Be creative whistling.gif

turnmoil
post Jun 17 2019, 10:45 AM

Casual
***
Junior Member
420 posts

Joined: Jan 2003
From: The Miau Miaw Niau Miow Land


i`m using svn
install plugin in eclipse to make it easier...
TSLord Tiki Mick
post Jun 17 2019, 11:56 AM

Regular
******
Senior Member
1,018 posts

Joined: Jul 2012
QUOTE(RNM @ Jun 17 2019, 10:20 AM)
Lot of grey area there. What stack you use? For PHP with framework mostly comes with migrate tool (code instead of sql files). same for java but limited anyway nope for old project. unless pump - export import the sql script manually.

Just to share my experience. I have some project using cvs revision with old plain stack. I created "deployer tool" to patch script (mostly java and perl) and the db (oracle). Be creative  whistling.gif
*
Our stack is Java, but I'm not looking for Java specific tool, and the we're using Oracle database.

Your deployer tool will do schema diff or it's just apply sql patch?

QUOTE(turnmoil @ Jun 17 2019, 10:45 AM)
i`m using svn
install plugin in eclipse to make it easier...
*
Yes, but after you deployed the schema on the database, how do you handle the version control. For example in v1.0 you have the following table:

CODE

Employee
- empid
- name
- depid


Then in v2.0 you updated the table to:

CODE

Employee
- empid
- name
- depid
- started_at


How do you manage the code and at the same time update the schema that's already deployed on your database?
RNM
post Jun 17 2019, 03:58 PM

Getting Started
**
Junior Member
187 posts

Joined: Jul 2017

QUOTE
Our stack is Java, but I'm not looking for Java specific tool, and the we're using Oracle database.

I also using java + oracle

So you are using SVN huh? I used it 4 years ago.

since it just like cvs revision which I mean revision per file (not like git, revision per commit)

So the trick is, create list of deployment list (I called it as deploy.files)

deploy.files will contain
CODE
/sql/update/patch/employee_patch.sql r2.0


and my deployer (I called it as deployer.sh), will do check what is my current version (of employee_patch.sql) on server

if not existing, OK.. continue
if local modified..NOK..abort!
if not modified and not updated..OK.. capture the rev and create rollback.files...continue
rollback.files will contain
CODE
/sql/update/patch/employee_patch.sql r1.0


so my deployer.sh will execute my ugly function (based on file path and extension) together with sqlplus. something like this
CODE

local MAKE_RETURN="-1"
       MAKE_RETURN=$(exit | sqlplus -S "$userName"/"$password" @"${filePath}" >> "${DEPLOYMENT_DIR}/${TASK}/$LOG_DIR/${SQL_LOG}")


and check the error, if error show on my deploy log, rollback!

This post has been edited by RNM: Jun 17 2019, 04:02 PM

 

Change to:
| Lo-Fi Version
0.0121sec    0.24    5 queries    GZIP Disabled
Time is now: 29th March 2024 - 08:28 AM