gem-hunter

Constructive is a complete ecosystem for modular PostgreSQL development. Design your database schema, manage it with our package manager, and get a production-ready GraphQL API automatically. Build composable database modules, version them like npm packages, and deploy with confidence.

# Install pgpm globally
npm install -g pgpm

# Start local Postgres (via Docker) and export env vars
pgpm docker start
eval "$(pgpm env)"

Tip: Already running Postgres? Skip the Docker step and just export your PG* vars.

# 1. Create a workspace
pgpm init workspace
cd my-app

# 2. Create your first module
pgpm init
cd packages/your-module

# 3. Install a package
pgpm install @pgpm/faker

# 4. Deploy everything
pgpm deploy --createdb --database mydb1
psql -d mydb1 -c "SELECT faker.city('MI');"
>  Ann Arbor 

# 1. Create workspace
pgpm init workspace
cd my-app

# 2. Create your first module
pgpm init
cd packages/new-module

# 3. Add some SQL migrations to sql/ directory
pgpm add some_change

# 4. Deploy to database
pgpm deploy --createdb

# 1. Install workspace dependencies
pnpm install

# 2. Enter the packages/<yourmodule>
cd packages/yourmodule

# 3. Test the module in watch mode
pnpm test:watch

Constructive transforms how you build PostgreSQL applications by treating your database as a modular, version-controlled system. At its core is pgpm (PostgreSQL Package Manager), which brings npm-style package management to Postgres. Write SQL migrations, organize them into reusable modules, install database packages as dependencies, and deploy with deterministic, plan-driven migrations.

The framework automatically generates a secure, role-aware GraphQL API from your PostgreSQL schema using PostGraphile. This means you design your database once, and Constructive handles the API layer, authentication middleware, and developer tooling.

Constructive embraces a database-first development approach where your PostgreSQL schema is the source of truth. Row-Level Security (RLS) policies become your authorization layer, database functions become your business logic, and the GraphQL API is automatically derived from your carefully designed schema. This eliminates the impedance mismatch between your database and API while giving you the full power of SQL.

The heart of Constructive's modular approach. pgpm manages database schemas as versioned packages with dependency resolution, migration tracking, and reproducible deployments.

Features:

• Reusable database modules with dependency management
• Deterministic, plan-driven migrations with rollback support
• Tag-based versioning for coordinated releases
• Recursive module resolution across workspaces
• Export migrations from existing databases

Learn more: pgpm documentation

Full-featured command-line toolkit for managing Constructive projects. Provides the complete development workflow from scaffolding to deployment.

Features:

• Database-first development with automatic GraphQL generation
• Hot-reload development server with GraphiQL explorer
• Module scaffolding and workspace management
• Production deployment with versioning and rollback
• Built-in security with role-based access control

Learn more: @constructive-io/cli documentation

Express-based server powered by PostGraphile that automatically exposes your PostgreSQL database as a secure, scalable GraphQL API.

Features:

• Automatic API generation from database schema
• Row-Level Security (RLS) integration
• JWT authentication middleware
• File upload streaming to S3
• Dynamic API configuration from meta-schema
• PostGIS, full-text search, and advanced filtering

Learn more: @constructive-io/graphql-server documentation

# Run linting across all packages
pnpm lint

# Run linting for a specific package
cd packages/cli
pnpm lint

# Build all packages
pnpm build

# Clean build artifacts
pnpm clean

# Create workspace with pgpm
pgpm init workspace
cd my-app

# Create your first module
pgpm init

# Add database changes
pgpm add schema/app
pgpm add tables/users
pgpm add functions/authenticate

# Deploy to database
pgpm deploy --createdb

# Install reusable database modules
pgpm install @pgpm/base32
pgpm install @pgpm/faker

# Deploy with dependencies
pgpm deploy

# Tag a release
pgpm tag v1.0.0 --comment "Initial release"

# Generate deployment plan
pgpm plan

# Deploy to production
pgpm deploy --to @v1.0.0

# Verify deployment
pgpm verify

import { getConnections } from 'pgsql-test';

let db, teardown;

beforeAll(async () => {
  ({ db, teardown } = await getConnections());
});

beforeEach(() => db.beforeEach());
afterEach(() => db.afterEach());
afterAll(() => teardown());

test('user authentication', async () => {
  db.setContext({ role: 'authenticated', 'jwt.claims.user_id': '123' });
  const result = await db.query('SELECT current_user_id()');
  expect(result.rows[0].current_user_id).toBe('123');
});

Modular by Design - Organize your database into reusable packages with clear dependencies. Share common schemas across projects, version them independently, and compose complex systems from simple building blocks.

Database-First Development - Your PostgreSQL schema is the source of truth. Design your data model with the full power of SQL, leverage Row-Level Security for authorization, and let Constructive generate your API automatically.

Production-Ready - Built for teams shipping real applications. Deterministic migrations, comprehensive testing tools, automatic API generation, and deployment automation mean you can move fast without breaking things.

Developer Experience - Hot-reload development server, GraphiQL explorer, automatic TypeScript types, and comprehensive testing utilities make building PostgreSQL applications a joy.

Constructive applications follow a clear architecture:

1. Database Layer - PostgreSQL schemas with tables, functions, views, and RLS policies organized into modules
2. Migration Layer - pgpm manages schema changes with version-controlled, plan-driven deployments
3. API Layer - PostGraphile automatically generates a GraphQL API respecting your RLS policies
4. Client Layer - Auto-generated TypeScript types and query builders for type-safe client development

This architecture eliminates the traditional ORM layer and API boilerplate, letting you focus on your data model and business logic.

• pgpm - PostgreSQL Package Manager for modular database development with npm-style dependency management
• @constructive-io/cli - Full-featured command-line toolkit for Constructive projects with scaffolding, migrations, and deployment
• @pgpmjs/core - Core migration engine with module orchestration and dependency resolution

• @constructive-io/graphql-server - Express-based API server powered by PostGraphile with RLS integration, JWT auth, and meta API routing
• @constructive-io/graphql-explorer - GraphiQL interface for exploring your auto-generated GraphQL API
• graphile-settings - Centralized PostGraphile plugin configuration with connection filters, PostGIS, and full-text search

• pgsql-test - Isolated PostgreSQL test environments with per-test transaction rollbacks and RLS simulation
• pgsql-seed - PostgreSQL seeding utilities for CSV, JSON, SQL data loading, and pgpm deployment
• drizzle-orm-test - Drop-in pgsql-test replacement for Drizzle ORM with context management and RLS testing
• graphile-test - GraphQL testing utilities for PostGraphile projects with snapshot support
• supabase-test - Supabase-optimized test harness with JWT helpers and local stack integration
• pg-query-context - Session context injection for setting role, JWT claims, and session variables

• introspectron - PostgreSQL schema introspection for generating SDKs and metadata
• @constructive-io/graphql-codegen - GraphQL code generation (types, operations, SDK) from schema/endpoint
• pg-codegen - TypeScript interfaces and classes generated from PostgreSQL tables
• @constructive-io/graphql-query - Fluent GraphQL query builder for PostGraphile schemas with schema-aware introspection
• @constructive-io/query-builder - TypeScript SQL query builder for SELECT, INSERT, UPDATE, DELETE with JOIN and GROUP BY support

• @constructive-io/s3-streamer - Direct S3 streaming with automatic content-type detection and metadata extraction
• etag-hash - S3-compatible ETag computation for file integrity verification
• etag-stream - Transform stream for computing ETags during upload
• uuid-hash - Deterministic UUID generation from content hashing for deduplication
• uuid-stream - Streaming UUID generation based on piped file content
• upload-names - Collision-resistant filename generation for structured uploads
• content-type-stream - MIME type detection via magic bytes during streaming

• pgsql-parser - SQL parsing and deparsing engine for PostgreSQL syntax conversion
• libpg-query-node - Node.js bindings for libpg_query to convert SQL into parse trees
• pg-proto-parser - Protobuf parser for PostgreSQL Protocol Buffers with TypeScript generation
• pg-ast - AST construction and transformation utilities for Postgres query structures
• @pgsql/types - TypeScript type definitions for PostgreSQL AST nodes
• @pgsql/enums - TypeScript enums for PostgreSQL AST for safe parsing logic
• @pgsql/utils - AST manipulation utilities for constructing and transforming syntax trees

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.