Convex-Dev/convex / .claude/skills/convex-db
convex-db
VerifiedUse 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 Type | CVM Type | Notes |
|---|---|---|
| BIGINT | AInteger | Any CVM integer; read as NUMERIC (BigDecimal) |
| INTEGER | CVMLong | 64-bit signed integer; read as BIGINT |
| DOUBLE | CVMDouble | 64-bit float |
| VARCHAR | AString | Unicode string |
| BOOLEAN | CVMBool | true/false |
| VARBINARY / BLOB | ABlob | Binary data |
| TIMESTAMP | CVMLong | Milliseconds since epoch |
| ANY | ACell | Dynamic 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
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| 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: 118k | Updated | Claude CodeCodex | Verified | Install |
| 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: 118k | Updated | Claude CodeCodex | Verified | Install |
| 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: 118k | Updated | Claude CodeCodex | Verified | Install |
| 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: 118k | Updated | Claude CodeCodex | Verified | Install |