Limitations & Gotchas

Eight chapters have made a fair case for SQLite's own real strengths. This chapter covers its genuine limitations with the same honesty — closing out sqlite1-1's own roadmap entirely.

No User/Permission System — A Structural Consequence, Not an Oversight

sqlite1-1's own "no server" model means there's genuinely nothing analogous to MySQL's user accounts or Postgres's own roles — there's no server process to authenticate against in the first place, so the entire concept of database-level user permissions has no place to live in SQLite's own architecture.

In practice, access control for a SQLite database is entirely a matter of operating-system file permissions (who can read or write the actual .db file) plus whatever the application itself chooses to implement. This is a genuinely different security model, not simply a lesser one — but it does mean the application and its deployment take on responsibility that MySQL/Postgres would otherwise absorb at the database layer itself. This is exactly what makes sqlite1-7's own "database-level per-user access control" disqualifying criterion structurally true, not just an arbitrary rule.

Historically Limited ALTER TABLE Support

SQLite's ALTER TABLE has real, historical, genuine limitations compared to MySQL/Postgres. For a long time, it could only rename a table, add a column, or rename a column — it could not drop a column, change a column's type, or add/remove constraints directly.

SQLite 3.35 (2021) added DROP COLUMN support — a real, recent improvement, worth naming honestly, echoing sqlite1-3's own STRICT-tables-as-a-recent-fix pattern.

For anything still beyond what ALTER TABLE supports, the classic, genuinely accepted SQLite idiom — not a hack — is: create a new table with the desired schema, copy the data over, drop the old table, and rename the new one into place.

No Procedural Language Equivalent to PL/pgSQL

postgres1-8 covered PL/pgSQL (and Postgres's own pluggable multi-language extensibility); MySQL has its own stored-procedure dialect. SQLite has neither. It does support triggers, but trigger bodies are limited to ordinary SQL statements — not a full procedural language with variables, loops, or control flow.

This is a genuine, structural limitation rather than just "SQLite being simpler." Embedding logic inside the database engine itself doesn't fit SQLite's own architecture naturally, since the entire point of SQLite is that the application already has direct, in-process access to the data. Most logic that would live in a stored procedure in MySQL/Postgres simply lives in ordinary application code instead when using SQLite — a genuinely different, not necessarily worse, division of responsibility.

Type Affinity Gotchas, Revisited

sqlite1-3's own warn-box already covered this in depth: mixed storage classes in one column can produce surprising sort and comparison results, since SQLite orders storage classes in a fixed sequence rather than comparing mixed values as one type. Worth restating briefly here as this chapter closes the loop on it, per sqlite1-1's own roadmap.

A Few Other Honest, Smaller Limitations

• Historically limited RIGHT JOIN/FULL OUTER JOIN support — modern SQLite has added support for both, worth an accurate, current note rather than an outdated claim.
• No native network/inet types the way postgres1-3 covered for Postgres.
• Database size is practically capped by available disk, though the theoretical limit (around 281 TB) is enormous — not an actual practical concern for the vast majority of real use cases.

Hands-On Exercises