Learn SQLite - Top and Common Commands to Get Started

Key SQLite Commands for New Users

Contents

1. About SQLite

2. Set Up

3. Application

4. Access

5. User Management

6. Basic Operations

7. Data Operations

8. Maintenance

9. Advanced

10. Security

11. Monitoring and Performance

---

About SQLite

Overview 

• SQLite is a C-language library 

• SQLite that implements a small, fast, self-contained, high-reliability, full-featured SQL database engine

• SQLite is serverless, which means that it does not require a separate server process and reads and writes directly to ordinary disk files

• Commonly used in embedded systems, mobile applications, and small to medium-sized web apps 

Key Features

• Lightweight

  • Minimal setup and administration

  • Easy to get started and to integrate into apps

• Serverless

  • Operates as a library

  • Eliminates the need for a standalone (separate) server process, list most databases

• Cross-Platform

  • Compatible with various operating systems including Windows, Linux, macOS, iOS, and Android

• ACID Compliance

  • Ensures atomicity, consistency, isolation, and durability of transactions

• Single Database File

  • Entire database is stored in a single file, simplifying distribution and backups

• Zero Configuration

  • No setup or administration required

  • Ideal for embedded applications

Comparison to Alternatives

• MySQL

  • MySQL is more suitable for larger, multi-user applications requiring high performance and scalability

  • SQLite is better for local storage and smaller-scale apps

• PostgreSQL

  • Offers more advanced features, better compliance with SQL standards, and superior concurrency 

  • SQLite is simpler and more lightweight, ideal for embedded systems

• MariaDB

  • Provides enterprise-level features and performance

  • SQLite is more portable and easier to manage for smaller, single-user apps

• MongoDB

  • A NoSQL database designed for high flexibility and scalability with unstructured data

  • SQLite is better suited for simple, structured data storage and applications requiring minimal overhead

---

Set-Up

Check Installation and Version

$ sqlite3 --version

Output:

3.37.2 2022-01-06 13:25:41 872ba2566d82a20c224ca33249231912ad82971edc46b29818d5dalt1

Installation 

$ sudo apt update
$ sudo apt install sqlite3

Uninstallation 

$ sudo apt remove sqlite3
$ sudo apt autoremove

---

Application 

Start-Up

• Since SQLite does not run as a service, you do not need to start it up

• You simply invoke it using the sqlite3 commands through the CLI

Check Status

Since SQLite is not a service, it doesn't have a status to check

Check Port

SQLite is not a server-based database, so it doesn't listen on a port

Power-Off

There is no need to power off SQLite since it doesn't run as a service

---

Access

Single Login into Database

$ sqlite3 database_name.db

Persist Login into Database

• SQLite doesn't require persistent login management since it is a file-based database

• Your databases are each one file

• You can simply access your databases by accessing the file directly

---

User Management

• SQLite does not support creating users and managing privileges 

• This is because it is designed to be a simple, file-based database engine

• User management is handled by the operating system's file permissions

---

Basic Operations

List Databases 

$ ls *.db

Create Database

To create a database, simply provide a filename with the .db extension using the sqlite3 command:

• The same command is used to access an existing database

• This means that the command is more like a create if not exists command 

• When you run this command below, it will take you to a SQL command line interface, which will allow you to execute SQL commands against your database. Once inside, we can execute any CRUD action.

• If you run the create database command below, but you do not create any tables and exist, the database will NOT be created since there is nothing that has been written inside the file to alter its default state  - you must at least create one database table for the database file to persist.

$ sqlite3 new_database.db

Example:

$ sqlite3 company.db

Clear Database Data

DELETE FROM table_name;

Delete Database

$ rm database_name.db

---

Data Operations

Create Table

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

Example:

CREATE TABLE departments (
    dept_id INTEGER PRIMARY KEY AUTOINCREMENT,
    dept_name TEXT NOT NULL UNIQUE
);
CREATE TABLE employees (
    emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
    emp_name TEXT NOT NULL,
    emp_email TEXT UNIQUE,
    dept_id INTEGER,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Insert Data

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example:

INSERT INTO departments (dept_name) VALUES
    ('Finance'),
    ('Human Resources'),
    ('Marketing');
INSERT INTO employees (emp_name, emp_email, dept_id) VALUES
    ('John Doe', 'john.doe@example.com', 1),
    ('Jane Smith', 'jane.smith@example.com', 2),
    ('Michael Johnson', 'michael.johnson@example.com', 3);

Update Data

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

UPDATE employees
SET emp_email = 'john.doe.new@example.com'
WHERE emp_name = 'John Doe';

Delete Data

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM employees
WHERE emp_name = 'Michael Johnson';

Query Data

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

SELECT emp_name, emp_email, dept_name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.dept_id;

---

Maintenance

Backup Database

$ cp database_name.db backup_database_name.db

Restore Database

$ cp backup_database_name.db database_name.db

Optimize Database

To optimize an SQLite database:

VACUUM;

Check Logs

• SQLite does not have a log file by default

• Any logging would need to be implemented by the application using SQLite

---