Tables

Lux includes relational tables built directly into the application database. Define typed fields, unique constraints, foreign keys, and indexes, then query with WHERE, ORDER BY, LIMIT, and JOIN. No external database required.

TCREATE

Create a new table with SQL-style column definitions. Types include STR, INT, FLOAT, BOOL, TIMESTAMP, UUID, JSON, and ARRAY. Constraints such as PRIMARY KEY, UNIQUE, and REFERENCES users(id) are supported inline.

TCREATE table col TYPE [constraints], col TYPE [constraints], ...

127.0.0.1:6379> TCREATE users id INT PRIMARY KEY, name STR UNIQUE, email STR UNIQUE, age INT
OK
127.0.0.1:6379> TCREATE posts id INT PRIMARY KEY, title STR, body STR, author_id INT REFERENCES users(id)
OK

TINSERT

Insert a row into a table. Fields are specified as key-value pairs. Returns the auto-incremented row ID.

TINSERT table field value [field value ...]

127.0.0.1:6379> TINSERT users name alice email alice@example.com age 30
(integer) 1
127.0.0.1:6379> TINSERT users name bob email bob@example.com age 25
(integer) 2

TSELECT (by primary key)

Retrieve rows using a SQL-like predicate. For a primary-key lookup, use WHERE id = ....

TSELECT * FROM table WHERE id = value

127.0.0.1:6379> TSELECT * FROM users WHERE id = 1
1) "name"
2) "alice"
3) "email"
4) "alice@example.com"
5) "age"
6) "30"

TSELECT

Query rows with full SQL-like syntax. Supports WHERE with operators =, !=, >, <, >=, <=, IN / NOT IN, plus ORDER BY, LIMIT, and JOIN.

TSELECT columns FROM table [JOIN table alias ON left = right] [WHERE field op value [AND ...]] [ORDER BY field ASC|DESC] [LIMIT n] [OFFSET n]

127.0.0.1:6379> TSELECT * FROM users WHERE age > 20 ORDER BY age DESC LIMIT 10
1) 1) "id"
   2) "1"
   3) "name"
   4) "alice"
   5) "email"
   6) "alice@example.com"
   7) "age"
   8) "30"
2) 1) "id"
   2) "2"
   3) "name"
   4) "bob"
   5) "email"
   6) "bob@example.com"
   7) "age"
   8) "25"

JSON and arrays

JSON and ARRAY columns are queried by dotted path, like a JavaScript object. A path that does not resolve makes the row a non-match (never an error). IS VALID tests existence, not truthiness, so 0, false, and "" all count as valid. CONTAINS tests array membership, and TINDEX declares an index on a JSON path for range queries at scale.

Encrypted columns

Add ENCRYPTED to a non-key scalar column to store its values encrypted in snapshots, tiered storage, and WAL replay records. Reads return plaintext to authorized database callers. Add SEARCHABLE when you need exact WHERE col = value lookups or a UNIQUE constraint on the encrypted column. Encrypted columns cannot be primary keys, foreign keys, sequence columns, or vector columns, cannot use DEFAULT, and cannot be ordered.

127.0.0.1:6379> TCREATE secrets id UUID PRIMARY KEY, token STR ENCRYPTED, email STR UNIQUE ENCRYPTED SEARCHABLE
OK
127.0.0.1:6379> TINSERT secrets token "api-token" email alice@example.com
(integer) 1
127.0.0.1:6379> TSELECT * FROM secrets WHERE email = alice@example.com
1) "id"
2) "018f..."
3) "token"
4) "api-token"
5) "email"
6) "alice@example.com"

127.0.0.1:6379> TCREATE events id INT PRIMARY KEY, metadata JSON, tags ARRAY
OK
127.0.0.1:6379> TINSERT events metadata '{"plan":{"tier":"pro"},"count":0}' tags '["a","b"]'
(integer) 1
127.0.0.1:6379> TSELECT * FROM events WHERE metadata.plan.tier = pro
127.0.0.1:6379> TSELECT * FROM events WHERE metadata.count IS VALID
127.0.0.1:6379> TSELECT * FROM events WHERE tags CONTAINS a
127.0.0.1:6379> TSELECT * FROM events WHERE id IN ( 1 2 3 )
127.0.0.1:6379> TINDEX events metadata.plan.tier STR
OK

TUPDATE

Update rows matching a predicate. Only the provided fields are modified.

TUPDATE table SET field value [field value ...] WHERE field op value [AND ...]

127.0.0.1:6379> TUPDATE users SET age 31 WHERE id = 1
(integer) 1

TDELETE

Delete rows matching a predicate.

TDELETE FROM table WHERE field op value [AND ...]

127.0.0.1:6379> TDELETE FROM users WHERE id = 2
(integer) 1

TDROP

Drop an entire table and all its data.

127.0.0.1:6379> TDROP users
OK

TCOUNT

Return the number of rows in a table.

127.0.0.1:6379> TCOUNT users
(integer) 2

TSCHEMA

Show the schema of a table, including field names, types, and constraints.

127.0.0.1:6379> TSCHEMA users
1) "id INT PRIMARY KEY"
2) "name STR UNIQUE"
3) "email STR UNIQUE"
4) "age INT"

TLIST

List all tables in the database.

127.0.0.1:6379> TLIST
1) "users"
2) "posts"

Full example

Create a users table and a posts table with a foreign key, insert data, and query with a join.

127.0.0.1:6379> TCREATE users id INT PRIMARY KEY, name STR UNIQUE, email STR UNIQUE, age INT
OK
127.0.0.1:6379> TCREATE posts id INT PRIMARY KEY, title STR, body STR, author_id INT REFERENCES users(id)
OK

127.0.0.1:6379> TINSERT users name alice email alice@example.com age 30
(integer) 1
127.0.0.1:6379> TINSERT users name bob email bob@example.com age 25
(integer) 2

127.0.0.1:6379> TINSERT posts id 1 title "Hello World" body "My first post" author_id 1
(integer) 1
127.0.0.1:6379> TINSERT posts id 2 title "Lux Tables" body "Tables are great" author_id 1
(integer) 2
127.0.0.1:6379> TINSERT posts id 3 title "From Bob" body "Bob's post" author_id 2
(integer) 3

127.0.0.1:6379> TSELECT * FROM posts p JOIN users u ON p.author_id = u.id WHERE p.author_id = 1
1) 1) "id"
   2) "1"
   3) "title"
   4) "Hello World"
   5) "body"
   6) "My first post"
   7) "author_id"
   8) "1"
   9) "users.name"
  10) "alice"
  11) "users.email"
  12) "alice@example.com"
  13) "users.age"
  14) "30"
2) 1) "id"
   2) "2"
   3) "title"
   4) "Lux Tables"
   5) "body"
   6) "Tables are great"
   7) "author"
   8) "1"
   9) "users.name"
  10) "alice"
  11) "users.email"
  12) "alice@example.com"
  13) "users.age"
  14) "30"

SDK example

The TypeScript SDK exposes a higher-level table builder. Reads and mutations return { data, error }, and realtime queries return a subscription object.

import Lux from "@luxdb/sdk";

const lux = new Lux("lux://127.0.0.1:6379");

await lux.call("TCREATE", "users", "id INT PRIMARY KEY, name STR UNIQUE, email STR UNIQUE, age INT");
await lux.call("TCREATE", "posts", "id INT PRIMARY KEY, title STR, author_id INT REFERENCES users(id)");

const { data: insertedId, error: insertError } = await lux
  .table("users")
  .insert({ name: "alice", email: "alice@example.com", age: 30 });

const { data: rows, error: rowsError } = await lux
  .table("users")
  .select()
  .gt("age", 20)
  .order("age", { ascending: false })
  .limit(10);

const { data: updated } = await lux
  .table("users")
  .update({ age: 31 })
  .eq("id", insertedId!);

const { data: deleted } = await lux
  .table("users")
  .delete()
  .eq("name", "alice");

const { data: joined } = await lux
  .table("posts")
  .select()
  .join("users", "u", "posts.author_id", "u.id")
  .where("u.name", "=", "alice")
  .run();

if (insertError || rowsError) throw insertError ?? rowsError;

Build on Lux without guessing.