postgresql

Overview

Use postgresql for a consistent PostgreSQL workflow: hidden-password connections, schema and row inspection, focused changes, exports, native SQL and logical backups without shell tools.

PostgreSQL without flattening its model

PostgreSQL is a client-server relational database with strong typing, transactions and a rich native SQL language. A database can contain schemas, and schemas contain tables and other objects, so “schema” here is also a database namespace—not only a description of columns. Use the short surface for inspection and routine changes, then native SQL after : when PostgreSQL-specific features matter.

Syntax

postgresql [options] · postgresql : <sql>

Reference

Inspection

-l lists databases, -t lists public tables/views, -s shows column definitions, -n previews rows and -f searches text across one table. -o exports table rows as JSON or CSV.

Editing

-a inserts one JSON object, -w updates rows selected by one field/value pair and -d deletes matching rows. Use native SQL for joins, transactions and advanced PostgreSQL features.

Dump and restore

Because lil does not invoke system pg_dump, dump creates a logical JSON snapshot of public table rows. load restores that lil JSON format or executes a supplied SQL file. Use the server's native backup tooling when a complete PostgreSQL cluster/schema backup is required.

Connection

-c requests the password outside command history. -use reconnects to another database with the same session credentials.

Safety

Commands run with the privileges of the connected PostgreSQL user.

The lil JSON dump is data-only: schema objects and indexes are not recreated. JSON restore is transactional, and export or dump refuses a table above 100000 rows instead of writing partial data.

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.

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.

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.

Database  shop

products
orders

id	sku	name	category	price	active
1	SHOE-1	City shoe	shoes	79.90	t
2	SHOE-2	Trail shoe	shoes	109.00	t

Status    connected
Server    PostgreSQL 17
Host      localhost:5432
User      app
Database  shop

postgres
shop
template1

Column    Type           Nullable
id        bigint         no
sku       text           no
name      text           no
category  text           no
price     numeric(10,2)  no
active    boolean        no

id	sku	name	category	price
1	SHOE-1	City shoe	shoes	79.90
2	SHOE-2	Trail shoe	shoes	109.00

Saved  products.json

Inserted  1

Updated  1

Deleted  1

products
4

Saved  shop.json

Affected  2
Time      2 ms

+

Table not found: missing_table

CREATE TABLE products (id BIGSERIAL PRIMARY KEY, sku text UNIQUE NOT NULL, name text NOT NULL, category text NOT NULL, price numeric(10,2) NOT NULL, active boolean NOT NULL DEFAULT true);
INSERT INTO products(sku,name,category,price,active) VALUES ('SHOE-1','City shoe','shoes',79.90,true),('BAG-1','Canvas bag','bags',44.50,true);

Related documentation