Forgiving by design
SQLite has a flexible type system: the type belongs to the value, and a column's declared type is a preference called an affinity. The documentation says any column, except an INTEGER PRIMARY KEY, can store any kind of value. A text value in an integer column is accepted if it cannot be converted. STRICT tables, available from SQLite 3.37.0, enforce rigid types, but only for tables declared that way. Dates have no type of their own; they are stored as text, real numbers or integers by convention.
That flexibility keeps small applications moving, and it also means bad values can sit in a database for years without an error. A stricter engine will reject them the first day you move.
- List the declared type and the actual storage classes in use in each important column.
- Decide how dates and booleans are stored and check every row follows it.
Foreign keys are off unless you turn them on
SQLite does not enforce foreign keys unless each connection runs PRAGMA foreign_keys = ON. The documentation says this default is for backwards compatibility and that future releases might change it, so applications should set it explicitly. If your code never set it, references may be broken: orders pointing at deleted customers, rows pointing at nothing.
You can find the damage. PRAGMA foreign_key_check returns one row per violation, and PRAGMA integrity_check examines the file structure and constraints such as unique and not null but, the documentation notes, does not detect foreign key errors. quick_check is faster but skips unique-constraint and index-content checks. Run the checks on a copy of the file.
- Run foreign_key_check and integrity_check on a copy and keep the output.
- Make the application enable foreign keys on every connection.
One writer, and when that stops being enough
SQLite supports unlimited simultaneous readers but only one writer at any instant. Write-ahead logging lets readers and the writer proceed together, but there is still only one writer, and every process must run on the same machine; WAL does not work over a network filesystem. The project's own guidance is to consider a client/server database when many computers access the file over a network, when many writers must act at the same instant, or when data approaches very large sizes.
"Database is locked" errors and slow bursts of writes are the usual sign you are meeting that limit. They may also be fixed by shorter write transactions, so measure before deciding on a move.
- Find which writes are long and whether they can be batched or shortened.
- Move only when the single-writer limit is the proven constraint.
What the fixed jobs do not cover here
No fixed-price job in this catalogue covers a SQLite-backed application. The duplicate-merge, add-column and data-layer jobs are written for PostgreSQL and MySQL, and their intake stops on any other engine. The checks above are free to run yourself on a copy of the file. A SQLite-specific scope would be quoted separately, and so would a move from SQLite to a client/server engine.
If you are thinking of such a move, the checks above show how much cleaning the data needs first. Tools such as pgloader read a SQLite file and load PostgreSQL, applying default type casts and optionally resetting sequences, but their documentation does not say they carry foreign keys across, so check the result.
- Send versions and invented examples first, never the database file.
- Treat any move as a separate decision with its own rehearsal.
Sources and limits
- SQLite: Datatypes Checked 2026-10-11.
- SQLite uses flexible typing: a column's declared type gives it an affinity, any column except an INTEGER PRIMARY KEY can store any type, and STRICT tables (3.37.0) enforce rigid typing.
- SQLite: Foreign key support Checked 2026-10-11.
- Foreign key constraints are disabled by default and each connection must enable them with PRAGMA foreign_keys.
- SQLite: PRAGMA statements Checked 2026-10-11.
- PRAGMA foreign_key_check reports each violation, integrity_check does not detect foreign key errors, and quick_check skips unique-constraint and index-content checks.
- SQLite: Write-ahead logging Checked 2026-10-11.
- In WAL mode readers and the writer do not block each other, but there is only one writer at a time and all processes must be on the same host.
- SQLite: Appropriate uses Checked 2026-10-11.
- SQLite allows unlimited simultaneous readers but one writer at any instant, and client/server databases are suggested when many computers access the file over a network or write concurrency is high.