Recommended schema migration workflow with Drizzle ORM

[pablasso]

I'm using Drizzle and from reading your docs I was assuming a workflow like this to do schema migrations:

1. Create a new branch
2. Run npx drizzle-kit push:mysql on the branch
3. Create a Deploy Request
4. Merge the request

My problem is that for step 2, doing a push requires an empty DB (elsewhere it will fail because my tables already exist), so I've been dropping my tables or doing manual alter statements before creating my Deploy Requests. Is there anyway to create a blank branch? or I'm I looking at this the wrong way?

When automating this I was thinking on using pscale shell to clean out my schema, but I felt uneasy about leaving scripts around that may delete content if a dev connects to the wrong DB.

[orware]

Hello @pablasso,

For the Step 2 issue you mentioned, that seems a bit odd based on what I'm reading about the push command here in the Drizzle documentation:
https://orm.drizzle.team/kit-docs/overview#prototyping-with-db-push

Based on this section:

How it works?

When you run the command drizzle-kit push drizzle executes the following steps:

1. It retrieves your schema from the database and converts it to the “drizzle-schema” format.
2. The command reads all your schema files containing drizzle tables and converts them to the “drizzle-schema” format as well.
3. Drizzle then compares the two schemas and generates a set of statements that need to be executed against your database. These statements ensure that the database is synchronized with the schemas defined in your code.

To me, the above makes it sound like Drizzle would be capable of comparing the initial state of your development branch and then coming up with the needed queries to then get the branch's schema to match what you may have defined locally in your Drizzle code.

Is there a specific error the push currently fails with when the database is not empty?

Generally, there's no harm in clearing out the existing tables/schema within a development branch, but we do not provide a method for generating a blank branch currently, since the expectation is for the schema for development branches to match their parent branch initially, and then be modified afterward.

Most ORMs seem to have a reset type feature that could be used to clear the schema, but since that doesn't seem to be directly available within Drizzle currently, some of the tips mentioned here could be useful too:
https://www.answeroverflow.com/m/1130802621750448160

The pscale shell option could work too, but if you wanted to keep everything within Drizzle then you should be able to script it out that way too as an intermediate step between 1 and 2 in the list you shared above. The good thing about our deploy request workflow / safe migrations feature is that for production branches direct DDL changes such as DROP TABLE / TRUNCATE TABLE are prevented so even if the actions were attempted against a production branch they would fail helping to provide you with some protection.

[pablasso]

Is there a specific error the push currently fails with when the database is not empty?

Yeah it complains that tables already exists, for example:

sqlMessage: 'target: androni.-.primary: vttablet: rpc error: code = AlreadyExists desc = Table 'label' already exists (errno 1050) (sqlstate 42S01) (CallerID: cpjt3fq12myu461yd36h): Sql: "create table label (\n\tid bigint not null auto_increment,\n\tname varchar(255) not null,\n\tconstraint label_id primary key (id),\n\tconstraint label_name_unique unique key (name)\n)", BindVars: {REDACTED}'

To me, the above makes it sound like Drizzle would be capable of comparing the initial state of your development branch and then coming up with the needed queries to then get the branch's schema to match what you may have defined locally in your Drizzle code.

I was assuming the same, but maybe I'm just doing something wrong. I'll look up what am I doing wrong with Drizzle then.

The good thing about our deploy request workflow / safe migrations feature is that for production branches direct DDL changes such as DROP TABLE / TRUNCATE TABLE are prevented so even if the actions were attempted against a production branch they would fail helping to provide you with some protection.

This is great! if I cannot get it working I'll just script out cleaning up the branch with pscale shell in the meanwhile.