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.

  • 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 2 '13 at 13:26
  • 1
    Some anecdotes about SQLite and concurrency (mostly positive): sqlite3 concurrent access – Daniel B Dec 2 '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 3 '13 at 4: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 3 '13 at 10:07
up vote 23 down vote accepted

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.

  • Nor do most external websites. The database engine can be swapped out if something like EF is used, although PostGres would probably be a better choice from the start. – Robert Harvey Dec 2 '13 at 18:29
  • @RobertHarvey what is EF? – Mike Pennington Dec 2 '13 at 19:30
  • @MikePennington: Entity Framework. I imagine there are other ORMs that also have database transparency, or at least can swap out the drivers. – Robert Harvey Dec 2 '13 at 22:14
  • 1
    The pythonic equivalent would be something like SQL Alchemy – Wyatt Barnett Dec 2 '13 at 23:16
  • "I tend to view SQLite as a very sophisticated file format that supports SQL queries." Perfect definition. – Wildcard Jan 21 '17 at 9:57

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.

  • 1
    Well, backups can be done via the SQLite Backup API. 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 which will lead to a corrupt SQLite database. – Brian Dec 2 '13 at 23:32
  • SQLite is transactional. Use the Backup API or adapt MediaWiki's backup script to perform online backups. Your general understanding of SQLite's security model is correct. The official security advice is 'use common sense': think about how people could access your database file and design your web application accordingly. – Iain Samuel McLean Elder Dec 2 '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 3 '13 at 3:57

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.