Using SQLite for Simple Database Storage

Web applications frequently need a persistent data store, and a database is often the most appropriate choice. Although some users have succeeded in using a file-based database engine such as Access, getting full SQL compliance usually involves a database engine. For a large number of applications, this adds unnecessary complexity. SQLite is a good compromise between simplicity and full SQL compliance.

SQLite can be downloaded for free from http://www.sqlite.org. This site contains full documentation, binary versions for Linux and Windows, and wrappers for a very wide variety of languages.

Features

SQLite is a disk-based database engine. The entire database resides in a single disk file. Access control is provided by the operating system’s own security. 

Most of SQL92 is supported, including views, sub queries and triggers. The database is transaction compliant, although any transaction locks the entire database until it has completed.  It’s easier to list the features that it doesn’t support.  It doesn’t support foreign key constraints, alter table statements, writable views, right and full outer joins, and the grant and revoke statements.  These are only the features that I use (or would use) regularly.  A complete list is available as part of the documentation.

The one thing that isn’t supported that might confuse people is data typing.  As far as SQLite is concerned, all data types are strings. As a C and C++ programmer, I revolted at this idea until I had a chance to put it into use.  As long as access to the database is controlled by the program, and you design uniform interfaces, the consistency checks provided by data typing are not as necessary.  For a language like PHP, which has a fairly flexible concept of data typing, the typeless database is pretty much unnoticed.

SQLite is natively accessible through a C interface library that is remarkably easy to use.  It is also natively supported by PHP 5, and via an add-on module to PHP 4.  Wrapper libraries make it accessible to TCL, a COM interface makes it available for Visual Basic, ASP, ADO.Net, and the latest beta of Zeos Database Objects from http://www.zeoslib.net make SQLite available as a native database type to Delphi users.

The database engine also allows multiple simultaneous users.
Concurrency support is somewhat weak, because locking occurs on the entire database using the operating system’s file locking support. 

When to Use SQLite

Because it doesn’t require a database engine which has to be installed and maintained, SQLite is great for any situation where SQL support is necessary but simplicity is desired.

If the application is to have multiple users, SQLite is a solid choice if most access is reading. Because of the locking issues, multi user applications with a high ratio of writes to reads and a heavy user load are not a good match with SQLite. For a situation like that, a server database engine is preferred.

This combination of factors make SQLite a good choice for a lot of web applications, which make heavy use of persistent storage, but don’t need to support a lot of interactive users at one time.  Guest book and user preference tools are good candidates because they typically require a very small number of writes per user and a whole lot of reads.

{mospagebreak title=SQLite in Action}

To demonstrate SQLite in action, I’ll create a simple user registration system.  While my application is targeted at the family Christmas Wish list application that we use for our gift exchange, this tool could be easily adapted to any application.  I’ve written the class in VB.NET, using the ADO.NET SQLite provider at http://sourceforge.net/projects/adodotnetsqlite/. This provider can be downloaded in binary or source form. 

The ADO.NET provider returns results just like any other ADO.NET provider.  The native interfaces to SQLite return data in more unique ways which are covered in the SQLite documentation.

The application needs to collect a unique user name, an email address for confirmation, and a password.  We’ll protect the password by md5 encoding it in our database.  To keep the length of the article down,
I’ll only show the parts relevant to database interaction.  At the end
I’ll provide a link that will let you download a working user-adding application, so you can see the code in action.

create table user (
  id integer primary key,
  name varchar(40) not null,
  password char(32) not null,
  email varchar(60) not null
);

create index auth on user (name, password);

Although SQLite does not honor the data type declarations, I have left them in place because they are good reminders of what the fields should represent.  This SQL is also highly portable to other database engines, such as MySQL.  Keeping the portability options open is important in case my application grows.

SQLite provides a feature similar to MySQL’s auto_increment fields on any field declared as an integer primary key.  In SQL this can be retrieved with “SELECT LAST_INSERT_ROWID()”.  An earlier incarnation of this application used PHP and PEAR::DB to interact with the database.  PEAR::DB doesn’t deal well with auto_increment fields, preferring to use a sequence instead.  To support that feature PEAR::DB created the following schema entries:

CREATE TABLE user_seq (id INTEGER UNSIGNED PRIMARY KEY);
CREATE TRIGGER user_seq_cleanup AFTER INSERT ON user_seq
     BEGIN
         DELETE FROM user_seq WHERE id<LAST_INSERT_ROWID();
     END;

Trigger support in a lightweight, embedded database like SQLite is an impressive feature. Features like that have lead to the popularity of this engine.  We don’t need that feature in our application because we’re using a different provider, but I showed it here to demonstrate SQLite’s versatility and power.

{mospagebreak title=An Example User Object}

Because I’m a huge believer in the benefits of object oriented design, we’ll need a User object that will handle all of the actual database interface.  The prototype looks like this:

Imports System.Data
Imports Finisar.SQLite
Imports System.Security
Imports System.Text

Public Class User

  Public Id As Integer
  Public Name As String
  Public Email As String

  Public Conn As SQLiteConnection

  Public Sub Load(ByVal UID As Integer)
  Public Function Authenticate(ByVal N As String, ByVal P As String) _
                   As Boolean
  Public Sub Save()
  Public Sub Delete()
  Public Function Unique(ByVal requested As String) As Boolean
  Public Function HashPassword(ByVal s As String) As String
  Public Sub Populate(ByRef rdr As SQLiteDataReader)

End Class

The first group of functions provide basic persistent storage methods, and we’ll concentrate there first.

The Populate(ByRef rdr AS SQLiteDataReader) sub bears some mentioning. It’s responsible for populating the properties of the User object from a database reader.  When multiple User objects are placed in an aggregate data type, it’s useful to retrieve all of the user objects in a single query, and populate each of them as they’re inserted into the aggregate object.  Several internal methods also use it.

Public Sub Populate(ByRef rdr As SQLiteDataReader)

    Id = rdr(“id”)
    Name = rdr(“name”)
    Email = rdr(“email”)

End Sub

With this is place, we’re ready to create the Load(Integer) sub. 

Public Sub Load(ByVal UID As Integer)

    Dim Query As New _
      SQLiteCommand(“SELECT id, name, email FROM user WHERE id=?”, Conn)
    Dim Reader As SQLiteDataReader

    Query.CreateAndAddUnnamedParameters()
    Query.Parameters(0).Value = UID
    Query.Prepare()
    Reader = Query.ExecuteReader
    If Reader.Read Then
        Populate(Reader)
    End If

End Sub

The SQLite ADO.NET provider supports prepared statements and parameter substitution, as you can see from the text of the SQLiteCommand object.  Coming from the native C interface to this library, I was a little thrown by this provider’s behavior.  In the C interface, the Prepare method would be called before assigning parameters, parameters assigned, and then the query executed. 

In this interface, the CreateAndAddUnnamedParameters method is called, then parameters are assigned, and finally the Prepare method is called.  While I’m sure that the author had his reasons, the inconsistency caused a great deal of vexation, and the method name is excessively wordy.

{mospagebreak title=Saving a Record}

Saving a record is slightly more complicated, because the query can be either an update or an insert.  In the case of an insert, it’s important for us to retrieve the value of the newly created primary key.  SQLite has an SQL function called LAST_INSERT_ROWID() which will return this value for us.

Public Sub Save()

  Dim Query As New SQLiteCommand()

  Query.Connection = Conn
  If Id > 0 Then
    Query.CommandText = “UPDATE user SET name=?, email=? WHERE id=?”
    Query.CreateAndAddUnnamedParameters()
    Query.Parameters(0).Value = Name
    Query.Parameters(1).Value = Email
    Query.Parameters(2).Value = Id
    Query.Prepare()
    Query.ExecuteNonQuery()
  Else
    Query.CommandText = “INSERT INTO user (name, email, password) ” _
                        + “VALUES (?, ?, ‘*’)”
    Query.CreateAndAddUnnamedParameters()
    Query.Parameters(0).Value = Name
    Query.Parameters(1).Value = Email
    Query.Prepare
    Query.ExecuteNonQuery()
    Query.CommandText = “SELECT LAST_INSERT_ROWID()”
    Id = Query.ExecuteScalar
  End If

End Sub

The last of our storage methods is Delete().

Public Sub Delete()

  Dim Query As New SQLiteCommand(“DELETE FROM user WHERE id=?”, Conn)
 
  Query.CreateAndAddUnnamedParameters()
  Query.Parameters(0).Value = Id
  Query.Prepare()
  Query.ExecuteNonQuery()

End Sub

For the sake of completeness, I’ll also show the implementations of the methods for password and account verification handling.  The first function that’s necessary is the password setting function.  Passwords will be stored as md5 encoded strings, so even if the contents of the user table are compromised, password information will not be divulged.  In VB.NET, this is a two step process.  First, the password must be encoded:

Private Function HashPassword(ByVal s As String) As String

  Dim tmpSource As Byte()
  Dim tmpHash As Byte()

  tmpSource = ASCIIEncoding.ASCII.GetBytes(s)
  tmpHash = New Cryptography.MD5CryptoServiceProvider.ComputeHash(tmpSource)
 
  Return BitConverter.ToString(tmpHash)

End Function

The result of this function is a 47 character string which consists of the hexadecimal representation of the MD5 hash of our password.  Each hexadecimal number is separated by dashes.

{mospagebreak title=Storing it in the Database}

With the password encoded, even if people can read the database file they will have some difficulty reverse engineering the hash to determine a password.  Now we can store it in the database.

Public Sub SetPassword(ByVal pwd As String)

  Dim Query As New SQLiteCommand(“UPDATE user SET password=? WHERE id=?”, Conn)
  Dim p As String

  p = HashPassword(pwd)
  Query.CreateAndAddUnnamedParameters()
  Query.Parameters(0).Value = p
  Query.Parameters(1).Value = Id
  Query.Prepare()
  Query.ExecuteNonQuery()

End Sub

So that users can log in to our application, we need a way to verify user names and passwords. The Authenticate method takes the user name and password as arguments, verifies their authenticity, and if they’re good, it will also populate the user object.

Public Function Authenticate(ByVal N As String, ByVal P As String) As Boolean

  Dim Query As New SQLiteCommand(“SELECT id, name, email FROM user ” +
                                 ” WHERE name=? AND password=?”, Conn)
  Dim Reader As SQLiteDataReader
  Dim pwd As String

  pwd = HashPassword(p)
  Query.CreateAndAddUnnamedParameters()
  Query.Parameters(0).Value = N
  Query.Parameters(1).Value = pwd
  Query.Prepare()
  Reader = Query.ExecuteReader

  If Reader.Read Then
    Populate(Reader)
    Return True
  Else
    Return False
  End If

End Function 

We also need to assure uniqueness for our user names.  The database schema itself only enforces uniqueness for the id.  We could put a UNIQUE constraint on the name field, but then our application would only die with a cryptic error, and it would really be better to inform the user of the problem gracefully and give them a chance to change things.  Thus our Unique function will check on the requested name to see if its already in use.

Public Function Unique(ByVal requested As String) As Boolean

  Dim Query As New SQLiteCommand()
  Dim count As Integer

  Query.Connection = Conn
  Query.CommandText = “SELECT COUNT(id) FROM user WHERE name=?”
  Query.CreateAndAddUnnamedParameters()
  Query.Parameters(0).Value = requested
  Query.Prepare()
  count = Query.ExecuteScalar()

  If count = 0 Then
    Return True
  Else
    Return False
  End If

End Function

I now have a complete User object which can maintain the membership on my website.  Because I’ve used the SQLite database, I can do this without having to configure a complicated database connection.  This can save me considerable money in a web hosting situation, and reduce my system administration overhead if I run this application on my own server. 

{mospagebreak title=The Last Step}

The last step which I haven’t shown is the connection to the database.  Like other providers a connection string is necessary. Because the database is disk based rather than server based, the connection string is very different.  The following command connected to the database in my sample application:

conn = New SQLiteConnection(“Data Source=c:accountsuser.db;Version=3″)
conn.Open()

This is pretty much the minimal connection string necessary. Using an absolute path to the database is mandatory. When I tried to refer to a file in the application folder as a local file, the provider attempted to look for it in my systems folder. Likewise, if I excluded the version parameter the provider made random and inaccurate guesses as to the version of the database I was using.  Make sure to match this version number to the major version number of the client you use to create the database.

Using SQLite on Windows requires slightly more administrative skill than on other platforms. During a transaction (such as an Insert or Update), the database changes are stored in a journal file until the entire transaction can be completed and written to the database. The web server process will need write access to the file, and permission to create files in the database directory. This permissions management can’t be easily accomplished via an FTP connection, so you’ll need console or remote desktop access to your web server. Keep this in mind if you’re considering using SQLite in a hosted application environment.

Getting SQLite

SQLite is down-loadable from http://www.sqlite.org. The ADO.NET data provider I used can be downloaded from http://sourceforge.net/projects/adodotnetsqlite/. Other providers are available, including COM objects which can be used from traditional ASP pages.

If your interest lies instead with desktop applications, SQLite is very easy to deploy. For Windows applications the engine is encapsulated in a DLL which must be distributed with your program.  No special configuration is needed to give access to the SQLite databases, so even including the library in a ZIP file with your executable will be sufficient for distribution. 

3 thoughts on “Using SQLite for Simple Database Storage

  1. Hello!

    Great article here. SQLite really is a simple, fast and powerful database and using on small websites to reduce cost off using large databases (SQL Server, for instance) is great and have to be discussed and exemplified.

    Great work !

    Thanks.

  2. Its a very helpful article…I have been looking for database designed with SQLite..and atlast found this and very happy with it.Its very informative.

    Thanks
    Angel

  3. First of all: Really good article. I was looking for something like this.

    I have one problem though:
    My current VB.NET application has a DLL that takes care of the connections with the SQ Lite databases. With this tutorial I have changed the DLL.

    Currently the DLL is putting the result of a query into a data-table. This data table can be used by the main application for further actions.

    With the new format (your tutorial) I have problems getting the result (the reader) into the data table:

    comm = New SQLiteCommand(Query, db) ‘Everything set and works.
    Dim reader As SQLiteDataReader = comm.ExecuteReader()
    Dim dt As New DataTable
    ‘Everything fine till the next line
    dt.Load(reader)

    This dt.Load(reader) gives the error:
    A first chance exception of type ‘System.Data.ConstraintException’ occurred in System.Data.dll

    My Visual Studio is in Dutch, so I hope I can translate the Inner Exception:
    Limitations could not be enabled. On or more rows contain values that are not compatible to not-null values, unique values or different keys

    Do you have any idea?

    Thanks in advance

[gp-comments width="770" linklove="off" ]