如何应对增量数据库架构演变 [英] How one should handle incremental database schema evolution

问题描述

我有一个Play框架驱动的应用程序，其中数据库作为持久层(为此我使用了Slick).我启用了演进功能，生成了1.sql文件，并成功将其推出了生产环境.

I have a Play framework powered application with database as a persistance layer (and I use Slick for that). I have enabled evolutions, generated 1.sql file and successfully rolled it out to production.

客户端请求需要修改数据库架构的新功能-即.添加新表，添加新列以及更改现有列的可空性.

Client requests new features that require database schema modifications - ie. adding new tables, adding new columns and changes to existing columns' nullability.

一旦更新了所有Slick的Table定义和相关代码，我将再次生成模式并将其放置为2.sql.正确要求运行Evolution，但是...生成的Evolution并不反映1.sql状态之上的增量更新，而是说明如何从头开始创建数据库模式的说明(即CREATE TABLE包含所有列，包括新列)而不是添加列"案例.

Once all Slick's Table definitions and related code are updated, I generate schema once again and place it as 2.sql. Evolutions are correctly requested to be run but... generated evolutions does not reflect an incremental update on top of 1.sql state but rather instructions how to create database schema from scratch (ie. CREATE TABLE with all the columns, including new ones rather than ADD COLUMN casuses).

是否有可能实现增量更新，以便我可以轻松地在生产环境中运行它以将数据库从版本1升级到版本2(#1和#2之间的SQL"diff")，还是必须手动进行?创造那些进化?

Is it possible to achieve an incremental update so that I can easily just run it in production to get database from revision #1 to revision #2 (SQL "diff" between #1 and #2) or do I have to manually create those evolutions?

推荐答案

您必须在v1之后手动创建这些版本.

You do have to manually create those evolutions after v1.

对于我们的产品，在产品进行装运前开发时，我们将保持生成演变"的状态.准备好部署v1后，我们将关闭自动演化生成，并从手动演化开始.

For our products we leave the "generate evolutions" turned on while products are in pre-ship development. Once v1 is ready to be deployed, we turn off the automatic evolution generation and start with manual evolutions.

虽然这可能不是最佳选择，但是作为一个团队，我们更愿意知道人们正在编码和检查DDL(有时是DML)SQL语句以进行演化，而不是使用自动脚本来决定应该删除表并重新创建.在某些产品中，我们在持续改进和适应的过程中拥有数百年的evos.该系统运行良好.

While this may feel sub-optimal, as a team we feel more comfortable in knowing that humans are coding and reviewing DDL (and sometimes DML) SQL statements for evolutions, rather than an automated script deciding that perhaps a table should be dropped and recreated. In some products, we have several hundred evos spanning years of continuous improvement and adaptation. The system works really well.

这篇关于如何应对增量数据库架构演变的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！