December 8, 2024
Tech

SQLite To MySQL

By Felix Jerde / August 1, 2023

The procedure of migrating databases from SQLite to MySQL server is not so hard, compared to transfer between another DBMS. The reason behind this relative simplicity is really because SQLite doesn’t have such sophisticated database objects as stored procedures and is not designed for concurrent connections. What this means is, SQLite databases are simply used as storages while all data handling logic is placed in external application(s). For this reason, it’s basically required in transferring the data from SQLite to MySQL database.

Despite these factors of simplicity, migration from SQLite to MySQL may be challenging due to the following reasons:

  1. SQLlite and MySQL have different methods to getting out of strings inside INSERT INTO clauses
  2. SQLlite uses ‘t’ and ‘f’ for booleans, MySQL uses 1 and 0

There is also a wide variety of methods to migrate from SQLite to MySQL, let’s explore the most popular options. One of the best and easy strategy is, migration using sqlite3 and mysql commands:

  1. dump the sqlite database with the utility sqlite3 via statement

$ echo “.dump archive” | sqlite3 mydb.sdb > mydb.sql

Adjust the process to the sqlite3 database as necessary. Keep in mind that you have to install sqlite3 first.

  1. create the database in MySQL if it is necessary via statement

$ echo “CREATE DATABASE mydb ” | mysql -u root -p

  1. restore with the items in the file mydb.sql, massaged slightly as the following

$ sed -e ‘/PRAGMA/d’ -e’s/BEGIN/START/’ -e ‘s/”archive”/archive/’ < mydb.sql | mysql -u root -p –database=mydb

This would develop a database dbtest in MySQL, simply by using a single table “archive” holding those items in your sqlite archive file. Keep in mind that this process may not work together with large complex SQLite databases due to essential alteration in formats of DDL and INSERT statements between SQLite and MySQL.

Last method is to apply one among conversion scripts on Perl or Python that can automate conversion SQL script from SQLite to MySQL format. Here is a good instance of Perl script that handles most vital variations between SQLite and MySQL while converting the script file:

#! /usr/bin/perl

while ($line = <>){

if (($line !~  /BEGIN TRANSACTION/) && ($line !~ /COMMIT/) && ($line !~ /sqlite_sequence/) && ($line !~ /CREATE UNIQUE INDEX/)){

if ($line =~ /CREATE TABLE \”([a-z_]*)\”(.*)/){

$name = $1;

$sub = $2;

$sub =~ s/\”//g;

$line = “DROP TABLE IF EXISTS $name;\nCREATE TABLE IF NOT EXISTS $name$sub\n”;

}

elsif ($line =~ /INSERT INTO \”([a-z_]*)\”(.*)/){

$line = “INSERT INTO $1$2\n”;

$line =~ s/\”/\\\”/g;

$line =~ s/\”/\’/g;

}else{

$line =~ s/\’\’/\\\’/g;

}

$line =~ s/([^\\’])\’t\'(.)/$1THIS_IS_TRUE$2/g;

$line =~ s/THIS_IS_TRUE/1/g;

$line =~ s/([^\\’])\’f\'(.)/$1THIS_IS_FALSE$2/g;

$line =~ s/THIS_IS_FALSE/0/g;

$line =~ s/AUTOINCREMENT/AUTO_INCREMENT/g;

print $line;

}

}

Finally, the best commercial software will allow you to fully automate the conversion of SQLite databases into MySQL format. An example of such tools is SQLite to MySQL available through Intelligent Converters.

This software provides an excellent opportunity to customize every available parameter in the conversion process. There’s option to customize resulting table structure that allows you to enhance the name and type of each column or exclude some columns from conversion. Furthermore, the software can migrate a database from SQLite to MySQL server automatically or export the data into local MySQL script file containing SQL statements to create tables and fill all the data. The subsequent option can be used should the target MySQL server doesn’t accept remote connections.

You may also like