<story><title>Atlas – Terraform but for Database Migrations</title><url>https://atlasgo.io/</url></story><parent_chain></parent_chain><comment><author>atombender</author><text>In my experience, this sort of thing breaks down when you have a large production database that requires carefully crafted migrations to avoid affecting the existing system and taking too many locks.&lt;p&gt;For example, with Postgres some indexes have to be performed with &amp;quot;CREATE INDEX CONCURRENTLY&amp;quot; or &amp;quot;DROP INDEX CONCURRENTLY&amp;quot;, which cannot be done inside a transaction. (Also, a &amp;quot;CREATE INDEX&amp;quot; like this can fail halfway and leave behind an invalid index that must be manually deleted.) Whether this must be done with &amp;quot;CONCURRENTLY&amp;quot; or not isn&amp;#x27;t something the tool can know.&lt;p&gt;In some cases, a change must done in multiple steps. For example, if you change a column from &amp;quot;NULL&amp;quot; to &amp;quot;NOT NULL&amp;quot;, you have to provide a default value. Updating the database with a default value can in fact be a huge operation that might even have to be done in multiple stages to avoid locking rows for too long. There&amp;#x27;s no way to easily express this in a DSL. M&lt;p&gt;Then there&amp;#x27;s the database support. A tooo like this needs to support a huge range of features. Postgres has extensions (CREATE INDEX ... USING), special index operator classes, functional indexes, partitioning, and so on. I&amp;#x27;ve seen many ORMs or SQL adapters (ActiveRecord&amp;#x2F;ARel, Squirrel, Goqu, Sequelize) try to be smart with how they let you build SQL from high-level code, but they all fail to cover all cases. (Recently I needed to do &amp;quot;ORDER BY CASE ... END&amp;quot; and was using Goqu, which has support for case expressions, but did not support sorting on them.)&lt;p&gt;So while a tool like this might be good when you&amp;#x27;re just starting out, for a &amp;quot;real&amp;quot; app you want to avoid this sort of automation, because the tool is almost certainly not going to be smart enough.&lt;p&gt;I&amp;#x27;m a fan of non-magical tools that let me just write SQL migrations, like dbmate and Goose, because that gives me full control. Having a tool to magically figure out the diff isn&amp;#x27;t super helpful, because I really need to know the diff myself when writing the migration, in order to make it predictable. It&amp;#x27;s simply more convenient to specify the order yourself.</text></comment>
<story><title>Atlas – Terraform but for Database Migrations</title><url>https://atlasgo.io/</url></story><parent_chain></parent_chain><comment><author>redact207</author><text>I&amp;#x27;ll never be comfortable with any tool for that automatically generates schema changes, as I&amp;#x27;m just never sure at what point it decides to delete parts of my prod db.&lt;p&gt;All of my migrations are dumb DDL statements rolled up into a version. I know exactly what the final state is as it gets run and used when integration testing, staging etc.&lt;p&gt;It&amp;#x27;s boring but pretty bulletproof. I can rename a table and be confident it&amp;#x27;ll work, rather than some tool that might decide to drop and create a new table.&lt;p&gt;I can also continue to use SQL and not have to learn HCl for databases. This is useful when I want to control how updates are done, if I want index updates to be done concurrently Vs locking the table.</text></comment>