SQLite vs MySQL: quale database scegliere?

Due forme diverse di database

SQLite e MySQL parlano entrambi SQL ed entrambi salvano righe in tabelle, ma il modo in cui si inseriscono in un sistema è completamente diverso. SQLite è una libreria: la tua applicazione la include e legge da un file su disco. MySQL è un server: un processo separato a cui ti colleghi tramite socket o rete.

Questa sola distinzione determina tutto il resto: come li installi, quanti writer possono lavorare contemporaneamente, come fai i backup, come fai il deploy. La maggior parte delle domande su SQLite vs MySQL sono in realtà domande su embedded vs client-server.

-- SQLite: apri un file e hai un database.
sqlite3 app.db

-- MySQL: collegati a un server in esecuzione.
mysql -h localhost -u root -p

Il comando di SQLite apre (o crea) un file. Il comando di MySQL apre una connessione verso un processo che deve già essere in esecuzione, configurato e pronto ad accettare accessi.

Architettura: embedded contro client-server

In un'app con SQLite, il motore del database gira dentro il tuo programma. Nessuna porta, nessun demone, nessun systemctl start. Una chiamata alla libreria sqlite3 legge e scrive pagine direttamente da un file su disco.

MySQL è l'opposto. Il server mysqld custodisce i dati, gestisce le connessioni, applica i permessi, esegue il query planner e gestisce i lock. La tua app è un client che invia stringhe SQL attraverso la rete e riceve in cambio righe di risultato.

Le conseguenze pratiche:

• Deploy. SQLite viaggia con la tua app: un eseguibile, un file. MySQL richiede un server separato da installare, mettere in sicurezza, monitorare e salvare.
• Accesso di rete. MySQL espone una porta, quindi più server applicativi possono collegarsi allo stesso database. SQLite presuppone un solo processo (o pochi processi che collaborano) sulla stessa macchina.
• Permessi. MySQL ha utenti, ruoli e istruzioni GRANT. L'unico sistema di permessi di SQLite sono i permessi del sistema operativo sul file del database.

Nessuna delle due forme è "migliore". Risolvono problemi diversi.

Concorrenza e scritture

È qui che i due si separano davvero. Il motore InnoDB di MySQL usa lock a livello di riga: molte connessioni possono scrivere su righe diverse nello stesso momento senza bloccarsi a vicenda.

SQLite serializza le scritture a livello di database. Un solo writer alla volta, punto. I lettori possono lavorare accanto al writer (soprattutto in modalità WAL), ma un secondo writer aspetta il suo turno.

-- SQLite: va bene per molti lettori e un solo writer alla volta.
PRAGMA journal_mode = WAL;

-- MySQL: molti writer, lock a grana fine.
-- (Nessuna configurazione speciale: InnoDB lo fa di default.)

Per un'app con uno o due processi che fanno scritture moderate, come uno strumento desktop, un'app mobile o un piccolo CMS, le scritture serializzate di SQLite di solito sono abbastanza veloci da non accorgertene nemmeno. Per un servizio web trafficato con centinaia di connessioni che inseriscono ordini o aggiornano sessioni, il lock a livello di riga di MySQL fa la differenza tra "va bene" e "tutto è in coda dietro un unico lock".

Tipi di dati

MySQL ha un elenco lungo e rigido di tipi: TINYINT, INT, BIGINT, VARCHAR(n), DATETIME, DECIMAL(p,s), BLOB, JSON e molti altri. Dichiari una colonna INT e MySQL rifiuterà una stringa.

SQLite usa invece l'affinità di tipo. I tipi delle colonne sono suggerimenti, non regole imposte. Puoi mettere una stringa in una colonna INTEGER e SQLite la salverà (a meno che tu non scelga le tabelle STRICT, introdotte nella versione 3.37).

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL
);

INSERT INTO products (name, price) VALUES ('Caffè', 3.50);
INSERT INTO products (name, price) VALUES ('Tè', '2.75');  -- stringa in una colonna REAL

SELECT id, name, price, typeof(price) FROM products;

Entrambe le righe vengono inserite senza errori. La flessibilità è comoda quando fai prototipi, sorprendente quando ti aspetti la sicurezza dei tipi a livello di database. Usa le tabelle STRICT quando vuoi in SQLite un controllo come quello di MySQL.

Le differenze di sintassi che incontrerai davvero

La maggior parte dell'SQL di base, cioè SELECT, JOIN, WHERE, GROUP BY, è identica. Le differenze si concentrano in poche aree:

• Chiavi primarie con incremento automatico. SQLite usa INTEGER PRIMARY KEY (che si incrementa da solo per impostazione predefinita). MySQL usa INT AUTO_INCREMENT PRIMARY KEY.
• Virgolette per gli identificatori. MySQL permette i backtick per gli identificatori (`table`). SQLite usa le virgolette doppie ("table"), come prevede lo standard SQL.
• Funzioni sulle date. MySQL ha NOW(), CURDATE(), DATE_ADD(). SQLite ha datetime('now'), date('now'), datetime('now', '+1 day').
• Sintassi di LIMIT. Entrambi supportano LIMIT n OFFSET m, quindi qui sono compatibili.
• Booleani. MySQL ha BOOLEAN (un alias di TINYINT(1)). SQLite salva i booleani come 0 e 1 in colonne INTEGER.

-- MySQL
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at DATETIME DEFAULT NOW()
);

-- SQLite
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    created_at TEXT DEFAULT (datetime('now'))
);

Stessa intenzione, parole chiave diverse. Il modello mentale resta valido; la sintassi richiede un piccolo adattamento.

Prestazioni: dipende dalla domanda

"SQLite è più veloce di MySQL?" non ha una risposta unica.

Per un singolo processo che fa letture e scritture locali, SQLite spesso è più veloce: nessun passaggio di rete, nessuna comunicazione tra processi, nessun parser di query che gira in uno spazio di indirizzi separato. Una SELECT in SQLite è in pratica una chiamata di funzione.

Con molte connessioni concorrenti che scrivono sullo stesso database, MySQL passa in vantaggio grazie ai lock a livello di riga. Il modello a writer singolo di SQLite fa emergere presto la contesa con quel tipo di carico.

Per i carichi a prevalenza di letture con la modalità WAL attiva, SQLite scala sorprendentemente bene: i lettori non si bloccano tra loro né bloccano l'unico writer. Molti siti in produzione servono traffico reale con SQLite.

Non scegliere in base ai benchmark che leggi online. Scegli in base al tuo modo reale di accedere ai dati.

Quando ciascuno è la scelta giusta

Usa SQLite quando:

• Il database vive accanto a una sola applicazione (app mobile, strumento desktop, CLI, sito piccolo).
• Vuoi un deploy senza configurazione: basta distribuire il file.
• Le letture superano di molto le scritture, oppure le scritture sono poco frequenti.
• Ti serve un database di test embedded che rispecchi l'SQL di produzione.
• Stai facendo un prototipo e non vuoi ancora pensare a un server.

Usa MySQL quando:

• Più server applicativi devono condividere un unico database.
• Hai molti writer concorrenti.
• Ti servono permessi utente granulari e gestione dei ruoli.
• Stai costruendo su uno stack (LAMP, le configurazioni cloud gestite più comuni) che si aspetta MySQL.
• Gli strumenti operativi, come replica, ripristino point-in-time e monitoraggio, sono un requisito irrinunciabile.

Una regola approssimativa: se descriveresti le tue esigenze di archiviazione come "un'app, un disco", probabilmente SQLite basta. Se diresti "un servizio, con persone che lo gestiscono", usa MySQL (o PostgreSQL).

Migrare dall'uno all'altro

Partire con SQLite e passare a MySQL più avanti è una strada ben battuta, e un ottimo piano. Gli schemi si traducono con piccoli adattamenti e i dati si esportano in modo pulito con .dump dalla CLI di SQLite. Dovrai soprattutto sistemare la sintassi dell'incremento automatico, le funzioni sulle date e le funzionalità specifiche di SQLite (indici parziali dalle forme strane, WITHOUT ROWID, tabelle STRICT) che non hanno un equivalente diretto in MySQL.

Nella direzione opposta, da MySQL a SQLite, è più raro ma comunque fattibile, di solito per analisi offline, copie embedded di una parte dei dati o fixture di test.

Il punto: scegliere SQLite oggi non ti vincola. L'SQL che scrivi si trasferisce, e anche quello che hai capito.

Prossimo passo: SQLite vs PostgreSQL

MySQL è il confronto più comune, ma PostgreSQL è l'altro database che vedrai messo accanto a SQLite, e lì le differenze sono ancora diverse. È la prossima pagina.