Can I export a Sqlite db using RedBean PHP ORM and Import to MySQL?

Question

I have a simple web app that I've been building using redbean PHP which has a really simple structure with three bean types:

areas buildings persons

All relationships are exclusive 1:Many. So, a Person belongs to only 1 Building, and a Building belongs to 1 Area.

Area
  BuildingList
    PersonList

Currently, I have been developing it using Sqlite3 for ease of development, but I want to move the data to mySQL. I have a lot of data that I've already added.

Is there an easy way to use RedBean to Export ALL beans to the new MySql Database?

I know I can search for a sqlite -> MySQL/MariaDB converter, but I also potentially want to be able to use this in reverse to make migrating the site super easy to move/backup/change DBs.

What I've tried below:

R::setup('sqlite:/' . __DIR__ . '/data/database.db'); 
R::addDatabase('mysql', $MySqlConn );

$old_datas = R::findAll( 'area' );
R::selectDatabase( 'mysql' );

foreach ($old_datas as $bean) {
    $new_area = R::dispense('area');
    $new_area->importFrom( $bean );
    $id = R::store( $new_area );
    var_dump( $new_area ); // shows new data
}

var_dump( R::findAll( 'area' ) ); // returns empty array

Have you tried `exportAll()` mentioned in [redbean docs](http://www.redbeanphp.com/index.php?p=/import_and_export)? — weirdan, Jan 12 '16 at 03:53
`exportAll()` is great and all and might work using recursion to add to the MySQL db. I haven't gone too far down that path, but it's looking more and more likely that I'll have to write the functions to do it manually like this. — Armstrongest, Jan 13 '16 at 01:56
Can I suggest [Fregata](https://github.com/AymDev/Fregata) (disclaimer: I am the author) ? There is a [demo project](https://github.com/AymDev/Fregata-demo) with a **MySQL** to **PostgreSQL** example migration if you want. It is ORM/database agnostic, although there's a integrated *Doctrine DBAL* foreign keys migration system but you can ignore it. I never worked with RedBean but I'd be glad to write a detailed answer if you have specific requirements. — AymDev, Jun 02 '21 at 12:02

score 1 · Answer 1 · edited May 23 '17 at 10:28

I'll preface this by saying that I've not personally tried to do so, but since you can dump an entire database via the sqlite3 console, then my first approach would be to do so, and then try importing that file into MySQL.

Dumping a sqlite3 database from the sqlite console:

sqlite> output database_dump.sql sqlite> .dump sqlite> .quit

Importing a .sql file into MySQL (as described here):

mysql> use DATABASE_NAME mysql> source path/to/file.sql

Can I export a Sqlite db using RedBean PHP ORM and Import to MySQL?

1 Answers1