Getting Started with SQLite

Feb 2, 2024 by Robert Gravelle

SQLite is a lightweight, self-contained, and serverless relational database management system (RDBMS) that is
widely used for embedded systems, mobile applications, and small to medium-sized websites. It is easy to set up,
requires minimal configuration, and offers a powerful set of features for managing and manipulating data. In this
guide, we will walk you through the process of getting started with SQLite, including installation and using the
popular Chinook sample database for SQL examples.

Windows:

Visit the SQLite download page at https://www.sqlite.org/download.html.
Scroll down to the “Precompiled Binaries for Windows” section.
Download the appropriate precompiled binary for your system architecture (32-bit or 64-bit).
Extract the downloaded ZIP file to a location on your machine.
Open the extracted folder and locate the sqlite3.dll executable.
To make SQLite accessible from any command prompt window, add the folder containing sqlite3.dll
to your system’s PATH environment variable.

macOS:

SQLite is pre-installed on macOS, so there’s no need for a separate installation.
Open the Terminal application.
Type sqlite3 and press Enter to start the SQLite shell.

Linux:

Most Linux distributions come with SQLite pre-installed. If not, you can install it using your package
manager.

For Debian/Ubuntu: sudo apt-get install sqlite3
For Red Hat/Fedora: sudo dnf install sqlite
For Arch Linux: sudo pacman -S sqlite

Once installed, open the terminal and type sqlite3 to start the SQLite shell.

Connecting to the Chinook Database:

Now that we’ve created a new connection for the Chinook database, let’s open the connection so that we can interact with the database. To do that:

Locate the Chinook item in the Connections Pane and click on it in order to highlight it.
Select File -> Open Connection from the main menu. That should show the main database.

Querying Data

To retrieve information from the Chinook database, you can use the SELECT statement. For
example “SELECT * FROM artists;”:

select_artists_query (120K)

Filtering Data

Filtering allows you to narrow down your results. For instance, try “SELECT trackid, name, composer FROM tracks WHERE composer = ‘Ludwig van Beethoven’;”:

select_specific_artist_query (83K)

Updating Records

To update existing data, we can use the UPDATE statement, or simply edit the data in place!

editing_a_record (141K)

Inserting Records

To add a new record, no need to use the INSERT statement; in Navicat, we can simply click the Add Record button:

add_record_button (24K)

That will append a new empty row to the table, ready for data entry:

new_record (11K)

Deleting Records

Deleting a record in Navicat is equally straightforward; just highlight the row to remove and click the Delete key. A dialog will appear, asking for confirmation:

deleting_a_record (38K)

In today’s blog, we learned how to get started with SQLite, including the installation process and how to perform basic SQL operations against the
popular Chinook sample database. Whether you’re a beginner or an experienced developer, SQLite’s simplicity and versatility make it
an excellent choice for various applications. Moreover, Navicat for SQLite (or Navicat Premium) 16 is the perfect tool to explore SQLite’s
more advanced features and capabilities and to efficiently manage your data.