SQLite Files Explained: Structure, Use Cases, and Key Limits

A SQLite file is a single disk file that holds an entire relational database — tables, indexes, and schema together. SQLite's own documentation calls it the "main database file" and notes that "the complete state of an SQLite database is usually contained" in it. The word usually is doing real work in that sentence.

If you have ever been handed a .db, .sqlite, or .sqlite3 file and wondered whether you received all of the data, this is the format to understand properly.

What a SQLite file actually is

SQLite describes itself as "an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine." The same page states that SQLite "does not have a separate server process." An application links the library and reads the file.

Two consequences follow. First, the database travels as one artifact, which is why so many applications ship their data this way. Second, the format has to be extremely stable, because those files outlive the software that wrote them. SQLite lists "stable, enduring file format" among its headline features. It also states that the code is in the public domain, "free for use for any purpose, commercial or private."

The scale is easy to underestimate. SQLite's own about page says it "is the most widely deployed database in the world with more applications than we can count."

What is inside the file

The 100-byte header

The first bytes identify the format. At offset 0, the file carries a 16-byte header string: SQLite format 3\000. That signature is how tools recognise the file regardless of its extension.

The next field matters more than it looks. At offset 16 sits a 2-byte integer holding "the database page size in bytes." The documentation states it "must be a power of two between 512 and 32768 inclusive, or the value 1 representing a page size of 65536." All multibyte fields in the header are stored most significant byte first.

Two more bytes follow at offsets 18 and 19: the file format write version and read version. The documentation notes the value is "1 for legacy; 2 for WAL."

Pages, not rows

Underneath the header the file is a stack of fixed-size pages. The specification is explicit: "The main database file consists of one or more pages. The size of a page is a power of two between 512 and 65536 inclusive. All pages within the same database are the same size."

Pages are numbered from 1, and the maximum page number is 4,294,967,294. Tables and indexes live inside those pages as B-tree structures, which is why a text editor shows you very little.

The sidecar files nobody mentions

This is the part that catches people out. The documentation says the complete state is "usually" in one file. It then names the exception. During a transaction, SQLite "stores additional information in a second file called the 'rollback journal'." In WAL mode that second file is a write-ahead log.

So a copy taken while the application is mid-write may be missing committed data that still lives in the sidecar. If a colleague sends you a .db and nothing else, and the numbers look slightly stale, this is the first thing to check.

How to open a SQLite file

There are three routes, and the right one depends on what you intend to do next.

Read it with a viewer. Desktop and browser-based SQLite viewers open the file, list the tables, and let you click through rows. This is the fastest way to answer "what is even in here," and it is usually enough for a first look.

Query it with the command line or a library. The sqlite3 shell and the standard library bindings in Python, Node, and most other languages read the format directly. This is the route when you already know the schema and want a specific number.

Export a table and analyse it elsewhere. Dump a table to CSV and take it into whatever tool your team already uses. You lose the relationships between tables, which is the one thing the format was protecting. Export the joined result rather than the raw tables where you can.

Why a tool might say the file is not a database

The specification explains this one. Every valid file begins with a 16-byte header string, SQLite format 3\000. A reader that opens a file and does not find that signature at offset 0 has not been handed a SQLite database.

Three ordinary causes cover most cases. The file transferred incompletely, so the header is there but the rest is truncated. The file is encrypted or wrapped by an application, so the first bytes are something else. Or the extension is misleading, and what you actually received was a plain export renamed by somebody being helpful.

How big can a SQLite file get

Bigger than the question usually implies. SQLite's limits page states that the maximum size of a database file is 4,294,967,294 pages. At the maximum page size of 65,536 bytes, that is a maximum database size of about 281 terabytes.

The page is refreshingly honest about that figure. It notes the upper bound "is untested since the developers do not have access to hardware capable of reaching this limit."

Row counts are bounded by the same wall. The theoretical maximum is 2^64 rows in a table. The documentation points out that this limit "is unreachable since the maximum database size of 281 terabytes will be reached first."

For practical work the useful takeaway is the opposite of a limit. If somebody hands you a .db and warns that it is large, the format is almost certainly not what will stop you. The page size chosen when the file was created, and whether it carries indexes, will affect the experience far more than any documented ceiling.

Where you meet SQLite files

• Application exports. Desktop and mobile apps often store history, settings, and message logs in a SQLite file you can copy out.
• Analytics handoffs. Engineers ship a snapshot as one file rather than granting database access.
• Devices and telemetry. Embedded systems write locally because there is no server to talk to.
• Archives. The format's long-term stability makes it a common choice for datasets that must stay readable for years.
• Browser and tooling internals. Many local tools keep state this way, which is why the extension turns up in support tickets.

What the WAL and journal files are for

You may have copied a .db and found a -wal or a -journal file sitting next to it. Those are the sidecars the specification describes, and deleting them is how people lose data.

The rollback journal is the older mechanism. Before changing a page, SQLite writes the original version of that page into the journal. If the write is interrupted, the original can be put back, which is what makes a transaction survive a crash.

The write-ahead log inverts the arrangement. Changes go into the log first and the main file is updated later. The header flags which mode a database is in. The file format write version at offset 18 is "1 for legacy; 2 for WAL."

The practical rule follows directly from the sentence about complete state. Suppose the database is in WAL mode and somebody hands you only the main file. The most recent committed changes may still be sitting in the log you did not receive.

So when you are given a database file, ask two questions. Was the application closed cleanly when the copy was taken, and did anything else come with it? Both answers are usually yes, and the one time they are not is the time the numbers quietly disagree with production.

SQLite file vs CSV vs Parquet

	SQLite file	CSV	Parquet
Shape	Many tables, one file	One table, one file	One table, one file or folder
Types	Stored with the data	Inferred by the reader	Stored with the data
Relationships	Kept, via keys and indexes	Lost	Lost
Human-readable	No	Yes	No
Written to be queried	Yes, with SQL	No	Yes, by analytics engines
Common failure	Missing journal or WAL sidecar	Type and delimiter guessing	Toolchain support

If you work with these formats regularly, our explainers on Parquet files and TSV files cover the same ground for those two.

Why teams choose the format

Nothing to run. Because SQLite "does not have a separate server process," a handoff is a file copy rather than a provisioning ticket.

Types survive the trip. A date column arrives as a date. Anyone who has watched a CSV reader turn an identifier into scientific notation understands the value of that.

Relationships survive too. Several related tables stay together in one artifact, so the joins that made the data meaningful are still available.

Durability is designed in. SQLite lists transactions "even after power loss" among its core features, which is why so much embedded software relies on it.

Limits worth knowing

One file, one writer at a time. The engine is embedded rather than served, so the concurrency model is different from a client-server database. That is a design choice, not a defect, but it shapes what the file is good for.

Page size is fixed at creation. Every page in a database is the same size, and that size is recorded in the header. You choose it once.

The sidecar rule again. Any copy, backup, or upload routine that grabs only the main file can miss whatever was in the journal or write-ahead log.

Opacity. A SQLite file does not skim the way a CSV does. Reading it takes a tool, which is exactly the friction that stalls a lot of analysis.

How to get answers out of a SQLite file

The traditional route is to install a client, open the file, learn the schema, and start writing SQL. That is fine when you already know the tables. It is slow when you were handed the file this morning and the meeting is this afternoon.

The shorter route is to ask the question directly. Powerdrill Bloom lets you work with your data in natural language and returns an answer with its source attached. The homepage promises that "every number comes back with the page, the row and the figure behind it." From there the same workspace can produce charts, sheets, or a short deck.

Two related pages are worth knowing if this is your regular workflow. Chat with Database covers the conversational route into structured data, and Text to SQL covers the case where you want the query itself. If your handoff arrives as a flat export instead, the CSV AI assistant page covers that path.

One more thing the header tells you

Because the page size lives at a fixed offset, you can learn something useful about a file before opening it properly. A database created with a 4,096-byte page behaves differently from one created with 65,536-byte pages. That choice was made once, when the file was created.

It is not a number you change casually afterwards. It belongs in the same mental bucket as a schema decision rather than a setting.

Conclusion

A SQLite file is a whole database in one artifact. It holds a 16-byte signature, a page size recorded at offset 16, and a stack of fixed-size pages carrying your tables and indexes. It travels well, keeps its types, and stays readable for years.

Remember the one caveat the specification is careful about. The complete state is usually in that file. During a transaction, part of it lives in a rollback journal or a write-ahead log alongside it. Check for the sidecar before you trust the copy.

When you have the file and need the answer rather than the schema, try Powerdrill Bloom and ask your question against the data directly.

Frequently asked questions

What is the difference between .db, .sqlite, and .sqlite3?

Nothing structural. All three are conventional extensions for the same format, and the real identifier is the 16-byte header string SQLite format 3\000 at the start of the file.

How do I know what page size a SQLite file uses?

It is recorded in the header. A 2-byte integer at offset 16 holds the page size in bytes. It must be a power of two between 512 and 32768, or the value 1 representing 65536.

Is a SQLite file the complete database?

Usually, but not always. The documentation states that during a transaction SQLite keeps additional information in a rollback journal. In WAL mode that information goes to a write-ahead log instead.

Can I open a SQLite file in Excel?

Not directly, because the file stores B-tree pages rather than rows of text. The common path is to export a table to CSV first, or to use a tool that reads the database format and returns results.

Is SQLite free to use commercially?

Yes. SQLite states that its code is in the public domain and is "free for use for any purpose, commercial or private."