mysql / mariadb ?

[marc-odp]

Is there an update coming compatible with mysql / mariadb ?

[dantownsend]

@marc-odp There are no immediate plans at the moment, but I'd like to at some point.

[mavwolverine]

@dantownsend any change in plans for mysql? Thanks

[dantownsend]

@virajkanwade It's not impossible, it's just that all of the projects I currently work on at the moment use Postgres, so finding the time to support MySQL is tough. I need to do a proof of concept to get an idea of how much work it'll be - most of the SQL will probably translate over OK.

[nocturn9x]

Bumping this again because Piccolo is the only ORM that I don't just tolerate, but actually like. I would use Postgres all the time if I could, but sadly at work I don't get to decide what database type we use for some of our projects so being able to use piccolo for MySQL would be neat

[sinisaos]

I've been playing around with MySQL support for a few days now and this is branch. I'm not a MySQL expert, but I was surprised by the lack of column types and features that Postgres offers out of the box, but the biggest surprise is that MySQL alterations DDL statements (like CREATE, DROP, ALTER TABLE etc.) will commit the changes immediately in transaction and it is not possible to roll back the migration steps (DDL cannot be rolled back, and tables created within a transaction remain after rollback and that is bad for data integrity). This affects Piccolo's automatic migrations, which is automatically wrapped in a transaction because Postgres fully supports transactional DDL. @dantownsend or others, feel free to try the branch and any help is welcome if we are going to support MySQL (although due to the way MySQL handles transactions, it is highly questionable whether we want to)

[nocturn9x]

Kinda crazy that MySQL behaves this way, didn't know

[sinisaos]

@nocturn9x I realized that when migration unittests failed, but the table was saved in db (error that the table already exists). After that I looked online and saw this MySQL limitation (which I think is bad and I don't know of any reason to use MySQL instead of Postgres, unless it's really necessary and there's no other way). Piccolo automatic migrations does not work, but we can do manual migrations with raw sql (wrap_in_transaction=False argument in MigrationManager to run the migration outside of a transaction, but then we are responsible for rollback) and as I see, it works on a simple example. Except for the transactions, another issue is setting or deleting null values ​​because MySQL requires a column type like this: ALTER TABLE table_name MODIFY column_name COLUMN_TYPE NULL but with the current design of Piccolo migrations we cannot retrieve metadata on migration execution, because Piccolo does not store it (but we can use manual migration with raw SQL to override the syntax differences between Postgres and MySQL). There are probably more issues, but feel free to try the branch as most of the columns and methods are covered in that branch.

[nocturn9x]

I didn't need any more reasons to dislike MySQL, but you sure gave me some more! All hail PostgreSQL :)

[sinisaos]

@dantownsend Would you like me to make a PR for this? I will if you have the time and desire to review a large PR and if MySQL support (which will be limited due to automatic migrations and lack of column types) should even be part of Piccolo ORM?

[sinisaos]

@dantownsend @nocturn9x I opened PR. Any help, feedback or advice is welcome. Thanks in advance.

[nocturn9x]

@dantownsend @nocturn9x I opened PR. Any help, feedback or advice is welcome. Thanks in advance.

Awesome!

[sinisaos]

@nocturn9x It would be great if you could try MySQL support. I would appreciate any comments, suggestions, or information on whether it works or not (if something doesn't work, I will try to fix it). Thanks in advance for your time and effort.

[nocturn9x]

I'll have to make some time to test this, will let you know if I manage it :)

[sinisaos]

Thanks.