SQLite: The Universal SQL Database Engine

Linode
Linode
Aug 15, 2017 · 3 min read

By Steven J. Vaughan-Nichols

Image for post
Image for post
Hidden away in code, SQLite brings database power to many programs.

Today’s quiz is: What’s the most popular database of all? MySQL? Nope. Oracle Database? Nah. Microsoft SQL Server? Try again. IBM DB/2? Wrong. Arguably the most popular database of all is SQLite.

Never even heard of it? Find that hard to believe? SQLite’s inside every smartphone, macOS and Windows; and the Chrome, Firefox, and Opera web browser. The reason you haven’t heard of it is because it’s hidden inside the code of numerous operating systems and programs. There, the SQLite C library provides database services for applications.

This public-domain — not open-source — software is not a database server. Instead, embedded within programs, it provides standard SQL-92 database management server (DBMS) servers without the server part.

While that may sound like an oxymoron, it really is an atomicity, consistency, isolation, and durability (ACID) compliant database. SQLite uses PostgresSQL as its reference platform.

D. Richard Hipp created in 2000 SQLite while working as a US Navy contractor. His goal was to create a library that could deliver database functionality without a server or a database administrator. He was successful, which is why SQLite is so omnipresent today.

Technically, SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It’s a zero-conf database. In other words, the user doesn’t need to do anything to set up the database or interact with it. All that must be taken care of by the developer.

As you’d expect from a C library, SQLite is a small library. With all features enabled, the SQLite library size can fit in under 500KB of RAM. If optional features are omitted, SQLite can be fit in under 300KB. SQLite can also be made to run in minimal stack space (4KB) and minimal heap (100KB). This makes SQLite popular for memory-constrained devices such as smartphones.

As with any database, there’re tradeoffs between memory usage and speed. SQLite runs faster with more memory, but even in minimal memory environments it performs well.

Of course, you don’t get everything with SQLite that you get with a full-scale DBMS. For example, SQLite stores all its definitions, tables, indices, and the data in a single database, which, in turn is held within a single file.

In addition, when the database is edited, there is no record locking. Instead the database file is locked during writing. Thus, while you can have multiple simultaneous reads of a SQLite database, writes must be performed one at a time sequentially. Starting with version 3.7, SQLite can use write-ahead logging in some, but not all, situations for simultaneous reads and writes.

So with all that in mind, when should you use SQLite and when should you avoid it?

According to SQLite’s developers, the library works well with embedded and Internet of Things devices, desktop applications, and low to medium traffic websites. Where it doesn’t work well is with client-server applications, high-volume websites, very large data-sets, or high-concurrency applications.

The bottom line is that for many programs SQLite gives you all the database power you need without any of the headaches of incorporating a full-powered DBMS into your application. It’s simple, lightweight, and fast. Consider trying it for your next project. I think you’ll be pleased with the results.

Please feel free to share below any comments or insights about your experience with or questions about using SQLlite. And if you found this blog useful, please consider sharing it through social media.

About the blogger: Steven J. Vaughan-Nichols is a veteran IT journalist whose estimable work can be found on a host of channels, including ZDNet.com, PC Magazine, InfoWorld, ComputerWorld, Linux Today and eWEEK. Steven’s IT expertise comes without parallel — he has even been a Jeopardy! clue. And while his views and cloud situations are solely his and don’t necessarily reflect those of Linode, we are grateful for his contributions. He can be followed on Twitter (@sjvn).

Linode Cube

We’re covering everything from tech news and industry…

Linode

Written by

Linode

Cloud Hosting for You. Sign up today and take control of your own server! Contact us via ticket or email for all support inquiries: https://www.linode.com/contact

Linode Cube

We’re covering everything from tech news and industry happenings to event recaps and general tips.

Linode

Written by

Linode

Cloud Hosting for You. Sign up today and take control of your own server! Contact us via ticket or email for all support inquiries: https://www.linode.com/contact

Linode Cube

We’re covering everything from tech news and industry happenings to event recaps and general tips.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store