SQLite, keep connection open or close every time?

[Bob Sneidar]

(I'm pulling all this from memory when I read up on it a couple years back so if I am mistaken feel free to correct me).

There are a few other reasons to think twice about sqLite. One that comes to mind is that there is no real typing. If you write text to an int column it will happily do so. Normally an SQL database will throw an error. If you count on error callbacks to check for data consistency (I do not because I don't want to have to write a function to parse the many things that might go wrong with a DB write) then this will be an issue.

Another is that sqLite does not support all the SQL query statements that mySQL or MSSQL and other high end SQL databases might <glances over at Lynn>. I understand each has a superset of statements for special purposes. I am talking about things you might expect are supported of fully supported, and will be surprised to find it doesn't.

Thirdly, you cannot simply update an sqLite schema any way you like. It only supports one auto-incrementing column, and new columns are appended to the end of the schema. To insert a new column before another column, you need to dump the data, create a new schema, then import the data into the new schema.

The big reason I can think of to use it is that it is very easy to set up and maintain. Security is handled by the file system it sits on. It's not accessible via network protocols. It can be backed up and restored with simple file protocols. No worries about data dumps and restores.

Here is a useful article on sqLite limits:

https://www.sqlite.org/limits.html

Bob S


On Nov 25, 2015, at 17:00 , Peter Haworth <pete at lcsql.com<mailto:pete at lcsql.com>> wrote:

Having said that, the SQLite website is itself driven by a SQLite database,
which they use as an example of how SQLite can work in a networked
environment under reasonably heavy, read-only loads.  Of course they have
almost exclusively readers, except when they themselves are changing the
website due to a new release or something similar.  As you say, WAL allows
concurrent reading with one writer so even that probably isn't a problem in
their environment.