Constructive CLI

Build secure, role-aware GraphQL backends powered by PostgreSQL with database-first development

Constructive CLI is a comprehensive command-line tool that transforms your PostgreSQL database into a powerful GraphQL API. With automated schema generation, sophisticated migration management, and robust deployment capabilities, you can focus on building great applications instead of boilerplate code.

✨ Features

• 🚀 Database-First Development - Design your database, get your GraphQL API automatically
• 🔐 Built-in Security - Role-based access control and security policies
• 📦 Module System - Reusable database modules with dependency management
• 🛠️ Developer Experience - Hot-reload development server with GraphiQL explorer
• 🏗️ Production Ready - Deployment plans, versioning, and rollback support

🚀 Quick Start

Installation

npm install -g @constructive-io/cli

Create Your First Project

# Initialize a new workspace
cnc init workspace
cd my-project

# Create your first module
cnc init

# Deploy to your database
cnc deploy --createdb

# Start the development server
cnc server

Visit http://localhost:5555 to explore your GraphQL API!

📖 Core Concepts

Workspaces and Modules

• Workspace: A collection of related database modules
• Module: A self-contained database package with migrations, functions, and types
• Dependencies: Modules can depend on other modules, creating reusable building blocks

Database-First Workflow

1. Design your database schema using SQL migrations
2. Deploy changes with cnc deploy
3. Develop against the auto-generated GraphQL API
4. Version and package your modules for distribution

🛠️ Commands

Getting Started

cnc init

Initialize a new Constructive workspace or module.

# Create a new workspace
cnc init workspace

# Create a new module (run inside workspace)
cnc init

# Use templates from GitHub repository (defaults to constructive-io/pgpm-boilerplates.git)
cnc init workspace --repo owner/repo
cnc init --repo owner/repo --from-branch develop

# Use templates from custom paths
cnc init workspace --template-path ./custom-templates
cnc init --template-path ./custom-templates/module

Options:

• --repo <repo> - Template repo (default: https://github.com/constructive-io/pgpm-boilerplates.git)
• --template-path <path> - Template sub-path (defaults to workspace/module) or local path override
• --from-branch <branch> - Branch/tag when cloning the template repo

Templates are cached for one week under the pgpm tool namespace. Run cnc cache clean if you need to refresh the boilerplates.

Development

cnc server

Start the GraphQL development server with hot-reload.

# Start with defaults (port 5555)
cnc server

# Custom port and options
cnc server --port 8080 --no-postgis

cnc explorer

Launch GraphiQL explorer for your API.

# Launch explorer
cnc explorer

# With custom CORS origin
cnc explorer --origin http://localhost:3000

🔄 Updates

The CLI performs a lightweight npm version check at most once per week (skipped in CI or when PGPM_SKIP_UPDATE_CHECK is set). Use cnc update to install the latest release (installs pgpm by default; pass --package @constructive-io/cli to target the CLI package).

Database Operations

cnc deploy

Deploy your database changes and migrations.

# Deploy to selected database
cnc deploy

# Create database if it doesn't exist
cnc deploy --createdb

# Deploy specific package to a tag
cnc deploy --package mypackage --to @v1.0.0

# Fast deployment without transactions
cnc deploy --fast --no-tx

cnc verify

Verify your database state matches expected migrations.

# Verify current state
cnc verify

# Verify specific package
cnc verify --package mypackage

cnc revert

Safely revert database changes.

# Revert latest changes
cnc revert

# Revert to specific tag
cnc revert --to @v1.0.0

Migration Management

cnc migrate

Comprehensive migration management.

# Initialize migration tracking
cnc migrate init

# Check migration status
cnc migrate status

# List all changes
cnc migrate list

# Show change dependencies
cnc migrate deps

Module Management

cnc install

Install PGPM modules as dependencies.

# Install single package
cnc install @constructive-io/auth

# Install multiple packages
cnc install @constructive-io/auth @constructive-io/utils

cnc extension

Interactively manage module dependencies.

cnc extension

cnc tag

Version your changes with tags.

# Tag latest change
cnc tag v1.0.0

# Tag with comment
cnc tag v1.0.0 --comment "Initial release"

# Tag specific change
cnc tag v1.1.0 --package mypackage --changeName my-change

Packaging and Distribution

cnc plan

Generate deployment plans for your modules.

cnc plan

cnc package

Package your module for distribution.

# Package with defaults
cnc package

# Package without deployment plan
cnc package --no-plan

Utilities

cnc export

Export migrations from existing databases.

cnc export

cnc kill

Clean up database connections and optionally drop databases.

# Kill connections and drop databases
cnc kill

# Only kill connections
cnc kill --no-drop

💡 Common Workflows

Starting a New Project

# 1. Create workspace
mkdir my-app && cd my-app
cnc init workspace

# 2. Create your first module
cnc init

# 3. Add some SQL migrations to sql/ directory
# 4. Deploy to database
cnc deploy --createdb

# 5. Start developing
cnc server

Using Custom Templates

You can use custom templates from GitHub repositories or local paths:

# Initialize workspace with templates from GitHub
cnc init workspace --repo owner/repo

# Initialize workspace with templates from local path
cnc init workspace --template-path ./my-custom-templates

# Initialize module with custom templates
cnc init --template-path ./my-custom-templates

# Use specific branch from GitHub repository
cnc init workspace --repo owner/repo --from-branch develop

Template Structure: Custom templates should follow the same structure as the default templates:

• For workspace: boilerplates/workspace/ directory
• For module: boilerplates/module/ directory
• Or provide direct path to workspace/ or module/ directory

Working with Existing Projects

# 1. Clone and enter project
git clone <repo> && cd <project>

# 2. Install dependencies
cnc install

# 3. Deploy to local database
cnc deploy --createdb

# 4. Start development server
cnc server

Production Deployment

# 1. Create deployment plan
cnc plan

# 2. Package module
cnc package

# 3. Deploy to production
cnc deploy --package myapp --to @production

# 4. Verify deployment
cnc verify --package myapp

Get Graphql Schema

Fetch and output your GraphQL schema in SDL.

• Option 1 – Programmatic builder (from database schemas):

• Write to file:

  • cnc get-graphql-schema --database constructive --schemas myapp,public --out ./schema.graphql

• Print to stdout:

  • cnc get-graphql-schema --database constructive --schemas myapp,public

• Option 2 – Fetch from running server (via endpoint introspection):

• Write to file:

  • cnc get-graphql-schema --endpoint http://localhost:3000/graphql --headerHost meta8.localhost --out ./schema.graphql

• Print to stdout:

  • cnc get-graphql-schema --endpoint http://localhost:3000/graphql --headerHost meta8.localhost

Options:

• --database <name> (Option 1)
• --schemas <list> (Option 1; comma-separated)
• --endpoint <url> (Option 2)
• --headerHost <hostname> (Option 2; optional custom HTTP Host header for vhost-based local setups)
• --auth <token> (Option 2; optional; sets Authorization header)
• --header <name: value> (Option 2; optional; repeatable; adds request headers, last value wins on duplicates)
• --out <path> (optional; if omitted, prints to stdout)

Notes:

• If your local dev server routes by hostname (e.g., meta8.localhost), but is reachable at http://localhost:<port>, use:
  • cnc get-graphql-schema --endpoint http://localhost:3000/graphql --headerHost meta8.localhost
• You can repeat --header to add multiple headers, e.g.: --header 'X-Mode: fast' --header 'Authorization: Bearer abc123'

Tip:

• For Option 1, include only the schemas you need (e.g., myapp,public) to avoid type naming conflicts when multiple schemas contain similarly named tables.

⚙️ Configuration

Environment Variables

Constructive respects standard PostgreSQL environment variables:

export PGHOST=localhost
export PGPORT=5432
export PGDATABASE=myapp
export PGUSER=postgres
export PGPASSWORD=password

🆘 Getting Help

Command Help

# Global help
cnc --help

# Command-specific help
cnc deploy --help
cnc server -h

Common Options

Most commands support these global options:

• --help, -h - Show help information
• --version, -v - Show version information
• --cwd <dir> - Set working directory

Codegen

Generate types, operations, and SDK from a schema or endpoint.

# From SDL file
cnc codegen --schema ./schema.graphql --out ./codegen

# From endpoint with Host override
cnc codegen --endpoint http://localhost:3000/graphql --headerHost meta8.localhost --out ./codegen

Options:

• --schema <path> or --endpoint <url>
• --out <dir> output root (default: graphql/codegen/dist)
• --format <gql|ts> documents format
• --convention <dashed|underscore|camelcase|camelUpper> filenames
• --headerHost <host> optional HTTP Host header for endpoint requests
• --auth <token> Authorization header value (e.g., Bearer 123)
• --header "Name: Value" repeatable headers
• --emitTypes <bool> --emitOperations <bool> --emitSdk <bool> --emitReactQuery <bool>
• --config ./config.json Use customized config file

Config file (JSON/YAML):

# Use a JSON config to override defaults
cnc codegen --config ./my-options.json

Example my-options.json:

{
  "input": {
    "schema": "./schema.graphql",
    "headers": { "Host": "meta8.localhost" }
  },
  "output": {
    "root": "graphql/codegen/dist/codegen-config",
    "reactQueryFile": "react-query.ts"
  },
  "documents": {
    "format": "gql",
    "convention": "dashed",
    "excludePatterns": [".*Module$", ".*By.+And.+$"]
  },
  "features": {
    "emitTypes": true,
    "emitOperations": true,
    "emitSdk": true,
    "emitReactQuery": true
  },
  "reactQuery": {
    "fetcher": "graphql-request"
  }
}

Education and Tutorials

1. 🚀 Quickstart: Getting Up and Running Get started with modular databases in minutes. Install prerequisites and deploy your first module.

2. 📦 Modular PostgreSQL Development with Database Packages Learn to organize PostgreSQL projects with pgpm workspaces and reusable database modules.

3. ✏️ Authoring Database Changes Master the workflow for adding, organizing, and managing database changes with pgpm.

4. 🧪 End-to-End PostgreSQL Testing with TypeScript Master end-to-end PostgreSQL testing with ephemeral databases, RLS testing, and CI/CD automation.

5. ⚡ Supabase Testing Use TypeScript-first tools to test Supabase projects with realistic RLS, policies, and auth contexts.

6. 💧 Drizzle ORM Testing Run full-stack tests with Drizzle ORM, including database setup, teardown, and RLS enforcement.

7. 🔧 Troubleshooting Common issues and solutions for pgpm, PostgreSQL, and testing.

Related Constructive Tooling

🧪 Testing

• pgsql-test: 📊 Isolated testing environments with per-test transaction rollbacks—ideal for integration tests, complex migrations, and RLS simulation.
• supabase-test: 🧪 Supabase-native test harness preconfigured for the local Supabase stack—per-test rollbacks, JWT/role context helpers, and CI/GitHub Actions ready.
• graphile-test: 🔐 Authentication mocking for Graphile-focused test helpers and emulating row-level security contexts.
• pg-query-context: 🔒 Session context injection to add session-local context (e.g., SET LOCAL) into queries—ideal for setting role, jwt.claims, and other session settings.

🧠 Parsing & AST

• pgsql-parser: 🔄 SQL conversion engine that interprets and converts PostgreSQL syntax.
• libpg-query-node: 🌉 Node.js bindings for libpg_query, converting SQL into parse trees.
• pg-proto-parser: 📦 Protobuf parser for parsing PostgreSQL Protocol Buffers definitions to generate TypeScript interfaces, utility functions, and JSON mappings for enums.
• @pgsql/enums: 🏷️ TypeScript enums for PostgreSQL AST for safe and ergonomic parsing logic.
• @pgsql/types: 📝 Type definitions for PostgreSQL AST nodes in TypeScript.
• @pgsql/utils: 🛠️ AST utilities for constructing and transforming PostgreSQL syntax trees.
• pg-ast: 🔍 Low-level AST tools and transformations for Postgres query structures.

🚀 API & Dev Tools

• @constructive-io/graphql-server: ⚡ Express-based API server powered by PostGraphile to expose a secure, scalable GraphQL API over your Postgres database.
• @constructive-io/graphql-explorer: 🔎 Visual API explorer with GraphiQL for browsing across all databases and schemas—useful for debugging, documentation, and API prototyping.

🔁 Streaming & Uploads

• etag-hash: 🏷️ S3-compatible ETags created by streaming and hashing file uploads in chunks.
• etag-stream: 🔄 ETag computation via Node stream transformer during upload or transfer.
• uuid-hash: 🆔 Deterministic UUIDs generated from hashed content, great for deduplication and asset referencing.
• uuid-stream: 🌊 Streaming UUID generation based on piped file content—ideal for upload pipelines.
• @constructive-io/s3-streamer: 📤 Direct S3 streaming for large files with support for metadata injection and content validation.
• @constructive-io/upload-names: 📂 Collision-resistant filenames utility for structured and unique file names for uploads.

🧰 CLI & Codegen

• pgpm: 🖥️ PostgreSQL Package Manager for modular Postgres development. Works with database workspaces, scaffolding, migrations, seeding, and installing database packages.
• @constructive-io/cli: 🖥️ Command-line toolkit for managing Constructive projects—supports database scaffolding, migrations, seeding, code generation, and automation.
• @constructive-io/graphql-codegen: ✨ GraphQL code generation (types, operations, SDK) from schema/endpoint introspection.
• @constructive-io/query-builder: 🏗️ SQL constructor providing a robust TypeScript-based query builder for dynamic generation of SELECT, INSERT, UPDATE, DELETE, and stored procedure calls—supports advanced SQL features like JOIN, GROUP BY, and schema-qualified queries.
• @constructive-io/graphql-query: 🧩 Fluent GraphQL builder for PostGraphile schemas. ⚡ Schema-aware via introspection, 🧩 composable and ergonomic for building deeply nested queries.

Credits

🛠 Built by the Constructive team — creators of modular Postgres tooling for secure, composable backends. If you like our work, contribute on GitHub.

Disclaimer

AS DESCRIBED IN THE LICENSES, THE SOFTWARE IS PROVIDED "AS IS", AT YOUR OWN RISK, AND WITHOUT WARRANTIES OF ANY KIND.

No developer or entity involved in creating this software will be liable for any claims or damages whatsoever associated with your use, inability to use, or your interaction with other users of the code, including any direct, indirect, incidental, special, exemplary, punitive or consequential damages, or loss of profits, cryptocurrencies, tokens, or anything else of value.