Skip to content

Convex-Dev/convex / .claude/skills/convex-db

convex-db

Verified

Use Convex DB — a lattice-backed SQL database. Use when helping users write queries, connect via JDBC or PostgreSQL clients, create tables, insert/query data, or use the direct lattice API.

Using Convex DB

Convex DB provides SQL access over lattice data. Connect via JDBC, PostgreSQL wire protocol, or the direct lattice API.

Reference: convex-db/README.md for full documentation including replication, PostgreSQL server setup, and architecture details.

Connecting

JDBC (Java)

// In-memory
Connection conn = DriverManager.getConnection("jdbc:convex:mydb");

// Persistent (Etch-backed, survives restarts)
Connection conn = DriverManager.getConnection("jdbc:convex:file:/data/mydb.etch");

Driver auto-registers via ServiceLoader. Class: convex.db.jdbc.ConvexDriver

PostgreSQL Clients (psql, DBeaver, DataGrip, Python, etc.)

# Start the PG server
java -cp convex-db.jar convex.db.psql.PgServer -p 5432 -d mydb

# Then connect with any PG client
psql -h localhost -p 5432 -d mydb
import psycopg2
conn = psycopg2.connect(host="localhost", port=5432, dbname="mydb")

Creating Tables

CREATE TABLE users (id, name, email)

Column 0 (first column) is always the primary key. Types are inferred from inserted data.

Names follow the SQL standard by default: unquoted names fold to upper case and compare exactly, so this creates USERS. A table created through the lattice API as users must be quoted in SQL ("users"), unless the database's identifier policy is :lower (IdentifierPolicy.LOWER.applyTo(db)), as a PostgreSQL user would expect.

Inserting Data

INSERT INTO users VALUES (1, 'Alice', 'alice@example.com')

For bulk loading, use prepared statements with batch:

PreparedStatement ps = conn.prepareStatement("INSERT INTO users VALUES (?, ?, ?)");
for (int i = 0; i < 10000; i++) {
    ps.setLong(1, i);
    ps.setString(2, "Name-" + i);
    ps.setString(3, "email-" + i + "@example.com");
    ps.addBatch();
}
ps.executeBatch();

Querying

-- Point lookup (fast — O(log n) via PK index pushdown)
SELECT * FROM users WHERE id = 1

-- Filtering, sorting, pagination
SELECT name, email FROM users WHERE name LIKE 'A%' ORDER BY name LIMIT 10

-- Joins
SELECT c.name, o.amount
FROM customers c INNER JOIN orders o ON c.id = o.customer_id

-- Aggregations
SELECT department, COUNT(*), AVG(salary)
FROM employees GROUP BY department HAVING COUNT(*) > 5

Supported SQL

  • DDL: CREATE TABLE, DROP TABLE
  • DML: INSERT, UPDATE, DELETE
  • Queries: SELECT, WHERE, ORDER BY, LIMIT, OFFSET
  • Joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN
  • Aggregations: GROUP BY, HAVING, COUNT, SUM, AVG, MIN, MAX
  • Expressions: CASE WHEN, COALESCE, CAST, BETWEEN, IN, LIKE, IS NULL
  • Functions: ABS, FLOOR, CEIL, SQRT, UPPER, LOWER, TRIM, SUBSTRING, LENGTH, CONCAT

Transactions

conn.setAutoCommit(false);
stmt.execute("INSERT INTO users VALUES (2, 'Bob', 'bob@example.com')");
stmt.execute("UPDATE users SET email = 'new@example.com' WHERE id = 1");
conn.commit();    // atomic merge — all changes become visible
// or conn.rollback() to discard

Column Types

SQL TypeCVM TypeNotes
BIGINTAIntegerAny CVM integer; read as NUMERIC (BigDecimal)
INTEGERCVMLong64-bit signed integer; read as BIGINT
DOUBLECVMDouble64-bit float
VARCHARAStringUnicode string
BOOLEANCVMBooltrue/false
VARBINARY / BLOBABlobBinary data
TIMESTAMPCVMLongMilliseconds since epoch
ANYACellDynamic type

Direct Lattice API

For programmatic access without SQL overhead:

// The merge policy is the application's choice; RowLWW merges rows by last write
ConvexDB cdb = ConvexDB.create(RowLWW::merge);
SQLDatabase db = cdb.database("mydb");

// Create table
db.tables().createTable("users", new String[]{"id", "name", "email"});

// Insert
db.tables().insert("users", 1, "Alice", "alice@example.com");

// Point lookup
AVector<ACell> row = db.tables().selectByKey("users", 1);

// Scan all
Index<ABlob, AVector<ACell>> all = db.tables().selectAll("users");

// Delete
db.tables().deleteByKey("users", 1);

Performance Tips

  • Use PK lookups (WHERE id = ?) for point queries — O(log n) via index pushdown
  • Use PreparedStatements — plans compile once, reuse across executions
  • Use batch inserts for bulk loading — significantly faster than individual statements
  • Full scans are O(n) — filter on PK when possible

Building and Testing

Run from the repository root — see the build-convex skill.

# Build (-am also builds convex-core, which this depends on)
./mvnw -B -T1C install -pl convex-db -am

# Run tests
./mvnw -B -T1C test -pl convex-db -am
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall