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?

Taeniafuge

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.

Pentose

Could you reduce the chances of that by copying the file on the server first? – Barthold

I see your point - what could cause SQLite to make changes other than data updates (if anything)? – Taeniafuge

@Mark B -Does your blog have comments? Otherwise, I'm fairly certain that SQLite doesn't make any changes to itself unless you insert/update/delete data. – Barthold

Mark B: Eventually, you're going to add something that will insert/update data. So just invest an hour and you can forget about this issue. Even better: When you add a download page, you can use a script to automate backups. – Pentose

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).

Unselfish

Good suggestion - I'll certainly look into SQL Compact Edition as that would just 'slot in' nicely. – Taeniafuge

@Unselfish after 11 year of this answer I believe Sqlite is best option. Microsoft dropping SQL compact support in 2021 while Sqlite is continuously developed – Serried

Answer 3

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).

Algar

Answer 4

SQLite can handle this easily - go for it.

Prehension

Answer 5

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.

Intercostal

Answer 6

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

Estebanesteem

Answer 7

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.

Wainscoting

Answer 8

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.

Schall

I'm with DiscountASP.NET (through multiple recommendations), and I am totally happy with the service, so I won't be moving. However, they don't support MySql, and the MS databases they do support cost between £5 and £10 monthly - granted it's not a lot of cash, but why pay it if I don't have to? – Taeniafuge

That's really pricey. Yeah, if it's just a hobby site with low volume, no harm in trying. – Schall

Answer 9

In general, SQLite isn't meant for a high-traffic website, but it can do quite well on websites getting 100,000 hits/day or less. The SQLite org website gets more than 500,000 hits/day, and generates 2 million or more DB interactions/day ... all handled by SQLite.

Here are some things that will dramatically speed up SQLite's performance:

• Index your tables
• Use transactions for multiple commands instead of executing one at a time.
• Learn about write-ahead logging

Do a Google search on each of the above with SQLite ... your DB performance will improve dramatically.

An SQLite DB can actually be faster than a MySQL, PostGRE, MS SQL Server DB, or other hosted server-based DBs for 2 reasons:

1). SQLite is usually stored on the same machine as the website, rather than a separate server machine, eliminating round trip network latency response times.

2.) For smaller read/write requests, the SQLite engine is executing far less code, which can also be faster.

For a smaller website, a smaller DB engine like SQLite could actually be faster and more efficient.

Hodeida

Answer 10

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.

Cucurbit

plain text? whatever you write as your data storage functionality, chances are high it will be wayyyy slower than sqlite. – Estebanesteem

I did not mean to store data in a txt file for retrieval.. Say for example, you have a simple counter for visitors, instead of using the database, you could just use a txt file called visits.txt and store 1 in it. For every new visit, just increment 1 to the file and save it. Obviously this is much quicker if you just get occassional visitors. – Cucurbit