Please suggest the best way to organize development databases.

Question

I am currently working on a web project in which we all connect to the same development database.

And, like other centralized systems, over time this database is the only point of failure.

If one of the developers carelessly dumps some dirty data into the database, all other developers suffer from it.

Therefore, I think that maybe we should do something, say, each of us makes a copy of the original database, and we configured our web application to connect to the local database.

In my case, the main members of the team are five developers, one tester (mainly testing using the "black box"). The development process continues as follows: each developer is responsible for one auxiliary function and works on his own branch, and then we combine his branch on the body on which the tester is testing the application.

Please give me some advice.

Answer 1

The problem with the fact that each of them develops from its own separate copy of the database is that they will not pick up changes from other developers.

For example, if someone adds a column to a database table, other developers will not be aware of this. Someone may also add the same column inadvertently.

And if someone changes the stored procedure in such a way that requires changing the application code (for example, to add an input parameter), other developers will not recognize this. If they receive updated code from the source control, it will not work with their local copy of the database.

I agree that having an increasingly confusing development database is a problem. But I'm not sure that developing from multiple copies of the database will reduce the number of development problems.

One alternative that I acknowledge may not be available to you is to periodically copy the production database into development. As a rule, this can happen only after a new version, which makes any changes to the scheme of the production database. But for this you must have a production base.

Answer 2

A solution that works very well for my company is to run a database in a virtual machine for each developer.

We created one virtual machine for each database we support (oracle, db2, mssql, mysql). Now, each developer can simply copy and start the virtual machine locally without the need to install it.

Answer 3

I found it very useful to spend time creating a system in which each developer has his own database, which is cleaned, rebuilt and populated with test data every time they run their unit tests. That way, you can never be with each other. Of course, a continuous integration and testing server should also have its own databases.

While DDL and test data are in version control, each works against one database. Another advantage is that if I work on a function that requires a database change, everyone receives both the code and the DDL + test data necessary for this change.

In the Java field, DbUnit in our case, together with the Hibernate Maven plugin, is very useful for this. Of course, a simple home solution can succeed.

Answer 4

In my projects, the development database is always located on the local developers machine. We use SQL Server Developer Edition or SQL Server Express. We save the SQL script to create the database checked in the source code repository. Anyone who needs to recreate their local database can use this. One team member is responsible for supporting the SQL script, and any changes to the database first go to it (as usual, SQL scripts). It gets the latest script version from SCM, updates its local copy and generates an updated script creation that replaces the script in SCM. At the same time, the accompanying code changes are checked in SCM so that the code and the database are synchronized. All others are synchronized with this version.

This gives people the freedom to work in parallel and, if necessary, change the scheme, including experimental changes that may be discarded. We also use a local DB as a source for dummy data for local testing of a web application, and not for unit testing, we usually scoff at this DB, but testing for integration and UI. Saving separate local databases allows each developer to configure it for testing as needed without any coordination with others.

It should also be mentioned that we use Red Gate SQL Compare to propagate only changes around when the locator of the local coordinator base is under official verification in the version. This is an alternative to crashing and re-creating the database and saves existing data much better. Depending on the database changes, this can be a trivial or somewhat complicated operation. We always use it to update control and test and production databases, if the changes are so trivial that they can be done manually (without errors).

Answer 5

How about a local local SQLite database. Many Rails developers are happy with this solution.

Answer 6

We use the same basic process that other people describe here, with some significant changes. Each developer usually has his own instance of db, and we manage it with change scripts. Here are the details:

• We have a base script database to create the initial db. This includes creating a table in db, which we use to track the version of the db schema.
• All SPs, views, and functions are written to separate files.
• If you need to make changes to the schema, write a script that will change this. He must check the schema version table to make sure db is in the correct version to apply this schema change. Things like Red-Gate tools really help write these scripts, but they are not essential.
• We have a small program that we wrote that automatically runs all of these scripts against a database. It will check the current db schema version and run new schema change scripts. It will always run all SP scripts, browsing, etc.

It gets a little messy at the points, because the schema change scripts must have their version number encoded in the file name, and you may have conflicts when two developers create the same version of the script. And there are times when you can have a db that is in an inconsistent state and the automated process will fail. But overall it worked very well for us.