Databases

SQLite User Guide

| Product: SQLite

Overview

This guide covers the deployment and use of SQLite on Linux using cloudimg AMIs from the AWS Marketplace. SQLite is a lightweight, serverless, self contained SQL database engine. It requires no separate server process, stores the entire database in a single file, and is the most widely deployed database engine in the world.

What's included in this AMI:

  • SQLite3 command line interface preinstalled
  • Dedicated data volume at /opt/sqlite
  • OS package update script for keeping the system current
  • AWS CLI v2 for AWS service integration
  • Systems Manager Agent (SSM) for remote management
  • CloudWatch Agent for monitoring
  • Latest security patches applied at build time
  • 24/7 cloudimg support with guaranteed 24 hour response SLA

Prerequisites

Before launching this AMI, ensure you have:

  1. An active AWS account
  2. An active subscription to the SQLite listing on AWS Marketplace
  3. An EC2 key pair for SSH access
  4. Familiarity with EC2 instance management and SSH

Recommended Instance Type: t3.small (2 vCPU, 2 GB RAM) or larger. The minimum requirements are 1 vCPU, 1 GB RAM, and 20 GB disk space.

Step 1: Launch the AMI

  1. Navigate to the AWS Marketplace and search for "SQLite cloudimg"
  2. Click Continue to Subscribe, accept the terms, then Continue to Configuration
  3. Select your preferred Region and Software Version
  4. Click Continue to Launch
  5. Choose Launch through EC2 for full control over instance configuration
  6. Select your instance type (t3.small recommended)
  7. Configure storage: 20 GB gp3 minimum
  8. Configure your Security Group with the following inbound rules:
Port Protocol Source Purpose
22 TCP Your IP SSH access

Note: SQLite is a serverless database with no network listener. Only SSH access is required.

  1. Select your EC2 key pair and launch the instance

Step 2: Connect via SSH

Once your instance is running and has passed both status checks (2/2), connect using SSH:

ssh -i your-key.pem ec2-user@<public-ip-address>

To switch to the root user:

sudo su -

Step 3: Launch SQLite

Start the SQLite interactive shell:

sqlite3

This opens SQLite in memory. To create or open a persistent database file:

sqlite3 /opt/sqlite/mydata.db

Step 4: Create Tables and Query Data

Create a table:

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL,
    quantity INTEGER DEFAULT 0
);

Insert data:

INSERT INTO products (name, price, quantity) VALUES ('Widget A', 9.99, 100);
INSERT INTO products (name, price, quantity) VALUES ('Widget B', 19.99, 50);
INSERT INTO products (name, price, quantity) VALUES ('Gadget C', 29.99, 25);

Query data:

SELECT * FROM products;
SELECT name, price FROM products WHERE price > 10 ORDER BY price DESC;
SELECT SUM(price * quantity) AS total_value FROM products;

View table schema:

.schema products

List all tables:

.tables

Exit SQLite:

.exit

Importing and Exporting Data

Import a CSV file:

.mode csv
.import /path/to/data.csv tablename

Export to CSV:

.headers on
.mode csv
.output /opt/sqlite/export.csv
SELECT * FROM products;
.output stdout

Create a SQL dump:

sqlite3 /opt/sqlite/mydata.db .dump > /opt/sqlite/backup.sql

Restore from a SQL dump:

sqlite3 /opt/sqlite/restored.db < /opt/sqlite/backup.sql

Server Components

Component Install Path
SQLite /opt/sqlite

Note: Component versions may be updated on first boot by the automatic OS package update script.

Filesystem Layout

Mount Point Size Description
/ 38 GB Root filesystem
/boot 2 GB Operating system kernel files
/opt/sqlite 9.8 GB SQLite data directory

Key directories:

Directory Purpose
/opt/sqlite Default location for SQLite database files

Scripts and Log Files

Script/Log Path Description
initial_boot_update.sh /stage/scripts Updates the OS with the latest packages on first boot
initial_boot_update.log /stage/scripts Output log for the boot update script

On Startup

An OS package update script runs on first boot to ensure the image is fully up to date. You can disable this by removing the script and its crontab entry:

rm -f /stage/scripts/initial_boot_update.sh

crontab -e
# Delete the following line, save and exit:
@reboot /stage/scripts/initial_boot_update.sh

Troubleshooting

sqlite3 command not found

  1. Check if SQLite is installed: which sqlite3
  2. Try the full path if not in PATH
  3. Install if missing: yum install sqlite or apt install sqlite3

Database file is locked

  1. Ensure only one process is writing to the database at a time
  2. Check for other processes accessing the file: fuser /opt/sqlite/mydata.db
  3. SQLite supports concurrent reads but only one writer at a time

Permission denied on database file

  1. Check file ownership: ls -la /opt/sqlite/mydata.db
  2. Change ownership if needed: chown ec2-user:ec2-user /opt/sqlite/mydata.db
  3. Ensure the directory is writable

Database file is corrupt

  1. Try the integrity check: sqlite3 mydata.db "PRAGMA integrity_check;"
  2. If corrupt, restore from your most recent backup

Security Recommendations

  • Restrict SSH access: Only allow port 22 from trusted IP addresses
  • Protect database files: Set appropriate file permissions (chmod 600 for sensitive databases)
  • Back up regularly: Copy database files to S3 for disaster recovery
  • Use parameterized queries in applications to prevent SQL injection
  • Keep SQLite updated: Apply security patches when available
  • Encrypt sensitive databases: Consider using SQLite Encryption Extension (SEE) for data at rest encryption

Support

If you encounter any issues with this product, contact cloudimg support:

  • Email: support@cloudimg.co.uk
  • Website: www.cloudimg.co.uk
  • Support hours: 24/7 with guaranteed 24 hour response SLA