CODEWITHSUNDEEP
sqliteblogssystem.data.sqlite
Mark Bell
Mark Bell

Reputation: 29735

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

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?

Upvotes: 8

Views: 3204

Answers (10)

MontanaMan
MontanaMan

Reputation: 212

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.

Upvotes: 0

Peter Mortensen
Peter Mortensen

Reputation: 31603

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

Upvotes: 2

Thomas Weller
Thomas Weller

Reputation: 11717

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.

Upvotes: 1

Pasta
Pasta

Reputation: 2491

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.

Upvotes: -2

Stefan Ernst
Stefan Ernst

Reputation: 2781

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

Upvotes: 1

AngryHacker
AngryHacker

Reputation: 61606

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.

Upvotes: 0

picrap
picrap

Reputation: 1254

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.

Upvotes: 1

Galwegian
Galwegian

Reputation: 42227

SQLite can handle this easily - go for it.

Upvotes: 1

nitzmahone
nitzmahone

Reputation: 13940

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

Upvotes: 2

Aaron Digulla
Aaron Digulla

Reputation: 328556

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.

Upvotes: 3

Related Questions