Getting Started with SQLite3

Relational databases have been in use for a long time. They became popular thanks to management systems that implement the relational model extremely well, which has proven to be a great way to work with data [especially for mission-critical applications]. In this tutorial we are going to understand how to work with a very powerful, embedded relational database management system called SQLite3.

SQLite is an amazing library that gets embedded inside the application that makes use of. As a self-contained, file-based database, SQLite offers an amazing set of tools to handle all sorts of data with much less constraint and ease compared to hosted, process based (server) relational databases. When an application uses SQLite, the integration works with functional and direct calls made to a file holding the data.

SQLite’s Supported Data Types

NULL:

NULL value.

INTEGER:

Signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

Pros of SQLite

The entire database consists of a single file on the disk, which makes it extremely portable.

Standards-aware:

Although it might appear like a “simple” DB implementation, SQLite uses SQL. It has some features omitted (RIGHT OUTER JOIN or FOR EACH STATEMENT), however, some additional ones are baked in.

Great for developing and even testing:

During the development phase of most applications, for a majority of people it is extremely likely to need a solution that can scale for concurrency. SQLite, with its rich feature base, can offer more than what is needed for development with the simplicity of working with a single file and a linked C based library.

Cons of SQLite

No user management:

Advanced databases come with the support for users, i.e. managed connections with set access privileges to the database and tables. Given the purpose and nature of SQLite (no higher-levels of multi-client concurrency), this feature does not exist.

Lack of possibility to tinker with for additional performance:

Again by design, SQLite is not possible to tinker with to obtain a great deal of additional performance. The library is simple to tune and simple to use. Since it is not complicated, it is technically not possible to make it more performant than it already, amazingly is.

After it is installed we can start the session. Open the Terminal/Command Line and enter the command sqlite3

Meta Commands

Meta Commands are used to define output format for tables, examine databases and for other administrative operations. They always start with a dot. Even .help is a meta command. You can go through the list. Here are some that will frequently come in handy:

Dropping a table means deleting the entire table. The location_history table can be deleted with the following query:

DROP TABLE location_history;

Conclusion

SQLite3 offers many advantages over other relational databases. You can read of SQLite distinctive features here. Most PHP Frameworks and many other web frameworks including Django and Ruby on Rails have SQLite3 as their default database. Being lightweight makes it preferable for local storage in web browsers. It is also employed as client storage for many Operating Systems. It is one of the most widely deployed database engines.

In the tutorial we interacted with an SQLite database system. There are GUI applications to do the same, without having to learn commands: