Wrm 3.8.1

WormScript (WRM)

Stop writing boilerplate. Start building features.

Website: wrm.furnissdesign.co.uk

WormScript turns your database schema into a complete, read-made prototype .NET application — API, data layer, and web UI — in minutes. No manual CRUD coding, no repetitive scaffolding. Just run a command and get a fully functional prototype you can deploy and extend.

Perfect for:

• Rapid prototyping — Go from schema to running API in under a minute
• MVP delivery — Get a working full-stack app to validate your idea, then enhance it
• Internal tools — Generate CRUD apps for internal workflows without the overhead
• Reducing handoff friction — Designers and PMs can see a live interface while you focus on logic

What you get out of the box: Reads you PostgreSQL or MySQL schema and generates:

• A Complete API — A Multi-platform Service supporting RESTful controllers with CRUD operations, paging, filtering, and auto-generated Swagger documentation
• Clean data layer — Type-safe Dapper repositories with automatic mapping between database and API (DTO) models
• Ready-to-use UI — React components (forms, tables, cards, dropdowns) wired directly to your API
• Authentication & authorization — Optional JWT-based role and organization-scoped access control
• Deployment-ready — Docker Compose setup, PowerShell deployment scripts, and production configurations included. Run a script to create fully functioning Docker containers for your API, database, MCP Server, Redis and RabbitMQ. Add Cloudflare with a command.
• Easy customization — Add SQL comments to tables to control behavior (soft/hard deletes, paging, visibility, custom finders)
• Advanced features
  • CREATE an MCP service with a single command
  • Add REDIS in in-memory caching with a simple TABLE COMMENT
  • Publish changes to RabbitMQ with a single annotation on the table
  • Add GraphQL endpoints with a single FEATURE toggle
  • Add user-handling, file uploads, multi-tenancy hiearchical organisations, user-subscribers, event publishing with simple FEATURE toggles

Add WrmScript as an AI Skill

Now also available as an AI skill for your agents! Add it to your agent with:

npx skills add Alearian/wrm -g

<br/>

---

Basic Usage

1. Initialize a new project with wrm init MyProject React.
2. Edit the generated .wrm/MyProject.wrm with your connection string.
3. Add features and SQL files as needed.
4. Run wrm build.
5. Sit back and watch your application-starter being generated!

Table of Contents

• Installation
• Quick Start
• Usage
  • Commands
  • WRM Script Syntax
  • Features System
• Project Structure
• Generated Output
• Configuration
• Database Support
• Examples
• Contributing
• Supported Technologies

Additional Documentation:

• WrmScript Syntax - Full command reference and syntax details
• Template System - Template markers and available templates
• Features - Feature system details

---

Installation

Install WormScript as a global .NET tool:

dotnet tool install --global Wrm

Verify installation:

wrm help

---

Quick Start

1. Initialize a New Project

Choose the name of your project wisely, it will be used throughout the generated code. No spaces or underlines. Case sensitive.

wrm init MyProject react
cd MyProject

This creates:

• .wrm/MyProject.wrm - Build script
• .wrm/MyProject.sql - Database schema

2. Define Your Database Schema

Edit .wrm/MyProject.sql:

-- STAR-SYSTEMS
DROP TABLE IF EXISTS star_systems;
CREATE TABLE star_systems(
    star_system_id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    system_name VARCHAR(255) NOT NULL
);

-- STARS - Stars that belong in a star-system
DROP TABLE IF EXISTS stars;
CREATE TABLE stars(
    star_id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    star_system_id INTEGER,
    star_name VARCHAR(255) NOT NULL
);

-- PLANETS
DROP TABLE IF EXISTS planets;
CREATE TABLE planets(
    planet_id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    star_id INTEGER,
    planet_name VARCHAR(255) NOT NULL,
    planet_code VARCHAR(25) NOT NULL
);

3. Create and Run Your Database

Create a PostgreSQL database named MyProject and run the SQL script to set up the schema.

Optionally don't use the DATABASE RUN command in the Wrm script and rely on an existing database.

4. Configure Your Wrm Script

Edit .wrm/MyProject.wrm:

CREATE PROJECT MyProject
    CONNECTION POSTGRES 'Host=localhost:5432;Username=postgres;Password=postgres;Database=MyProject'
    FEATURE SWAGGER
    FEATURE SOFT DELETE

DATABASE RUN '.wrm/MyProject.sql'

CREATE TESTDATA IFEMPTY USECASES 10
CREATE MODELS
CREATE API CONTROLLERS
CREATE API SERVICE
CREATE COMPONENTS

5. Build Your Project

wrm build

Your application-starter is now generated!

---

Usage

Commands

wrm <init|build|test|list|help> [options]

• init - Initialize a new project
• build - Build the project
• test - Test database connectivity
• list - List project components for debugging

wrm init [ProjectName] [WebComponentType]

Creates a new WormScript project structure. A base set of components is created for each entity table in the required web framework form.

wrm init MyProject react      # React components
wrm init MyProject coreui     # CoreUI components

wrm build

Executes the .wrm script and generates all code.

wrm build

wrm test connection

Tests database connectivity.

wrm test connection

wrm list [OPTION]

Lists project components for debugging.

Options:

• ENTITIES - All entity tables
• MODELS - Generated models
• PORTS - API ports configuration
• TEMPLATES - Available templates
• TABLES - Database tables
• FEATURES - Enabled features
• CONFIG - Configuration settings
• CONNECTION - Database connection info (masked)

wrm list tables
wrm list features

wrm database

Shows database information and connection details.

wrm database

wrm help [command]

Shows help information.

wrm help
wrm help init
wrm help build

---

WRM Script Syntax

WormScript uses a simple, declarative syntax:

Basic Structure

-- Comments start with -- or //

CREATE PROJECT ProjectName
    CONNECTION POSTGRES 'Host=localhost;Database=mydb;Username=user;Password=pass'
    FEATURE SWAGGER              -- Enable Swagger docs
    FEATURE SOFT DELETE          -- Use soft delete (is_deleted column)
    -- FEATURE HARD DELETE   -- Alternative: permanent deletion

DATABASE RUN '.wrm/schema.sql'

CREATE MODELS               -- Generate model classes
CREATE API CONTROLLERS      -- Generate API controllers
CREATE API SERVICE          -- Generate microservice code
CREATE COMPONENTS           -- Generate web components
CREATE TESTDATA IFEMPTY USECASES 10  -- Generate test data

Commands

Command	Description
CREATE PROJECT <name>	Initialize project configuration (required first)
DATABASE RUN '<filepath>'	Execute SQL script during schema creation phase (before code generation)
DATABASE SEED '<filepath>'	Execute seed script AFTER schema and code generation (for reference data, no conflicts)
CREATE MODELS	Generate C# model classes from database tables
CREATE MODEL <tablename>	Generate single model
CREATE API SERVICE	Generate all API controllers
CREATE COMPONENTS	Generate web UI components
CREATE LOOKUP ON '<table>' BY '<fields>'	Generate lookup/finder methods (see CREATE LOOKUP)
CREATE MODEL FLAT <name> FROM <t> WHERE\|WITH (<tables>)	Generate a flat (horizontally joined) read model
CREATE MODEL TREE <name> FROM <t> WHERE\|WITH\|EXPANDING\|HAVING (<tables>)	Generate a hierarchical (nested) read model
CREATE TESTDATA [options]	Generate test data
CREATE TESTTABLE [name]	Create test table with all data types

Connection Strings

-- PostgreSQL
CONNECTION POSTGRES 'Host=localhost:5432;Database=mydb;Username=user;Password=pass'

-- MySQL
CONNECTION MYSQL 'Server=localhost;Database=mydb;User=user;Password=pass'

DATABASE RUN vs DATABASE SEED

Two commands execute SQL scripts at different stages:

DATABASE RUN — Executes during schema creation (early in the build):

• Used for: Creating tables, adding columns, defining schemas, custom functions
• Timing: Runs during CREATE MODELS stage
• Use when: Tables need to exist before code generation

DATABASE RUN '.wrm/MyProject.sql'           -- Main schema
DATABASE RUN 'sql/custom-functions.sql'    -- Database functions

DATABASE SEED — Executes at the very end (after everything is built):

• Used for: Populating lookup tables, seeding reference data, inserting default records
• Timing: Runs after all code generation and features are processed
• Use when: Data should be inserted after the schema is complete to avoid conflicts with WRM-generated seed data

DATABASE SEED 'sql/seed-enums.sql'         -- Populate ENUM tables
DATABASE SEED 'sql/seed-organisations.sql' -- Create default organisations
DATABASE SEED 'sql/seed-roles.sql'         -- Seed default roles/permissions

Key difference: Use DATABASE SEED to avoid conflicts when features (like AUTH or ORGANISATIONS) also seed data.

---

Composite Models — FLAT and TREE

Composite models join two or more tables and generate a read-only API endpoint with a flattened or nested JSON structure.

FLAT models

Produce a horizontally joined result set — one row per child match (Cartesian expansion).

-- INNER JOIN: only parents that have a matching child
CREATE MODEL FLAT UserSubmissions FROM submissions WHERE (users);

-- LEFT JOIN: all parents, null columns if no child match
CREATE MODEL FLAT EventDetails FROM events WITH (locations);

Generated JSON (FLAT):

[
  { "submissionId": 1, "submissionTitle": "My Entry", "userId": 3, "userName": "Alice", "email": "alice@example.com" }
]

TREE models — child collections

Produce a hierarchical result with List<ChildDto> properties.

-- LEFT JOIN: all parents, empty list if no children
CREATE MODEL TREE UserWithSubmissions FROM users WITH (submissions);

Generated JSON (TREE WITH):

[
  { "userId": 1, "userName": "Alice", "submissions": [
      { "submissionId": 1, "submissionTitle": "My Entry" }
  ]}
]

TREE models — FK expansion

EXPANDING and HAVING replace a foreign key column on the parent with a full nested object.

-- Replace location_id on events with a full LocationDto object
CREATE MODEL TREE EventWithLocation FROM events EXPANDING (locations);

-- Expand multiple FK references in one model
CREATE MODEL TREE SubmissionExpanded FROM submissions EXPANDING (users, events);

Generated JSON (TREE EXPANDING):

[
  {
    "submissionId": 1,
    "submissionTitle": "My Entry",
    "user": { "userId": 3, "userName": "Alice" },
    "event": { "eventId": 2, "eventName": "Hackathon 2026" }
  }
]

Join variant summary

Variant	JOIN type	Result
WHERE	INNER JOIN	only parents with ≥1 matching child
WITH	LEFT OUTER JOIN	all parents; null columns / empty list if no match
EXPANDING	LEFT OUTER JOIN	FK column replaced by nested object; object is null if FK is null or ref missing
HAVING	INNER JOIN	FK column replaced by nested object; only parents with a non-null matched reference returned

---

Features System

WormScript includes a modular feature system. Enable features with the FEATURE sub command:

CREATE PROJECT <name>
FEATURE SOFT DELETE    -- Soft delete (is_deleted column, marks records as deleted)
-- FEATURE HARD DELETE -- Alternative: permanent deletion (default)
FEATURE BASE           -- Base functionality (required by others)
FEATURE AUTH           -- Authentication/Authorization (JWT)
FEATURE ORGANISATIONS  -- Multi-tenancy support with hierarchical organizations
FEATURE USERS          -- User management tables
FEATURE FILEHANDLING   -- File attachment support
FEATURE GRAPHQL        -- GraphQL API layer
FEATURE REDIS          -- Redis-based caching
FEATURE RABBITMQ       -- RabbitMQ event publishing

See Features.md for full list and descriptions.

Feature Dependencies:

Features automatically enable their dependencies. For example:

• ORGANISATIONS requires BASE
• AUTH requires BASE and USERS
• FILEHANDLING requires BASE

---

Project Structure

After running wrm init MyProject react and wrm build, you get:

MyProject/
├── .wrm/
│   ├── MyProject.wrm              # Build script
│   └── MyProject.sql              # Database schema
│
├── MyProjectData/                 # Data Access Layer
│   ├── Config/
│   │   └── DbConnectionConfig.cs
│   ├── Database/
│   │   └── BaseRepository.cs      # Base repository class
│   ├── Models/
│   │   ├── IDatabaseModel.cs      # Model interface
│   │   ├── UserDbModel.cs         # Entity models
│   │   ├── UserFlatModel.cs       # Flat/joined models
│   │   └── UserTreeModel.cs       # Hierarchical models
│   ├── Repositories/
│   │   ├── UserRepository.cs      # Dapper CRUD operations
│   │   └── ...
│   └── MyProjectData.csproj
│
├── MyProjectService/              # API Service Layer
│   ├── Controllers/
│   │   ├── UserController.cs
│   │   └── ...
│   ├── GraphQL/                   # (if GRAPHQL feature enabled)
│   │   ├── UserQLQuery.cs
│   │   └── UserQLMutation.cs
│   ├── MCP/                       # (if MCP feature enabled)
│   │   └── UserMCPController.cs
│   ├── Security/                  # (if AUTH feature enabled)
│   │   ├── AuthController.cs
│   │   └── JwtTokenService.cs
│   ├── Config/
│   ├── Logging/
│   ├── Program.cs
│   ├── appsettings.json
│   └── MyProjectService.csproj
│
├── MyProjectReact/                # Web UI (React)
│   ├── src/
│   │   ├── components/
│   │   │   └── project/
│   │   │       └── User/
│   │   │           ├── UserApi.js
│   │   │           ├── UserForm.jsx
│   │   │           ├── UserTable.jsx
│   │   │           ├── UserCard.jsx
│   │   │           └── UserDropdown.jsx
│   │   ├── routes/
│   │   └── layouts/
│   └── package.json
│
├── MyProject.Tests/               # Unit Tests
│   ├── UserDbFullTests.cs
│   ├── UserDbQuickTests.cs
│   └── MyProject.Tests.csproj
│
├── docker/                        # Docker Configurations
│   ├── docker-compose.yml
│   ├── docker-compose.prod.yml
│   └── Dockerfile
│
└── MyProject.sln                  # Solution file

---

Generated Output

API Controllers

For each table, WormScript generates:

UserController.cs:

[ApiController]
[Route("api/[controller]")]
public class UserController : ControllerBase
{
    private readonly UserRepository _repository;

    [HttpGet]
    public async Task<IActionResult> GetAll() { ... }

    [HttpGet("paged")]
    public async Task<IActionResult> GetAllPaged(int page, int pageSize) { ... }

    [HttpGet("{id}")]
    public async Task<IActionResult> GetById(int id) { ... }

    [HttpPost]
    public async Task<IActionResult> Create([FromBody] UserDbModel model) { ... }

    [HttpPut("{id}")]
    public async Task<IActionResult> Update(int id, [FromBody] UserDbModel model) { ... }

    [HttpDelete("{id}")]
    public async Task<IActionResult> Delete(int id) { ... }

    // Custom finder methods based on column annotations
    [HttpGet("email/{email}")]
    public async Task<IActionResult> FindByEmail(string email) { ... }
}

Data Models

WormScript generates a dual-model architecture:

DbModels (internal, snake_case for Dapper):

internal class UserDbModel : IDatabaseModel
{
    public int user_id { get; set; }
    public DateTime created_at { get; set; }
    public int? created_by { get; set; }
    public bool is_deleted { get; set; }
    public string username { get; set; }
    public string email { get; set; }
}

DTOs (public, PascalCase for API with XML docs):

public class UserDto
{
    /// <summary>
    /// Unique identifier for the User
    /// </summary>
    [JsonIgnore]
    public int Id { get; set; }

    /// <summary>
    /// Username (required)
    /// </summary>
    public string Username { get; set; }

    /// <summary>
    /// Email (required)
    /// </summary>
    public string Email { get; set; }
}

Mappers are generated to convert between DbModel and DTO internally in the repository layer. Controllers and services work exclusively with DTOs.

Flat Models: Read-only models for joined queries across tables (see Flat and Tree Models).

Tree Models: Hierarchical models with nested sub-objects for parent-child relationships.

Dapper Repositories

UserRepository.cs:

public class UserRepository : BaseRepository
{
    public async Task<UserDbModel> GetById(int id) { ... }
    public async Task<IEnumerable<UserDbModel>> GetAll() { ... }
    public async Task<PagedResult<UserDbModel>> GetAllPaged(int page, int pageSize) { ... }
    public async Task<int> Create(UserDbModel model) { ... }
    public async Task<bool> Update(UserDbModel model) { ... }
    public async Task<bool> Delete(int id) { ... }
    public async Task<bool> Upsert(UserDbModel model) { ... }

    // Custom finders based on ## annotations
    public async Task<UserDbModel> FindByEmail(string email) { ... }
}

Web Components

UserApi.js:

const API_BASE = '/api/user';

export const UserApi = {
    getAll: () => fetch(API_BASE),
    getById: (id) => fetch(`${API_BASE}/${id}`),
    create: (data) => fetch(API_BASE, { method: 'POST', body: JSON.stringify(data) }),
    update: (id, data) => fetch(`${API_BASE}/${id}`, { method: 'PUT', body: JSON.stringify(data) }),
    delete: (id) => fetch(`${API_BASE}/${id}`, { method: 'DELETE' }),
};

---

Configuration

Database Annotations

Use SQL comments to control code generation:

CREATE TABLE users(
    userId INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    email VARCHAR(255) NOT NULL,           
    username VARCHAR(255) NOT NULL,
    organization_id INTEGER,                -- '_id' allows for automatic foreign key detection
    nickname VARCHAR(100)
);
COMMENT ON COLUMN user.email IS '##';       -- Creates FindByEmail() method in API
COMMENT ON COLUMN user.username IS '##';    -- Creates FindByUsername() method in API
COMMENT ON COLUMN user.nickname IS 'HIDE';  -- field is hidden from API and UI

COMMENT ON TABLE users IS 'PAGED';          -- API must use the skip & take parameters for paging

The ## fieldname annotation tells WormScript to generate a finder method for that column.

Special Comment Annotations

FindBy

Creation of a lookup/finder method for the field. Add ',PAGED' to indicate the method should return paged results.

COMMENT ON COLUMN table.field IS '##'

Name

Marks the field as a "name" field for display purposes.

COMMENT ON COLUMN table.field IS 'NAME'

Every table should have one field as the designated name field. If one is not specified, WormScript will look for common candidates like name, title, or description. This may be used in dropdowns and other UI elements. It doesn't have to be unique.

Hide

Hides the field from API and UI.

COMMENT ON COLUMN table.field IS 'HIDE'

Enumeration tables

Defines the table as an enumeration.

COMMENT ON TABLE table IS 'ENUM'

PAGED tables

Marks the table for paginated API endpoints. Generated controllers and repositories will include skip and take parameters.

COMMENT ON TABLE table IS 'PAGED'

READONLY tables

Marks a table as read-only. No insert, update, or delete methods are generated.

COMMENT ON TABLE table IS 'READONLY'

Attachment support

When the FILEHANDLING feature is enabled, attachment endpoints are generated for tables that opt in. By default, tables must explicitly include the ATTACHMENTS keyword in their table comment. If FileHandlingByDefault is enabled in config, all eligible tables get attachments unless they opt out with NOATTACHMENTS.

COMMENT ON TABLE documents IS 'PAGED, ATTACHMENTS'    -- opt-in to file attachments
COMMENT ON TABLE lookups IS 'ENUM, NOATTACHMENTS'      -- explicitly opt-out

Route grouping (ROUTE=<group>)

Controls the URL prefix of the generated controller. By default, controllers are generated at api/<table>. Adding ROUTE=<group> to the table comment routes that controller under api/<group>/<table>, letting you partition the API surface by domain without splitting projects.

COMMENT ON TABLE archimate_exchange_files IS 'PAGED, ROUTE=archimate';
-- generated route: api/archimate/archimateexchangefiles

COMMENT ON TABLE ai_agents IS 'PAGED, ROUTE=ai';
-- generated route: api/ai/aiagents

The annotation is parsed out of the table comment before other keywords — it can appear in any position alongside ENUM, PAGED, TREE, FLAT, etc. Values are case-sensitive and must not contain whitespace, commas, or semicolons.

Precedence (highest to lowest):

1. ROUTE 'api/<group>' on the CREATE API CONTROLLERS script command (see below) — overrides every table's annotation for that batch
2. ROUTE=<group> in the table comment
3. Default api

CREATE API CONTROLLERS script overrides

The .wrm script can generate controllers in batches with an explicit route prefix and/or a filtered table list. This is how multi-service layouts are assembled — one CREATE API CONTROLLERS per route group, writing into its own project folder.

CREATE API CONTROLLERS ROUTE 'api/archimate' TABLES archimate_exchange_files, archimate_element_classifications;
CREATE API CONTROLLERS ROUTE 'api/ai' TABLES ai_agents, ai_skills, ai_suggestions;
CREATE API CONTROLLERS;   -- everything else at default api/<table>

• ROUTE '<prefix>' — sets the URL prefix for every controller generated in this batch. Overrides any ROUTE= annotation on the included tables.
• TABLES t1, t2, ... — restricts generation to the listed tables. Omit to include all tables.

A common pattern is to pair this with SQL annotations so architecture-as-data drives the default layout, and the build script only overrides when the distribution needs to change.

SERVICE=<suffix> (reserved)

Reserved for multi-project scaffolding (Option B). Parsed and stored on the table model but not yet emitted by the generator.

Column Naming Conventions

Columns with certain names are treated specially for UI generation:

Column Name Contains	UX Effect
latitude	UX builds data-entry and validation relevant to a latitude or longitude
longitude	UX builds data-entry and validation relevant to a latitude or longitude
email	Email UX and validation
password	Password UX and validation
enum	Builds radio selection based on the referenced ENUM TABLE
w3w	UX data-entry and handling for What3Words

Database Support

PostgreSQL (Recommended)

CREATE TABLE users(
    userId INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    username VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Features:

• GENERATED ALWAYS AS IDENTITY for auto-increment
• LIKE clause for table inheritance
• TIMESTAMPTZ for timestamps
• JSON/JSONB support
• Schema support

MySQL (Partial support)

CREATE TABLE users(
    userId INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

---

Examples

Complete Example

1. Create project:

wrm init BlogApp react
cd BlogApp

2. Define schema (.wrm/BlogApp.sql):

DROP TABLE IF EXISTS posts;
CREATE TABLE posts(
    post_id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    author_id INTEGER NOT NULL,
    -- ## author_id
    published_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE
);

DROP TABLE IF EXISTS comments;
CREATE TABLE comments(
    comment_id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    post_id INTEGER NOT NULL,
    -- ## post_id
    content TEXT NOT NULL,
    author_name VARCHAR(255),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

3. Configure build (.wrm/BlogApp.wrm):

CREATE PROJECT BlogApp
    CONNECTION POSTGRES 'Host=localhost:5432;Database=blogapp;Username=postgres;Password=postgres'
    FEATURE SWAGGER
    FEATURE SOFT DELETE
    FEATURE BASE
    FEATURE USERS

DATABASE RUN '.wrm/BlogApp.sql'

CREATE TESTDATA IFEMPTY USECASES 20
CREATE MODELS
CREATE API SERVICE
CREATE COMPONENTS

4. Build:

wrm build

5. Run API:

cd BlogAppService
dotnet run

Your API is now running with:

• GET /api/post - Get all posts
• GET /api/post/paged?page=1&pageSize=10 - Get paged posts
• GET /api/post/{id} - Get post by ID
• GET /api/post/author/{authorId} - Get posts by author
• POST /api/post - Create post
• PUT /api/post/{id} - Update post
• DELETE /api/post/{id} - Soft delete post
• GET /api/comment/post/{postId} - Get comments by post

---

Flat and Tree Models

WormScript can generate composite models that join data from multiple tables. These are read-only models useful for API responses that combine related data.

Flat Models

Flat models join columns from related tables into a single denormalized model. Use HAVING when the primary table's ID appears in the subtable (parent expands on children), or WITH when the subtable's ID appears in the primary table (child refers to parent).

-- Parent-to-child: star_system_id appears in stars and planets
CREATE MODEL FLAT StarSystemDetail FROM star_systems HAVING stars, planets;

-- Child-to-parent: organisation_id appears in the users table
CREATE MODEL FLAT UserDetail FROM users WITH organisations;

-- Use ALL to include every table that references the primary key
CREATE MODEL FLAT StarSystemFull FROM star_systems HAVING ALL;

Tree Models

Tree models create hierarchical structures with nested sub-objects using Dapper multi-mapping. The generated repository uses SQL joins and splits results into parent/child objects.

CREATE MODEL TREE StarSystemTree FROM star_systems HAVING stars, planets;

This generates a model where StarSystemTree contains nested collections of Star and Planet objects.

---

CREATE LOOKUP

The CREATE LOOKUP command generates additional finder methods (repository + API endpoint) for a table, allowing you to query records by one or more columns. This is the script-based equivalent of using ## column comments but gives you explicit control over multi-column lookups and custom naming.

Syntax

CREATE LOOKUP ["<name>"] ON '<table_name>' BY '<field_list>';

Part	Required	Description
"<name>"	No	Optional custom name for the generated method. If omitted, WormScript generates one automatically as FindBy<Field1>[And<Field2>...]
ON '<table_name>'	Yes	The database table to create the lookup for. Must be a table already read by CREATE MODELS
BY '<field_list>'	Yes	One or more column names to look up by, joined with & for compound lookups

Examples

Single-column lookup:

CREATE LOOKUP ON 'attachments' BY 'attachment_type';

Generates FindByAttachmentType(string attachmentType) — returns all attachments matching the given type.

Multi-column lookup:

CREATE LOOKUP ON 'entity_attachments' BY 'entity_id&entity_type&organisation_id';

Generates FindByEntityIdAndEntityTypeAndOrganisationId(int entityId, string entityType, int organisationId) — returns all entity attachments for a specific entity within an organisation.

Custom-named lookup:

CREATE LOOKUP "FindActiveByOrg" ON 'entity_attachments' BY 'availability_policy_id&organisation_id';

Generates FindActiveByOrg(int availabilityPolicyId, int organisationId) instead of the auto-generated name.

What Gets Generated

For each CREATE LOOKUP, WormScript generates:

1. Repository method — A Dapper query method in the entity's repository class that accepts the lookup column(s) as parameters and returns matching records.
2. API endpoint — A GET route on the entity's controller, e.g. GET /api/entityattachment/attachmenttype/{attachmentType}.
3. FindBy API endpoint — A controller method wired to call the repository finder.

Usage Notes

• CREATE LOOKUP must appear after CREATE MODELS in your .wrm script, as the table metadata must be loaded first.
• The table name should match the database table name (snake_case), not the model name.
• Column names in the BY clause must be actual database column names (snake_case).
• Duplicate lookups (same fields on the same table) are silently skipped.
• Features like AUTH and FILEHANDLING automatically register their own lookups via internal .wrm scripts — you don't need to add those manually.

Real-World Example

CREATE PROJECT MyApp
    CONNECTION POSTGRES 'Host=localhost;Database=myapp;Username=postgres;Password=postgres'
    FEATURE BASE
    FEATURE AUTH
    FEATURE FILEHANDLING

DATABASE RUN '.wrm/MyApp.sql'

CREATE MODELS

-- Custom lookups beyond what ## annotations provide
CREATE LOOKUP ON 'attachments' BY 'attachment_type';                        -- All attachments of a given type
CREATE LOOKUP ON 'attachments' BY 'storage_type_id&organisation_id';        -- All attachments for an org stored a certain way
CREATE LOOKUP ON 'entity_attachments' BY 'entity_id&entity_type&organisation_id';  -- All attachments for a specific entity

CREATE API SERVICE
CREATE COMPONENTS

---

Template Fragment System (WRM_APPLY)

Templates can include reusable fragment files using the //WRM_APPLY directive. Fragment files are prefixed with # and contain reusable code blocks.

// In a controller template:
//WRM_APPLY "#GetAllPaged.cs"
//WRM_APPLY "#MetaData.cs"
//WRM_APPLY "#JsonSchema.cs"

At build time, each //WRM_APPLY directive is replaced with the contents of the referenced fragment file. Fragment files support all the same WRM conditional blocks and token replacements as regular templates.

Available fragment templates: | Fragment | Description | |----------|-------------| | #GetAllPaged.cs | GetAll endpoint with optional pagination | | #FindBy.cs | Repository FindBy method | | #FindByApi.cs | Controller FindBy endpoint | | #FindByOrg.cs | Organisation-scoped FindBy method | | #MetaData.cs | Metadata endpoint returning entity field definitions | | #JsonSchema.cs | JSON Schema endpoint for the entity DTO | | #HardDelete.cs | Permanent delete endpoint | | #SoftDelete.cs | Soft-delete endpoint with optional force-delete |

---

Naming Conventions

WormScript intelligently converts between naming conventions:

Context	Convention	Example
Database	snake_case	user_profile
C# Classes	PascalCase	UserProfile
C# Properties	PascalCase	UserProfile
JavaScript	camelCase	userProfile
UI Labels	Spaced	User Profile
Singular Forms	Auto	users → User

---

Dependencies

Runtime Requirements

• .NET 8.0 or 9.0 SDK/Runtime
• Database server (PostgreSQL or MySQL)

Generated Project Dependencies

The generated projects include:

• ASP.NET Core 8.0/9.0
• Dapper 2.1.66
• Npgsql (for PostgreSQL)
• Serilog (logging)
• Swashbuckle.AspNetCore + Annotations (Swagger/OpenAPI)
• Optional: JWT Bearer, Entity Framework Core, HotChocolate (GraphQL)

---

Contributing

WormScript is developed by Furniss Software. For issues, feature requests, or contributions:

1. Check existing issues
2. Follow the project's coding standards
3. Test generated code thoroughly
4. Submit pull requests with clear descriptions

---

License

Copyright (c) 2026 David Furniss. All rights reserved.

---

Support

• Documentation: Check .wrm/ folder examples after running wrm init
• Issues: Report bugs via the project repository
• Database Connection: Use wrm test connection to diagnose connectivity
• Debugging: Use wrm list commands to inspect project state

---

Supported Technologies

Backend

• .NET 9.0 - Core framework
• ASP.NET Core - Web API
• Entity Framework Core - Schema reading
• Dapper - Generated CRUD operations with multi-mapping
• Serilog - Logging

Frontend

• React - Modern UI library
• React JSX - Advanced component patterns
• CoreUI - Bootstrap-based admin templates

Databases

• PostgreSQL (primary, fully supported)
• MySQL (supported)

Docker

• Docker - Containerization of database + API
• Docker Compose - Multi-container orchestration

---

Changelog

v3.8.1

v3.8.0

• Released to NuGet

v3.7.0

• Released to NuGet
• RabbitMQ Subscriber project — New CREATE RABBITMQ SERVICE and CREATE RABBITMQ SUBSCRIBERS commands generate a complete .NET hosted-service project that consumes from RabbitMQ publisher queues. Includes a full project scaffold (SubscriberProject/), per-entity <Entity>RabbitMqSubscriber hosted-service classes, and automatic Program.cs DI registration patching. Optional NOTIFY modifier wires INotifier<T> injection into each subscriber for downstream write-time notification. Requires FEATURE RABBITMQ in the parent project
• INotifier<T> / NoNotifier<T> in generated projects — Generated API and subscriber projects now include INotifier<T> and NoNotifier<T> in their Services/ folder, providing a fire-and-forget notification abstraction that can be replaced with any backend
• Multiple targeted solution files — WriteSolutionFile() now emits up to five focused .sln files: ProjectNameAll.sln (root), ProjectNameData.sln, ProjectNameApi.sln, ProjectNameMCP.sln, and ProjectNameSubscriber.sln (emitted only when the subscriber .csproj exists). Replaces the single ProjectName.sln
• ProjectBuilder partial class refactor — Split the 1,400-line orchestrator into eight focused partial-class files: .cs (core), .Models.cs, .Api.cs, .Mcp.cs, .Subscriber.cs, .Solution.cs, .Azure.cs, .Web.cs. Zero behaviour change; all public APIs unchanged
• Docker Compose improvements — docker-compose.yml and docker-compose.api.yml updated with improved REDIS and RABBITMQ service definitions, health checks, and named volume configuration

v3.6.2

• Released to NuGet
• RabbitMQ Notifier — New NOTIFY=RABBITMQ support generates a per-entity <Entity>RabbitMqNotifier class (fire-and-forget) alongside the existing publisher. Shares the project-wide topic exchange; each entity gets a dedicated notification queue <project>.<entity>.notify with binding <project>.<entity>.notify.#. Past-tense routing keys: inserted | updated | deleted | deletedall | markdeleted | restored
• FEATURE RABBITMQ ... NOTIFY CORE|ALL — New optional subcommand on the FEATURE RABBITMQ line automatically applies NOTIFY=RABBITMQ and the service layer to tables without per-table annotation. CORE targets WRM-managed feature tables (users, organisations, auth, etc.); ALL targets every non-hidden, non-composite table. Tables with an explicit annotation or a publisher are never overridden
• CORE table annotation — Feature SQL scripts now mark all WRM-managed tables with CORE in their COMMENT ON TABLE. Used by ApplyRabbitMqNotifyScope() to identify feature tables; transparent to all other annotation processing
• Bare PUBLISH / NOTIFY keywords — COMMENT ON TABLE … IS 'PUBLISH' and 'NOTIFY' now default to RABBITMQ type, so you don't need to write PUBLISH=RABBITMQ explicitly
• CACHE as alias for CACHED — COMMENT ON TABLE … IS 'CACHE' is now accepted alongside CACHED
• Publisher + Notifier conflict guard — Declaring both PUBLISH=<type> and NOTIFY=<type> on the same table now raises a hard error at build time; a publisher delegates writes to a subscribing service which must own any downstream notification
• #FindByService.cs refactored — Service-layer FindBy and DeleteBy fragments now use injected _repository instead of instantiating new ModelRepository(...). DeleteBy gains //WRM_IF_PUBLISH and //WRM_IF_NOTIFY blocks for publisher/notifier integration
• Publisher file naming fix — RabbitMQ publisher output file now uses BaseTableName (e.g. SubeventsRabbitMqPublisher.cs) matching the service layer pattern; was incorrectly using BaseModelName
• Table comment parsing refactored — Improved parsing of SQL table comment annotations for cleaner, more maintainable code
• Metadata JSON copying to target project — Generated projects now include metadata JSON files for enhanced configuration and introspection
• RabbitMQ Publisher improvements — Enhanced RabbitMQ publisher implementation for more reliable event publishing
• REDIS command bug fix — Fixed command syntax issue in Redis operations
• Documentation updates — Updated CLAUDE.md and project docs for improved clarity

v3.6.1

• Released to NuGet
• wrm run CLI command — wrm run [api|mcp] [<script>] launches the generated API or MCP service via dotnet run. Script selection follows the same rule as wrm build (single .wrm in ./.wrm, or one named explicitly). Validates that the script contains exactly one CREATE API SERVICE (or CREATE MCP SERVICE for MCP) and at most one of each CREATE API CONTROLLERS / CREATE MCP CONTROLLERS
• wrm deploy CLI command — wrm deploy [<target>] runs make-devcert.ps1 then publish-<target>.ps1 from the project's Development/ folder. Default target is docker. Supports docker, docker-api, docker-mcp, docker-cloudflare, docker-prod, dotnet, plus any custom publish-<name>.ps1 dropped into the folder
• ScriptCommandCounter validator — pure-regex helper that counts CREATE API|MCP SERVICE|CONTROLLERS phrases in a script and enforces the run-target rules; covered by 7 unit tests
• CommandRunner.RunService and RunPowerShellScript — new methods for streaming dotnet run output and dispatching PowerShell scripts (auto-falls back from pwsh to powershell.exe)
• ProjectBuilder.RunProject / DeployProject — execute the script in inert RUN / DEPLOY list-mode so config paths populate without code generation, then shell out to dotnet or pwsh
• Help pages for run and deploy — wrm help run and wrm help deploy describe usage and options
• Feature WRM scripts support composite models — Feature .wrm scripts (e.g. 10-organisations.wrm) can now declare CREATE MODEL FLAT|TREE commands alongside CREATE LOOKUP. Models declared by a feature are generated automatically when that feature is enabled, without any user .wrm script entry
• EXPANDING / HAVING join types corrected — EXPANDING now uses LEFT OUTER JOIN (ref object is null when FK is null or ref missing); HAVING now uses INNER JOIN (only parents with a non-null matched reference are returned). Previously the two join types were swapped
• Nullable sub-model property for EXPANDING — Generated C# property for an EXPANDING reference is now typed as RefTypeDbModel? (nullable); HAVING properties remain non-nullable
• ModelTable.IsComposite flag — Composite tables set IsComposite = true so CreateModels() skips re-processing them. Prevents feature-declared composite models (which run before CreateModels()) from being overwritten by the plain-model generation pass
• RunFeatureWrmScript / RunFeatureWrmScripts rename — ProjectBuilder.AddLookups() renamed to RunFeatureWrmScript(); WrmScriptReader.AddFeatureLookups() renamed to RunFeatureWrmScripts() to reflect their broader purpose
• Feature WRM script missing-file tolerance — RunFeatureWrmScript() silently skips a missing script file (logs at Debug) rather than throwing

v3.3.5

• Released to NuGet
• Composite FLAT model generation — CREATE MODEL FLAT name FROM parent WHERE|WITH (children) generates horizontally joined read models with INNER or LEFT JOIN SQL, Dapper multi-mapping repositories, read-only controllers, and service classes
• Composite TREE model generation — CREATE MODEL TREE name FROM parent WHERE|WITH|EXPANDING|HAVING (children) generates hierarchical models with nested List<ChildDto> collections (WHERE/WITH) or FK-replaced nested objects (EXPANDING/HAVING)
• ORGANISATIONS support for composite models — FindByOrganisation() and FindByOrganisationAsync() generated in FLAT and TREE repositories when parent table has organisation_id, filtering results by the user's organisation hierarchy via recursive CTE
• Async repository methods for TREE models — GetAllAsync() and FindByIdAsync() added to !ModelRepositoryTree.cs template using Dapper QueryAsync with multi-mapping
• Flat controller DI fix — !MVCControllerFlat.cs now correctly injects AppConfig (not DbConnectionConfig) to match ControllerCommon base class
• ORGS substring replacement fix — ComponentWriter.CreateMethods() now strips ORGS-variant tokens before the shorter base tokens to prevent literal ORGS appearing in generated repository method signatures
• GetBaseTable case-insensitive lookup — Project.GetBaseTable() now converts input to PascalCase and matches case-insensitively against both BaseTableName and DatabaseTableName
• Composite model DatabaseTableName isolation — Composite ModelTable instances set their own DatabaseTableName to the model name to prevent SingleOrDefault collision in _project.Tables
• Bidirectional FK detection — FLAT model generation detects both forward FK (parent has child_id) and reverse FK (child has parent_id) when building JOIN conditions
• Service layer generation architecture improvements
• Enhanced multi-schema support

v3.3.4

• Released to NuGet
• Release process refactoring - Version increment now happens after NuGet upload for cleaner release workflow
• SeedData conflicts resolved - Fixed DATABASE SEED command conflicts with existing data
• NameType improvements - Enhanced singular/plural name handling
• SQL for AP (Availability Policies) - Added SQL support for availability policy features
• React TSX Auth fixes - Improved TypeScript/TSX authentication templates
• MVCController file handling refactor - Improved file handling in MVC controller generation
• Multi-schema support - Added SCHEMA keyword for multi-schema databases, WRM_SCHEMA token support
• Service layer consolidation - Service generation moved into API stage for better workflow
• Subscriber service integration - Initial subscriber service implementation
• DataApi enhancements - GetWhere DataApi support and table functionality additions
• Repository improvements - Enhanced FindBy and custom query support

---

Happy Coding with WormScript!

Transform your database into a ready-to-start application in minutes.

Version: 3.8.1 (3.8.0 released) Framework: .NET 9.0 License: Furniss Software (c) 2026