schemact

Schemact

A Zero-Dependency, AST-Based Database Schema Management Tool

Website: schemact.oxog.dev | Documentation: schemact.oxog.dev/docs.html | GitHub: github.com/ersinkoc/schemact Schemact is a revolutionary database migration tool that rejects the complexity of modern ORMs in favor of a native, zero-dependency approach using a custom declarative DSL (Domain Specific Language).

Features

• Zero Runtime Dependencies: Only uses Node.js built-ins (fs, path, crypto, etc.)
• Multi-Database Support: PostgreSQL, MySQL/MariaDB, and SQLite generators included
• Custom DSL: Write schema definitions in .sigl files with a clean, intuitive syntax
• AST-Based: Proper compiler pipeline (Lexer → Parser → AST → CodeGen)
• Database Agnostic: Core logic is pure; database interaction via adapter pattern
• Two-Way Sync: Apply migrations (Up) AND reverse-engineer existing databases (Introspection)
• Integrity Checking: SHA-256 hashing ensures migration files haven't been tampered with
• Transaction Support: All migrations run in transactions for safety

Installation

npm install -g schemact

Or use it in your project:

npm install --save-dev schemact

Quick Start

1. Initialize a new Schemact project

schemact init

This creates:

• migrations/ directory for your schema files
• schemact.config.js configuration file

2. Configure your database adapter

Edit schemact.config.js to set up your database connection:

import pg from 'pg';
const { Pool } = pg;

const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'mydb',
  user: 'postgres',
  password: 'password',
});

const adapter = {
  async connect() {},
  async disconnect() {
    await pool.end();
  },
  async query(sql) {
    const result = await pool.query(sql);
    return result.rows;
  },
  async transaction(queries) {
    const client = await pool.connect();
    try {
      await client.query('BEGIN');
      for (const sql of queries) {
        await client.query(sql);
      }
      await client.query('COMMIT');
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  },
};

export default {
  adapter,
  migrationsPath: './migrations',
  ledgerPath: './.schemact_ledger.json',
};

3. Create your first migration

schemact create users

This generates a timestamped file like migrations/20240101120000_users.sigl.

4. Edit the migration file

# Define user table
model User {
  id        Serial        @pk
  email     VarChar(255)  @unique @notnull
  username  VarChar(50)   @unique @notnull
  password  VarChar(255)  @notnull
  role      Enum('admin', 'user', 'guest') @default('user')
  isActive  Boolean       @default(true)
  createdAt Timestamp     @default(now)
}

5. Apply the migration

schemact up

6. Check migration status

schemact status

The Schemact DSL Syntax

Schemact uses .sigl files with a custom syntax designed for clarity and expressiveness.

Model Definition

model TableName {
  columnName  ColumnType  @decorator1 @decorator2
}

Supported Types

• Integers: Serial, Int, BigInt, SmallInt
• Strings: VarChar(n), Char(n), Text
• Boolean: Boolean
• Dates/Times: Timestamp, Date, Time
• Decimals: Decimal(p,s), Numeric(p,s), Real, DoublePrecision
• JSON: Json, Jsonb
• UUID: Uuid
• Enums: Enum('value1', 'value2', ...)

Decorators

Decorator	Description	Example
@pk	Primary key	id Serial @pk
@unique	Unique constraint	email VarChar(255) @unique
@notnull	NOT NULL constraint	name Text @notnull
@default(value)	Default value	active Boolean @default(true)
@ref(Table.column)	Foreign key	userId Int @ref(User.id)
@onDelete(action)	Foreign key delete action	@ref(User.id) @onDelete('cascade')

Special Values

• now - Maps to CURRENT_TIMESTAMP
• true/false - Boolean literals
• Strings must be quoted: 'value'

Raw SQL Escape Hatch

For operations not covered by the DSL, prefix lines with >:

> CREATE INDEX idx_users_email ON "User"("email");
> CREATE VIEW active_users AS SELECT * FROM "User" WHERE "isActive" = true;

Complete Example

# Blog schema

model User {
  id        Serial        @pk
  email     VarChar(255)  @unique @notnull
  username  VarChar(50)   @unique @notnull
  createdAt Timestamp     @default(now)
}

model Post {
  id          Serial        @pk
  title       VarChar(200)  @notnull
  content     Text
  authorId    Int           @ref(User.id) @onDelete('cascade')
  published   Boolean       @default(false)
  createdAt   Timestamp     @default(now)
}

# Create index for better query performance
> CREATE INDEX idx_posts_author ON "Post"("authorId");

CLI Commands

schemact init

Initialize a new Schemact project. Creates:

• migrations/ directory
• schemact.config.js configuration file

schemact create <name>

Create a new migration file with a timestamped filename.

Example:

schemact create add_users_table
# Creates: migrations/20240101120000_add_users_table.sigl

schemact up

Apply all pending migrations. Migrations are executed in chronological order based on their filename timestamps.

schemact down

Rollback the last batch of migrations. Automatically generates DROP statements from your schema definitions.

schemact status

Show the current state of migrations:

• Applied migrations
• Pending migrations
• Current batch number

schemact pull [schema]

Introspect an existing database and generate .sigl files. This is the "reverse engineering" feature.

Example:

schemact pull public
# Generates migrations/2024-01-01_introspected.sigl

schemact help

Display help information.

schemact version

Display version information.

Architecture

Schemact follows a clean, modular architecture:

Compiler Pipeline

.sact file → Lexer → Tokens → Parser → AST → Generator → SQL

• Lexer (src/ast/lexer.ts): Tokenizes input into meaningful chunks
• Parser (src/ast/parser.ts): Builds an Abstract Syntax Tree
• Generator (src/generators/postgres.ts): Converts AST to SQL (both UP and DOWN)

State Management

The Ledger (src/engine/ledger.ts) tracks applied migrations in .schemact_ledger.json:

• Stores SHA-256 hash of migration content
• Validates that applied migrations haven't been modified
• Manages batch numbers for rollbacks

Migration Execution

The Runner (src/engine/runner.ts) orchestrates the entire migration flow:

• Loads migration files
• Validates integrity
• Executes SQL in transactions
• Updates the ledger

Introspection

The Introspector (src/engine/introspector.ts) reverse-engineers databases:

• Queries information_schema tables
• Maps SQL types back to Schemact types
• Generates formatted .sigl files

Database Adapter Interface

Schemact is database-agnostic through the adapter pattern. Implement this interface for your database:

interface DbAdapter {
  connect(): Promise<void>;
  disconnect(): Promise<void>;
  query(sql: string): Promise<any[]>;
  transaction(queries: string[]): Promise<void>;
}

PostgreSQL Example

import pg from 'pg';
const { Pool } = pg;

const pool = new Pool({ /* config */ });

const adapter = {
  async connect() {},
  async disconnect() {
    await pool.end();
  },
  async query(sql) {
    const result = await pool.query(sql);
    return result.rows;
  },
  async transaction(queries) {
    const client = await pool.connect();
    try {
      await client.query('BEGIN');
      for (const sql of queries) {
        await client.query(sql);
      }
      await client.query('COMMIT');
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  },
};

MySQL Example

import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb',
});

const adapter = {
  async connect() {},
  async disconnect() {
    await pool.end();
  },
  async query(sql) {
    const [rows] = await pool.query(sql);
    return rows;
  },
  async transaction(queries) {
    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();
      for (const sql of queries) {
        await connection.query(sql);
      }
      await connection.commit();
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  },
};

export default {
  adapter,
  generator: new MySQLGenerator(), // Use MySQLGenerator for MySQL
  migrationsPath: './migrations',
  ledgerPath: './.schemact_ledger.json',
};

SQLite Example

import Database from 'better-sqlite3';

const db = new Database('./mydb.sqlite');

const adapter = {
  async connect() {
    // Enable foreign keys
    db.pragma('foreign_keys = ON');
  },
  async disconnect() {
    db.close();
  },
  async query(sql) {
    // For SELECT queries
    if (sql.trim().toUpperCase().startsWith('SELECT')) {
      return db.prepare(sql).all();
    }
    // For other queries
    db.prepare(sql).run();
    return [];
  },
  async transaction(queries) {
    const transaction = db.transaction(() => {
      for (const sql of queries) {
        db.prepare(sql).run();
      }
    });
    transaction();
  },
};

export default {
  adapter,
  generator: new SQLiteGenerator(), // Use SQLiteGenerator for SQLite
  migrationsPath: './migrations',
  ledgerPath: './.schemact_ledger.json',
};

Multi-Database Support

Schemact provides dedicated SQL generators for different database systems, each optimized for the target database's specific syntax and features:

Database-Specific Features

Feature	PostgreSQL	MySQL	SQLite
Auto-increment	SERIAL	INT AUTO_INCREMENT	INTEGER PRIMARY KEY AUTOINCREMENT
Enums	CHECK constraint	Native ENUM type	CHECK constraint
Booleans	Native BOOLEAN	BOOLEAN (TINYINT)	INTEGER (0/1)
JSON	JSON, JSONB	JSON	TEXT (stored as JSON string)
Timestamps	TIMESTAMP	TIMESTAMP	TEXT (ISO8601)
Identifiers	Double quotes "table"	Backticks `table`	Double quotes "table"
Foreign Keys	Native support + CASCADE	Native support + CASCADE	Native support (needs PRAGMA)
Character Sets	UTF-8 default	UTF8MB4 with collation	UTF-8 default

Choosing the Right Generator

When configuring Schemact, import and use the appropriate generator:

// For PostgreSQL
import { PostgresGenerator } from 'schemact';
generator: new PostgresGenerator()

// For MySQL/MariaDB
import { MySQLGenerator } from 'schemact';
generator: new MySQLGenerator()

// For SQLite
import { SQLiteGenerator } from 'schemact';
generator: new SQLiteGenerator()

The same .sigl files work across all databases - Schemact automatically generates the correct SQL syntax for each platform.

Programmatic Usage

You can also use Schemact programmatically:

import {
  Parser,
  PostgresGenerator,
  MySQLGenerator,
  SQLiteGenerator,
  MigrationRunner
} from 'schemact';

// Parse a .sact file
const ast = Parser.parse(`
  model User {
    id    Serial  @pk
    email Text    @unique
  }
`);

// Generate SQL for different databases
const postgresGen = new PostgresGenerator();
const mysqlGen = new MySQLGenerator();
const sqliteGen = new SQLiteGenerator();

const postgresSQL = postgresGen.generateUp(ast);
const mysqlSQL = mysqlGen.generateUp(ast);
const sqliteSQL = sqliteGen.generateUp(ast);

console.log(postgresSQL);
// [
//   'CREATE TABLE "User" (\n  "id" SERIAL PRIMARY KEY,\n  "email" TEXT UNIQUE\n);'
// ]

console.log(mysqlSQL);
// [
//   'CREATE TABLE `User` (\n  `id` INT AUTO_INCREMENT PRIMARY KEY,\n  `email` TEXT UNIQUE\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;'
// ]

// Run migrations programmatically
const runner = new MigrationRunner({
  adapter: myAdapter,
  generator: new PostgresGenerator(), // or MySQLGenerator(), SQLiteGenerator()
  migrationsPath: './migrations',
});

await runner.up();

Error Handling

Schemact provides clear, actionable error messages:

Integrity Errors

If a migration file is modified after being applied:

✗ Migration file "20240101_users.sigl" has been modified!
  This file was applied on 2024-01-01T12:00:00Z and must not be changed.
  Expected hash: abc123...
  Current hash: def456...

Parse Errors

If your .sigl file has syntax errors:

✗ Parse error at line 5, column 10: Expected column type

Configuration Errors

If your adapter isn't configured:

✗ Database adapter not configured.
  Please edit schemact.config.js and provide an adapter.

Project Structure

src/
├── ast/
│   ├── types.ts       # AST interfaces and error types
│   ├── lexer.ts       # Tokenizer
│   └── parser.ts      # AST builder
├── generators/
│   ├── base.ts        # SQL generator interface
│   └── postgres.ts    # PostgreSQL generator
├── engine/
│   ├── ledger.ts      # Migration state management
│   ├── runner.ts      # Migration orchestration
│   └── introspector.ts # Database reverse engineering
├── utils/
│   ├── colors.ts      # ANSI color codes
│   └── formatting.ts  # String formatting helpers
├── index.ts           # Main exports
└── cli.ts             # CLI entry point

Development

Build

npm run build

Watch Mode

npm run dev

Link for Local Development

npm link
schemact --help

Philosophy

Schemact is built on these core principles:

• Zero Bloat: No runtime dependencies. Ever.
• Proper Parsing: No regex hacks. Use a real compiler pipeline.
• Type Safety: Strict TypeScript with no any.
• Integrity: Cryptographic hashing ensures migrations are immutable.
• Simplicity: The DSL should be intuitive and readable.
• Database Agnostic: Core logic is pure; adapters handle DB specifics.

Comparison with Other Tools

Feature	Schemact	Knex	Prisma	TypeORM
Runtime Dependencies	0	~20	~50	~100
Custom DSL	✓	✗	✓	✗
AST-Based	✓	✗	✓	✗
Two-Way Sync	✓	✗	✓	✗
Integrity Checking	✓	✗	✗	✗
Database Agnostic	✓	✓	✓	✓
Transaction Support	✓	✓	✓	✓

Roadmap

• PostgreSQL support
• MySQL/MariaDB support
• SQLite support
• Column alterations (ALTER TABLE)
• Index management
• Enum type management
• Migration squashing
• Dry-run mode
• Parallel migrations
• Migration testing utilities

Contributing

Contributions are welcome! Please ensure:

• No new runtime dependencies
• Strict TypeScript (no any)
• Tests for new features
• Documentation updates

License

MIT

Credits

Built with Node.js built-ins and determination to avoid dependency bloat.

Schemact: Schema management, redefined.