Appropriate Uses for SQLite

#1

Post by vince_refiti » Fri, Apr 10, 2015, 8:27 AM UTC

Re: Appropriate Uses for SQLite

#2

Post by heyalexej » Fri, Apr 10, 2015, 11:29 AM UTC

I was fooled by the name for a long time. "SQLite? That's what you use to store 160 phone book entries or something." How wrong I was.

Re: Appropriate Uses for SQLite

#3

Post by SchizoDuckie » Fri, Apr 10, 2015, 12:31 PM UTC

heyalexej wrote: post #2

I was fooled by the name for a long time. "SQLite? That's what you use to store 160 phone book entries or something." How wrong I was.

I'm using WebSQL / Sqlite to store TV show data on the user's system. works like a charm, databases regularly grow to 10+mb, never had a problem with crashes, speed or anything like it. Plus, everything runs locally Now I really hope that Spartan can implement WebSQL as well...

Re: Appropriate Uses for SQLite

#4

Post by 127001brewer » Fri, Apr 10, 2015, 2:30 PM UTC

SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database.

I am curious to know how many people here solely use SQLite to power the back-end of their web application(s), especially when the page states, "SQLite does not compete with client/server databases."

(Or is the page referring to content-management-system-type websites?)

Re: Appropriate Uses for SQLite

#5

Post by derekp7 » Fri, Apr 10, 2015, 2:33 PM UTC

heyalexej wrote: post #2

I was fooled by the name for a long time. "SQLite? That's what you use to store 160 phone book entries or something." How wrong I was.

I'm using it for the data catalog for one of my projects (a Linux backup tool) which I've grown to something like 20GB, and storing the metadata catalog for 40 systems and about 30 backup sets for each one.

So far the main issues I've been having is I have a lot of complex joins, and it doesn't seem to want to use appropriate indexes all the time. If I'm joining 2 tables on an indexed field, it is ok, but as soon as I add a 3rd or 4th table the performance falls apart. So my current workaround is to join 2 tables into a temporary table, add an index, then join that temp table to a 3rd one.

Re: Appropriate Uses for SQLite

#6

Post by pstadler » Fri, Apr 10, 2015, 2:36 PM UTC

  "The SQLite website (https://www.sqlite.org/) uses SQLite itself, of course,
  and as of this writing (2015) it handles about 400K to 500K HTTP requests
  per day, about 15-20% of which are dynamic pages touching the database.
  Each dynamic page does roughly 200 SQL statements. [...]"

Wait... What? They're saying that their dynamic pages are executing 200 SQL statements per request?

Off-topic: I enjoyed reading this article which was posted here a while ago http://www.sqlite.org/testing.html

Re: Appropriate Uses for SQLite

#7

Post by zachrose » Fri, Apr 10, 2015, 2:37 PM UTC

Neat. I've also used an in-memory SQLite db for testing ActiveRecord models.

Also, the same list if uses can be applied to nedb as a lightweight alternative to MongoDB.

Re: Appropriate Uses for SQLite

#8

Post by bengali3 » Fri, Apr 10, 2015, 2:43 PM UTC

SchizoDuckie wrote: post #3

heyalexej wrote: post #2

I was fooled by the name for a long time. "SQLite? That's what you use to store 160 phone book entries or something." How wrong I was.

I'm using WebSQL / Sqlite to store TV show data on the user's system. works like a charm, databases regularly grow to 10+mb, never had a problem with crashes, speed or anything like it. Plus, everything runs locally Now I really hope that Spartan can implement WebSQL as well...

I look forward to using SQLite again. In 2010ish I used it for an airport and aircraft database to be installed with our iOS app for 'find near you' feature. lookup performance was perfect. but did have some performance snags on application start, a server call retrieved any DB records needing to be updated. I found SQLite update statements were slow (10mb DB of 10,000 records, less than 100 updates took 30s on first gen iphones), so instead of retrieving the records that were changed, we modified it to retrieve the latest 3mb (compressed) of the 10mb DB from the server, decompress and replace the single file which turned out to be simpler, more atomic and required much less code.

I really enjoyed the simplicity of it all.

Re: Appropriate Uses for SQLite

#9

Post by tootie » Fri, Apr 10, 2015, 2:48 PM UTC

How does SQLite handle replication? Can I have 10 app server nodes reading from one SQLite DB? NFS?

Re: Appropriate Uses for SQLite

#10

Post by nmrm » Fri, Apr 10, 2015, 2:53 PM UTC

tootie wrote: post #9

How does SQLite handle replication? Can I have 10 app server nodes reading from one SQLite DB? NFS?

From the article: Situations Where A Client/Server RDBMS May Work Better... High Concurrency: SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time.

I've used SQLite over NFS for replication. It worked fine.