Baseline for introspected schema
I've used "drizzle-kit introspect" to get my initial schema and migration files. How do I now mark that initial migration file as applied (create the _drizzle_migrations table)?
28 Replies
Any ideas?
I suggest running it in another database and checking how it handles it.
The you can probably do something similar to your main database.
I managed to do something but I'm not sure if what I did is correct. When you run the introspection it creates a schema.ts, a meta folder with some JSON files and the initial migration file. Since the schema described in that initial migration file already exists in the database it creates that initial migration file with all of the CREATE statements commented out. It turns out that if I try to run the migration with that commented out it will complain that there is a syntax error, but it it does add an empty __drizzle_migrations table in the database. Should that initial (empty) migration show up in that table or will it be empty until I generate and apply my first real migration? I'm assuming that I should not un-comment that initial migration and apply it.
Can you add some dummy sql that does nothing and then run it?
I don't know what the dummy migration would be though 😄
If I run the initial migration (which is commented out) I get
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/* (followed by some more commented SQL) If I delete the contents of that file before running it I get Error: Query was empty and if I un-comment the initial migration and run it it does as expected and complains that those tables already exist. BTW - I'm using MySql 8 and for this test I'm on Windows.
OK, here's a little progress. That initial migration starts with this:
-- Current sql file was generated after introspecting the database
-- If you want to run this migration please uncomment this code before executing migrations
That is followed by the SQL code commented out with /* */ style comments. If I leave the first two comments and delete the commented out creates then running the migration will add the __drizzle_migrations table and add the row that represents that initial migration. The weird thing is that my migration program just hangs after adding that row. Here's the program:
import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";
import { migrate } from 'drizzle-orm/mysql2/migrator';
const connection = await mysql.createConnection({
user: "redacted",
password: "redacted",
host: "127.0.0.1",
port: 3306,
database: "redacted",
});
const db = drizzle(connection, {logger: false} );
// this will automatically run needed migrations on the database
await migrate(db, { migrationsFolder: './drizzle' });
I figured out the hanging. I had assumed from the examples that migrate() closes database connection but apparently it does not. A call to connection.end() seems to fix it. Can anyone confirm that closing the database is the responsibility of the caller?
Now it gets really weird. To try a real migration I added a column to one of the tables in schema.ts and ran "drizzle-kit generate:mysql". The resulting migration drops all of the tables in the database!
@bloberenober Did you get a chance to look at this?Hey @wdbsb!
I'm here to help. So first of all, migration won't close any connection, so if you need to - you need to close in yourself
Regarding this issue, could you share a minimal reproduction example repository? I would gladly clone it and try to reproduce the problem
Thanks @Andrew Sherman, I'll put that together! Can you verify that this is the correct way to create the __drizzle_migrations table and mark the initial migration (commented out) created by the introspection as applied?
1. Run "npx drizzle-kit introspect:mysql" to create schema.ts and the initial commented-out migration SQL file.
2. Run code such a the code above so do a migration that does not change the exiting schema other than to add the __drizzle_migrations table
yes, this is the correct way
OK, got it
* Clone https://github.com/wdbsb/drizzle_simple.git
* Create a MySql database called drizzle_simple
* Use the existingdb.sql script to create the 'existing' tables
* Update the DB credentials in drizzle.config.ts and index.ts
* npm install
* npx drizzle-kit introspect:mysql
* npx tsc
* node index.js
* It should fail with a complaint about the comments
* Delete the "bb" and __drizzle_migrations tables leaving only the "aa" table
* Delete the drizzle folder
* npx drizzle-kit introspect:mysql
* node index.js
It should work with one table
GitHub
GitHub - wdbsb/drizzle_simple
Contribute to wdbsb/drizzle_simple development by creating an account on GitHub.
Forgot to mention, this is MySql 8.0.28
okey, no I see the issue
I misguided you
and we have an issue with introspect + generate flow in mysql
thanks for all the examples and explanations
But I can explain how to achieve a proper flow for you now
give me 5 mins
1. After you have done introspection, don't remove the comments! It's important to skip this migration just because all tables already exist. This is where I misguided you.
2. Add
breakpoints: false. It's a bug that we don't have a warning about it. It's important to switch this off during introspection. But switch it back on for any future generate commands. I'll fix warning for this flow
Then you can run index.ts, and the migration should work well.
Please let me know if that worked for you
@Andrew Sherman Thanks! I'll give it a try. And get back to you.
@Andrew Sherman That worked! Thanks for looking into this.
perfect!
@Andrew Sherman - I may have celebrated too soon! Applying the initial migration worked perfectly, but the next one dropped all of the tables. Here's what I did:
I added a new column called 'test' to the "aa" table in schema.ts
Then I set breakpoints back to true in drizzle.config.ts,
Finally I ran "npx drizzle-kit generate:mysql" and got this:
Can you reproduce this?
do you have latest drizzle-kit?
"drizzle-kit": "^0.19.13",
@Andrew Sherman I'm seeing the same "Query is empty" error. The migrations actually run, as I can see the changes, but the drizzle migration table does not get updated. Annoyingly, in my case, I;ve had to hand recreate my latest migration, as it's work done by a colleague in a different branch thats now being merged in
Hmm, looks like it was the breakpoints causing the issue. Seems the last statement in the SQL should not have a breakpoint
@Andrew Sherman Were you able to reproduce the migration that drops all of the tables?
I didn't have a chance to reproduce it, will look into it tomorrow
Thanks Andrew. I really appreciate your help.
@Andrew Sherman Did you get to look at this problem?
Hey @Andrew Sherman, I hate to keep bugging you, but have you had a chance to look at this?
@Andrew Sherman You must be really busy. Is there anyone else who can help with this?
I'm opening your repo now, sorry for such a delay
will try to play around with it
we left from here
Thanks Andrew
Ok, I finally made everything you wrote
so when I'm doing this
I have no issues and see this migration
Thanks for trying that. I'll try it on another computer to see if I just have some wierd configuration problem.
my issue was i had breakpoints, and messed them up when editing a migration manually