SQLite 3.53

Open the Marketplace in the Azure Portal, search for SQLite 3.53 and select the SecureAnyCloud publisher entry. Choose Create.

On the Networking tab, attach a network security group that allows inbound TCP 22 from your management IP range. SQLite has no daemon and no listening ports, so TCP 22 (SSH) is the only port that must be reachable from wherever you administer the VM.

Select Review + create, wait for validation, then select Create. Deployment takes around two minutes.

RG="sqlite-prod" LOCATION="eastus" VM_NAME="sqlite-01" ADMIN_USER="azureuser" GALLERY_IMAGE_ID="/subscriptions/<sub-id>/resourceGroups/azure-sac/providers/Microsoft.Compute/galleries/sacGallery/images/sqlite-3-53-ubuntu-24-04/versions/<version>" SSH_KEY="$(cat ~/.ssh/id_rsa.pub)" az group create --name "$RG" --location "$LOCATION" az network vnet create \
  --resource-group "$RG" \
  --name sqlite-vnet --address-prefix 10.96.0.0/16 \
  --subnet-name sqlite-subnet --subnet-prefix 10.96.1.0/24 az network nsg create --resource-group "$RG" --name sqlite-nsg az network nsg rule create \
  --resource-group "$RG" --nsg-name sqlite-nsg \
  --name allow-ssh --priority 100 \
  --source-address-prefixes "<your-mgmt-cidr>" \
  --destination-port-ranges 22 --access Allow --protocol Tcp az vm create \
  --resource-group "$RG" --name "$VM_NAME" \
  --image "$GALLERY_IMAGE_ID" \
  --size Standard_B2s --storage-sku StandardSSD_LRS \
  --admin-username "$ADMIN_USER" --ssh-key-values "$SSH_KEY" \
  --vnet-name sqlite-vnet --subnet sqlite-subnet --nsg sqlite-nsg \
  --public-ip-sku Standard

ssh azureuser@<vm-ip>

By the time you connect, sqlite-firstboot.service has already run on first boot, written the build-info banner and disabled itself.

Confirm that the first-boot sentinel exists. The command prints FIRSTBOOT_DONE:

sudo test -f /var/lib/sac/data/sqlite-firstboot.done && echo FIRSTBOOT_DONE

Read the build-info banner:

sudo cat /stage/scripts/sqlite-credentials.log

The banner includes SQLITE_VERSION=3.53.0, the binary, library and header paths, and a quickstart snippet.

Check the version. Expect a single line that begins with 3.53.0, followed by the build date and source ID:

sqlite3 -version

Confirm which binary the shell resolves. Expect /usr/local/bin/sqlite3:

which sqlite3

Confirm the version that pkg-config reports. Expect 3.53.0:

pkg-config --modversion sqlite3

List the libraries and headers. Expect libsqlite3.so (symlink), libsqlite3.so.0 (symlink), libsqlite3.so.0.8.6 (real file), libsqlite3.a (static) and the two header files:

ls -la /usr/local/lib/libsqlite3.* /usr/local/include/sqlite3*.h

Create a database, add three rows and read them back:

sqlite3 ~/notes.sqlite <<'SQL' CREATE TABLE notes(id INTEGER PRIMARY KEY, body TEXT NOT NULL, created TEXT DEFAULT CURRENT_TIMESTAMP); INSERT INTO notes(body) VALUES ('first note from sac'), ('second note'), ('third note'); SELECT id, body, created FROM notes ORDER BY id; SQL

The shell prints three rows with their auto-assigned ids and timestamps.

You can also open the interactive shell:

sqlite3 ~/notes.sqlite

Useful dot commands inside the shell include .tables, .schema notes, .headers on, .mode column, and .quit.

Run a full-text query against an in-memory FTS5 table:

sqlite3 :memory: <<'SQL' CREATE VIRTUAL TABLE doc USING fts5(body); INSERT INTO doc VALUES ('the quick brown fox jumps over the lazy dog'); INSERT INTO doc VALUES ('a stitch in time saves nine'); INSERT INTO doc VALUES ('the early bird catches the worm'); SELECT body FROM doc WHERE body MATCH 'fox OR bird'; SQL

The query returns two rows: the fox sentence and the bird sentence.

Extract a value from a JSON document and build a JSON object:

sqlite3 :memory: <<'SQL' SELECT json_extract('{"product":"sqlite","version":"3.53","modules":["fts5","rtree","json1"]}', '$.modules[1]'); SELECT json_object('a', 1, 'b', json_array(2, 3, 4)); SQL

The first query returns rtree, and the second returns a JSON object string.

Call the built-in math functions:

sqlite3 :memory: <<'SQL' SELECT round(pi(), 4); SELECT round(pow(2, 10)); SELECT round(sqrt(2), 6); SQL

The queries return 3.1416, 1024.0 and 1.414214.

Install gcc, then compile and run a small program that links the installed library:

sudo apt-get install -y --no-install-recommends gcc cat > /tmp/sqlite_demo.c <<'C' #include <stdio.h> #include <sqlite3.h> int main(void) { sqlite3 *db; sqlite3_stmt *stmt; sqlite3_open(":memory:", &db); sqlite3_prepare_v2(db, "SELECT sqlite_version();", -1, &stmt, NULL); sqlite3_step(stmt); printf("linked sqlite version: %s\n", sqlite3_column_text(stmt, 0)); sqlite3_finalize(stmt); sqlite3_close(db); return 0; } C gcc /tmp/sqlite_demo.c $(pkg-config --cflags --libs sqlite3) -o /tmp/sqlite_demo LD_LIBRARY_PATH=/usr/local/lib /tmp/sqlite_demo

The program prints linked sqlite version: 3.53.0.