VibeSQL Documentation

Complete technical reference for VibeSQL Micro, Server, and SDK.

Quick Start

VibeSQL Micro Micro

Download the single binary for your platform and run it. No installation, no sign-up.

# Download from GitHub releases
# https://github.com/PayEz-Net/vibesql-micro/releases

# Windows
.\vibesql-micro-windows-x64.exe

# macOS
./vibesql-micro-macos-arm64

# Linux
./vibesql-micro-linux-x64

The server starts on http://127.0.0.1:5173. Data is stored in ./vibe-data/ relative to where you run the binary.

# Test it immediately
curl -X POST http://127.0.0.1:5173/v1/query \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT 1 AS hello"}'

{
  "success": true,
  "rows": [{"hello": 1}],
  "rowCount": 1,
  "executionTime": 0.42
}

VibeSQL Server Server

Production deployment with Docker, Kubernetes, or bare metal.

# Docker
docker run -d -p 5432:5432 vibesql/server:latest

# Kubernetes
kubectl apply -f vibesql-server.yaml

Vibe SDK SDK

TypeScript client for Node.js and browser applications.

npm install @vibe/client

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

const client = createVibeClient({
  apiUrl: 'http://127.0.0.1:5173',
});

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

Editions Overview

Endpoint

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

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

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
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:

Query Limits Micro

VibeSQL Micro enforces fixed limits. These are hardcoded and not configurable.

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

The client tier is passed via the X-Vibe-Client-Tier HTTP header during HMAC authentication.

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. Default is 5 seconds (Micro) or tier-based (Server).

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

QUERY_TOO_LARGE HTTP 413

SQL query exceeds the 10 KB (10,240 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 embedded (Micro) or external (Server) 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:

Micro Configuration Micro

Environment Variables

Ports

Data Storage

Data is stored in ./vibe-data/ relative to the working directory where vibe serve is run. This directory contains PostgreSQL data files and is persistent across restarts.

Server Configuration Server

appsettings.json Keys

HMAC Authentication Server

VibeSQL Server authenticates requests using HMAC-SHA256 signatures. Micro does not require authentication (localhost only).

Required Headers

Optional Headers

Signature Computation

1. Build the string to sign:

{timestamp}|{method}|{path}

Example: 1706745600|POST|/v1/query

2. Compute HMAC-SHA256 using your signing key (Base64-decoded):

const stringToSign = `${timestamp}|${method}|${path}`;
const key = base64Decode(signingKey);
const signature = base64Encode(hmacSHA256(key, stringToSign));

3. Include in request headers:

curl -X POST http://your-server/v1/query \
  -H "Content-Type: application/json" \
  -H "X-Vibe-Timestamp: 1706745600" \
  -H "X-Vibe-Signature: aB3dEf...base64...==" \
  -d '{"sql": "SELECT 1"}'

Timing Constraints

Auth Error Codes

Public Endpoints Server

These endpoints do not require HMAC authentication:

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:

Schema Change Types

SDK Installation SDK

npm install @vibe/client

Environment Variables

Client Setup SDK

createVibeClient()

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

// Direct mode (Micro or direct Server access)
const client = createVibeClient({
  apiUrl: 'http://127.0.0.1:5173',
});

// Proxy mode (through IDP)
const client = createVibeClient({
  idpUrl: 'https://your-idp.example.com',
  clientId: 'your-client-id',
  signingKey: 'your-hmac-key-base64',
});

// With all options
const client = createVibeClient({
  apiUrl: 'http://127.0.0.1:5173',
  idpUrl: 'https://your-idp.example.com',
  clientId: 'your-client-id',
  signingKey: 'your-hmac-key-base64',
  defaultCollection: 'my_app',
  timeout: 30000,       // 30 seconds (default)
  debug: false,          // Enable debug logging
  getAccessToken: async () => 'bearer-token',
});

Configuration Interface

getVibeClient() — Singleton

import { getVibeClient } from '@vibe/client';

// Auto-initializes using environment variables
const client = getVibeClient();

Connection Modes

Direct mode: SDK connects directly to the VibeSQL API. Used with Micro or direct Server access.

Proxy mode: SDK routes requests through the IDP proxy at {idpUrl}/api/vibe/proxy. Enabled when idpUrl is set. The proxy handles HMAC signing and multi-tenant routing.

Collection CRUD SDK

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

list(options?)

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

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

get(id)

const user = await users.get(1);          // by number ID
const user = await users.get('abc-uuid'); // by string ID
// Returns User | null (null if not found)

create(data)

const newUser = await users.create({
  name: 'Alice',
  email: 'alice@example.com',
  role: 'admin',
});
// Returns the created User with server-assigned id

update(id, data)

const updated = await users.update(1, {
  name: 'Alice Smith',
});
// HTTP PATCH — partial update, returns updated User

delete(id)

await users.delete(1);
// Returns void

Filter Format

Simple filters use {field: value} syntax (operator defaults to eq):

// Simple equality filter
{ role: 'admin' }

// With explicit operator
{ age: { operator: 'gt', value: 21 } }

Filters are converted to the Vibe query format: [{ field, operator, value }]

Pagination Interface

Admin API SDK

const admin = client.admin;

Roles

// List roles
const roles = await admin.roles.list({ limit: 50, offset: 0 });

// Get role by ID
const role = await admin.roles.get(1);

// Create role
const newRole = await admin.roles.create({
  name: 'editor',
  description: 'Can edit content',
  source: 'custom',           // optional
});

// Update role
const updated = await admin.roles.update(1, {
  description: 'Updated description',
});

// Delete role
await admin.roles.delete(1);

Users

// List users
const users = await admin.users.list({ limit: 50 });

// Get user by ID
const user = await admin.users.get('user-uuid');

// Get user's roles
const roles = await admin.users.getRoles('user-uuid');

Tenant

// Get tenant configuration
const config = await admin.tenant.getConfig();
// Returns: { client_id, site_name, branding?: { logo_url?, primary_color? } }

Admin Type Definitions

Auth Utilities SDK

import {
  hasRole, hasAnyRole, hasAllRoles,
  isAdmin, isPlatformAdmin, isClientAdmin,
  getHighestRoleLevel, meetsRoleLevel,
  VibeRoles, ADMIN_ROLES, ROLE_HIERARCHY
} from '@vibe/client';

Role Constants

Role Groups

Utility Functions

// Usage examples
const userRoles = ['vibe_client_admin', 'vibe_app_user'];

isAdmin(userRoles);                    // true
isPlatformAdmin(userRoles);            // false
isClientAdmin(userRoles);              // true
hasRole(userRoles, 'platform_admin');  // false
getHighestRoleLevel(userRoles);        // 2
meetsRoleLevel(userRoles, 3);          // false

Error Handling SDK

import { VibeError } from '@vibe/client';

try {
  const user = await users.get(999);
} catch (error) {
  if (error instanceof VibeError) {
    console.log(error.code);       // 'NOT_FOUND'
    console.log(error.message);    // 'Document not found'
    console.log(error.status);     // 404
    console.log(error.isRetryable()); // false
  }
}

SDK Error Codes

Type Generation SDK

The @vibe/next-plugin package generates TypeScript types from your collection schemas automatically.

npm install @vibe/next-plugin

Configuration

Generated Output

The plugin generates:

• A .d.ts file for each collection with the document type
• An index.d.ts with re-exports and a VibeCollections interface
• Type augmentation for @vibe/client so client.collection('name') returns the correct type

// After type generation, collections are fully typed:
const users = client.collection('users');
// TypeScript knows users.list() returns ListResult<UserDocument>
// TypeScript knows users.get() returns UserDocument | null

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. All three are QA-tested and passing 10/10 scenarios.

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