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.5Stable
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

What it does

sqlite works directly with a database file: a practical choice for a small site, a local catalogue or a portable backup. There is no separate database server or password prompt. The command needs the PHP SQLite3 extension and filesystem permission to read and, for changes, write the file.

Practical steps

Open an existing file using sqlite -c db/shop.sqlite; -f on -c allows creating a new file. Once connected, -t lists tables, -s products shows the column structure, -n products 3 previews records and -f products "shoe" searches. -o exports one table to JSON/CSV; sqlite : SELECT ... runs native SQLite SQL. dump produces a SQL script, while load executes one; -use switches files.

Limits and safety

Opening without -f does not create a missing database, and symbolic-link paths are rejected. -a/-w/-d and native SQL can immediately alter the real file: make a copy first, especially before load or schema changes. -m optionally stores the file path in .lil.key, with -k/-K/-M to manage saved profiles. A table export stops above 100,000 rows. The example data comes from the bundled sandbox/db/shop.sqlite, not the visitor’s files.

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.5
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
sqlite -c db/shop.sqlite
Status  connected
File    sandbox/db/shop.sqlite
SQLite  (version depends on the host)
Show the current SQLite database file
sqlite
Status  connected
File    sandbox/db/shop.sqlite
SQLite  (version depends on the host)
Open an SQLite database; -f creates the file when it does not exist
sqlite -c db/new-shop.sqlite -f
Status  connected
File    sandbox/db/new-shop.sqlite
SQLite  (version depends on the host)
Open an SQLite database; -f creates the file when it does not exist
sqlite -c db/shop.sqlite -m shop-demo
Status  connected
File    sandbox/db/shop.sqlite
SQLite  (version depends on the host)
Saved   shop-demo
Open another existing SQLite database
sqlite -use db/shop.sqlite
Status  connected
File    sandbox/db/shop.sqlite
Show attached database files
sqlite -l
seq	name	file
0	main	sandbox/db/shop.sqlite
List tables and views
sqlite -t
name	type
orders	table
products	table
Show columns and data types
sqlite -s products
cid	name	type	notnull	dflt_value	pk
0	id	INTEGER	0	NULL	1
1	sku	TEXT	1	NULL	0
2	name	TEXT	1	NULL	0
3	category	TEXT	1	NULL	0
4	price	REAL	1	NULL	0
5	active	INTEGER	1	1	0
Preview table rows
sqlite -n products 3
id	sku	name	category	price	active
1	SHOE-1	City shoe	shoes	79.9	1
2	SHOE-2	Trail shoe	shoes	109.0	1
3	BAG-1	Canvas bag	bags	44.5	1
Find text across table columns
sqlite -f products "shoe" 3
id	sku	name	category	price	active
1	SHOE-1	City shoe	shoes	79.9	1
2	SHOE-2	Trail shoe	shoes	109.0	1
Export table rows
sqlite -o products -j
[
  {
    "id": 1,
    "sku": "SHOE-1",
    "name": "City shoe",
    "category": "shoes",
    "price": 79.9,
    "active": 1
  },
  {
    "id": 2,
    "sku": "SHOE-2",
    "name": "Trail shoe",
    "category": "shoes",
    "price": 109.0,
    "active": 1
  },
  {
    "id": 3,
    "sku": "BAG-1",
    "name": "Canvas bag",
    "category": "bags",
    "price": 44.5,
    "active": 1
  },
  {
    "id": 4,
    "sku": "HAT-1",
    "name": "Summer hat",
    "category": "accessories",
    "price": 25.0,
    "active": 0
  }
]
Export table rows
sqlite -o products -csv tmp/products.csv
Saved  sandbox/tmp/products.csv
Handle a missing database file
sqlite -o products -csv tmp/products.csv -f
Saved  sandbox/tmp/products.csv
Insert one row from a JSON object
sqlite -a products : {"sku":"NEW-1","name":"Sample","category":"test","price":5,"active":1}
Inserted  1
ID        5
Update matching rows from a JSON object
sqlite -w products sku HAT-1 : {"price":24}
Updated  1
Delete matching rows
sqlite -d products sku HAT-1
Deleted  1
Execute native SQL
sqlite : SELECT category, COUNT(*) AS total FROM products GROUP BY category
category	total
accessories	1
bags	1
shoes	2
Time  (varies) ms
Create a schema and data dump
sqlite dump tmp/shop-backup.sql
Saved  sandbox/tmp/shop-backup.sql
Execute SQL from a file
sqlite load db/sqlite.sql
SQLite error: table products already exists
Save the current database in .lil.key; default name is the file name
sqlite -m shop-demo
Saved  shop-demo
List saved databases or inspect one
sqlite -k
No saved connections
Open a saved database
sqlite -K missing
Saved connection not found: missing
Remove a saved database
sqlite -M missing
Saved connection not found: missing
Close the selected database file
sqlite -e
+
Open an SQLite database; -f creates the file when it does not exist
sqlite -c db/missing.sqlite
Database file not found
Open another existing SQLite database
sqlite -c db/shop.sqlite -use db/missing.sqlite
Database file not found
Show columns and data types
sqlite -s missing_table
Table not found: missing_table

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.52026-11-01

Production refinement

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

# Version history