Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid

SQLite3 use in production

by Akoya (Scribe)
on Jan 29, 2008 at 16:09 UTC ( #665000=perlquestion: print w/replies, xml ) Need Help??

Akoya has asked for the wisdom of the Perl Monks concerning the following question:

I have searched the monastery to glean all the wisdom I could in regard to using SQLite--nearly all of which is several years old. It appears that SQLite has matured over the last few years, and that concurrency has improved.

I am developing a small CGI::Application that will be deployed into a shared hosting environment at dreamhost. The app uses DBIx::Class for ORM, and has only about 10 tables. I am expecting no more than a dozen concurrent users, but scalability may become an issue.

What insight can you offer in regard to using SQLite3 in a light production environment?

I have MySQL available in both development and production environments, so is it worth even considering SQLite3?

Thanks, Akoya

Replies are listed 'Best First'.
Re: SQLite3 use in production
by hipowls (Curate) on Jan 29, 2008 at 16:25 UTC

    I use SQLite for prototyping as it can be used anywhere and it's fairly simple to swap DBD::drivers. I also find the firefox plugin SQLite Manager to be a handy tool.

    I can't comment on concurrency issues

      Thank you for your reply, and for the link to the SQLite Manager firefox plugin. I wasn't aware of that one.
Re: SQLite3 use in production
by jettero (Monsignor) on Jan 29, 2008 at 16:23 UTC

    I like sqlite, and I use DBD::SQLite in the code where I use it. It looks like a reasonably current version too (sqlite3.h).

    But I would say, no, it's not really ready for concurrent use. I base this opinion off my use of Trac. I love Trac, but if there were only like 8 people on my project, the constant concurrency problems would make me insane.

    This site is about perl though, so that's enough blathering from me about it.

    UPDATE: Yes, well, I find that sqlite can't deal with concurrency at all. I'm not very familiar with CGI::Application, but I'm reasonably sure that if you wrote a quick little app, say using DBIx::Class or DBIx::Simple or something fun like that... Anyway, an App with two threads that do selects or inserts (or a mix of both) for a few minutes. One of the threads would eventually encounter some fatal error. Now, you could write some kind of loop to try it over again and keep track of what has and hasn't been comitted, ... or you could just use a database that's designed for concurrent use.

    I'm assuming Simple and Class don't automatically retry errors. I could be wrong.


      Thank you for your reply. The insight of one who uses it regarding it's concurrency issues is greatly appreciated.

      Yes, I know this site is about Perl, as was my question. As stated, I was considering the use of SQLite3 in a Perl CGI::Application web app. Discussions here are not limited to the Perl core, but everything Perl.

Re: SQLite3 use in production
by samtregar (Abbot) on Jan 29, 2008 at 16:23 UTC
    I have MySQL available in both development and production environments, so is it worth even considering SQLite3?

    In a word, no. In my opinion, MySQL is better in practically every conceivable metric. The only thing SQLite has going for it is that it doesn't require you to have a database server installed and running!

    My favorite use for SQLite is in tests for modules that use DBI. It's so much easier than requiring end users to give you host/user/pass to a running MySQL.


      Thank you for your reply. I really like the idea of using DBD::SQLite for testing DBI modules.
Re: SQLite3 use in production
by zentara (Archbishop) on Jan 29, 2008 at 18:27 UTC
    In my experience, SQLite3 works great. It's benefit is that it is self-contained, and you don't need a giant db server going, like MySql or Postgres SQL. If you already are running MySQL or Postgres, you may as well make use of it. As mentioned above, with DBD, you can write the same code for any of those, and just make minor adjustments. I do this in ztkdb-sql. Right at the start of the script, it tests for which db you use, and adjusts accordingly; then let DBD take care of it.. So for ease of development, you can use SQLite3, and adjust for other dbs later.

    (P.S. I'm not a db guru, so take my code for what it is, NOT perfect code. :-) )

    I'm not really a human, but I play one on earth. Cogito ergo sum a bum
Re: SQLite3 use in production
by Anonymous Monk on Jan 29, 2008 at 22:50 UTC
Re: SQLite3 use in production
by Errto (Vicar) on Jan 30, 2008 at 14:55 UTC
    Another good use for SQLite, which is where I use it, is in redistributable and particularly end-user desktop applications. They're guaranteed to be single-user, and embedding MySQL would be more trouble than it's worth. SQLite requires no installation or configuration. Its only overhead is the DBD and supporting client libraries, but those are pretty light-weight. But for a server-side application that can accept multiple client connections, I don't see SQLite as offering much of an advantage.
Re: SQLite3 use in production
by smithers (Friar) on Mar 09, 2008 at 23:16 UTC
Re: SQLite3 use in production
by scorpio17 (Canon) on Jan 30, 2008 at 11:43 UTC
    I've been pondering a similar question myself. In my case, I'm developing a web app that will function as an online store-front, complete with user login, shopping cart, etc. So, basically every page hit is going to generate lots of database queries (session data, such as, is the user logged in? do they have items in their cart? as well as pulling items out of the inventory database for browsing). This is all great, in theory, but my web host provider has strict rules and regulations regarding how many computer resources one can use in a given month (disk, cpu, bandwidth, database access, etc.) I'm worried that I may go over my db limit with the current all-mysql implementation, so I'm considering using sqlite, or db_file, or something similar, that looks like a database in my code, but is really just a file on the server. This might work fine for small tables, sessions ids, etc. I've never seen anyone discuss issues related to partitioning up data this way, but I'd imagine it comes up frequently in practice, in shared hosting environments. In your case, sqlite might make sense in *some* areas, if not all.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://665000]
Approved by jettero
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (7)
As of 2021-06-24 20:43 GMT
Find Nodes?
    Voting Booth?
    What does the "s" stand for in "perls"? (Whence perls)

    Results (132 votes). Check out past polls.