Migrating PHP code from MySQL to SQLite, the basics

This is also for self-reference.

Data types


There’s a shitload of data types, really, just go look at the official documentation and bask in the glory of MySQL redundancy (j/k).


Very few, reassuring and simple data types. Basically text, numbers, and “whatever” (blob). Again, go look at the official documentation.

Database connection


$GLOBALS["dbcon"]=@mysqli_connect($dbhost, $dbuser, $dbpass);
if (mysqli_error($GLOBALS["dbcon"])) die("errore connessione db");
if (mysqli_error($GLOBALS["dbcon"])) die("errore connessione db");
if (mysqli_error($GLOBALS["dbcon"])) die("errore connessione db");


$db = new SQLite3(dirname(__FILE__)."/DB/db.sqlite");
// WAL mode has better control over concurrency.
// Source: https://www.sqlite.org/wal.html
$db->exec('PRAGMA journal_mode = wal;');
$db->exec('PRAGMA synchronous=NORMAL;');

(last couple of rows are only useful if you plan to have some -little- write-concurrency, otherwise don’t use them)

Very important thing to know: if you are writing code for a local-running application, SQLite connections will not “time out” as there’s no server to wait for your input, just a file on the disk (or memory, even!)






when you don’t expect results, so for INSERT, UPDATE or DELETE


when you expect multiple results, or several fields in a row


when you want returned a single-value result, for example when the query is something like SELECT timestamp FROM records WHERE id=$number LIMIT 1 (for this, with MySQL, you should parse the results with mysqli_fetch_array or similar, and then select the first value with [0])

Fetch results



when you want both associative and indexed arrays,


when you only need associative arrays, and


if you want only indexed arrays.


In parallel with above:




Speed considerations

If you don’t need associative arrays, you should always go for indexed arrays, since both in MySQL and SQLite they are fetched significantly faster; also, even if by very little, fetching only associative arrays is still faster then having both associative and indexed fetched together (and you’re not going to need those both anyway).






this function is not binary safe though at the time of writing (hasn’t been for a while from what I understand…)

Database functions

Just converting the PHP functions won’t be sufficient for most.

Think about time functions for examples, or DEFAULT values, or NULLing a NOT NULL timestamp column to have it automatically assigned to CURRENT_TIMESTAMP, these things are not present in SQLite.



NOW(), YEAR(), TIMEDIFF(), and another shitload of functions.


DEFAULT (Datetime('now','localtime'))

Several variations on the strftime() functions, of which the Datetime() above is an example.

Again, go look at the official documentation, as what you see above is my own translation for my own purposes, and you will find both in official sources and in the vast world of StackOverflow a plethora of readings and interpretations.

Leave a Reply

Your email address will not be published. Required fields are marked *