VibeSQL Documentation

Technical reference for VibeSQL Server and the SDK.

Quick Start

Prefer the command line? vsql is the open-source CLI for VibeSQL: query, manage schemas and seed data. See the install line on the home page.

VibeSQL Micro Micro

A single binary with embedded PostgreSQL 16, for local development and prototyping. Downloads are on the GitHub releases page.

VibeSQL Server Server

Build and run instructions are in the VibeSQL Server repository.

Vibe SDK SDK

TypeScript client for Node.js and browser applications.

import { createVibeClient } from '@payez/vibe-client';

const client = createVibeClient({
  apiUrl: 'https://your-vibesql-server',
});

const users = client.collection('users');
const result = await users.list({ limit: 10 });

Endpoint

All SQL queries are sent as JSON to a single HTTP endpoint:

POST /v1/query
Content-Type: application/json

This is the VibeSQL Server API. Server requires authentication on every request; see Authentication below.

Request Format

{
  "sql": "SELECT * FROM users WHERE id = 1"
}

Response Format

Success (HTTP 200)

{
  "success": true,
  "rows": [
    {"id": 1, "name": "Alice", "email": "alice@example.com"}
  ],
  "rowCount": 1,
  "executionTime": 0.42
}

Error (HTTP 4xx/5xx)

{
  "success": false,
  "error": {
    "code": "INVALID_SQL",
    "message": "Invalid SQL syntax",
    "detail": "PostgreSQL error: relation \"nonexistent\" does not exist"
  }
}

Value encoding notes:

• SQL NULL values are returned as JSON null
• JSONB columns are returned as JSON objects/arrays (not as strings)
• TIMESTAMP values are returned as ISO 8601 strings
• NUMERIC values are returned as JSON numbers
• BOOLEAN values are returned as JSON true/false

Supported SQL Statements

Queries must start with one of these 8 keywords (case-insensitive):

PostgreSQL Data Types

VibeSQL supports all standard PostgreSQL data types. Here is the complete reference with size restrictions:

CREATE TABLE

-- Basic table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE,
  age INTEGER,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Table with JSONB and UUID
CREATE TABLE documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  data JSONB NOT NULL,
  tags TEXT[],
  price NUMERIC(10,2),
  is_active BOOLEAN DEFAULT true
);

-- Table with foreign keys
CREATE TABLE messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  conversation_id UUID REFERENCES conversations(id),
  agent_id UUID REFERENCES agents(id),
  content TEXT,
  metadata JSONB,
  created_at TIMESTAMP DEFAULT NOW()
);

SELECT

-- All columns
SELECT * FROM users;

-- Specific columns
SELECT name, email FROM users;

-- With WHERE
SELECT * FROM users WHERE age > 21 AND is_active = true;

-- ORDER BY, LIMIT, OFFSET (pagination)
SELECT * FROM users
  ORDER BY name ASC
  LIMIT 10 OFFSET 20;

-- Aggregates
SELECT role, COUNT(*) AS count
  FROM users
  GROUP BY role
  HAVING COUNT(*) > 5;

-- JOINs
SELECT u.name, m.content
  FROM users u
  JOIN messages m ON u.id = m.user_id
  WHERE m.created_at > '2024-01-01';

-- Subqueries
SELECT * FROM users
  WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

INSERT

-- Single row
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- With RETURNING (get inserted data back)
INSERT INTO users (name, email)
  VALUES ('Bob', 'bob@example.com')
  RETURNING id, name, created_at;

-- Multiple rows
INSERT INTO users (name, email) VALUES
  ('Charlie', 'charlie@example.com'),
  ('Diana', 'diana@example.com');

-- With JSONB data
INSERT INTO documents (data) VALUES
  ('{"name": "Invoice", "amount": 99.50, "tags": ["billing", "q4"]}');

-- With ON CONFLICT (upsert)
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice Updated')
  ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

UPDATE

-- Update specific row
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- Update multiple columns
UPDATE users SET name = 'Alice Smith', age = 31 WHERE id = 1;

-- Update all rows (explicit)
UPDATE users SET is_active = false WHERE 1=1;

-- Update with RETURNING
UPDATE users SET name = 'Updated' WHERE id = 1 RETURNING *;

-- Update JSONB field
UPDATE documents
  SET data = jsonb_set(data, '{status}', '"completed"')
  WHERE id = 1;

DELETE

-- Delete specific row
DELETE FROM users WHERE id = 1;

-- Delete with condition
DELETE FROM messages WHERE created_at < '2024-01-01';

-- Delete all rows (explicit)
DELETE FROM temp_data WHERE 1=1;

-- Delete with RETURNING
DELETE FROM users WHERE id = 1 RETURNING id, name;

ALTER TABLE

-- Add column
ALTER TABLE users ADD COLUMN phone TEXT;

-- Drop column
ALTER TABLE users DROP COLUMN phone;

-- Rename column
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Change column type
ALTER TABLE users ALTER COLUMN age TYPE BIGINT;

-- Add constraint
ALTER TABLE users ADD CONSTRAINT email_unique UNIQUE (email);

-- Set default
ALTER TABLE users ALTER COLUMN is_active SET DEFAULT true;

-- Rename table
ALTER TABLE users RENAME TO accounts;

DROP TABLE

-- Drop table (relational tables only — see note above)
DROP TABLE users;

-- Drop if exists (no error if missing)
DROP TABLE IF EXISTS users;

-- Drop with cascading foreign keys
DROP TABLE IF EXISTS users CASCADE;

TRUNCATE

-- Remove all rows (faster than DELETE WHERE 1=1)
TRUNCATE TABLE users;

-- Truncate with cascade
TRUNCATE TABLE users CASCADE;

-- Truncate and reset auto-increment
TRUNCATE TABLE users RESTART IDENTITY;

JSONB Operators

VibeSQL supports all 9 PostgreSQL JSONB operators through standard SQL syntax.

-> Get Field as JSON

Returns a JSON element by key (object) or index (array). Result is JSON type.

SELECT data->'name' FROM documents;
-- Returns: "Alice" (as JSON string, with quotes)

SELECT data->'tags'->0 FROM documents;
-- Returns: "staff" (first array element)

SELECT data->'address' FROM documents;
-- Returns: {"city": "Portland", "state": "OR"}

->> Get Field as Text

Returns a JSON element as plain text. The most common operator for extracting values.

SELECT data->>'name' FROM documents;
-- Returns: Alice (as text, no quotes)

SELECT data->>'age' FROM documents;
-- Returns: 30 (as text)

SELECT data->'address'->>'city' FROM documents;
-- Returns: Portland

#> Get Path as JSON

Navigates a JSON path and returns JSON. Path is specified as a text array.

SELECT data #> '{address,city}' FROM documents;
-- Returns: "Portland" (as JSON string)

SELECT data #> '{tags,0}' FROM documents;
-- Returns: "staff" (as JSON string)

#>> Get Path as Text

Navigates a JSON path and returns text.

SELECT data #>> '{address,city}' FROM documents;
-- Returns: Portland (as text)

SELECT data #>> '{address,state}' FROM documents;
-- Returns: OR

@> Contains

Tests if the left JSONB value contains the right JSONB value. Ideal for filtering.

SELECT * FROM documents WHERE data @> '{"role": "admin"}';
-- Rows where data contains role=admin

SELECT * FROM documents WHERE data @> '{"tags": ["manager"]}';
-- Rows where tags array contains "manager"

SELECT * FROM documents WHERE data->'address' @> '{"state": "OR"}';
-- Rows where address.state = "OR"

<@ Contained By

Tests if the left JSONB value is contained by the right. Reverse of @>.

SELECT * FROM documents
  WHERE '{"role": "admin", "name": "Alice"}' <@ data;
-- Rows where data contains both role=admin AND name=Alice

? Key Exists

Tests if a key exists in the top-level of a JSONB object.

SELECT * FROM documents WHERE data ? 'name';
-- Rows that have a "name" key

SELECT * FROM documents WHERE data ? 'phone';
-- Rows that have a "phone" key

?| Any Key Exists

Tests if any of the given keys exist.

SELECT * FROM documents
  WHERE data ?| array['phone', 'email', 'name'];
-- Rows with at least one of: phone, email, or name

?& All Keys Exist

Tests if all of the given keys exist.

SELECT * FROM documents
  WHERE data ?& array['name', 'age', 'role'];
-- Rows that have ALL of: name, age, and role

Operator Summary

Common JSONB Patterns

-- Filter by nested value
SELECT data->>'name' AS name
  FROM documents
  WHERE data->'address'->>'city' = 'Portland';

-- Sort by JSONB field (cast to int for numeric sort)
SELECT data->>'name' AS name, (data->>'age')::int AS age
  FROM documents
  ORDER BY (data->>'age')::int DESC;

-- Count by JSONB field
SELECT data->>'role' AS role, COUNT(*) AS count
  FROM documents
  GROUP BY data->>'role';

-- Check array contains a specific value
SELECT * FROM documents
  WHERE data->'tags' @> '"manager"';

-- Extract multiple fields
SELECT
  data->>'name' AS name,
  data->>'role' AS role,
  data #>> '{address,city}' AS city
  FROM documents;

-- Multiple JSONB conditions
SELECT data->>'name' AS name
  FROM documents
  WHERE data @> '{"role": "admin"}'
    AND data ? 'address'
    AND (data->>'age')::int > 25;

JSONB Type Casting

The ->> and #>> operators always return text. Cast to other types as needed:

(data->>'age')::int             -- Cast to integer
(data->>'price')::numeric       -- Cast to decimal
(data->>'active')::boolean      -- Cast to boolean
(data->>'created')::timestamp   -- Cast to timestamp
(data->>'score')::double precision -- Cast to float

Safety Rules

VibeSQL enforces safety rules to prevent accidental data loss:

Tier-Based Limits Server

VibeSQL Server supports configurable, tier-based limits. Timeouts and row limits are set per tenant tier.

Default Timeouts by Tier

Configurable Limits

Error Format

All errors return a JSON response with "success": false and an error object:

{
  "success": false,
  "error": {
    "code": "ERROR_CODE",
    "message": "Human-readable message",
    "detail": "Additional context"
  }
}

Error Codes

INVALID_SQL HTTP 400

SQL query has syntax errors, references undefined tables/columns, uses unsupported functions, or doesn't start with a valid SQL keyword.

Triggers: SQL syntax errors, undefined table (42P01), undefined column (42703), undefined function (42883), data type mismatch (42804), invalid keyword.

Resolution: Check SQL syntax. Verify table and column names exist. Ensure query starts with one of: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, TRUNCATE.

MISSING_REQUIRED_FIELD HTTP 400

The sql field is missing or empty in the request body.

// Wrong: using "query" instead of "sql"
{"query": "SELECT 1"}

// Correct
{"sql": "SELECT 1"}

UNSAFE_QUERY HTTP 400

An UPDATE or DELETE statement is missing a WHERE clause.

// This fails:
{"sql": "DELETE FROM users"}

// This works (intentional delete-all):
{"sql": "DELETE FROM users WHERE 1=1"}

QUERY_TIMEOUT HTTP 408

Query exceeded the maximum execution time. Limits are tier-based.

Resolution: Optimize the query (add indexes, reduce data scanned), add LIMIT, break into smaller operations.

QUERY_TOO_LARGE HTTP 413

SQL query exceeds the 256 KB (262,144 bytes) size limit.

Resolution: Reduce query size. Break large INSERT statements into multiple smaller requests.

RESULT_TOO_LARGE HTTP 413

Query result exceeds 1,000 rows.

Resolution: Add LIMIT 1000 (or smaller) to your query. Use OFFSET for pagination. Add WHERE clauses to filter results.

DOCUMENT_TOO_LARGE HTTP 413

A JSONB document or statement exceeds PostgreSQL's internal limits.

Triggers: PostgreSQL SQLSTATE 54000 (program_limit_exceeded), 54001 (statement_too_complex).

Resolution: Reduce JSONB document size. Simplify deeply nested JSON structures. Break large documents into smaller related records.

INTERNAL_ERROR HTTP 500

Unexpected server error not covered by other error codes.

Resolution: Check server logs for details. Retry the request. If persistent, restart the server.

SERVICE_UNAVAILABLE HTTP 503

The server is not ready to handle requests (still starting up).

Resolution: Wait for the server to finish starting. Check that the process is running.

DATABASE_UNAVAILABLE HTTP 503

The PostgreSQL instance is unreachable.

Triggers: PostgreSQL process crashed, connection failure (08xxx), insufficient resources (53xxx), too many connections (53300).

Resolution: Restart the server. Check disk space (PostgreSQL needs space for WAL files). Check if another process is using the PostgreSQL port.

HTTP Status Summary

PostgreSQL SQLSTATE Mapping

VibeSQL maps PostgreSQL SQLSTATE codes to VibeSQL error codes:

Server Configuration Server

appsettings.json Keys

Authentication Server

Every POST /v1/query request to VibeSQL Server carries one of two Authorization headers. Without either, the answer is 401 AUTH_REQUIRED.

Schema Versioning Server

VibeSQL Server supports JSON Schema versioning and evolution for collections. Schemas are scoped by client (tenant) and collection.

Schema Properties

Migration Transforms Server

Schema migrations define transforms applied to documents when migrating between schema versions. Transforms are defined in the JSON Schema under the x-vibe-migrations extension.

Transform Definition

{
  "x-vibe-migrations": {
    "1_to_2": [
      {
        "field": "price",
        "transform": "multiply",
        "args": 100,
        "reason": "Convert dollars to cents"
      }
    ]
  }
}

Available Transforms

Cast Target Types

Compatibility Checks Server

Before applying schema changes, VibeSQL checks compatibility between the current and proposed schema. The levels include:

Schema Change Types

Change types include:

SDK Installation SDK

npm install @payez/vibe-client

Client Setup SDK

import { createVibeClient } from '@payez/vibe-client';

const client = createVibeClient({
  apiUrl: 'http://your-server:PORT',
});

Collections SDK

const users = client.collection<User>('users');

list(options?)

const result = await users.list({
  limit: 20,
  offset: 0,
  orderBy: 'created_at',
  orderDir: 'desc',      // 'asc' | 'desc'
  filter: { role: 'admin' },
});

// result.data       → User[]
// result.pagination → { total, limit, offset, hasMore }

create(data)

await users.create({
  name: 'Alice',
  email: 'alice@example.com',
});

vsql CLI All Editions

vsql is a command-line client for VibeSQL. See the repository for details.

Installation

# Install from the release file
npm install -g https://github.com/PayEz-Net/vsql/releases/download/v1.3.5/vsql-1.3.5.tgz
vsql version

Help

Run vsql --help for the commands, options and environment variables.

AI Coding Skills

VibeSQL ships AI coding skills — lightweight plugins that let your AI coding assistant talk directly to your VibeSQL database. No servers to run, no packages to install. Just drop a skill file into your editor and go.

What Skills Can Do

• Explore — list tables, describe schemas, inspect columns and types
• Query — SELECT with filters, joins, aggregations, JSONB operators
• Create — CREATE TABLE, ALTER TABLE, add columns, set defaults
• CRUD — INSERT...RETURNING, UPDATE...WHERE, DELETE...WHERE
• JSONB — full PostgreSQL JSONB support with all 9 operators

Every skill embeds the full VibeSQL API reference, so your AI assistant knows the endpoint, response format, error codes, safety rules, and PostgreSQL syntax without any extra configuration.

Claude Code Available Now

The /vibe-sql skill for Claude Code lets you talk to your database in natural language directly from your terminal.

# Examples
/vibe-sql show me all tables
/vibe-sql create a users table with name, email, and age
/vibe-sql add a row: name=Widget, price=9.99
/vibe-sql what's the average price in stripe_sales
/vibe-sql add a phone column to the users table

The skill translates your request to PostgreSQL SQL, executes it via POST /v1/query, and presents formatted results. It enforces safety rules (UPDATE/DELETE require WHERE) and handles all error codes.

Supported Platforms

The /vibe-sql skill ships for three AI coding tools today:

Same skill body, platform-specific frontmatter.

Installation

Skills are single files. Copy the skill into your project and your AI editor discovers it automatically.

Claude Code

git clone https://github.com/PayEz-Net/vibesql-skills.git /tmp/vibesql-skills
cp -r /tmp/vibesql-skills/claude/vibe-sql ~/.claude/skills/vibe-sql

Open Claude Code and type /vibe-sql show me all tables.

OpenCode

git clone https://github.com/PayEz-Net/vibesql-skills.git /tmp/vibesql-skills
cp -r /tmp/vibesql-skills/opencode/vibe-sql ~/.opencode/skills/vibe-sql

Codex CLI

git clone https://github.com/PayEz-Net/vibesql-skills.git /tmp/vibesql-skills
cp -r /tmp/vibesql-skills/codex/vibe-sql ~/.agents/skills/vibe-sql