Embedding an SQL Database with SQLite

If you want the convenience of SQL without the size and setup hassles of a database server, embed SQLite right in your program, whatever your favorite language.
Scripting Interfaces

As mentioned earlier, many client interfaces have been developed for SQLite. To give you a taste, a Python version of the previous C example is illustrated in Listing 3, and its Perl counterpart is shown in Listing 4. It doesn't get any easier. SQLite also can be used from the shell, which makes it amenable to system administration tasks. A shell version of our stock example is provided in Listing 5.

Listing 3. Python Example

Listing 4. Perl Example

Listing 5. Shell Example

Finally, because I am not a Java, Tcl, Ruby, Delphi, Lua, Objective C, PHP, Visual Basic, .NET, Mono, DBExpress, wxWindows, Euphoria or REXX programmer, I will have to refer the likes of you who are, to the SQLite Wiki to find your respective interfaces. See cvs.hwaci.com:2080/sqlite/wiki?p=SqliteWrappers for your preferred way to talk to SQLite.

Extending SQLite

SQLite includes a nice C framework in which to create your own functions and aggregates that can be called from SQL. Some wrappers, such as the Python wrapper, allow you to use this feature to implement them in the extension's language. SQL, such as INSERT INTO orders purchase_date values CURRENT_TIME(), is a simple matter of writing a callback function that looks something like Listing 6. Then, register the function and use it as shown in Listing 7.

Listing 6. Implementation of CURRENT_TIME()

Listing 7. Using CURRENT_TIME()

All of SQLite's built-in functions, such as avg(), min(), max() and sum(), with the exception of the magical typeof(), are implemented using this API. User-defined aggregates can be added just as easily. Doing something like SELECT variance(age) from population uses a very similar approach to creating functions. This, however, is left as an exercise for the reader. Hint: the file func.c includes some excellent examples. Like functions, SQLite uses the API to implement its aggregates as well.


For administration, SQLite offers an intuitive utility program conveniently named sqlite with which users of MySQL and PostgreSQL will feel perfectly at home. It has both shell and command-line modes. Within the shell, you can view a database's tables, schema and indexes, as well as execute SQL on the command line and in external files. It also has some nice modes for viewing data and VDBE output.

Though loading and unloading data can be done within the shell, it is even easier on the command line. Given a file containing valid DDL/DML (call it dump.sql), you can load it into a database (call it db), like so:

sqlite db < dump.sql

This creates the database db if it doesn't exit. The reverse process to dump a database would be:

sqlite db .dump > dump.sql
SQLite is powerful. Its wide application, ease of use, portability, speed, scalability, small footprint and clean code base make it a library that all programmers should have in their arsenals. And given its license, there is simply no reason not to. The SQLite Project is always looking for new users and developers, and it welcomes new ideas and engaging discussion. I hope you enjoy learning about and using it as much as I have.


Michael Owens (mike@mikesclutter.com) is a chemical engineer turned programmer. He works for a real estate firm in Dallas/Fort Worth, Texas using Linux and open source to develop in-house software. He is the creator and codeveloper of PySQLite.


Geek Guide
The DevOps Toolbox

Tools and Technologies for Scale and Reliability
by Linux Journal Editor Bill Childers

Get your free copy today

Sponsored by IBM

Upcoming Webinar
8 Signs You're Beyond Cron

Scheduling Crontabs With an Enterprise Scheduler
11am CDT, April 29th
Moderated by Linux Journal Contributor Mike Diehl

Sign up now

Sponsored by Skybot