File SQLite spiegati: struttura, casi d'uso e limiti principali

Un file SQLite è un singolo file su disco che contiene un intero database relazionale: tabelle, indici e schema insieme. La documentazione ufficiale di SQLite lo definisce il "file di database principale" e osserva che "lo stato completo di un database SQLite è solitamente contenuto" in esso. La parola solitamente svolge un ruolo cruciale in questa frase.

Se ti è mai capitato di ricevere un file .db, .sqlite o .sqlite3 e di chiederti se avessi ricevuto tutti i dati, questo è il formato che devi comprendere a fondo.

Cos'è in realtà un file SQLite

SQLite si descrive come "una libreria in-process che implementa un motore di database SQL transazionale, autonomo, serverless e a configurazione zero". La stessa pagina afferma che SQLite "non ha un processo server separato". Un'applicazione si collega alla libreria e legge il file.

Ne conseguono due implicazioni. In primo luogo, il database viaggia come un unico elemento, motivo per cui così tante applicazioni distribuiscono i propri dati in questo modo. In secondo luogo, il formato deve essere estremamente stabile, perché questi file sopravvivono al software che li ha scritti. SQLite elenca un "formato di file stabile e duraturo" tra le sue caratteristiche principali. Specifica inoltre che il codice è di pubblico dominio, "libero per l'uso per qualsiasi scopo, commerciale o privato".

È facile sottovalutare la portata di questo strumento. La pagina di presentazione di SQLite afferma che "è il database più diffuso al mondo, con più applicazioni di quante se ne possano contare".

Cosa c'è dentro il file

L'header da 100 byte

I primi byte identificano il formato. All'offset 0, the file contiene una stringa di intestazione di 16 byte: SQLite format 3\000. Questa firma è il modo in cui gli strumenti riconoscono il file, indipendentemente dalla sua estensione.

Il campo successivo è più importante di quanto sembri. All'offset 16 si trova un intero a 2 byte che contiene "la dimensione della pagina del database in byte". La documentazione specifica che "deve essere una potenza di due compresa tra 512 e 32768 inclusi, oppure il valore 1 che rappresenta una dimensione di pagina di 65536". Tutti i campi multibyte nell'header sono memorizzati a partire dal byte più significativo.

Seguono altri due byte agli offset 18 e 19: la versione di scrittura e la versione di lettura del formato del file. La documentazione indica che il valore è "1 per la versione legacy; 2 per WAL".

Pagine, non righe

Sotto l'header, il file è una pila di pagine a dimensione fissa. La specifica è esplicita: "Il file di database principale è composto da una o più pagine. La dimensione di una pagina è una potenza di due compresa tra 512 e 65536 inclusi. Tutte le pagine all'interno dello stesso database hanno la stessa dimensione".

Le pagine sono numerate a partire da 1 e il numero massimo di pagine è 4,294,967,294. Tabelle e indici risiedono all'interno di queste pagine come strutture B-tree, motivo per cui un editor di testo mostra ben poco.

I file collaterali che nessuno menziona

Questa è la parte che spesso trae in inganno. La documentazione afferma che lo stato completo si trova "solitamente" in un unico file. Poi menziona l'eccezione. Durante una transazione, SQLite "memorizza informazioni aggiuntive in un secondo file chiamato 'rollback journal'". In modalità WAL, questo secondo file è un write-ahead log.

Pertanto, una copia creata mentre l'applicazione sta scrivendo potrebbe non contenere i dati confermati che si trovano ancora nel file collaterale. Se un collega ti invia solo un file .db e i dati sembrano non aggiornati, questa è la prima cosa da verificare.

Come aprire un file SQLite

Ci sono tre strade possibili, e quella giusta dipende da ciò che intendi fare in seguito.

Leggerlo con un visualizzatore. I visualizzatori SQLite per desktop e browser aprono il file, mostrano l'elenco delle tabelle e consentono di scorrere le righe. Questo è il modo più rapido per capire "cosa c'è dentro" ed è solitamente sufficiente per una prima occhiata.

Interrogarlo tramite riga di comando o una libreria. La shell sqlite3 e i binding della libreria standard in Python, Node e nella maggior parte degli altri linguaggi leggono direttamente il formato. Questa è la strada da percorrere se conosci già lo schema e cerchi un dato specifico.

Esportare una tabella e analizzarla altrove. Esporta una tabella in formato CSV e importala in qualsiasi strumento già utilizzato dal tuo team. In questo modo si perdono le relazioni tra le tabelle, che è proprio l'aspetto protetto dal formato. Quando possibile, esporta il risultato della join anziché le tabelle grezze.

Perché uno strumento potrebbe segnalare che il file non è un database

La specifica spiega chiaramente questo punto. Ogni file valido inizia con una stringa di intestazione di 16 byte, SQLite format 3\000. Se un lettore apre un file e non trova questa firma all'offset 0, significa che non si tratta di un database SQLite.

Nella maggior parte dei casi, le cause sono tre. Il trasferimento del file non è stato completato, quindi l'header è presente ma il resto è troncato. Il file è crittografato o incapsulato da un'applicazione, quindi i primi byte sono diversi. Oppure l'estensione è fuorviante e ciò che hai ricevuto in realtà è una semplice esportazione rinominata da qualcuno per essere d'aiuto.

Quanto può diventare grande un file SQLite

Più grande di quanto solitamente si pensi. La pagina dei limiti di SQLite indica che la dimensione massima di un file di database è di 4,294,967,294 pagine. Con la dimensione massima della pagina di 65,536 byte, si ottiene una dimensione massima del database di circa 281 terabytes.

La pagina è incredibilmente onesta riguardo a questa cifra. Nota infatti che il limite massimo "non è testato poiché gli sviluppatori non hanno accesso a hardware in grado di raggiungere tale limite".

Anche il numero di righe si scontra con lo stesso ostacolo. Il massimo teorico è di 2^64 righe in una tabella. La documentazione sottolinea che questo limite "è irraggiungibile poiché verrà raggiunta prima la dimensione massima del database di 281 terabyte".

Per scopi pratici, la conclusione utile è l'opposto di un limite. Se qualcuno ti consegna un file .db avvisandoti che è grande, quasi certamente non sarà il formato a fermarti. La dimensione della pagina scelta al momento della creazione del file e la presenza o meno di indici influenzeranno l'esperienza d'uso molto più di qualsiasi limite teorico documentato.

Dove si incontrano i file SQLite

• Esportazioni di applicazioni. Le app desktop e mobili spesso memorizzano cronologia, impostazioni e registri dei messaggi in un file SQLite che è possibile copiare.
• Passaggi di dati per analisi. Gli ingegneri inviano uno snapshot come singolo file anziché concedere l'accesso al database.
• Dispositivi e telemetria. I sistemi embedded scrivono localmente perché non c'è un server con cui comunicare.
• Archivi. La stabilità a lungo termine del formato lo rende una scelta comune per i dataset che devono rimanere leggibili per anni.
• Componenti interni di browser e strumenti. Molti strumenti locali mantengono lo stato in questo modo, motivo per cui l'estensione compare spesso nei ticket di supporto.

A cosa servono i file WAL e journal

Ti sarà capitato di copiare un file .db e di trovare un file -wal o -journal accanto ad esso. Questi sono i file collaterali descritti nella specifica, e la loro eliminazione è il modo in cui le persone perdono i dati.

Il rollback journal è il meccanismo più datato. Prima di modificare una pagina, SQLite scrive la versione originale di quella pagina nel journal. Se la scrittura viene interrotta, l'originale può essere ripristinato, il che consente a una transazione di sopravvivere a un arresto anomalo.

Il write-ahead log inverte questa disposizione. Le modifiche vengono inserite prima nel log e il file principale viene aggiornato successivamente. L'header indica in quale modalità si trova il database. La versione di scrittura del formato del file all'offset 18 è "1 per la versione legacy; 2 per WAL".

La regola pratica deriva direttamente dalla frase sullo stato completo. Supponiamo che il database sia in modalità WAL e che qualcuno ti consegni solo il file principale. Le modifiche confermate più recenti potrebbero trovarsi ancora nel log che non hai ricevuto.

Quindi, quando ti viene consegnato un file di database, poniti due domande. L'applicazione è stata chiusa correttamente al momento della copia? È stato fornito qualcos'altro insieme ad esso? Entrambe le risposte sono solitamente sì, e l'unica volta in cui non lo sono è quella in cui i numeri non corrispondono silenziosamente a quelli di produzione.

File SQLite vs CSV vs Parquet

	File SQLite	CSV	Parquet
Struttura	Molte tabelle, un solo file	Una tabella, un solo file	Una tabella, un solo file o cartella
Tipi di dati	Memorizzati con i dati	Dedotti dal lettore	Memorizzati con i dati
Relazioni	Mantenute, tramite chiavi e indici	Perse	Perse
Leggibile dall'uomo	No	Sì	No
Progettato per essere interrogato	Sì, con SQL	No	Sì, da motori di analisi
Errore comune	Mancanza del file collaterale journal o WAL	Rilevamento errato di tipo e delimitatore	Supporto della toolchain

Se lavori regolarmente con questi formati, le nostre guide sui file Parquet e sui file TSV approfondiscono gli stessi aspetti per questi due formati.

Perché i team scelgono questo formato

Nulla da eseguire. Poiché SQLite "non ha un processo server separato", il passaggio dei dati consiste nella semplice copia di un file anziché in una richiesta di provisioning.

I tipi di dati sopravvivono al trasferimento. Una colonna di date arriva come data. Chiunque abbia visto un lettore CSV trasformare un identificativo in notazione scientifica comprende il valore di questo aspetto.

Anche le relazioni sopravvivono. Diverse tabelle correlate rimangono unite in un unico elemento, mantenendo disponibili le join che danno significato ai dati.

La durabilità è integrata nel design. SQLite elenca le transazioni "anche in caso di interruzione di corrente" tra le sue caratteristiche principali, motivo per cui molti software embedded si affidano ad esso.

Limiti da conoscere

Un solo file, un solo scrittore alla volta. Il motore è integrato anziché servito, quindi il modello di concorrenza è diverso da quello di un database client-server. Si tratta di una scelta di progettazione, non di un difetto, ma definisce gli ambiti di utilizzo ideali del file.

La dimensione della pagina è fissa al momento della creazione. Ogni pagina di un database ha la stessa dimensione, che viene registrata nell'header. La si sceglie una sola volta.

Ancora la regola del file collaterale. Qualsiasi procedura di copia, backup o caricamento che acquisisca solo il file principale rischia di perdere tutto ciò che si trovava nel journal o nel write-ahead log.

Opacità. Un file SQLite non si legge a colpo d'occhio come un CSV. Per leggerlo è necessario uno strumento, ed è proprio questo ostacolo a rallentare molte analisi.

Come ottenere risposte da un file SQLite

La strada tradizionale consiste nell'installare un client, aprire il file, comprenderne lo schema e iniziare a scrivere codice SQL. Questo approccio funziona se si conoscono già le tabelle, ma è lento se si riceve il file al mattino e la riunione è nel pomeriggio.

La strada più breve consiste nel porre direttamente la domanda. Powerdrill Bloom ti consente di lavorare con i tuoi dati utilizzando il linguaggio naturale e restituisce una risposta con la relativa fonte allegata. La homepage promette che "ogni numero viene restituito con l'indicazione della pagina, della riga e del dato di origine". Da lì, lo stesso spazio di lavoro può generare grafici, fogli di calcolo o una breve presentazione.

Se questo è il tuo flusso di lavoro abituale, vale la pena conoscere due pagine correlate. Chat with Database illustra l'approccio conversazionale ai dati strutturati, mentre Text to SQL copre il caso in cui desideri ottenere la query stessa. Se invece i dati ti vengono consegnati come esportazione piatta, la pagina dell'assistente CSV AI assistant descrive quel percorso.

Un'altra informazione fornita dall'header

Poiché la dimensione della pagina si trova a un offset fisso, puoi scoprire informazioni utili su un file prima ancora di aprirlo correttamente. Un database creato con una pagina da 4.096 byte si comporta diversamente da uno creato con pagine da 65.536 byte. Questa scelta è stata effettuata una sola volta, al momento della creazione del file.

Non si tratta di un numero che si può modificare facilmente in seguito. Rientra nella stessa categoria concettuale di una decisione sullo schema, piuttosto che di una semplice impostazione.

Conclusione

Un file SQLite è un intero database in un unico elemento. Contiene una firma di 16 byte, una dimensione di pagina registrata all'offset 16 e una pila di pagine a dimensione fissa che ospitano tabelle e indici. È facile da trasferire, mantiene i tipi di dati e rimane leggibile per anni.

Ricorda l'unico avvertimento su cui la specifica è molto attenta. Lo stato completo si trova solitamente in quel file. Durante una transazione, una parte di esso risiede in un rollback journal o in un write-ahead log adiacente. Verifica la presenza del file collaterale prima di considerare attendibile la copia.

Quando hai il file e hai bisogno di risposte anziché dello schema, prova Powerdrill Bloom e poni la tua domanda direttamente ai dati.

Domande frequenti

Qual è la differenza tra .db, .sqlite e .sqlite3?

Nessuna differenza strutturale. Tutte e tre sono estensioni convenzionali per lo stesso formato, e il vero identificatore è la stringa di intestazione di 16 byte SQLite format 3\000 all'inizio del file.

Come faccio a sapere quale dimensione di pagina utilizza un file SQLite?

È registrata nell'header. Un intero a 2 byte all'offset 16 contiene la dimensione della pagina in byte. Deve essere una potenza di due compresa tra 512 e 32768, oppure il valore 1 che rappresenta 65536.

Un file SQLite rappresenta il database completo?

Solitamente sì, ma non sempre. La documentazione indica che durante una transazione SQLite conserva informazioni aggiuntive in un rollback journal. In modalità WAL, queste informazioni vengono invece salvate in un write-ahead log.

Posso aprire un file SQLite in Excel?

Non direttamente, perché il file memorizza pagine B-tree anziché righe di testo. Il percorso comune consiste nell'esportare prima una tabella in formato CSV o nell'utilizzare uno strumento che legga il formato del database e restituisca i risultati.

SQLite è gratuito per l'uso commerciale?

Sì. SQLite dichiara che il suo codice è di pubblico dominio ed è "libero per l'uso per qualsiasi scopo, commerciale o privato".