Deployment guide

SQLite

Overview

This guide covers the deployment and use of SQLite on Linux using SecureAnyCloud 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 SecureAnyCloud support with guaranteed 24 hour response SLA

Prerequisites

You need:

An active AWS account
An active subscription to the SQLite listing on the AWS Marketplace
An EC2 key pair for SSH access
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 of disk space.

Launch the AMI

Open the AWS Marketplace and search for "SQLite SecureAnyCloud".
Select Continue to Subscribe, accept the terms, then select Continue to Configuration.
Choose your Region and Software Version.
Select Continue to Launch.
Choose Launch through EC2 for full control over instance configuration.
Select your instance type. t3.small is recommended.
Configure storage with a minimum of 20 GB gp3.
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, so SSH is the only access you need.

Select your EC2 key pair and launch the instance.

Connect via SSH

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

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

To switch to the root user, run:

sudo su -

Launch SQLite

Start the interactive SQLite shell:

sqlite3

This opens SQLite with an in-memory database. To create or open a persistent database file, pass its path:

sqlite3 /opt/sqlite/mydata.db

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 the 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 a 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: On first boot, the automatic OS package update script can update component versions.

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 bring the image fully up to date. To disable it, remove 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

Check whether SQLite is installed: which sqlite3
If it is not in your PATH, use the full path.
If it is missing, install it: yum install sqlite or apt install sqlite3

Database file is locked

Make sure only one process writes to the database at a time.
Check for other processes that access the file: fuser /opt/sqlite/mydata.db
Remember that SQLite supports concurrent reads but only one writer at a time.

Permission denied on database file

Check the file ownership: ls -la /opt/sqlite/mydata.db
If needed, change the ownership: chown ec2-user:ec2-user /opt/sqlite/mydata.db
Make sure the directory is writable.

Database file is corrupt

Run the integrity check: sqlite3 mydata.db "PRAGMA integrity_check;"
If the file is corrupt, restore from your most recent backup.

Security Recommendations

Restrict SSH access. Allow port 22 only from trusted IP addresses.
Protect database files. Set appropriate file permissions, for example chmod 600 for sensitive databases.
Back up regularly. Copy database files to S3 for disaster recovery.
Use parameterized queries. They prevent SQL injection in your applications.
Keep SQLite updated. Apply security patches when they are available.
Encrypt sensitive databases. Consider the SQLite Encryption Extension (SEE) for encryption at rest.

Support

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

Email: sales@secureanycloud.com

Website: secureanycloud.com

Support hours: 24/7 with guaranteed 24 hour response SLA

Deploy on AWS

Launch SQLite with 24/7 support from SecureAnyCloud.

View on Marketplace

Need Help?

Our support team is available 24/7.

sales@secureanycloud.com