17 ms·

I have used SQLite for Django applications with a few thousand users. It has had no problems. However, I just use the ORM and never configure the SQL directly.

I have used SQLite for Django applications with a few thousand users. It has had no problems. However, I just use the ORM and never configure the SQL directly. The vast majority of LAMP stack style web applications would be an ideal use case. However, I would consider how important RDMS features are to you which are not available in SQLite: - less sophisticated type and constraint system. - a severely limited ALTER TABLE. - No stored procedures. - limited selection of math and statistical functions. - no permission and user model, not to mention row-level security. To be clear, I don't think it's bad the SQLIte doesn't try to be an RDMS, but I would consider this perspective when making a decision, not performance which is great, and difficult to max out.

claytongulick 5y ago
I would add "limited json support" to this list. Under the hood, SQLite treats json as strings, you have to do some strange stuff with extract and computed fields indexing to index into it, which can be a bit fragile. My use case these days is hybrid rdbms / nosql, where most of my tables have defined columns for frequently queried data, and a jsonb "data" field for everything else. Postgres has impressive jsonb capabilities, and with 14 the index operators making querying it a dream. I love SQLite, but postgres' jsonb handing makes the additional operational overhead worth it to me.
simonw 5y ago
SQLite has a pretty powerful permission model via the set_authorizer hook - you can register a callback function which will be called each time SQLite attempts to read data. It's not widely used though from what I've seen. It's also really easy to add new custom SQL functions to a SQLite connection, which means the missing math functions shouldn't be a limitation. Here's an extension for example: https://github.com/nalgeon/sqlite-stats https://github.com/nalgeon/sqlite-stats

claytongulick 5y ago

I would add "limited json support" to this list. Under the hood, SQLite treats json as strings, you have to do some strange stuff with extract and computed fields indexing to index into it, which can be a bit fragile. My use case these days is hybrid rdbms / nosql, where most of my tables have defined columns for frequently queried data, and a jsonb "data" field for everything else. Postgres has impressive jsonb capabilities, and with 14 the index operators making querying it a dream. I love SQLite, but postgres' jsonb handing makes the additional operational overhead worth it to me.

simonw 5y ago

SQLite has a pretty powerful permission model via the set_authorizer hook - you can register a callback function which will be called each time SQLite attempts to read data. It's not widely used though from what I've seen. It's also really easy to add new custom SQL functions to a SQLite connection, which means the missing math functions shouldn't be a limitation. Here's an extension for example: https://github.com/nalgeon/sqlite-stats https://github.com/nalgeon/sqlite-stats