How do you control control over your database structure?
What's the easiest way to track changes to the project database structure?
When I change something in the database (for example, add a new table, add a new field to an existing table, add an index, etc.), I want it to propagate to the rest of the team and ultimately production server, with minimal problems and effort.
At the moment, the decision is rather weak and relies on people who remember that they are doing something that could happen.
Everything else is managed with standard version control software (Perforce in our case).
We're using MySQL, so tools that understand this would be helpful, although I would also be interested to know how other places handle this anyway, regardless of the database engine.
source to share
You can get a tool like Sql Compare from Red-Gate which allows you to point to two databases and this will let you know what's different and create alternative scripts for you.
If you are using .NET (Visual Studio) you can create a database project and check it against the original control.
source to share
This, in my opinion, has been said a lot about alrady. Anyway, I really like Rails' approach to the problem. This is code that has three things:
- Version number
- How changes are applied (updates the version table)
- Reversion method (sets the version to the previous version table)
So, every time you create a changeset, you create this code file that can rollback or update the database schema as it runs.
This, as code, you can commit to any version control system. You commit the first dump and then only the scripts.
The great thing about this approach is that you can easily propagate database changes to clients, whereas with a standard simple dump the schema and the update it uses, generating the update / rollback script is an inconvenience
source to share
At my company, every developer is advised to save all db sctructure changes in script files in the folder containing the module version number. These scripts are stored in the svn repository.
When the application starts, the db update code compares the current db version and the code version, and if the code is newer, it looks in the scripts folder and automatically applies all the db changes.
This way every application instance (on production or developer) always updates the db to its version of the code and works fine.
Of course, some automation could be added - if we find the right tool.
source to share
Poor Men Version Control:
A separate file for each object (table, view, etc.)
When modifying tables, you want to set CREATE TABLE to CREATE TABLE. Source code history is meant to convey history. You cannot make a meaningful difference between CREATE TABLE and ALTER TABLE
Try to make changes to the files, then transfer them to the original control and then commit them to the SQL database. Most tools don't support this well because you don't have to take control of the source code until you check, and you can't test without putting the code in SQL. So in practice you are trying to use SQL Redgate to compare your files against a SQL database. Otherwise, you are adopting a tight policy of deleting everything in the database and replacing it with what made it original control.
Change scripts are usually one-off, but there are applications like wordpress where you need to move the schema from 1.0 to 1.1, 1.1 to 1.5, etc. Each of them must be under source control and modified as such (i.e. how you find errors in a script that move you from 1.0 to 1.1, you create a new version of that script, not yet a script)
source to share