4 ms·

Anything similar possible with MySQL?

by _1tan 2mo ago

brandur 2mo ago
It doesn't look like MySQL has any concept like a template database. You could still migrate up a pristine database, use mysqldump to produce a schema, then load that into a series of test databases. It of course won't be as quick as the low level copies that Postgres is doing with a template database, but it'd be faster than re-running all your migrations. OOC — I've been trying to gather real world anecdotes on who is using MySQL these days given that even some of its biggest traditional champions like PlanetScale are talking a lot more about Postgres recently. Are you using MySQL as part of an existing project that was started years ago, or do you still intend to use it for new things going forward?
- Nextgrid 2mo ago
  MySQL has a lot of broken "features" that sadly some existing legacy code might be relying upon and there just isn't engineering capacity required to move to a saner DB, so they have to make do. I had a legacy project on MySQL that turned out to only "work" because string lookups were case-insensitive in that particular version or our configuration. Moving to Postgres and its correct behavior suddenly exposed a lot of bugs we needed to fix before we could complete the migration.
  - shadim80 2mo ago
    [flagged]
  - evanelias 2mo ago
    Case sensitivity/insensitivity is a property of the collation, which is configurable on a per-column basis. In Postgres the default collation is case-sensitive, and in MySQL the default collation is case-insensitive, but this does not mean one is "correct behavior" and the other is not. MySQL's out-of-the-box collation support is arguably a lot more thorough than Postgres's!
evanelias 2mo ago
In theory you can do filesystem-copy-speed population of large seed data using InnoDB's transportable tablespaces feature. For the test db use-case specifically though, unfortunately it's far less ergonomic than Postgres template databases.

Anything similar possible with MySQL?

by _1tan 2mo ago

Anything similar possible with MySQL?

It doesn't look like MySQL has any concept like a template database. You could still migrate up a pristine database, use mysqldump to produce a schema, then load that into a series of test databases. It of course won't be as quick as the low level copies that Postgres is doing with a template database, but it'd be faster than re-running all your migrations. OOC — I've been trying to gather real world anecdotes on who is using MySQL these days given that even some of its biggest traditional champions like PlanetScale are talking a lot more about Postgres recently. Are you using MySQL as part of an existing project that was started years ago, or do you still intend to use it for new things going forward?

Nextgrid 2mo ago
MySQL has a lot of broken "features" that sadly some existing legacy code might be relying upon and there just isn't engineering capacity required to move to a saner DB, so they have to make do. I had a legacy project on MySQL that turned out to only "work" because string lookups were case-insensitive in that particular version or our configuration. Moving to Postgres and its correct behavior suddenly exposed a lot of bugs we needed to fix before we could complete the migration.
- shadim80 2mo ago
  [flagged]
- evanelias 2mo ago
  Case sensitivity/insensitivity is a property of the collation, which is configurable on a per-column basis. In Postgres the default collation is case-sensitive, and in MySQL the default collation is case-insensitive, but this does not mean one is "correct behavior" and the other is not. MySQL's out-of-the-box collation support is arguably a lot more thorough than Postgres's!

evanelias 2mo ago

In theory you can do filesystem-copy-speed population of large seed data using InnoDB's transportable tablespaces feature. For the test db use-case specifically though, unfortunately it's far less ergonomic than Postgres template databases.