What is SQLite3?

SQLite3 Review

SQLite3 is a compact free database you can use easily to create and use a database. Though SQLite3 is not a full-featured database, it supports a surprisingly large set of the SQL standard, and is ideal for those just starting to learn SQL as well for developers that need a simple database engine to plug into their applications. As such, SQLite has become very popular with smart phone developers.

Learn SQL with SQLite

There are several reasons I feel that SQLite3 is suitable for beginners.

The first is that you don’t need to be an expert to install and configure it. In fact, getting SQLite3 to run is as simple as downloading the program and then running a simple command.

sqlite3 EssentialSQL.db

This is all that is needed to start up the database engine and start using the essential SQL database.

Second, the software is a really simple command line interface. Now you may think that is a fault, but it isn’t, as our goal is to learn SQL. And to really learn SQL, you need to understand its commands and instructions (syntax). SQLite lets you work distraction free with the essence of SQL.

Also, SQLite runs many different computer systems such as Apple OS X, Linux, and Windows. This was important to me, since I wanted to provide everyone with easy to follow SQL lessons, and didn’t want to leave anyone out.

The last reason I recommend SQLite to beginners is that it is free to use.

Other Free SQL Database Engines

There are other free database engines you can use to learn SQL. The most popular ones are Microsoft SQLExpress, MySQL, and Oracle Express.

Each of these are excellent databases and I have no trouble recommending them to anyone. If you look at the Database Engine rankings, you’ll see these are the top three databases in popularity. All three are full featured, and truly capable of supporting very large databases.

Once you learn SQL using SQLite, my recommendation is to pick one of these three products to continue your education.

What one would you choose next? I would pick the database that your company or department uses. Chances are it is either Oracle or Microsoft SQL. If you do a lot of work on the web, then I would gravitate towards MySQL.

SQLite3 Benefits

We covered some of the ways SQLite is suited for learning SQL; however, its benefits go beyond that. Though SQLite is a compact database, it is meant for serious applications! Airbus, the airplane manufacturer, even uses it on some flight software for their A350 line of aircraft. Some benefits of SQLite3 include:

Small and Self-Contained

No additional programs or components are required for it to run. The database engine can run on any modern PC or Smartphone.

SQLite doesn’t require a separate computer process to run. IT doesn’t rely on windows services, background daemons, nor separate computer hardware.

TRANSLATION: It is simple to install and use.

Portable

It is really simple to share databases. Just copy one file to do so. This is what makes it easy for me to provide you with the essential SQL sample database.

A SQLite database file is at home on Windows as much as it is OS X.

Standards Aware

SQLite is based on the standard SQL language. Some features are omitted (see below), but those implemented closely adhere to standards.

This means what you learn will easily translate to other database engines.

Public Domain

SQLite isn’t owned by any company. Instead of a software license, the code comes with a simple blessing:

Isn’t that refreshing?

SQLite Cons

There are disadvantages to SQLite. Since it is so compact and meant to run in a small space, some trade-offs were made.

Limited Data types

SQLite doesn’t support the date or timestamp data types. This is a disadvantage as many SQL puzzles, thus learning opportunities, are out of our reach in the beginning.

SQL Language Support

There are several features not supported in SQLite. Right Outer Joins aren’t supported nor are Full Outer Joins. Since Left Outer Joins are supported, and are similar to Right Outer Joins, the opportunity to learn isn’t lost.

It also isn’t easy to alter an existing table nor are there some referential integrity checks. These are pretty important features to have to manage a production database.

Size Limitations

SQLite isn’t meant to support extremely large databases. It isn’t able to scale to hundreds of users nor store gigabytes of data. It isn’t suitable where you expect a large number of users to simultaneously modify data (high concurrency), nor where there is a large volume of transactions.

What Others Say about SQLite3

In general, I would say most reviewers have positive things to say about SQLite3. All pretty much agree that SQLite isn’t meant to power your next ERP system (a large database), but is well suited for a personal database or one hosting several users.

• SQL does what it does well. SQLite just works.
• While searching for a minimal database, alkoksha.org, found SQLite3 to be the best choice.
• SQLite.org recommends not using SQLite for a client/server application. We agree.

Learning More About SQLite3

If you want to read more about SQLite3, then I would recommend Using SQLite, by Jay A. Kreibich (affiliate link). This book is well received by the community. It is definitely more technical than my training classes, but is a great way to get into more details about SQLite.