-----
title: "A Quick Overview of SQLite"
content-type: article
timestamp: 1132937558
tags: "review|databases"
-----
<p>A few months ago, my old hosting company started having problems with their servers. The servers would go down
    unexpectedly for 5-10 minutes on a relatively frequent basis, but for some weird reason... the MySQL databases were
    unusable for a couple of hours afterwards every time. "We had problems with MySQL, BUT the server was up, so we're
    still within the 99% uptime guarantee"... At the time I was thinking: "If only MySQL databases behaved like plain
    files..."</p>
<h3>What is SQLite?</h3>
<p>When PHP5 was first released, I discovered SQLite: <em>"...a small C library that implements a self-contained,
        embeddable, zero-configuration SQL database engine"</em> (as quoted from the <a
        href="http://www.sqlite.org">official site</a>). PHP5 offers native support to this little wonder, whose
    development actually started long before PHP5 was released, and can be used with many, many other programming
    languages.</p>
<p>SQLite organizes each database in a .db file, and implements most of the SQL 92 standards, to access the databases
    with no need of a server process running at the same time. Access is accomplished through standard reading/writing
    file operations.</p>
<p>Let's examine the pros and cons of using SQLite in your web applications.</p>
<h3>Features</h3>
<p>- SQLite is FREE &quot;for any purpose&quot;, <a href="http://www.sqlite.org/copyright.html">they say</a>.<br />- It
    doesn't rely on a server process to run<br />- You don't need to spend time configuring your installation, because
    there's nothing to configure!<br />- As there's no client-server negotiation, accesses to the database are much
    faster (2-3 times faster than a MySQL database)</p>
<p>As a consequence of all this, there's actually no concept of &quot;users&quot; allowed to access the database; as I
    said, the actual data of each database is stored into a single file, and as such, it has permissions which regulate
    access. If a script has read or write access to the file, a read/write sql instruction can be executed on the
    database. You can therefore simply protect your databases as you would protect any other file on your server.</p>
<p>- SQLite is small: the library is just 250KB, and takes care of everything, you don't need any other library or
    program to use it.<br />- SQLite can handle files up to 2 terabytes in size.<br />- SQLite implements most of the
    SQL 92 standard. This means you can usually use standard and well known queries to access it (with some exceptions,
    discussed in the next section).</p>
<p>- SQLite does not enforce datatype constraints. Is this a feature or a bug? Well, they call it a feature, but others
    may not agree. As a matter of fact, you can put a string into a field marked &quot;integer&quot; and vice versa, and
    furthermore, the string can be as big as you like! There's one exception to this rule, though. Columns marked as
    PRIMARY KEY must be of integer type.</p>
<h3>Limitations</h3>
<p>Now that you have read all of the preceding material, and know that PHP5 supports SQLite natively, you might be
    thinking about putting MySQL in the bin and using SQLite for everything instead: it's smaller, faster, portable,
    simpler, and headache-free... it's love at first sight. Right?</p>
<p>Well, the developers themselves decided to devote <a href="http://www.sqlite.org/whentouse.html">a page</a> to
    discuss when you should use SQLite and where you'd be better off sticking with your &quot;old&quot; database engine.
    Furthermore, being such a small and powerful piece of code, SQLite comes with some limitations which should be
    considered before starting to use it in a project:</p>
<p>- Not all SQL queries and syntax are supported. For a full list, have a look <a
        href="http://www.sqlite.org/omitted.html">here</a>. The most notable things you'll miss in SQLite are: the
    inability (for now) to &quot;ALTER TABLE&quot; (you do this, they say, by creating a new modified table and deleting
    the old one), no VIEW, and no CHECK or FOREIGN KEY constraints (they are &quot;parsed but not enforced&quot;).</p>
<p>- Syntax can be different sometimes. I noticed that, for example, in a JOIN between two or more tables, when
    accessing columns you ALWAYS have to specify &lt;table&gt;.&lt;column&gt;, whereas MySQL doesn't complain if there's
    ambiguity.</p>
<p>- SQLite is not suitable for projects which require a lot of semi-simultaneous writing operations. SQLite uses
    reader/writer locks: if there's someone reading from the database, writing to it is not allowed. This basically
    means that multiple simultaneous read operations (SELECT x FROM ...) have higher priority than write operations
    (INSERT, UPDATE, ...), which are therefore delayed.</p>
<p>- Do not use SQLite for big databases. Even though I said that (theoretically) databases up to 2 terabytes are
    supported, when your database is more than 1 GB, SQLite requires too much memory to run (256 bytes of RAM for each
    MB of database space, they say).</p>
<p>- Generally, if your website gets lots of traffic, SQLite shouldn't be your primary database engine, for the issues
    mentioned above. php.net uses SQLite for its site, but only on certain parts of it. If you get fewer than 100,000
    hits/day, SQLite should work fine - they say. So basically I can use for any site I make...</p>
<h3>Conclusions</h3>
<p>Considering all features and limitations, SQLite is an excellent solution for small or medium websites, embedded
    applications, programs which only need a small database to function and shouldn't be bound to a server, temporary
    databases, testing, and the like. Always keep in mind that you're working with files, so keep them protected
    wherever you put them in your server (a connection is established simply by specifying the path to the file).</p>
<p>Last but not least, if you're used to phpMyAdmin for administering your MySQL databases, there are similar tools for
    SQLite, such as <a href="http://www.sqlitemanager.org">sqlitemanager</a>.</p>