23

Many web frameworks, such as Flask or Django use SQLite as their default database. SQLite is compelling because it's included in python, and administrative overhead is pretty low.

However, most high traffic public production sites wind up using a heavier database: mySQL, Oracle, or postgresql.

The questions:

Assume:

  • Site traffic is moderate, and concurrent read/write access to the database will happen
  • We will use SQLAlchemy with SQLite write locks (although this comment makes me a little nervous)
  • The database will contain perhaps 60,000 records
  • Data structures do not require advanced features found in heavier databases

Is there ever a compelling case against SQLite concurrency for websites that serve as moderate-traffic internal corporate tools? If so, what conditions will cause SQLite to have concurrency problems?

I'm looking for known specific root causes, instead of general fear / unsubstantiated finger pointing.

Community
  • 1
Mike Pennington
  • 404
  • 3
  • 11
  • How is sqllite with features like replication etc that could be useful for backups etc? In SQLlite I get the impression that the app owns the db. Can you run admin scripts, etc while your app is up? – Doug T. Dec 02 '13 at 13:26
  • 1
    Some anecdotes about SQLite and concurrency (mostly positive): [sqlite3 concurrent access](http://stackoverflow.com/questions/4060772/sqlite3-concurrent-access) – Daniel B Dec 02 '13 at 13:50
  • 1
    In the case of an internal website, what is the compeling reason for SQLite? Any restrictions on installing an RDBMS? – JeffO Dec 03 '13 at 04:33
  • Other than simplifying the development environment on individual developer's laptops there isn't a reason. The question of course is whether we can reasonably simplify the development and production environment – Mike Pennington Dec 03 '13 at 10:07

2 Answers2

23

I recommend reading the official answer to your question, Appropriate Uses For SQLite. Specifically, the "Situations Where Another RDBMS May Work Better" warns that SQLite does not support concurrent writing:

SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.

From an appropriateness perspective, I tend to view SQLite as a very sophisticated file format that supports SQL queries. I would tend to avoid SQLite if I wanted to separate my database from my web application, as it is not optimized for this case. In short, SQLite is insufficiently scalable for use in some scenarios, so people running websites who hope to someday become popular may be better off starting with something scalable, rather than going with SQLite and later being forced to switch.

All that being said, SQLite is probably fine for most internal websites; typically internal websites don't require the same level of concurrency and scalability.

Brian
  • 4,480
  • 1
  • 22
  • 37
4

Putting on my IT Director hat I see a few no-gos here:

  • Risk of data corruption. Perhaps more percieved than real but end of the day this is a non-transactional file type DB that does not have much if any recourse to bad writes besides asking if you've got a recent backup. Speaking of which . . .
  • How do I back this thing up? In a manner I know I've got a good copy. Preferably without taking the app offline.
  • How can I secure access to the DB? My general understanding is SQL lite has none outside of the filesystem access, which is a decent start but not the be-all, end-all. Especially for web apps where you might want more graduated permissions than DBA or nothing.

From a developer point of view I think it is important to know why SqlLite is the default -- it is because it is easy and it demos well. If you are "selling" a platform to new developers, being able to fire up a working web app with minimal fuss is key. And having to stand up and properly configure a database server would be a massive stumbling block to be avoided.

Wyatt Barnett
  • 20,685
  • 50
  • 69
  • 1
    Well, backups can be done via the [SQLite Backup API](http://www.sqlite.org/backup.html). I'll acknowledge SQLite might not be quite as safe by default, since, unlike service-oriented systems, SQLite clients talk to the database file more directly. That said, SQLite does use a journal to protect against system failures, and should be reliable if the host OS properly supports the locking primitives SQLite uses (network disk I/O does not). The official site lists [several scenarios](http://www.sqlite.org/howtocorrupt.html) which will lead to a corrupt SQLite database. – Brian Dec 02 '13 at 23:32
  • [SQLite is transactional](http://www.sqlite.org/transactional.html). Use the [Backup API](http://www.sqlite.org/backup.html) or adapt [MediaWiki's backup script](http://www.mediawiki.org/wiki/Manual:Sqlite.php) to perform online backups. Your general understanding of SQLite's security model is correct. The [official security advice](http://www2.sqlite.org/cvstrac/wiki?p=SqliteWebSecurity) is 'use common sense': think about how people could access your database file and design your web application accordingly. – Iain Samuel McLean Elder Dec 02 '13 at 23:32
  • @Brian -- I'd be hard pressed to think of another DB that has an entire page dedicated to "how this database can be corrupted". I will add that I do find the sql lite project amazing -- they likely have that page because they are kind of nuts and also have something like 10 lines of test code for every line of production code and really like to be sure and thorough. – Wyatt Barnett Dec 03 '13 at 03:57