Can/should I run my web site against a SQLite database?

Question

I'm about to build a new personal blog/portfolio site (which will be written in ASP.NET), and I'm going to run it against a SQLite database. There are a few reasons for this:

1. The site will not be getting a lot of traffic, and from what I've read, SQLite is able to support quite a lot of concurrent users for reading anyway
2. I can back up all the content easily, just by downloading the db over FTP
3. I don't have to pay my hosting company every month for a huge SQL2008 database that I'm hardly using

So, should I go for it, or is this a crazy idea?

Answer 1

I'm not so sure about #2 (what happens if SQLite makes changes to the file while the FTP program is reading it?) but other than that, there is no reason to prefer one DB over the other (unless one of those DBs just can't do what you need).

[EDIT] Use an online backup to create the file for FTP download. That will make sure the file content is intact.

Even better, add a page (with password) to your site which creates the file at the press of a button, so your browser can download it.

Answer 2

It's just fine for a low traffic site as long as it's mostly read traffic. If it were me, I'd use SQL Compact Edition instead (same benefits as Sqlite- single file, no server), just because I'm a LINQ-head and the LINQ providers are "in the box" for it, but Sqlite has a decent LINQ library and managed support as well. Make sure your hosting company allows unmanaged code, or that you use the managed port of Sqlite (don't know its current stability though).

Answer 3

SQLite can handle this easily - go for it.

Answer 4

You should check, but I think that the Express version of SQL 2008 is free of charge. Anyway, I've been working with SQLite from .NET environment, and it works quite fine (but I haven't done any load test). And if you're not decided yet, you still can use a LINQ provider which will allow you later to switch from one database to another without rewriting your SQL code (I think to DbLinq, for example). If you plan to backup you database, you must ensure first that it is not used at the moment.

Answer 5

I'd say no. First off, I don't know who you are using for a provider, but with my provider (goDaddy), it's pretty cheap at $2.99 a month or so. I get 1 sql server db and 10 mysql dbs.
I don't know how much cheaper this can get.

Secondly, why risk it? Most provider plans include at least MySQL database. You can hook up with that.

Answer 6

A rule of thumb is that if the site can run on one server then SQLite is sufficient. That is what the creator of SQLite, D. Richard Hipp, said at approximately 13 min 30 secs into episode 26 of the FLOSS Weekly podcast.

Direct audio link (MP3 file, 24 MB, 51 min 15 sec).

Answer 7

SQLite answer this for you:

http://sqlite.org/whentouse.html

low-medium volume = okay, high volume = don't use it

in your case its a-ok to use sqlite

Answer 8

Are you using any SQL functionality? SUM, AVG, SORT BY, etc, if yes go use SQLite. If not, just use plain txt files to store your data. Also make sure that the database is outside the httpdocs folder or it is not web accessible.

Answer 9

Generally, yes.

But you should be aware of the fact that SQLite does not support everything that you might be used to from a 'real' DBMS. E.g. there are no constraints like foreign keys, unique indexes and the like, and AFAIK some (more advanced) datatypes are not available.

You should check for the various limitations here and here. If you can get along with that there's no reason to not use SQLite.