在没有 DBA 的情况下，由开发人员团队维护通用数据库架构的策略有哪些？
我很好奇其他人如何解决在没有 DBA 的情况下在许多（10+）开发人员之间维护和同步数据库更改的问题？我的意思基本上是，如果有人想要更改数据库，有哪些策略可以做到这一点？ （即我已经创建了一个“汽车”模型，现在我想将适当的 DDL 应用到数据库等。）
我们主要是一家 Python 商店，我们的 ORM 是 SQLAlchemy。以前，我们以使用 ORM 创建模型的方式编写模型，但最近我们放弃了这种方式，因为：
- 我们无法使用 ORM 跟踪更改
- ORM 的状态与数据库不同步（例如许多差异主要与索引和唯一约束有关）
- 除非开发人员通过电子邮件向团队记录数据库更改，否则无法审核数据库更改。
我们对这个问题的解决方案基本上是有一个“看门人”个人，他检查数据库中的每个更改，并将所有接受的数据库更改应用到accepted_db_changes.sql文件，从而需要创建任何数据库的开发人员更改将其请求放入 propose_db_changes.sql 文件中。我们签入此文件，并且当它更新时，我们都将更改应用到我们的开发计算机上的个人数据库。我们不会在模型上创建索引或约束，它们会显式应用于数据库。
我想知道维护数据库模式的策略是什么以及我们的策略是否合理。
谢谢！
I'm curious about how others have approached the problem of maintaining and synchronizing database changes across many (10+) developers without a DBA? What I mean, basically, is that if someone wants to make a change to the database, what are some strategies to doing that? (i.e. I've created a 'Car' model and now I want to apply the appropriate DDL to the database, etc..)
We're primarily a Python shop and our ORM is SQLAlchemy. Previously, we had written our models in such a way to create the models using our ORM, but we recently ditched this because:
- We couldn't track changes using the ORM
- The state of the ORM wasn't in sync with the database (e.g. lots of differences primarily related to indexes and unique constraints)
- There was no way to audit database changes unless the developer documented the database change via email to the team.
Our solution to this problem was to basically have a "gatekeeper" individual who checks every change into the database and applies all accepted database changes to an accepted_db_changes.sql file, whereby the developers who need to make any database changes put their requests into a proposed_db_changes.sql file. We check this file in, and, when it's updated, we all apply the change to our personal database on our development machine. We don't create indexes or constraints on the models, they are applied explicitly on the database.
I would like to know what are some strategies to maintain database schemas and if ours seems reasonable.
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
该解决方案是行政性的，而不是技术性的:)
一般规则很简单，项目中应该只存在树状依赖关系：
- 应该始终有一个单一的模式主源，与版本控制中的项目源代码一起存储
- 每次更新主源时，受主源更改影响的所有内容都应自动重新生成，不允许手动干预，如果自动生成不起作用 - 修复主源或生成器，不要手动更新源代码
- 所有重新生成应由更新主源的同一个人执行，并且包括主源更改在内的所有更改应被视为单个事务（单个源控制提交、每个受影响环境的单个构建/部署，包括数据库更新
）强制执行，这给出了 100% 可靠的结果。
主源基本上有 3 种可能的选择
1）数据库元数据，源是在连接到实时数据库的某些工具更新数据库后生成的
2）源代码，一些工具从源代码生成SQL方案，以特殊方式注释，然后在数据库上运行SQL
3）DDL，SQL schema和源代码都是由某种工具生成的
4) 使用一些其他描述（比如由生成 SQL 模式和源代码的特殊 Perl 脚本读取的文本文件）
1、2、3 同样好，前提是您需要的工具存在并且不太昂贵
4 是一种通用方法，但它应该从项目一开始就应用，并且需要用一种奇怪的语言编写数千行代码来维护
The solution is rather administrative then technical :)
The general rule is easy, there should only be tree-like dependencies in the project:
- There should always be a single master source of schema, stored together with the project source code in the version control
- Everything affected by the change in the master source should be automatically re-generated every time the master source is updated, no manual intervention allowed never, if automatic generation does not work -- fix either master source or generator, don't manually update the source code
- All re-generations should be performed by the same person who updated the master source and all changes including the master source change should be considered a single transaction (single source control commit, single build/deployment for every affected environment including DBs update)
Being enforced, this gives 100% reliable result.
There are essentially 3 possible choices of the master source
1) DB metadata, sources are generated after DB update by some tool connecting to the live DB
2) Source code, some tool is generating SQL scheme from the sources, annotated in a special way and then SQL is run on the DB
3) DDL, both SQL schema and source code are generated by some tool
4) some other description is used (say a text file read by a special Perl script generating both SQL schema and the source code)
1,2,3 are equally good, providing that the tool you need exists and is not over expensive
4 is a universal approach, but it should be applied from the very beginning of the project and has an overhead of couple thousands lines of code in a strange language to maintain
您是否尝试过 SQLalchemy Migrate 工具？
它们专门设计用于自动迁移您的数据库设计更改。
Have you tried the SQLalchemy Migrate tools?
They are specifically designed to auto-migrate your database design changes.
那么我假设您直接在物理数据库上设计数据库是否正确？很多年前我曾经这样做过，但是生成的数据库的质量非常差。如果您使用建模工具（我个人认为 Sybase pdesigner 仍然是同类中最好的，但环顾四周），每个人都可以对模型进行更改，并根据需要同步其本地数据库（它还会执行文档任务）。因此，根据 bobah 的帖子，master 是 pdesigner 模型而不是物理数据库。
您的
accepted_db_changes.sql文件是一份庞大的更改脚本列表吗？我不确定我喜欢更改文件名等的想法。鉴于两个数据库版本之间的区别是更改脚本的顺序列表，那么像这样的模型怎么样：每个更改（新文件）之前都经过审查承诺。
一般规则应该是有意识地努力在开发环境中尽可能多地自动化数据库部署；我们在这项工作上无疑获得了可观的投资回报率。您可以使用 redgate 等工具来生成 ddl（它有一个 api，但不确定它是否可以与 SQLAlchemy 一起使用）。 IMO，数据库更改应该是微不足道的，如果您发现它们阻塞，那么看看您可以自动化什么。
So am I correct in assuming you are designing your db directly on the physical db? I used to do this many years ago but the quality of the resultant db was pretty poor. If you use a modelling tool (personally I think Sybase pdesigner is still best of breed, but look around) everybody can make changes to the model and just sync their local db’s as required (it will also pick up documentation tasks). So, re bobah’s post, the master is the pdesigner model rather than a physical db.
Is your
accepted_db_changes.sqlfile one humongous list of change scripts? I’m not sure I like the idea of changing the file name, etc. Given that the difference between to two db versions is a sequential list of alter scripts, how about a model like:Where each change (new file) is reviewed before committing.
A general rule should be to make a conscious effort to automate as much of the db deployment in your dev environments as possible; we have defiantly got a respectable ROI on this work. You can use tools like redgate to generate your ddl (it has an api, not sure if it works with SQLAlchemy though). IMO, DB changes should be trivial, if you are finding they are blocking then look at what you can automate.
您可能会发现重构数据库这本书很有帮助，因为它包含管理数据库的一般策略，而不仅仅是如何折射它们。
他的系统期望每个开发人员都拥有自己的数据库副本以及在部署到生产之前使用的一些通用测试数据库。您的情况是本书中描述的更简单的情况之一，因为您没有许多使用数据库的单独应用程序（尽管您确实需要知道如何描述数据库迁移的人）。最重要的是能够根据源代码管理中的信息构建数据库，并通过小迁移描述更改（请参阅@WoLpH 的答案），而不仅仅是在数据库中进行更改。此外，如果您至少拥有 ORM <-> ，您会发现事情变得更容易。数据库测试以检查它们是否仍然同步。
You might find the book Refactoring Databases helpful as it contains general strategies for managing database, not just how to refractor them.
His system expects that every developer will have their own copy of the database as well as some general test database used before deploying to production. Your situation is one of the easier situations in the book describes as you don't have a number of separate applications using the database (although you do need someone who knows how to describe database migrations). The biggest thing is to be able to build the database from information in source control and have changes described by small migrations (see @WoLpH's answer) rather than just making the change in the database. Also you will find things easier if you have at least ORM <-> database tests to check that they are still in sync.