70.803
Interacting with SQLite through Console and Scripts

Introduction

SQLite is a popular software library that provides a relational database management system (RDBMS) that is designed to be embedded into software applications. It is a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is a file-based database system, which means that the entire database is stored in a single file on the file system of the host computer.

SQLite supports most of the standard SQL syntax and provides a lightweight and fast alternative to larger, more complex database systems. It is a widely used database technology in embedded systems, mobile applications, desktop software, and web applications. SQLite is often used for small to medium-sized applications or as a local data store for larger applications that use a more powerful database server for remote data storage and retrieval.

Serverless Architecture

When we say that SQLite is “serverless”, it means that it doesn’t require a separate server process to be running in order to access and manipulate the data in the database. Instead, SQLite is designed to be embedded directly into the application that uses it. This means that the database operations are performed directly by the application, without the need for a separate server process to be running in the background.

Additionally, SQLite is a “file-based” database system, which means that the entire database is stored in a single file on the file system of the host computer. This makes it easy to move the database between different machines or to back it up, since it’s just a matter of copying the file. The file-based approach also makes it easy to set up and use SQLite, since there is no need to install or configure a separate database server or create a separate database instance.

SQLite being serverless and file-based makes it a lightweight, self-contained, and easy-to-use database system that can be embedded into a wide range of applications, without the need for a separate database server or complex setup.

SQLite can be integrated into information systems through:

1. Language-specific APIs
   • JDBC for Java applications
   • Python’s sqlite3 module
   • Node.js adapters
2. Embedded Applications
   • Mobile applications
   • Edge computing systems
   • IoT devices
3. Development Tools
   • GUI administration tools
   • Command-line utilities
   • ORM frameworks

Installing SQLite Command Line Tools

The tools below do not need to be installed when accessing a SQLite database from an application written in Java, C++, R, Python, and most other languages. These language include a package or library that contains all the code necessary to access a SQLite database.

However, to run scripts, you must install the command line tools. Of course, you can access the same database from an application, R programs, R Notebooks, and the command line. This is often useful to inspect a database that’s created by an application.

The process of installing SQLite can vary depending on your operating system and how you plan to use it. Here are some general steps to follow:

Installing SQLite on Windows

• Go to the SQLite download page: https://www.sqlite.org/download.html
• Under the “Precompiled Binaries for Windows” section, download the appropriate version of SQLite for your Windows operating system (32-bit or 64-bit).
• Unzip the downloaded file and extract the contents to a directory on your computer.
• Add the directory where you extracted SQLite to the system’s PATH environment variable.

Installing SQLite on Mac

• Install Homebrew, if you don’t have it already, by following the instructions on the Homebrew website: https://brew.sh/
• Open a terminal window and run the following command: brew install sqlite
• Wait for Homebrew to download and install SQLite.

Installing SQLite on Linux

• Open a terminal window and run the following command to update the package list:

  sudo apt-get update

• Run the following command to install SQLite:

  sudo apt-get install sqlite3

• Wait for the installation to complete.

After installation, you can start using SQLite by running the sqlite3 command in your terminal or command prompt. This will launch the SQLite shell, where you can create, open, and manipulate SQLite databases.

Cloud SQLite

As an alternative to installing SQLite locally, you can upload SQLite database files to sqliteonline.com, or, use sqliteonline.com to create databases, run scripts, and download database files.

Creating a New Database

Creating a new database is SQLite is simple: open a database file that does not exist and SQLite will create a new database with that file name.

% sqlite3 customerDB.sqlitedb

SQLite database files can have any extension, but .sqlitedb and .db are most common.

Worked Example

In the worked example below, we create a new database using a creation script through the management console for SQLite. The script is generic and standard SQL, so it will work just as well on any other relational database, including MySQL, Oracle, and SQL Server. After creation of the database, sample data is added to the database via the script, although this could have also been done via imports of CSV files. Finally, we execute a set of queries against the sample data in the database.

Create Database

Download the organization database creation script: create-orgdb.sql. A script is a plain text file containing SQL statements.

Using either the command line tools sqlite3 or the cloud version of SQLite, source (run) the script to execute all of the SQL statements in the script. This will create the database with sample data.

Inspect the tables in the database either by studying the SQL creation code or listing the tables. It is a useful practice to visualize the database structure by drawing an Entity-Relationship or UML diagram.

The tutorial below will show you how to create the database, connect to it, and inspect the tables. Watch it only after you attempted to do this yourself. It assumes that you have the SQLite tools installed locally on your computer.

Build Queries

Once you are familiar with the database, use the interactive command line tool to build SQL queries for the following questions:

Query 1

Find the distinct number of workers who work in the HR department and who earn more than ₹225,000.

Click for Solution

select distinct count(*) as 'NumWorkers'
  from Worker as w
 where w.department = 'HR'
   and w.salary > 250000;
    

Query 2

Find the last name and title of all workers and the department they work in who earn less than the average salary.

Click for Solution

select last_name, worker_title
  from Worker AS w JOIN Title AS t ON (w.WORKER_ID = t.WORKER_REF_ID)
   and w.salary < (select avg(salary) from Worker);
    

Query 3

What is the average salary paid for all workers in each department? List the department, the average salary for the department, and the number of workers in each department. Name the average column ‘AvgSal’ and the number of workers column to ‘Num’.

Click for Solution

select department, AVG(salary) as 'AvgSalary', count(*) as 'NumWorkers'
  from Worker
 group by department;
    

Query 4

What is the total compensation for each worker (salary and bonus) on a per monthly basis? List the name of the worker, their title, and the their monthly compensation (annual compensation divided by 12). Change the header for compensation to ‘MonthlyComp’ and round it to the nearest whole number.

Click for Solution

select last_name, ((salary + bonus_amount) / 12) as 'MonthlyComp'
  from Worker as w join Bonus as b on (w.WORKER_ID = b.WORKER_REF_ID)
    

Query 5

List the full names of all workers in all capital letters who did not get a bonus.

Click for Solution

select upper(last_name || ", " || first_name) as 'FullName'
  from Worker
 where worker_id not in (select worker_ref_id from Bonus)
 order by last_name;
    

Query 6

What are the full names of all workers who have ‘Manager’ in their title. Do not “hard code” the titles; use string searching.

Click for Solution

select upper(last_name || ", " || first_name) as 'FullName', t.WORKER_TITLE
  from Worker w, Title t 
 WHERE w.WORKER_ID = t.WORKER_REF_ID
   and t.WORKER_TITLE LIKE '%Manager%';
    

Tutorial

SQLite CLI

SQLite’s command-line interface (CLI) provides direct access to SQLite’s core functionality through a terminal-based interface. While graphical tools exist, understanding the CLI is fundamental for database administrators and developers requiring programmatic database interactions.

The SQLite CLI can be initiated in two distinct modes. For transient operations:

% sqlite3

The % above indicates that the command is typed from your operating system’s command shell or a terminal.

This creates an ephemeral, in-memory database. For persistent storage, specify a database file:

% sqlite3 library.db

Once you have launched the SQLite CLI, you are in the CLI’s command shell and no longer the operating system’s command shell. We indicate the SQLite CLI command shell with >. Note that SQLite CLI commands always start with a period (.). Any SQL statement would not start with a period.

Session termination is accomplished via the .exit meta-command:

>.exit

Database Administration Commands

SQLite provides meta-commands for database administration:

/* List all attached databases */
>.databases
main: C:\path\to\library.db r/w

/* Display available tables */
>.tables
BOOKS  AUTHORS  BORROWERS

/* Examine schema definitions */
>.schema

SQL Scripts

To execute a SQLite “script”, i.e., a sequence of SQL statements separated by semi-colons and contained in a text file, from the command-line using the sqlite3 program (download and install from sqlite.com), you can either feed the script to the sqlite3 management console program or use the .read command within the sqlite3 shell.

1. Feeding the script to sqlite3 using redirection

You can pipe the contents of your SQL script file directly to the sqlite3 program. For example, if you have a script named “my_script.sql”, you can execute it like this:

sqlite3 my_database.db < my_script.sql

This opens the specified database (“my_database.db”), reads the SQL commands from the script file (separated by semi-colons), and executes them in sequence.

2. Using Get-Content (PowerShell)

In PowerShell on Windows, you can use Get-Content to read the script and then pipe the output to sqlite3:

Get-Content my_script.sql -Raw | sqlite3 my_database.db

This opens the database and executes the SQL script.

3. Using the .read command within the sqlite3 shell

Start the sqlite3 management console shell by open a terminal or command prompt and running sqlite3 my_database.db to connect to your database (“my_database.db”). Then use the .read SQLite management command inside the sqlite3 management shell to execute the script.

sqlite3 my_database.db

sqlite> .read my_script.sql

This opens the database “my_database.db”, then reads and executes the SQL commands from text file “my_script.sql”.

Hints

• Make sure to specify the database file when using the command-line interface (e.g., sqlite3 my_database.db)
• Ensure that your SQL script file is accessible by the sqlite3 program
• Commands starting with a period (.) are special commands within the sqlite3 shell and are management commands and not SQL statements
• The SQL script file must be a plain text file containing valid SQL commands separated by semicolons

Summary

Management consoles, available for every database, are an essential tool for database development and administration. Some are command-line oriented and some have graphical user interfaces. Some even allow database design through integrated diagramming tools.

Aside from allowing us to create databases and perform ad hoc queries, the provide a number of additional benefits.

• Centralized control: A management console provides a centralized interface for managing and monitoring a database, allowing administrators to configure and monitor the database more easily and efficiently.

• Efficient performance monitoring: A management console provides real-time monitoring of the database’s performance, enabling administrators to quickly identify and troubleshoot performance issues. This can help improve the overall performance of the database, reducing downtime and improving the end user experience.

• Enhanced security: A management console can help to enhance the security of a database by providing tools to manage users and permissions, set up auditing, and monitor the database for suspicious activity. This can help prevent unauthorized access to the database and protect sensitive data.

• Automated backup and recovery: Many management consoles include tools for automated backup and recovery of the database, helping to ensure data is protected in the event of a disaster or other unforeseen circumstances.

• Simplified administration: A management console can simplify database administration by providing a graphical user interface that simplifies complex database tasks. This can help reduce the need for specialized skills or knowledge, making it easier for a wider range of staff to manage and maintain the database.

Overall, a management console for a database can help streamline database management, facilitate ad hoc querying, improve performance and security, and simplify administration, making it an essential tool for database development.

---

Files & Resources

All Files for Lesson 70.803

---

References

Command Line Shell For SQLite

Kreibich, J. A. (2010). Using SQLite (1st ed.). O’Reilly Media.

SQLite Development Team. (2024). SQLite Documentation. SQLite Consortium. https://sqlite.org/docs.html

Errata

None collected yet. Let us know.

Let us know.