SQLSmith

JavaScript Schema to Postgres (Supabase) Migration Generator

SQLSmith converts JavaScript object schemas into clean, safe, Supabase-ready Postgres SQL migration files with schema diffing, CLI tools, and type overrides.

🚀 Features

• ✨ JS → SQL Schema Generation - Write schemas in JavaScript, get clean SQL
• 🔄 Schema Diffing - Generate ALTER statements from schema changes
• 📦 Supabase-Ready - Timestamped migrations in the Supabase format
• 🎯 Shorthand Notation - Concise column definitions ("uuid:pk", "text:unique:notnull")
• 🔐 RLS Policy Templates - Optional Supabase Row Level Security policy generation
• ⚙️ Highly Configurable - Type overrides, naming strategies, timestamps, and more
• 🧪 Well-Tested - Comprehensive test suite included
• 🛠️ CLI + Programmatic API - Use from command line or Node.js code

📦 Installation

npm install -D sqlsmith
# or
yarn add -D sqlsmith

🏁 Quick Start

1. Initialize SQLSmith

npx sqlsmith init

This creates:

• sqlsmith.config.js - Configuration file
• schema.example.js - Example schema file

2. Define Your Schema

Edit schema.example.js:

exportdefault{users: {id: "uuid:pk",name: "text:notnull",email: "text:unique:notnull",age: "integer:default=18",profile: {bio: "text",avatar_url: "text"},created_at: "timestamptz:default=now()"},profiles: {id: "uuid:pk",user_id: "uuid:fk=users.id",visibility: "text:default='public'"}};

3. Generate SQL

# Generate SQL file
npx sqlsmith generate --schema=schema.example.js --out=schema.sql
# Or create a timestamped migration
npx sqlsmith migrate --schema=schema.example.js --message="init"

4. Output

Generated SQL:

CREATETABLEIF NOT EXISTS public.users (
id uuid PRIMARY KEY,
name textNOT NULL,
email text UNIQUE NOT NULL,
age integer DEFAULT 18,
profile jsonb,
created_at timestamptz DEFAULT now()
);
CREATETABLEIF NOT EXISTS public.profiles (
id uuid PRIMARY KEY,
user_id uuid REFERENCESpublic.users(id),
visibility text DEFAULT 'public'
);

📚 CLI Commands

init

Create configuration and example schema files

npx sqlsmith init

generate

Generate SQL from schema

npx sqlsmith generate --schema=schema.js --out=schema.sql

migrate

Create timestamped migration file

npx sqlsmith migrate --schema=schema.js --message="create users table"

diff

Generate ALTER statements from schema differences

npx sqlsmith diff --from=old-schema.js --to=new-schema.js --out=diff.sql

policies

Generate Supabase RLS policy templates

npx sqlsmith policies --table=users --out=policies.sql

💻 Programmatic API

import{generateSQL,diffSchemas,migrate}from"sqlsmith";// Generate SQL from schemaconstsql=generateSQL(schemaObject);// Generate ALTER statementsconstdiff=diffSchemas(oldSchema,newSchema);// Create migration fileconstfilePath=migrate(schemaObject,"./supabase/migrations","init");

📝 Schema Notation

Shorthand Format

Column definitions use the format: "<type>[:modifier1][:modifier2]..."

{id: "uuid:pk",// Primary keyemail: "text:unique:notnull",// Unique and NOT NULLage: "integer:default=18",// Default valuecreated_at: "timestamptz:default=now()",// Function defaultuser_id: "uuid:fk=users.id",// Foreign keysettings: "jsonb:default='{}'"// JSON default}

Supported Modifiers

• pk - Primary key (implies NOT NULL)
• notnull - NOT NULL constraint
• unique - UNIQUE constraint
• default=<value> - Default value
• fk=<table>.<column> - Foreign key reference

Type Mapping

JavaScript types to PostgreSQL:

string→textnumber→integerboolean→booleanuuid→uuidobject→jsonbarray→jsonb

Explicit PostgreSQL types supported:

• text, varchar, integer, bigint, smallint
• numeric, decimal, real, double precision
• uuid, boolean, date, timestamp, timestamptz
• json, jsonb, text[], integer[], etc.

Nested Objects

Nested objects are automatically converted to jsonb:

{users: {profile: {bio: "text",avatar_url: "text"}}}

Becomes:

profile jsonb

⚙️ Configuration

sqlsmith.config.js:

exportdefault{schema: 'public',// PostgreSQL schemanamingStrategy: 'snake_case',// snake_case or camelCasetimestamps: true,// Add created_at/updated_atjsonForObjects: true,// Convert nested objects to jsonbforeignKeyStrategy: 'inline',// inline or separateTablepolicyGeneration: false,// Generate RLS policiessupabaseDir: './supabase/migrations',typeOverrides: {// Custom type mappingsid: 'uuid',email: 'varchar(255)'}};

Options

Option	Type	Default	Description
schema	string	'public'	PostgreSQL schema name
namingStrategy	string	'snake_case'	Naming convention
timestamps	boolean/object	false	Auto-add timestamps
jsonForObjects	boolean	true	Convert objects to jsonb
foreignKeyStrategy	string	'inline'	FK strategy
policyGeneration	boolean	false	Generate RLS policies
supabaseDir	string	'./supabase/migrations'	Migration directory
typeOverrides	object	{}	Custom type mappings

🔄 Schema Diffing

SQLSmith can generate ALTER statements by comparing two schemas:

// old-schema.jsexportdefault{users: {id: "uuid:pk",name: "text"}};// new-schema.jsexportdefault{users: {id: "uuid:pk",name: "text",email: "text:unique:notnull"// Added column}};

npx sqlsmith diff --from=old-schema.js --to=new-schema.js

Output:

ALTERTABLEpublic.users ADD COLUMN email text UNIQUE NOT NULL;

Destructive Changes

SQLSmith warns about destructive changes:

-- WARNING: DROP_COLUMN on users.name is DESTRUCTIVE!-- ALTER TABLE public.users DROP COLUMN name;

🔐 Supabase Integration

Migrations

SQLSmith generates timestamped migration files compatible with Supabase:

supabase/migrations/
20251121143022_create_users.sql
20251121143045_add_profiles.sql

RLS Policies

Generate Row Level Security policy templates:

npx sqlsmith policies --table=users

Output:

-- Enable Row Level SecurityALTERTABLEpublic.users ENABLE ROW LEVEL SECURITY;
-- Policy: Authenticated users can view all records
CREATE POLICY "authenticated_select_policy"ONpublic.users
FOR SELECT
TO authenticated
USING (true);
-- Policy: Users can update their own records
CREATE POLICY "user_update_own_policy"ONpublic.users
FOR UPDATE
TO authenticated
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

🧪 Testing

Run the test suite:

npm test

Tests include:

• Parser validation
• SQL generation
• Schema diffing
• Type mapping
• Configuration handling

📁 Project Structure

sqlsmith/
├── bin/
│ └── sqlsmith.js # CLI entry point
├── src/
│ ├── index.js # Main API
│ ├── cli.js # CLI commands
│ ├── parser/
│ │ ├── parseSchema.js # Schema parser
│ │ └── validateSchema.js # Schema validator
│ ├── generator/
│ │ ├── sqlCreate.js # CREATE TABLE generator
│ │ ├── sqlAlter.js # ALTER TABLE generator
│ │ └── typeMapper.js # Type mapping
│ ├── integrator/
│ │ ├── supabaseWriter.js # Migration file writer
│ │ └── policyGenerator.js# RLS policy generator
│ └── utils/
│ ├── fileWriter.js # File utilities
│ ├── logger.js # Logger
│ └── timestamp.js # Timestamp generator
├── templates/
│ ├── migration.sql.tpl # Migration template
│ └── policy.template.sql # Policy template
├── examples/
│ └── schema.example.js # Example schema
├── tests/
│ ├── parser.test.js # Parser tests
│ └── generator.test.js # Generator tests
└── package.json

🤝 Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

📄 License

MIT © Your Name

🔗 Links

• GitHub Repository
• NPM Package
• Supabase Documentation
• PostgreSQL Documentation

✨ Examples

Basic Schema

exportdefault{posts: {id: "uuid:pk",title: "text:notnull",content: "text",author_id: "uuid:fk=users.id",published: "boolean:default=false",created_at: "timestamptz:default=now()"}};

With Timestamps Config

// sqlsmith.config.jsexportdefault{timestamps: {created_at: true,updated_at: true}};// schema.jsexportdefault{articles: {id: "uuid:pk",title: "text:notnull"// created_at and updated_at added automatically}};

Complex Schema

exportdefault{organizations: {id: "uuid:pk",name: "text:notnull",settings: "jsonb:default='{}'",created_at: "timestamptz:default=now()"},users: {id: "uuid:pk",email: "text:unique:notnull",org_id: "uuid:fk=organizations.id",role: "text:default='member'",metadata: {bio: "text",avatar: "text",preferences: {}}},posts: {id: "uuid:pk",author_id: "uuid:fk=users.id",org_id: "uuid:fk=organizations.id",title: "text:notnull",content: "text",tags: "text[]",published_at: "timestamptz"}};

---

Built with ❤️ for the Supabase and PostgreSQL community

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Installation

npm install

Installation

npm install

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Usage

See the documentation for more details.

Installation

npm install

Installation

npm install