SQLite3 Review – Great for Beginners and Those Learning SQL

Essentialsql.com SQLite3 is a compact free database you can use easily 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.



What Others Say about SQLite3
There are other free database engines you can use to learn SQL.  The most popular 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 or 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: 

May you do good and not evil 
May you find forgiveness for yourself and forgive others 
May you share freely, never taking more than you give. 

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.

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 are curious to learn more about SQLite I would recommend you start here to begin our free course on learning SQL using SQLite3.  The course assumes you know nothing about programming, so anyone, really anyone who can drag a file onto their computer, can do the course.

If you want 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 detail about SQLite.