sqlite

sqlite opens a database file directly, inspects schema and rows, edits data, exports tables, runs native SQL and creates SQL dumps.

DatabaseVersion 1.1.4Stable
lil-terminal

Overview

Use sqlite directly on database files: open a file, inspect tables and columns, preview or edit rows, export data, run native SQL and create SQL backups without a database server.

A database that lives in a file

SQLite is a relational SQL database stored in an ordinary file and embedded into the process that opens it. There is no separate database server or network login, which makes it excellent for learning, local tools and many small-to-medium applications. The simplicity does not remove database rules: transactions, indexes, constraints, file permissions and concurrent writers still matter.

Syntax

lil-terminal
sqlite [options] · sqlite : <sql>

Reference

sqlite

show the current SQLite database file

sqlite -c <file> [-f] [-m [name]]

open an SQLite database; -f creates the file when it does not exist

-use <file>

open another existing SQLite database

-l

show attached database files

-t

list tables and views

-s <table>

show columns and data types

-n <table> [rows]

preview table rows

-f <table> <text> [rows]

find text across table columns

-o <table> [-j|-csv] [file] [-f]

export table rows

-a <table> : <json>

insert one row from a JSON object

-w <table> <field> <value> : <json>

update matching rows from a JSON object

-d <table> <field> <value>

delete matching rows

-m [name]

save the current database in .lil.key; default name is the file name

-M <name>

remove a saved database

-k [name]

list saved databases or inspect one

-K <name>

open a saved database

-e

close the selected database file

sqlite : <sql>

execute native SQL

sqlite load <file.sql>

execute SQL from a file

sqlite dump [file.sql] [-f]

create a schema and data dump

Connection

-c opens an existing file. Add -f to create a new SQLite database. The opened database file becomes the active $ file when the current core supports that feature. -use switches to another existing file.

Inspection and editing

-l shows attached database files, -t lists tables/views, -s shows PRAGMA table_info, -n previews rows and -f searches text across one table. -a, -w and -d provide small JSON-based row edits; : remains available for complete SQL.

Export and backup

-o exports one table as JSON or CSV. dump creates a SQL schema/data script and load executes an SQL file.

Safety

SQLite writes directly to the selected file. Keep a backup before schema changes or destructive SQL.

Table export refuses more than 100000 rows instead of writing a partial file; the SQL dump remains the full schema-and-data backup path.

Saved connections

Without -m, connection data remains scoped to the current PHP session. -m [name] explicitly stores the current profile in the protected .lil.key file (0600), -K <name> reconnects from it, -k lists or inspects profiles without exposing a stored password, and -M <name> removes one. kill removes .lil.key together with the other core state. Treat the file as sensitive because an opted-in profile can contain database credentials.

Interactive sandbox preview

Run the prepared examples directly on the page. Their results are prebuilt and shown in the browser; no terminal command is executed on the server.

Open a SQLite file and inspect data

Install and manage

Install the current compatible version. Choose Base for the complete feature set or Minimum for the reduced package; use upgrade to update a module that is already installed.

Base and Minimum are two packages of the same current module release, not separate command versions.

Base package

Install the complete package with local command reference and every published mode.

install sqlite
Minimum package

Install the reduced package. Local reference is omitted; help <cmd> -i opens the current server reference.

install sqlite -m
Exact public version

Install exactly this published version.

install sqlite -v 1.1.4
Check or update

Ask upgrade to check compatibility and replace the installed block only when needed.

upgrade sqlite
Remove module

Remove the extension block while leaving the core and unrelated modules intact.

install sqlite -u

Examples

Examples are copyable command lines, not actions executed by this website. Review paths, permissions and destructive flags before running them on a real project.

Open a SQLite file and inspect data
cd \sandbox\db | sqlite -c shop.sqlite | sqlite -t | sqlite -n products 5
mysql.sql
postgresql.sql
redis.txt
shop.sqlite
sqlite.sql

Status  connected
File    sandbox/db/shop.sqlite
SQLite  3.x

products
orders

id	sku	name	category	price	active
1	SHOE-1	City shoe	shoes	79.9	1
2	SHOE-2	Trail shoe	shoes	109	1
3	BAG-1	Canvas bag	bags	44.5	1
Sqlite · shop.sqlite
shop.sqlite
Status  connected
File    sandbox/db/shop.sqlite
SQLite  3.x
Sqlite · products
sqlite -s products
Column    Type     Not null  Key
id        INTEGER  no        PRIMARY
sku       TEXT     yes       UNIQUE
name      TEXT     yes
category  TEXT     yes
price     REAL     yes
active    INTEGER  yes
Sqlite · products
sqlite -f products "shoe" 20
id	sku	name	category	price
1	SHOE-1	City shoe	shoes	79.9
2	SHOE-2	Trail shoe	shoes	109
Sqlite · products
sqlite -o products -csv products.csv
Saved  products.csv
Sqlite · products
sqlite -a products : {"sku":"BAG-1","name":"Travel bag","price":49.90}
Inserted  1
ID        5
Sqlite · products
sqlite -w products sku BAG-1 : {"price":44.90}
Updated  1
Sqlite · products
sqlite -d products sku BAG-1
Deleted  1
Sqlite · select
sqlite : SELECT category,COUNT(*) FROM products GROUP BY category
category	COUNT(*)
accessories	1
bags	1
shoes	2
Sqlite · dump
sqlite dump shop-backup.sql
Saved  shop-backup.sql
Sqlite · load
sqlite load \sandbox\db\sqlite.sql
+
Sqlite · -e
sqlite -e
+
Handle a missing database file
sqlite -c missing.sqlite
Database file not found

File used in this example: sandbox/db/sqlite.sql

The example uses a file or directory from the protected /sandbox/ documentation workspace.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  sku TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  price REAL NOT NULL,
  active INTEGER NOT NULL DEFAULT 1
);
INSERT INTO products (sku,name,category,price,active) VALUES
('SHOE-1','City shoe','shoes',79.90,1),
('SHOE-2','Trail shoe','shoes',109.00,1),
('BAG-1','Canvas bag','bags',44.50,1);

Related documentation

Which databases can I work with from the terminal? →Is lil-terminal safe to leave on a production site? →

Current public release

v1.1.42026-08-08

Saved SQLite databases

Adds named SQLite paths in .lil.key with explicit save, inspect, reopen and remove operations.

# Version history