SQLite PRAGMA Tuning

Introduction

In this lab, you will explore SQLite PRAGMA tuning to optimize database performance and reliability. You will learn how to configure key aspects of SQLite's behavior using PRAGMA statements. We will cover configuring the journal mode, enabling foreign key checks, performing integrity checks, and adjusting the cache size.

Create a Database and Configure Journal Mode

In this step, you will create a SQLite database and configure its journal mode. The journal mode controls how SQLite handles transactions and ensures data integrity.

First, open your terminal in the LabEx VM. Your default path is /home/labex/project.

Create a SQLite database named test.db and enter the SQLite shell using the following command:

sqlite3 test.db

This command creates the database file test.db (if it doesn't exist) and opens the SQLite command-line tool. You'll see a prompt like this:

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

Now, let's configure the journal mode. SQLite offers several journal modes, including DELETE, TRUNCATE, PERSIST, MEMORY, WAL, and OFF. WAL (Write-Ahead Logging) provides a good balance of performance and reliability.

Execute the following SQL command to set the journal mode to WAL:

PRAGMA journal_mode=WAL;

This command configures the database to use Write-Ahead Logging. WAL improves concurrency and performance by writing changes to a separate WAL file before applying them to the database.

To verify the journal mode, run:

PRAGMA journal_mode;

Expected Output:

wal

This confirms that the journal mode is set to WAL.

Enable Foreign Key Support

In this step, you will enable foreign key support in your SQLite database. Foreign keys enforce relationships between tables and help maintain data integrity.

Within the SQLite shell (if you exited in the previous step, reconnect with sqlite3 test.db), execute the following command:

PRAGMA foreign_keys = ON;

This command enables foreign key enforcement for the current database connection. Note that you must enable foreign keys for each new connection to the database.

To verify that foreign key support is enabled, run:

PRAGMA foreign_keys;

Expected Output:

1

This output confirms that foreign key support is enabled.

Create Tables with Foreign Key Relationship

Now that foreign key support is enabled, let's create two tables with a foreign key relationship to demonstrate its functionality.

Execute the following SQL commands to create a users table and an orders table:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    amount REAL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

These commands create two tables:

• users: Stores user information with columns id (primary key) and name.
• orders: Stores order information with columns id (primary key), user_id (foreign key referencing users.id), and amount.

The FOREIGN KEY (user_id) REFERENCES users(id) clause establishes a relationship between the orders table and the users table. It ensures that the user_id in the orders table must exist in the id column of the users table.

Test Foreign Key Constraint

In this step, you will test the foreign key constraint to see how it prevents invalid data from being inserted into the database.

First, insert a user into the users table:

INSERT INTO users (id, name) VALUES (1, 'Alice');

This command inserts a new user with id 1 and name 'Alice' into the users table.

Now, try to insert an order into the orders table with a user_id that does not exist in the users table:

INSERT INTO orders (user_id, amount) VALUES (999, 100.0);

Because foreign key checks are enabled, this command will fail with an error message:

Error: FOREIGN KEY constraint failed

This demonstrates that the foreign key constraint is working correctly, preventing you from creating an order for a non-existent user.

Next, insert a valid order with the existing user_id:

INSERT INTO orders (user_id, amount) VALUES (1, 100.0);

This command will succeed because the user_id 1 exists in the users table.

Perform an Integrity Check

In this step, you will perform an integrity check on your SQLite database to ensure that there are no inconsistencies or errors.

Execute the following command within the SQLite shell:

PRAGMA integrity_check;

This command checks the entire database for any inconsistencies or errors. If the database is healthy, the output will be ok.

Expected Output:

ok

If the database is corrupted, the output will contain error messages indicating the nature and location of the corruption.

Adjust Cache Size and Exit

In this final step, you will adjust the cache size used by SQLite and then exit the SQLite shell.

The cache size determines how much memory SQLite uses to store recently accessed data. Increasing the cache size can improve performance, especially for read-heavy workloads.

Execute the following command to set the cache size to 4MB (4096 KB):

PRAGMA cache_size = 4096;

This command sets the cache size to 4MB for the current database connection.

To verify the cache size, run:

PRAGMA cache_size;

Expected Output:

4096

Finally, exit the SQLite shell:

.exit

This command closes the database connection and returns you to the command line.

Summary

In this lab, you have explored SQLite PRAGMA tuning. You learned how to configure the journal mode for improved performance and reliability, enable foreign key support to enforce data integrity, create tables with foreign key relationships, test foreign key constraints, perform integrity checks to identify database corruption, and adjust the cache size to optimize memory usage. These skills provide a solid foundation for working with SQLite databases effectively.