Drizzle Migrate with Turso

On the migrate command drizzle-kit generate:sqlite && npx tsx ./drizzle/migrate.ts

//./drizzle/migrate.ts
import { migrate } from 'drizzle-orm/libsql/migrator'
import db from './db'

(async () => {
  await migrate(db, { migrationsFolder: './drizzle/migrations' })
})()

//./drizzle/migrate.ts
import { migrate } from 'drizzle-orm/libsql/migrator'
import db from './db'

(async () => {
  await migrate(db, { migrationsFolder: './drizzle/migrations' })
})()

I get error

return new api_1.LibsqlError(e.message, code, 
LibsqlError: SERVER_ERROR: Server returned HTTP status 500`

return new api_1.LibsqlError(e.message, code, 
LibsqlError: SERVER_ERROR: Server returned HTTP status 500`

33 Replies

Andrii Sherman•8mo ago

seems like something on the Turso side

christradingOP•8mo ago

is using drizzle push drizzle-kit push:sqlite recommended against for production? We should be using drizzle migrate drizzle-kit generate:sqlite even if we're not migrating any data to/from any other existing database? I solely want to use this to set up our database and tables Is there a way to have npx tsx ./drizzle/migrate.ts only run if there are schema changes from drizzle-kit generate:sqlite? Ie. if it there are No schema changes, nothing to migrate :sleeping: then would like not to do the second command which would then fail

Angelelz•8mo ago

I believe Turso has branches, in which you can use push without issues and only merge to prod if you are ok with the result I can think of a way but you need to know bash. You could pipe the result to sed and run the second command if "nothing to migrate" is found in the result Chatgpt can probably help you put that together

christradingOP•8mo ago

Okay. I changed my migration flow with - drizzle-kit generate:sqlite in the local development environment. I then git push the resulting migration files - tsx ./drizzle/migrate.ts in the build environment Seems to run so far. Let me know if this is sound or not

Angelelz•8mo ago

I'll always be in favor of migration because I have a history in the migration files

christradingOP•8mo ago

Is there a timeline for supporting turso/libsql schema databases? https://discord.com/channels/933071162680958986/1238328990062743562/1238336180790235157 Would you know @Andrew Sherman or @Angelelz ? Just curious how far I should expect backlog working on this 🙂

Angelelz•8mo ago

I can't look at your link, it says it's in a server I'm not a member of

christradingOP•8mo ago

Oh. Its the Turso discord server

christradingOP•8mo ago

christradingOP•8mo ago

https://github.com/tursodatabase/libsql/pull/1366

GitHub

Add Connection::transactional_batch by haaawk · Pull Request #1366 ...

This new method executes a batch of statements atomically in a transaction. This will be used by all SDKs to provide batch method.

Andrii Sherman•8mo ago

we are working with turso team to fix it

christradingOP•8mo ago

okay 🙂 🙏 looking forward to it

Aaron•8mo ago

Any timelines on when this maybe available at all? @Andrew Sherman

christradingOP•7mo ago

turso said it should be fixed but I'm still getting the same issue: migration is 'successful' but no tables/columns show up in the Turso database. I am on the latest dependencies Is there way I can get more information from the migration? I turned on logger: true but I'm not getting any more information in my build terminal log

  const db: LibSQLDatabase<typeof schema> = drizzle(turso, {
    logger: true,
    schema,
  })

  const db: LibSQLDatabase<typeof schema> = drizzle(turso, {
    logger: true,
    schema,
  })

drizzle-kit migrate
drizzle-kit: v0.22.5
drizzle-orm: v0.31.2
No config path provided, using default path
Reading config file '/codebuild/output/.../drizzle.config.ts'
[⣷] applying migrations...
[2K[1G[✓] migrations applied successfully!

drizzle-kit migrate
drizzle-kit: v0.22.5
drizzle-orm: v0.31.2
No config path provided, using default path
Reading config file '/codebuild/output/.../drizzle.config.ts'
[⣷] applying migrations...
[2K[1G[✓] migrations applied successfully!

Ah, drizzle-kit migrate depends on the drizzle.config.ts file and not at all on the db instance, so the logger setting is irrelevant I re-configured my env variables in my production build step and it's working for now for me 🙂

alphabeeblebrox•5mo ago

@christrading were you able to get migration to work with schema databases (Multi-DB Schemas)? I am still getting 500 Server Errors when I try to apply migrations using drizzle-kit migrate or drizzle-kit push

alphabeeblebrox•5mo ago

In case anyone else is facing the same issue, I have figured out why it was not working for me. I had to upgrade libSQL versions of my Turso DB Group as explained here https://docs.turso.tech/api-reference/groups/update-database-versions. And migrations are working fine after that.

Turso

Welcome to Turso - Turso

SQLite for Production. Powered by libSQL.

christradingOP•4mo ago

@Angelelz How do you go back to a previous migration point in history? I had a migration that failed but that still made changes to the database table. I did git reset HEAD~2 and git push -f to before the schema and migration changes where I added the couple field rows in a table, but when I check my table with Drizzle, the rows are still there I wouldn't expect a failed migration to still make the changes (halfway or incorrectly) so now I'm simply trying to revert back before the failed migration so I may re-try to correctly apply the intended changes

Angelelz•4mo ago

Turso and some of the other database providers have branching systems similar to git. You can revert back that way. Depending on how you apply your migration, some changes might occur. You should try to run the migration in a transaction so that if anything goes wrong, everything should revert back. If you use connection pulls, then that might be a problem. Right now I don't remember if drizzle wraps the migration in a transaction

christradingOP•4mo ago

How do I run the migration in a transaction? I just run drizzle-kit migrate

Angelelz•4mo ago

I'm not totally sure but sqlite might not allow to run DDL statements within transactions. You might need to check with turso In any case, the branching system should save your database and you should be able to revert On the application side, you need to both revert the changes to your typescript schema and drop the migration that you generated

christradingOP•4mo ago

Given that I removed my schema and migration changes with git reset and force push, and thus unfortunately can't really get that back, should I do anything first before applying drizzle-kit drop? As the docs say Please don’t delete any files in migrations folder manually, it might break drizzle-kit https://orm.drizzle.team/kit-docs/commands#drop-migration ?

Drizzle ORM - List of commands

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Angelelz•4mo ago

The drop command won't look at your code, only at the list of migrations that have been generated and let you select the one you want to delete

christradingOP•4mo ago

"let you select the one you want to delete" so it only works in an interactive console? I wont be able to use drizzle-kit drop in my pre-build step of my deployment pipeline? that's where i do my drizzle-kit migrate. or are there any flags available for it to just drop the one most recent migration?

Angelelz•4mo ago

I don't know if there is any non-interactive option for drop. I can't answer questions about your architecture either, but I'm not sure I'd like to put the drop command in my deployment pipeline

christradingOP•4mo ago

Okay I guess Ill ask the Turso team then because yeah drizzle-kit drop seems to do just the same thing as what I did with git reset and force push the last migration change away. the __drizzle_migrations folder in Turso didnt remove the last migration upon doing so

Angelelz•4mo ago

The drop command won't do anything in the database. It will only delete the generated migration file

christradingOP•4mo ago

can i just manually delete the most recent row in the __drizzle_migrations folder? i cant, i just "delete 1 record" and it deletes everything D: @Angelelz is there anyway to apply/unapply database changes from migration files at this point? the last migration file/change was applied, i removed the files with incorrect understanding trying to undo the database changes, so now it's out of sync, when i do any other migration change to remedy it i also found and tried adding in the exact same file changes from the original migration commit I undid, but its considering it as a second migration instead of the same one

Angelelz•4mo ago

Drizzle doesn’t have that API. Some database administration tools create up and down migrations and I think the drizzle team is working on something like that What you can do is manually drop columns or tables to get the database to the state it was in

christradingOP•4mo ago

yeah i can drop columns or tables manually but that one migration i dont want is still in the __drizzle_migrations folder so it'll always be present and out of sync with whatever else i modify i think with all the id fields being NULL I cannot modify or delete a single row from that folder

christradingOP•4mo ago

do you know a way to modify/delete that one row? when i try to do anything it either errors out or modifies all rows, which is not the behaviour id expect @Angelelz would I be able to expect this fix which so far works to be reliable and stable? https://discord.com/channels/933071162680958986/1281003002479509636/1281752596876496979

Angelelz•4mo ago

You can use the hash or created_at to identify the row I can’t see that link it’s in a different server

christradingOP•4mo ago

Okay. I've been trying to figure this out for the past while. I think I got it fixed unless it just totally breaks later down the road. What migration the pnpm migrate script thinks has or should be applied pertains to the _journal.json. So I removed the "ghost" migration from there, and pnpm generate; pnpm migrate applied a second migration which is dropping columns I added to get the database back to what it was before. I then committed the removal of those two sets migration changes of add columns and drop columns with the _journal.json, xxxx_snapshot.json, and xxxx_yyyy_zzzz.sql files. Then I removed those two most recent __drizzle_migrations rows with the SQL runner with

DELETE FROM "__drizzle_migrations"
WHERE created_at = (
    SELECT MAX(created_at)
    FROM "__drizzle_migrations"
);

DELETE FROM "__drizzle_migrations"
WHERE created_at = (
    SELECT MAX(created_at)
    FROM "__drizzle_migrations"
);

Angelelz•4mo ago

You shouldn’t delete migrations manually or mess with that folder at all Unless you know what you are doing In database as a service databases, you can create a branch to try you migration and merge to prod only when you verified that it’s correct

Gaming

Programming

Drizzle Migrate with Turso