SQLite Introduction and Resources – Embedded Database System

SQLite is a relational database management system (RDBMS) embedded in a programming library, and, unlike other popular database systems, it is not a client-server database engine, and does not have a separate server process.

SQLite source code is distributed in the public domain, making its use free for both commercial and private purposes.

These facts make SQLite a very popular choice as an embedded database software solution. SQLite is the most widely deployed database engine today, used by countless applications and platforms.

Brief History

SQLite was initially designed in the spring of 2000 by D Richard Hipp during his time at defense industry giant General Dynamics. At the time, Hipp was working on software design for United States Navy guided missile destroyers.

The main goal of SQLite development was to allow an application to run without installing a database management system, or requiring a database administrator, in other words, to embed the database into the application that uses it.

SQLite version 1.0 was released in August 2000, with storage based on the GNU Database Manager (gdbm) format. Version 2.0 introduced a custom B-tree storage implementation instead of the gdbm and added transaction capabilities. SQLite version 3.0 introduced manifest typing and internationalization capabilities, along with many other improvements.

SQLite Features and Design

Since the SQLite library is an integral part of the application that uses it, the application uses simple function calls to communicate with SQLite, thus reducing latency. The entire database is stored as a single cross-platform file on the host machine.

This design approach requires less configuration than client-server databases, so SQLite is also described as zero-conf.

Multiple processes or threads can access the database concurrently, and several read requests can be processed in parallel. Write access to the database can only be granted if no other requests are being serviced. This limitation comes from the fact that SQLite uses file system permissions and locks for access control, so it locks the entire database during writing. SQLite version 3.7 introduces write-ahead logging (WAL) to allow for concurrent reads and writes. These facts mean that SQLite is not ideally suited for write-intensive deployments and tasks.

SQLite implements most of the SQL-92 standard and uses PostgreSQL as a reference platform. SQLite supports complex queries, but it has limited support for the ALTER TABLE function, since it cannot modify or delete columns.

SQLite also uses a different type system, unusual for an SQL compatible database system. Instead of declaring a type to a column, like in most SQL database systems, types are assigned to individual values. SQLite is weakly typed, for example you can insert a string value into an integer column. This feature makes the SQLite more flexible, but it is also a source of criticism, because it lacks the data integrity features provided by the statically typed columns in other SQL DBMs.

Transactions in SQLite are consistent and durable, even after system crashes or power failures. SQLite supports terabyte-sized databases and gigabyte-sized strings and blobs, while maintaining a small code footprint of less than 500 KB fully configured. SQLite has a simple and easy to use self-contained API with no external dependencies.

SQLite is a cross-platform solution, available for numerous platforms and programming languages like: Android, BSD, iOS, Linux, Mac, Solaris, VxWorks, Windows, and C, C#, C++, Clipper, Curl, Elixir, F#, Haskell, Java, JavaScript, Objective-C, OCaml, Perl, PHP, Python, Ruby, Scheme, Smalltalk, Swift, etc. It is also easy to port SQLite to other systems since it is written in ANSI-C that is relatively easy to compile.

Using SQLite

SQLite is a popular choice for database engine in cellphones, tablets, MP3 players, set-top boxes and other embedded devices because of its efficient use of memory and storage space.

Another popular use of SQLite is its use as an application file format. Rather than using a proprietary file format for storing information from an application, developers often use a SQLite database. This practice saves time and effort, as it avoids creating and troubleshooting a custom file parser, and the data is easily accessible across different platforms.

Since SQLite requires no configuration and stores data in ordinary disk files, it is also often used as a database for small and medium web sites.

Some of the applications using SQLite include the Google Chrome, Opera, Safari and Android browsers, Mozilla Firefox and Thunderbird, Skype, Adobe Lightroom and Adobe Reader, etc.

SQLite is easy to obtain and install from the official SQLite website so you can give it a try. If your work includes development in some of the described usage scenarios, you will probably need SQLite sooner or later, since it remains very popular and freely available.

What about Support for SQLite?

Free support for the SQLite is available through the public mailing list. You can browse the mailing list archives or sign up as a mailing list member.

There are other more advanced support options, such as the Annual Maintenance Subscription (AMS) or the Technical Support Agreement, but they are not free. You can check the SQLite website Professional Support section for more details.

SQLite Resources

There are many resources for SQLite available online, since it is quite popular and has numerous applications. Learning SQLite should not be a problem using these quality resources:

• SQLite Official Documentation is very detailed and you can find many useful articles and contents there.
• SQLiteTutorial.net website is a great tutorial covering everything you need to know about SQLite through extensive hands-on practices.
• Tutorials Point SQLite Tutorial is very detailed, covering everything from the SQLite installation to advanced use. This tutorial will give you a quick start with SQLite and make you comfortable with SQLite programming.
• An Introduction to SQLite on YouTube is a GoogleTechTalk by Richard Hipp. This talk provides a quick overview of SQLite, its history, its strengths and weaknesses, and describes situations where it is much more useful than a traditional client/server database.

SQLite Books

Books about SQLite are also popular, and you have a nice choice of titles available. We suggest going through the free online resources first, as they are really good, and you might not need a book after all. If you prefer the feel of a book, we have singled out a few:

• The Definitive Guide to SQLite (2006) by Mike Owens: this is the first book to devote complete coverage to the popular embedded open source database SQLite. The book acts as both an ideal tutorial and reference guide.
• Using SQLite (2010) by Jay Kreibich: with SQLite, you’ll discover how to develop a database-backed application that remains manageable in size and complexity. With this book, you’ll get a crash course in data modeling, become familiar with SQLite’s dialect of the SQL database language, and much more.

Conclusion

In conclusion, SQLite is widely used, mature, and thanks to the abundance of free online resources, it can be mastered with relative ease.

It has some obvious limitations, arising from the general principles behind its design. However, that’s sort of the whole point of SQLite — it’s meant to be lean and handle your database needs without hogging a lot of resources.

Further Reading and Resources

We have more guides, tutorials, and infographics related to coding and development:

• SQL Resources: our general SQL resource that is critical for all relational database developers.
• MySQL Introduction and Resources: another very popular database system.
• PostgreSQL Introduction and Resources: a popular database system on its own, SQLite is partly based on it.

Ultimate Guide to Web Hosting

Check out our Ultimate Guide to Web Hosting. It will explain everything you need to know in order to make an informed choice.

Ultimate Guide to Web Hosting