Image by Maddie Franz - Pexels

Migrating from SQLite to MySQL

selfhosted Jun 30, 2022

The last few days I've been working on refactoring code and simultaneously changing SQL queries to work with MySQL instead of SQLite.

The one thing I have noticed is that SQLite is so much more forgiving when it comes to syntax for column names or comparison.

Below are a few (read, all) samples that I have ran in to:

(note, all are samples)

Table names

In SQLite there was a query:

# SQLite
select blah from Device where 1;

The table's name was in reality not written with a capital.

select blah from device where 1;

Is the correct syntax for MySQL.

Reserved words

and special characters in column names

In SQLite columns with special characters and/or reserved words are between double quotes, in MySQL between back-ticks:

# SQLite
select "bl-ah" from somewhere;
select `bl-ah` from somewhere;

Note, in MySQL something between doublequotes turned out to be printed fully instead of the underlying value. Which means that "ipaddress" would print the word 'ipaddress' rather than the value ''


The original import was from .csv files, which meant that in many cases 'int' is not always 'int' and often enough 'null' is not 'Null'. Which meant that in cases where you would expect a specific value (length) it was better to look for that rather than trying to find out is null was null etc.

# SQLite
select MAC from networking where length(MAC) == 17 limit 1;
select MAC from networking where length(MAC) = 17 limit 1;

To be fair, SQLite does allow just one '='.


Sometimes you just want to display it all together, this has also been the majority of changes:

# SQLite
select (firstname ||' '|| lastname) as fullname from privacy;
select concat(firstname ,' ', lastname) as fullname from privacy;

And those were it. As for updating and inserting into the tables there seem to have been no issues.


Riccardo B.

Riccardo is an all round Linux Systems Engineer with over 20 years of experience and a knack for Automation. Favoring acronyms like NAO, IaC, SRE and more. Also hardly ever writes in third person :)