The problem with having everybody develop from their own separate copy of the database is that they won't pick up the other developers' changes.
For example, if someone adds a column to the database table, the other developers won't be aware of that. Someone else may also add the same column inadvertently.
And, if someone alters a stored procedure in a way that requires a change in the app code (for example, to add an input parameter), the other developers won't know that. If they get updated code from source control, it won't work on their local database copy.
I agree that having an increasingly muddied-up development database is a problem. But I am not sure that developing from multiple copies of the database is going to reduce the number of development problems.
One alternative, which I recognize may not be available to you, is to periodically copy the production database to development. Generally, this can only occur after a new release which makes whatever changes are needed in the production database schema. But you have to have a production database to do this.
A solution that works very well for my company is to run a database in a virtual machine for each developer.
We have set up one virtual machine for each database we support (oracle, db2, mssql, mysql). Now every developer can simply copy and run the virtual machine locally without having to install it.
I've found it very helpful to take the time to set up a system where each developer has his own database, which gets flushed, rebuilt and filled with test data every time they run their unit tests. In this way, you can never be in each-other's way. Of course a continuous integration and test server should also have their own databases.
As long as the DDL and test-data are in version control, every one is working against the same database. Another advantage is that if I'm working on a feature that requires a DB change, everyone gets both the code and the DDL + test-data required for that change.
In the Java sphere, DbUnit, in our case together with a Hibernate Maven plugin, is very helpful for that. Of course, a simple homebrew solution may do fine.
在我的项目中，开发数据库始终位于开发人员本地计算机上。我们使用 SQL Server Developer Edition 或 SQL Server Express。我们在源代码存储库中保留一个用于创建签入数据库的 SQL 脚本。任何需要重新创建本地数据库的人都可以使用它。一名团队成员负责维护 SQL 脚本，任何数据库更改都会首先由他处理（通常作为 SQL 脚本）。他从 SCM 获取最新版本的脚本，更新其本地副本并生成更新的创建脚本来替换 SCM 中的脚本。同时，伴随的代码更改将被签入 SCM，以便代码和 DB 同步。其他人都同步到这个版本。
这使人们可以自由地并行工作并根据需要进行模式更改，包括可能被删除的实验性更改。我们还使用本地数据库作为 Web 应用程序本地测试的虚拟数据源——不是单元测试，我们通常为此模拟数据库，而是集成和 UI 测试。将本地数据库保持独立可以让每个开发人员根据需要对其进行设置以进行测试，而无需与其他人进行任何协调。
我还应该提到，我们使用 Red Gate 的 SQL Compare 来传播协调器本地化后的更改DB处于官方签入版本。这是删除并重新创建数据库的替代方法，并且可以更好地保留现有数据。根据数据库的更改，这可能是一个微不足道的操作，也可能是有些复杂的操作。我们总是使用它来更新 QA/测试和生产数据库，除非更改非常微不足道，可以手动完成（没有错误）。
On my projects the development database is always on the developers local machine. We use either SQL Server Developer Edition or SQL Server Express. We keep a SQL script for creating the checked in DB in the source code repository. Anyone needing to recreate their local DB can use this. One team member is responsible for maintaining the SQL script and any database changes go to him first (as SQL scripts, typically). He gets the most recent version of the script from SCM, updates his local copy and generates an updated creation script which replaces the script in SCM. At the same time accompanying code changes are checked into SCM so that the code and the DB are in sync. Everyone else synchronizes to this version.
This gives people the freedom to work in parallel and make schema changes as needed, including experimental changes that may be dropped. We also use the local DB as the source for dummy data for local testing of the web app -- not unit testing, we generally mock the DB out for that, but integration and UI testing. Keeping the local databases separate allows each dev to set it up for their testing as needed without any coordination with others.
I should also mention that we use Red Gate's SQL Compare to propagate just the changes around once the coordinator's local DB is at the official checked in version. This is an alternative to dropping and re-creating the DB and works a lot better to preserve the existing data. Depending on the DB changes this can either be a trivial or somewhat complex operation. We always use it to update the QA/Test and Production DBs unless the changes are so trivial that they can be made by hand (without mistakes).
We use about the same basic process that other people have described here, with some significant changes. Each developer normally has their own db instance and we manage it with change scripts. Here are the details:
We have a base database script to create the initial db. This includes creating a table in the db that we use to keep track of the schema version of the db.
All SPs, views, and functions are scripted into individual files.
If you need to make a schema change, you write a script that does that change. It must check the schema version table to make sure that the db is at the right version to apply this schema change. Things like the Red-Gate tools help greatly to 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 schema version of the db and only run new schema change scripts. It will always run all of the SP, view, etc scripts.
It gets a bit sloppy at points because the schema change scripts must have their version number encoded in the file name and you can have conflicts when two developers both create the same version 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 has worked very well for us.
发布评论
评论（6）
让每个人都从自己单独的数据库副本进行开发的问题是，他们不会接受其他开发人员的更改。
例如，如果有人向数据库表添加一列，其他开发人员将不会意识到这一点。其他人也可能会无意中添加同一列。
而且，如果有人以需要更改应用程序代码的方式更改存储过程（例如，添加输入参数），其他开发人员将不会知道。如果他们从源代码管理获取更新的代码，则该代码将无法在其本地数据库副本上运行。
我同意日益混乱的开发数据库是一个问题。但我不确定从数据库的多个副本进行开发是否会减少开发问题的数量。
一种替代方案（我认为您可能无法使用）是定期将生产数据库复制到开发中。通常，这只能在新版本发布后发生，该版本对生产数据库模式进行了所需的任何更改。但你必须有一个生产数据库才能做到这一点。
The problem with having everybody develop from their own separate copy of the database is that they won't pick up the other developers' changes.
For example, if someone adds a column to the database table, the other developers won't be aware of that. Someone else may also add the same column inadvertently.
And, if someone alters a stored procedure in a way that requires a change in the app code (for example, to add an input parameter), the other developers won't know that. If they get updated code from source control, it won't work on their local database copy.
I agree that having an increasingly muddied-up development database is a problem. But I am not sure that developing from multiple copies of the database is going to reduce the number of development problems.
One alternative, which I recognize may not be available to you, is to periodically copy the production database to development. Generally, this can only occur after a new release which makes whatever changes are needed in the production database schema. But you have to have a production database to do this.
对于我的公司来说，一个非常有效的解决方案是在虚拟机中为每个开发人员运行一个数据库。
我们为我们支持的每个数据库（oracle、db2、mssql、mysql）设置了一台虚拟机。现在每个开发人员都可以简单地在本地复制并运行虚拟机，而无需安装它。
A solution that works very well for my company is to run a database in a virtual machine for each developer.
We have set up one virtual machine for each database we support (oracle, db2, mssql, mysql). Now every developer can simply copy and run the virtual machine locally without having to install it.
我发现花时间建立一个系统非常有帮助，每个开发人员都有自己的数据库，每次运行单元测试时都会刷新、重建并填充测试数据。这样，你们就永远不会妨碍对方。当然，持续集成和测试服务器也应该有自己的数据库。
只要 DDL 和测试数据处于版本控制中，每个人都在针对同一个数据库工作。另一个优点是，如果我正在开发一个需要更改数据库的功能，每个人都会获得该更改所需的代码和 DDL + 测试数据。
在 Java 领域，DbUnit（在我们的例子中）与 Hibernate Maven 插件一起对此非常有帮助。当然，一个简单的自制解决方案可能就可以了。
I've found it very helpful to take the time to set up a system where each developer has his own database, which gets flushed, rebuilt and filled with test data every time they run their unit tests. In this way, you can never be in each-other's way. Of course a continuous integration and test server should also have their own databases.
As long as the DDL and test-data are in version control, every one is working against the same database. Another advantage is that if I'm working on a feature that requires a DB change, everyone gets both the code and the DDL + test-data required for that change.
In the Java sphere, DbUnit, in our case together with a Hibernate Maven plugin, is very helpful for that. Of course, a simple homebrew solution may do fine.
在我的项目中，开发数据库始终位于开发人员本地计算机上。我们使用 SQL Server Developer Edition 或 SQL Server Express。我们在源代码存储库中保留一个用于创建签入数据库的 SQL 脚本。任何需要重新创建本地数据库的人都可以使用它。一名团队成员负责维护 SQL 脚本，任何数据库更改都会首先由他处理（通常作为 SQL 脚本）。他从 SCM 获取最新版本的脚本，更新其本地副本并生成更新的创建脚本来替换 SCM 中的脚本。同时，伴随的代码更改将被签入 SCM，以便代码和 DB 同步。其他人都同步到这个版本。
这使人们可以自由地并行工作并根据需要进行模式更改，包括可能被删除的实验性更改。我们还使用本地数据库作为 Web 应用程序本地测试的虚拟数据源——不是单元测试，我们通常为此模拟数据库，而是集成和 UI 测试。将本地数据库保持独立可以让每个开发人员根据需要对其进行设置以进行测试，而无需与其他人进行任何协调。
我还应该提到，我们使用 Red Gate 的 SQL Compare 来传播协调器本地化后的更改DB处于官方签入版本。这是删除并重新创建数据库的替代方法，并且可以更好地保留现有数据。根据数据库的更改，这可能是一个微不足道的操作，也可能是有些复杂的操作。我们总是使用它来更新 QA/测试和生产数据库，除非更改非常微不足道，可以手动完成（没有错误）。
On my projects the development database is always on the developers local machine. We use either SQL Server Developer Edition or SQL Server Express. We keep a SQL script for creating the checked in DB in the source code repository. Anyone needing to recreate their local DB can use this. One team member is responsible for maintaining the SQL script and any database changes go to him first (as SQL scripts, typically). He gets the most recent version of the script from SCM, updates his local copy and generates an updated creation script which replaces the script in SCM. At the same time accompanying code changes are checked into SCM so that the code and the DB are in sync. Everyone else synchronizes to this version.
This gives people the freedom to work in parallel and make schema changes as needed, including experimental changes that may be dropped. We also use the local DB as the source for dummy data for local testing of the web app -- not unit testing, we generally mock the DB out for that, but integration and UI testing. Keeping the local databases separate allows each dev to set it up for their testing as needed without any coordination with others.
I should also mention that we use Red Gate's SQL Compare to propagate just the changes around once the coordinator's local DB is at the official checked in version. This is an alternative to dropping and re-creating the DB and works a lot better to preserve the existing data. Depending on the DB changes this can either be a trivial or somewhat complex operation. We always use it to update the QA/Test and Production DBs unless the changes are so trivial that they can be made by hand (without mistakes).
个人本地 SQLite 数据库怎么样？许多 Rails 开发人员对该解决方案感到满意。
What about personal local SQLite database. Many Rails Developer are happy with that solution.
我们使用与其他人在此描述的基本流程相同的流程，但有一些重大变化。每个开发人员通常都有自己的数据库实例，我们通过更改脚本来管理它。详细信息如下：
它有时会有点草率，因为架构更改脚本必须将其版本号编码在文件名中，并且当两个开发人员都创建相同的版本脚本时可能会发生冲突。有时，您的数据库可能处于不一致的状态，并且自动化过程将会失败。但总的来说，它对我们来说非常有效。
We use about the same basic process that other people have described here, with some significant changes. Each developer normally has their own db instance and we manage it with change scripts. Here are the details:
It gets a bit sloppy at points because the schema change scripts must have their version number encoded in the file name and you can have conflicts when two developers both create the same version 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 has worked very well for us.