Moving from SQLite to MySQL db?

[iyzpx]

Good Morning, thank you for the quick implementation of the other feature requests. Its nice to see that the development continues.

Just wonder if there is an easy way to move from SQLite to a MySQL Database? I thought this could be done with a dump but then I figured out that the syntax is quite different.

[jagauthier]

I wouldn't say there's an easy way. You have to dump the data and re-import it. Which means recreating tables and sql queries. Personally, I would create an empty mysql DB to get the structure. Then convert the sqlite dump into mysql inserts, and run those queries. This will most likely take some effort.

[jbtronics]

That is not really possible on a database level (or will at least need much manual work), as the SQL syntax differs for the different databases (especially the syntax for creating database schemes, but also the syntax for more complex data types). Also there are subtle differents in the way datetimes are represented, how Indizes and foreign keys works, etc. All of this makes migration using raw SQL really tedious.

In part-DB itself we normally never work with that low level SQL stuff and the difference between the platforms (as these are abstracted away by the Doctrine DBAL and ORM). On this abstract level a migration should be a lot easier, by reading in all entities (like parts, categories, etc), from the old database, which gives them in the database independent Version Part-DB uses and write them in the new database.

That is however not really high in my priority list, as it is not so often needed, I guess. And SQLite does not have that big disadvantages against MySQL.

[virtadpt]

I just ran into this particular issue (in the context of being unable to upgrade Part-DB-server when using SQLite) and found a workable, straightforward, but somewhat annoying solution. What I did was export all of my parts, categories, manufacturers, storage locations, and suppliers to JSON documents (full exports, specifically). I then edited my .env.local file to point to the MySQL server I was going to use and did

• php-8.2 bin/console doctrine:database:drop --force
• php-8.2 bin/console doctrine:database:create
• php-8.2 bin/console doctrine:migrations:migrate

After going through the setup process, I re-imported the JSON dumps. Total time was probably about ten minutes for a couple of hundred parts in my collection.