Migrate SQLite to MySQL

Introducing SQLite

SQLite is a kind of open-source RDBMS that is distinguished from most other database management systems built on client-server model, since it is a server-less embedded database. This means that SQLite database runs within the software that accesses the data, eliminating the need for a separate server. Due to such architecture, SQLite is highly reliable, efficient system working perfectly in low-memory environments.

One of the major benefits of SQLite is its cross-platform compatibility, allowing it to run on various modern OS. As an RDBMS contained within a C library, SQLite can be utilized by applications written in any programming language that can connect to external libraries written in C. For example, it is ideal for developing embedded software in digital devices, such as mobile phones, game consoles, cameras, and set-top boxes. Additionally, SQLite can serve as a temporary dataset for applications to process data.

Furthermore, SQLite's simplicity and versatility make it a great database engine for most websites that handle low to medium-traffic HTTP requests. It is also commonly used in educational institutions for training and learning purposes due to its ease of use and setup.

In case of a small database that does not require comprehensive user management capabilities, SQLite is a suitable option. However, it may not be the best choice if you need to collaborate with other users or if you require scalability and customization. MySQL, on the other hand, can support multiple users and handle large amounts of data in the form of tables. As your business grows and your database expands, SQLite may no longer be sufficient for your needs. Therefore, if you are dealing with significant volumes of data and looking for powerful DBMS with rich set of advanced features, it is reasonable to migrate from SQLite to MySQL.

Introducing MySQL

MySQL is extremely popular open-source Relational Database Management System distributed under the GNU license. It supports most of the standard structured query language (SQL) using to define, query and update the database. MySQL is faster, highly scalable, and more user-friendly compared to other popular DBMS.

It follows the client-server model, where the database runs on a server and data is accessed by clients and workstations over the network. The server then responds to GUI requests from the clients by returning the requested output. MySQL supports various OS and multiple programming languages such as PHP, PERL, JAVA, C++, C, etc.

Many organizations and companies widely use MySQL for web development as it forms a critical part of the LAMP stack with support for PHP and Perl that facilitates building websites and web applications. Besides, great flexibility of MySQL allows database professionals configure the database server to suit various purposes ranged from online stores to business intelligence and data analysis. With its high-speed load capacities, unique memory caches, and other performance-enhancing features, MySQL can meet all performance requirements of corporate-scale data warehouses.

Do I Need to Migrate to MySQL?

Both SQLite and MySQL have its cons and pros, so it is important to evaluate if benefits of migration worth the efforts. SQLite is a great choice of embedded database for small or medium size portable software like mobile applications or games. However, it is not recommended to use SQLite for:

Large volume of data. SQLite official website recommends not to use it for databases exceeding 1TB.
Simultaneous data access. Since SQLite reads and writes data directly to disk, it restricts parallel reading and allows only one writing operation at any time.

On the other hand, MySQL ideally fits for websites and web applications and achieves well scalability due to replication support and automatic sharding (available in commercial MySQL products). At the same time MySQL is not fully complied with the SQL standard, that may be a stopper for projects requiring at least near-complete SQL compliance.

Migration Strategy #1

The most popular and straight forward method of SQLite to MySQL migration is based on combination of sqlite3 and mysql command line tools:

export SQLite database into SQL-statements that create tables and copy the data
```
sqlite3 {SQLite database} .dump > {output script file}
```

create the target MySQL database if it is necessary

echo "CREATE DATABASE {MySQL database}" | mysql -u {MySQL user} -p

modify the script file so it complies with MySQL syntax
- remove all lines that start with PRAGMA, BEGIN TRANSACTION and COMMIT
- replace quotes and '[',']' symbols that may surround database object names by MySQL equivalent '`'
- replace AUTOINCREMENT by AUTO_INCREMENT
- convert boolean values 't' and 'f' into 1 and 0
- convert VARCHAR types without specified length into TEXT

import the resulting script file into MySQL database

mysql -u {MySQL user} -p {MySQL database} < {script file}

Step 3 specified above can be implemented manually or automated via Perl or Python script that handles differences between SQLite and MySQL syntax. Below is an example of Perl script with similar capabilities:

#! /usr/bin/perl

line: while (<>) {
    next line if (/BEGIN TRANSACTION/ || /COMMIT/ || 
        /sqlite_sequence/ || /CREATE UNIQUE INDEX/);

    my $str;
    if (/^INSERT INTO (.*) VALUES(.*)/i) {
        my $names = $1;
        $names =~ s/\"/`/g;
        $names =~ s/\[/`/g;
        $names =~ s/\]/`/g;
        $str = "INSERT INTO $names VALUES $2\n";
    } else {
        s/\"/`/g;
        s/\[/`/g;
        s/\]/`/g;
        $str = $_;
    }
    $str =~ s/([^\\'])\'t\'(.)/$1THIS_IS_TRUE$2/g;
    $str =~ s/THIS_IS_TRUE/1/g;
    $str =~ s/([^\\'])\'f\'(.)/$1THIS_IS_FALSE$2/g;
    $str =~ s/THIS_IS_FALSE/0/g;
    $str =~ s/AUTOINCREMENT/AUTO_INCREMENT/g;
    print $str;
}

Migration Strategy #2

Database migration from SQLite to MySQL can be done via Perl or Python script. However, this approach is quite complicated and may require Perl or Python programming skills.

Below is an example of how to migrate SQLite database to MySQL using Python/Django:

Define databases in settings.py configuration file to tell Django about database servers will be used.

DATABASES = {
    'sqlite': {
        'ENGINE': 'django.db.backends.sqlite3', 
        'NAME': 'sqlite3.db',
        'USER': '',
        'PASSWORD': '',
        'HOST': '',
        'PORT': '',
    },
    'mysql': {
        'NAME': 'mysql_database',
        'ENGINE': 'django.db.backends.mysql',
        'USER': 'mysql_user',
        'PASSWORD': 'mysql_password',
        'HOST': 'mysql_host',
        'PORT': '3306'
    }
}

Compose a Python script like this

objlist = ModelObject.objects.using('sqlite').all()
for obj in objlist:
    obj.save(using='mysql')

The provided code is obviously just a demonstration of the method and may not be sufficient for a complete SQLite to MySQL database migration. In this case commercial third-party solutions may be considered to automate and simplify overall migration procedure.

Migration Strategy #3

There are commercial tools combining user-friendly graphical interface with powerful capabilities to migrate a database from SQLite to MySQL in fully automated mode. Users do not need to have any database administration or development skills to do migration using such software. The key features must be provided by high quality SQLite to MySQL migration tools:

Support for the appropriate versions of the source and target databases
Support for DBaaS variations of MySQL such as Azure for MySQL and Amazon RDS is an extra advantage
Migration of main database entries - schemas, data, indexes and relationships between tables (foreign keys)
Merging and synchronizing the target database with SQLite data via primary key or unique index
Command line support is an extra advantage suitable for scripting and scheduling purposes

One of those database migration tools in our SQLite to MySQL converter. Read this tutorial to learn more about the product.

Have questions? Contact us