Suggestion: Supporting Executing SQL for migration use cases

[nikeokoronkwo]

I currently use the postgres package for using postgreSQL in Dart, but I have found it difficult to apply migrations to my postgreSQL database as the package does not support series of statements, and I feel like having to parse/split those statements by hand correctly would either mean making my own alternative SQL parser (to actually get it right), or risk doing it incorrectly. I have also not found any suitable Dart package that does migrations like this.

Therefore, it would be nice if it were possible to parse and execute a complete file in the postgres package. That way, one can implement their own Dart script to do migrations. It would also be helpful if one wants to quickly migrate their DB from another language to Dart, rather than having to manually dissect every statement in a SQL file and run.

I do not have an API implementation to suggest for this, but if one is needed I can help with something :)

If possible, it would also be nice to be able to do migrations in the package itself (similar to what packages like sqlx in Rust do). It could be a simple API or a command that given a directory containing SQL files, it applies migrations to each.
However, if the above can be done it would still be fine

[isoos]

I currently use the postgres package for using postgreSQL in Dart, but I have found it difficult to apply migrations to my postgreSQL database as the package does not support series of statements, and I feel like having to parse/split those statements by hand correctly would either mean making my own alternative SQL parser (to actually get it right), or risk doing it incorrectly.

You can specify the query mode at statement, session, connection or pool-level, and the "simple" query protocol allows sending multiple statements: https://pub.dev/documentation/postgres/latest/postgres/QueryMode.html#simple

Note, however, that the simple query protocol does not allow parameters, you cannot mix them.

[nikeokoronkwo]

This works! Thanks.
Any plans concerning supporting migrations natively?

[isoos]

Any plans concerning supporting migrations natively?

I'm not really sure: what is missing to do that?

[nikeokoronkwo]

Nothing much really.
I like the fact that packages like sqlx have support for doing migrations on compile time: doing this in Dart might require source gen

[isoos]

I like the fact that packages like sqlx have support for doing migrations on compile time: doing this in Dart might require source gen

Can you link to one or a few examples that shows the input and the output of that code gen?

[nikeokoronkwo]

Rust macros expand during compilation, so I do not have a good example that can show what it would look like.