@memberjunction/sql-converter

Overview

A production-grade SQL dialect conversion pipeline that converts SQL Server (T-SQL) to PostgreSQL. The pipeline uses a rule-based architecture where each SQL statement type is handled by a specialized conversion rule, producing deterministic, executable PostgreSQL output.

Current status: 0 errors across the v5.0 baseline (273 stored procedures) + 6 incremental migrations. 465 unit tests passing.

Key Features

• Rule-based conversion with 11 specialized rules
• Statement classification (27 distinct types)
• Context-aware conversion (CREATE TABLE column types inform INSERT boolean casting)
• Ordered output grouping (Tables -> FKs -> Views -> Functions -> Triggers -> Data -> Grants -> Comments)
• Global post-processing with 25+ transformations
• Database verification and cross-database audit
• LLM fallback for unhandled patterns

Architecture

flowchart TD
    A[SQL Server File] --> B[Preprocess<br/>Flyway placeholders]
    B --> C[Split on GO<br/>Batch separator]
    C --> D[SubSplitter<br/>Compound batches]
    D --> E[StatementClassifier<br/>27 statement types]
    E --> F{Statement Type}
    F -->|SKIP_*| G[Skip / Discard]
    F -->|Convertible| H[Match Rules<br/>by AppliesTo]
    H --> I[PreProcess Phase]
    I --> J{BypassSqlglot?}
    J -->|Yes| K[Rule handles<br/>full conversion]
    J -->|No| L[sqlglot transpile]
    L --> M[PostProcess Phase]
    K --> N[Route to<br/>OutputGroup]
    M --> N
    N --> O[Assemble Groups<br/>in dependency order]
    O --> P[PostProcessor<br/>Global cleanup]
    P --> Q[PostgreSQL Output]

Quick Start

Installation

npm install @memberjunction/sql-converter

CLI Usage

npx mj sql-convert --source migrations/v5/Baseline.sql --output migrations/pg/Baseline_PG.sql

Programmatic Usage

import { convertFile, getTSQLToPostgresRules } from '@memberjunction/sql-converter';

const result = convertFile({
  Source: 'path/to/input.sql',
  SourceIsFile: true,
  OutputFile: 'path/to/output.sql',
  Rules: getTSQLToPostgresRules(),
  OnProgress: (msg) => console.log(msg),
});

console.log(`Converted ${result.Stats.Converted} of ${result.Stats.TotalBatches} batches`);

Pipeline Deep Dive

sequenceDiagram
    participant F as File Input
    participant P as Preprocessor
    participant S as Splitter
    participant SS as SubSplitter
    participant C as Classifier
    participant R as Rules Engine
    participant G as Output Groups
    participant PP as PostProcessor
    participant O as Output

    F->>P: Raw SQL (replace ${flyway:defaultSchema} with __mj)
    P->>S: Processed SQL
    S->>SS: GO-separated batches
    SS->>C: Sub-split individual statements

    loop For each statement
        C->>R: StatementType + SQL
        alt SKIP type
            R-->>G: Discard
        else Has matching rule
            R->>R: PreProcess (if defined)
            alt BypassSqlglot = true
                R->>R: PostProcess(original, original, context)
            else BypassSqlglot = false
                R->>R: sqlglot transpile
                R->>R: PostProcess(transpiled, original, context)
            end
            R->>G: Route to appropriate group
        end
    end

    G->>PP: Assembled output (Tables, FKs, Views, ...)
    PP->>O: Final PostgreSQL SQL

Rule System

How Rules Work

Each rule implements IConversionRule:

interface IConversionRule {
  Name: string;                  // Display name
  AppliesTo: StatementType[];    // Which statement types
  Priority: number;              // Lower = runs first
  BypassSqlglot?: boolean;       // Skip sqlglot transpilation
  PreProcess?(sql: string, context: ConversionContext): string;
  PostProcess?(sql: string, originalSQL: string, context: ConversionContext): string;
}

Rule Priority Chain

flowchart LR
    A["CreateTableRule<br/>Priority 10"] --> B["ViewRule<br/>Priority 20"]
    B --> C["ProcedureToFunctionRule<br/>Priority 30"]
    C --> D["FunctionRule<br/>Priority 35"]
    D --> E["TriggerRule<br/>Priority 40"]
    E --> F["InsertRule<br/>Priority 50"]
    F --> G["ConditionalDDLRule<br/>Priority 55"]
    G --> H["AlterTableRule<br/>Priority 60"]
    H --> I["CreateIndexRule<br/>Priority 70"]
    I --> J["GrantRule<br/>Priority 80"]
    J --> K["ExtendedPropertyRule<br/>Priority 90"]

ConversionContext

State accumulated across the pipeline:

interface ConversionContext {
  SourceDialect: string;        // 'tsql'
  TargetDialect: string;        // 'postgres'
  Schema: string;               // '__mj'
  TableColumns: Map<string, Map<string, string>>;  // table -> column -> PG type
  HandWrittenFunctions: Map<string, string>;        // function name -> PG replacement
  CreatedFunctions: Set<string>;   // successfully converted functions
  CreatedViews: Set<string>;       // successfully converted views
}

flowchart LR
    CT["CreateTableRule<br/>Populates TableColumns"] --> IR["InsertRule<br/>Reads TableColumns<br/>for boolean casting"]
    P["ProcedureToFunctionRule<br/>Populates CreatedFunctions"] --> GR["GrantRule<br/>Reads CreatedFunctions<br/>to skip invalid grants"]
    V["ViewRule<br/>Populates CreatedViews"] --> GR

Rule-by-Rule Documentation

1. CreateTableRule (Priority 10)

Applies to: CREATE_TABLE | Bypasses sqlglot: Yes

Converts SQL Server CREATE TABLE statements to PostgreSQL DDL.

Conversions:

• Bracket identifiers [schema].[table] to schema."table"
• SQL Server types to PG types (see Type Mapping table below)
• IDENTITY(1,1) to GENERATED BY DEFAULT AS IDENTITY
• DEFAULT (NEWSEQUENTIALID()) to DEFAULT gen_random_uuid()
• DEFAULT (GETUTCDATE()) to DEFAULT NOW()
• DEFAULT ((0)) for BIT to DEFAULT FALSE
• DEFAULT ((1)) for BIT to DEFAULT TRUE
• Removes CLUSTERED/NONCLUSTERED, ON [PRIMARY], COLLATE
• Quotes PascalCase column names
• Tracks column types in ConversionContext.TableColumns

Example:

-- Input (T-SQL)
CREATE TABLE [__mj].[Users] (
  [ID] [uniqueidentifier] NOT NULL DEFAULT (newsequentialid()),
  [Name] [nvarchar](100) NOT NULL,
  [IsActive] [bit] NOT NULL DEFAULT ((1)),
  CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([ID])
)

-- Output (PostgreSQL)
CREATE TABLE __mj."Users" (
  "ID" UUID NOT NULL DEFAULT gen_random_uuid(),
  "Name" VARCHAR(100) NOT NULL,
  "IsActive" BOOLEAN NOT NULL DEFAULT TRUE,
  CONSTRAINT "PK_Users" PRIMARY KEY ("ID")
);

2. ViewRule (Priority 20)

Applies to: CREATE_VIEW | Bypasses sqlglot: Yes

Converts CREATE VIEW statements with full expression conversion.

Conversions:

• CREATE [OR ALTER] VIEW to CREATE OR REPLACE VIEW
• CROSS APPLY to CROSS JOIN LATERAL
• OUTER APPLY to LEFT JOIN LATERAL
• Adds ON TRUE for LEFT JOIN LATERAL without ON clause
• Wraps LATERAL function calls: func(args) AS alias to (SELECT * FROM func(args)) AS alias
• STRING_AGG WITHIN GROUP rewriting
• All expression helpers (date functions, CHARINDEX, STUFF, etc.)
• Column reference quoting: alias.PascalCol to alias."PascalCol"
• Skips views referencing sys.* tables or flyway

3. ProcedureToFunctionRule (Priority 30)

Applies to: CREATE_PROCEDURE | Bypasses sqlglot: Yes

Converts SQL Server stored procedures to PostgreSQL functions.

Conversions:

• Parses parameters (name, type, default, OUTPUT flag)
• Maps SQL Server types to PG types for parameters
• Replaces @var with p_var in function body
• @@ROWCOUNT to _v_row_count, @@ERROR to 0
• WHILE BEGIN...END to WHILE...LOOP...END LOOP
• IF BEGIN...END to IF...THEN...END IF
• SET @var = to p_var :=
• Extracts DECLARE statements into PG DECLARE block
• INSERTED to NEW, DELETED to OLD
• Determines return type (VOID, TABLE row type, SETOF)
• Wraps in CREATE OR REPLACE FUNCTION ... $$ LANGUAGE plpgsql;
• Skips procedures with sys.* references, temp tables, STRING_SPLIT

4. FunctionRule (Priority 35)

Applies to: CREATE_FUNCTION | Bypasses sqlglot: Yes

Converts SQL Server functions (scalar and table-valued) to PostgreSQL.

Features:

• Checks for hand-written function replacements first (7 built-in: StripToAlphanumeric, GetProgrammaticName, ToTitleCase, ToProperCase, ExtractVersionComponents, parseEmail, parseDomain, fnInitials)
• Inline table-valued functions to RETURNS TABLE(...) AS $$ ... $$ LANGUAGE sql;
• Scalar functions to RETURNS type AS $$ ... $$ LANGUAGE plpgsql;
• WITH recursive CTE detection to WITH RECURSIVE
• DECLARE extraction, WHILE/IF conversion, type mapping

5. TriggerRule (Priority 40)

Applies to: CREATE_TRIGGER | Bypasses sqlglot: Yes

Converts SQL Server triggers to PostgreSQL trigger functions.

Pattern:

-- Input
CREATE TRIGGER trgAfterUpdate ON [__mj].[Users]
AFTER UPDATE AS ...

-- Output
CREATE OR REPLACE FUNCTION __mj."trgAfterUpdate_func"() RETURNS TRIGGER AS $$
BEGIN ... END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER "trgAfterUpdate" AFTER UPDATE ON __mj."Users"
FOR EACH ROW EXECUTE FUNCTION __mj."trgAfterUpdate_func"();

• Special handling for UpdatedAt triggers (common pattern)
• Skips triggers using TRIGGER_NESTLEVEL() or UPDATE()
• INSERTED to NEW, DELETED to OLD

6. InsertRule (Priority 50)

Applies to: INSERT, UPDATE, DELETE | Bypasses sqlglot: Yes

Converts DML statements with expression helpers.

Order of operations (important for correctness):

1. Convert identifiers
2. Remove N prefix
3. Remove COLLATE
4. Convert common functions (BEFORE quoting to avoid quoting function names like LEN)
5. String concatenation (+ to ||)
6. CHARINDEX, STUFF, CONVERT, IIF, CAST types
7. Quote PascalCase identifiers
8. Ensure semicolon

7. ConditionalDDLRule (Priority 55)

Applies to: CONDITIONAL_DDL | Bypasses sqlglot: Yes

Converts IF NOT EXISTS conditional DDL patterns.

Two patterns:

1. Conditional Index: IF NOT EXISTS (SELECT ... sys.indexes ...) CREATE INDEX to CREATE INDEX IF NOT EXISTS
2. DO $$ Block: IF NOT EXISTS (...) BEGIN ... END to DO $$ BEGIN IF NOT EXISTS (...) THEN ... END IF; END $$;

Features:

• Type conversions (NVARCHAR to VARCHAR, BIT to BOOLEAN, etc.)
• INFORMATION_SCHEMA casing (PG requires lowercase)
• PascalCase identifier quoting (skips string literals to avoid corrupting UUIDs)

8. AlterTableRule (Priority 60)

Applies to: FK_CONSTRAINT, PK_CONSTRAINT, CHECK_CONSTRAINT, UNIQUE_CONSTRAINT, ENABLE_CONSTRAINT, ALTER_TABLE | Bypasses sqlglot: Yes

Converts ALTER TABLE statements and constraint definitions.

Conversions:

• Identifier conversion, COLLATE removal
• Type conversions for defaults and columns
• GETUTCDATE() to NOW(), NEWSEQUENTIALID() to gen_random_uuid()
• Removes CLUSTERED/NONCLUSTERED, ASC/DESC in constraints, ON [PRIMARY]
• FK constraints: adds DEFERRABLE INITIALLY DEFERRED
• CHECK constraints: adds NOT VALID
• WITH CHECK CHECK CONSTRAINT to comment (no-op in PG)

9. CreateIndexRule (Priority 70)

Applies to: CREATE_INDEX | Bypasses sqlglot: Yes

Converts CREATE INDEX statements.

Conversions:

• Removes CLUSTERED/NONCLUSTERED
• Removes WITH (PAD_INDEX = ...) option blocks
• Removes ON [filegroup]
• Removes INCLUDE columns (simplified)
• Fixes boolean values: =(1) to =TRUE, =(0) to =FALSE

10. GrantRule (Priority 80)

Applies to: GRANT, REVOKE | Bypasses sqlglot: Yes

Converts GRANT and REVOKE statements.

Features:

• Adds FUNCTION keyword for EXECUTE grants (PG requirement)
• Skips grants on functions not in CreatedFunctions set
• Skips grants on views (vw*) not in CreatedViews set
• Removes N prefix from strings

11. ExtendedPropertyRule (Priority 90)

Applies to: EXTENDED_PROPERTY | Bypasses sqlglot: Yes

Converts sp_addextendedproperty calls to PostgreSQL COMMENT statements.

Supports:

• Named parameters (@name=, @value=, etc.)
• Positional parameters
• TABLE to COMMENT ON TABLE
• VIEW to COMMENT ON VIEW
• COLUMN to COMMENT ON COLUMN
• PROCEDURE/FUNCTION to commented out (PG needs signature)

Statement Classification

The StatementClassifier uses pattern matching to assign one of 27 types:

Type	Examples
CREATE_TABLE	CREATE TABLE [schema].[name] (...)
CREATE_VIEW	CREATE VIEW, CREATE OR ALTER VIEW
CREATE_PROCEDURE	CREATE PROC[EDURE] (unless references sys.*)
CREATE_FUNCTION	CREATE FUNCTION
CREATE_TRIGGER	CREATE TRIGGER
CREATE_INDEX	CREATE [UNIQUE] [NONCLUSTERED] INDEX
ALTER_TABLE	ALTER TABLE ADD COLUMN, ALTER TABLE ALTER COLUMN
FK_CONSTRAINT	ALTER TABLE ADD CONSTRAINT ... FOREIGN KEY
PK_CONSTRAINT	ALTER TABLE ADD CONSTRAINT ... PRIMARY KEY
CHECK_CONSTRAINT	ALTER TABLE ADD CONSTRAINT ... CHECK
UNIQUE_CONSTRAINT	ALTER TABLE ADD CONSTRAINT ... UNIQUE
ENABLE_CONSTRAINT	ALTER TABLE WITH CHECK CHECK CONSTRAINT
INSERT / UPDATE / DELETE	DML statements
GRANT / DENY / REVOKE	DCL statements
CONDITIONAL_DDL	IF NOT EXISTS (...) BEGIN ... END
EXTENDED_PROPERTY	sp_addextendedproperty
COMMENT_ONLY	Pure comment blocks
SKIP_SESSION	SET NUMERIC_ROUNDABORT, SET ANSI_PADDING, etc.
SKIP_ERROR	IF @@ERROR
SKIP_SQLSERVER	SQL Server-specific code
SKIP_NOCHECK	WITH NOCHECK
SKIP_PRINT	PRINT statements
UNKNOWN	Unclassified statements

Output Grouping

Converted statements are grouped in dependency order:

flowchart TD
    A[DDL: Tables, PKs, Indexes] --> B[Helper Functions fn*]
    B --> C[Views]
    C --> D[Stored Procedures sp*]
    D --> E[Triggers]
    E --> F[Data: INSERT/UPDATE/DELETE]
    F --> G[FK & CHECK Constraints]
    G --> H[Grants]
    H --> I[Comments]
    I --> J[Other / Errors]

Type Mapping Reference

SQL Server	PostgreSQL	Notes
UNIQUEIDENTIFIER	UUID
BIT	BOOLEAN
INT	INTEGER
BIGINT	BIGINT	Unchanged
SMALLINT	SMALLINT	Unchanged
TINYINT	SMALLINT	PG has no TINYINT
FLOAT	DOUBLE PRECISION
REAL	REAL	Unchanged
MONEY	NUMERIC(19,4)
SMALLMONEY	NUMERIC(10,4)
DECIMAL(p,s)	NUMERIC(p,s)
NVARCHAR(n)	VARCHAR(n)
NVARCHAR(MAX)	TEXT
VARCHAR(MAX)	TEXT
NCHAR(n)	CHAR(n)
NTEXT	TEXT
DATETIME	TIMESTAMPTZ
DATETIME2	TIMESTAMPTZ
DATETIMEOFFSET	TIMESTAMPTZ
SMALLDATETIME	TIMESTAMPTZ
IMAGE	BYTEA
VARBINARY(n/MAX)	BYTEA
XML	XML	Unchanged
SQL_VARIANT	TEXT
HIERARCHYID	TEXT
IDENTITY(1,1)	GENERATED BY DEFAULT AS IDENTITY	Column attribute

Function Mapping Reference

T-SQL Function	PostgreSQL Equivalent
ISNULL(a, b)	COALESCE(a, b)
GETUTCDATE()	NOW()
GETDATE()	NOW()
SYSDATETIMEOFFSET()	NOW()
NEWID()	gen_random_uuid()
NEWSEQUENTIALID()	gen_random_uuid()
LEN(s)	LENGTH(s)
SCOPE_IDENTITY()	lastval()
SUSER_SNAME()	current_user
CHARINDEX(sub, str)	POSITION(sub IN str)
STUFF(s, start, len, repl)	OVERLAY(s PLACING repl FROM start FOR len)
DATEADD(unit, n, date)	(date + n * INTERVAL '1 unit')
DATEDIFF(day, start, end)	EXTRACT(DAY FROM (end::TIMESTAMPTZ - start::TIMESTAMPTZ))
DATEPART(unit, date)	EXTRACT(field FROM date)
CONVERT(type, expr)	CAST(expr AS mapped_type)
IIF(cond, t, f)	CASE WHEN cond THEN t ELSE f END
SELECT TOP N	SELECT ... LIMIT N
'a' + 'b' (string concat)	'a' || 'b'
N'text'	'text' (prefix removed)
RAISERROR(msg, n, n)	RAISE EXCEPTION msg

ExpressionHelpers

Shared expression-level converters used across multiple rules:

• convertIdentifiers — [schema].[name] to schema."name"
• convertDateFunctions — DATEADD, DATEDIFF, DATEPART with full unit mapping
• convertCharIndex — 2-arg and 3-arg CHARINDEX
• convertStuff — STUFF to OVERLAY
• convertStringConcat — + to || for string contexts
• convertIIF — Nested IIF with paren-aware argument splitting
• convertTopToLimit — SELECT TOP N to LIMIT N
• convertCastTypes — CAST AS type mappings
• convertConvertFunction — CONVERT(type, expr) to CAST
• removeNPrefix — N’text’ to ‘text’
• removeCollate — Remove COLLATE clauses
• convertCommonFunctions — ISNULL, GETUTCDATE, NEWID, LEN, etc.
• quotePascalCaseIdentifiers — Quote PascalCase identifiers while preserving strings and comments

PostProcessor

Global cleanup pass applied to the complete output. Key transformations:

1. Bracket cleanup — Remove remaining [brackets] (preserve PG array access)
2. Session control removal — SET NOEXEC, SET NUMERIC_ROUNDABORT, SET XACT_ABORT
3. RAISERROR to RAISE EXCEPTION
4. Type name unquoting — "UUID" to UUID, "BOOLEAN" to BOOLEAN, etc.
5. Remaining type conversions — nvarchar to VARCHAR, uniqueidentifier to UUID
6. Boolean fixes — =(1) to =TRUE, =(0) to =FALSE
7. session_replication_role — 'DEFAULT' to 'origin'
8. PL/pgSQL fixes — END\n$$ to END;\n$$, empty BEGIN blocks
9. FK clause ordering — DEFERRABLE after ON DELETE/UPDATE
10. LIKE character classes — SQL Server patterns to PG regex
11. CHECK constraint fixes — len() to LENGTH(), isjson() to JSON validation
12. INFORMATION_SCHEMA — Force lowercase
13. Schema-qualified identifiers — Quote unquoted PascalCase after __mj.
14. ISNULL to COALESCE — Final pass
15. IF OBJECT_ID DROP to DROP ... IF EXISTS
16. GRANT EXEC to GRANT EXECUTE with DO $$ wrapper
17. CREATE INDEX to CREATE INDEX IF NOT EXISTS
18. Long index names — Truncate to 63 chars with MD5 hash suffix
19. Backslash line fix — Fix lines starting with \ in INSERT string literals
20. Excessive blank lines — Collapse 4+ blank lines to 3

Extensibility Architecture

The converter is built on three extensible subsystems that enable support for new source/target dialect combinations beyond T-SQL → PostgreSQL.

RuleRegistry

Central registry for SQL conversion rules. Supports multiple source→target dialect combinations with case-insensitive dialect lookup and automatic priority sorting.

import { RuleRegistry } from '@memberjunction/sql-converter';

// Register rules for a dialect pair
RuleRegistry.RegisterAll(myRules);

// Retrieve sorted rules for a dialect pair
const rules = RuleRegistry.GetRules('tsql', 'postgres');

// Check available combinations
const combos = RuleRegistry.GetRegisteredCombinations();
// [{ Source: 'tsql', Target: 'postgres' }]

// Check if rules exist for a combination
RuleRegistry.HasRules('tsql', 'mysql'); // false

Method	Description
Register(rule)	Register a single rule for its declared SourceDialect/TargetDialect
RegisterAll(rules)	Register multiple rules at once
GetRules(source, target)	Get sorted rules (by priority ascending) for a dialect pair
GetRegisteredCombinations()	List all registered {Source, Target} pairs
HasRules(source, target)	Check if rules exist for a combination
Clear()	Clear all rules (for testing)

Rules self-register via SourceDialect and TargetDialect properties on IConversionRule. The registry uses case-insensitive matching and returns rules sorted by Priority (ascending).

TypeResolver

Centralized SQL type resolution that delegates to @memberjunction/sql-dialect’s DataTypeMap with MJ-specific overrides. All type mapping in the converter goes through this module so knowledge lives in one place.

import { resolveType, parseTypeString } from '@memberjunction/sql-converter';

// Parse a SQL type string into components
const parsed = parseTypeString('NVARCHAR(255)');
// { BaseName: 'NVARCHAR', Length: 255 }

// Resolve a T-SQL type to PostgreSQL
resolveType('UNIQUEIDENTIFIER');    // 'UUID'
resolveType('NVARCHAR(MAX)');       // 'TEXT'
resolveType('DECIMAL(18,2)');       // 'NUMERIC(18,2)'
resolveType('DATETIME');            // 'TIMESTAMPTZ' (MJ override)

// Resolve for inline CAST/CONVERT expressions
resolveInlineType('DATETIME2(7)');  // 'TIMESTAMPTZ'

MJ-specific overrides (applied before the dialect TypeMap):

• DATETIME, DATETIME2, SMALLDATETIME → TIMESTAMPTZ (MJ stores everything with timezone)
• NTEXT → TEXT
• SQL_VARIANT, HIERARCHYID → TEXT

To add support for a new target dialect, add a new branch in the internal getTypeMap() function that constructs the dialect’s TypeMap.

DialectHeaderBuilder

Pluggable interface for generating dialect-specific output file headers. Each target dialect can register a header builder that produces the preamble needed for the converted SQL file.

import { registerHeaderBuilder, getHeaderBuilder, DialectHeaderBuilder } from '@memberjunction/sql-converter';

// Built-in: PostgreSQL header builder (auto-registered)
const pgBuilder = getHeaderBuilder('postgres');
const header = pgBuilder?.BuildHeader('__mj');
// Generates: CREATE EXTENSION, CREATE SCHEMA, SET search_path, implicit int->bool cast

// Register a custom builder for a new dialect
class MySQLHeaderBuilder implements DialectHeaderBuilder {
  readonly TargetDialect = 'mysql';
  BuildHeader(schema: string): string {
    return `SET NAMES utf8mb4;\nCREATE SCHEMA IF NOT EXISTS \`${schema}\`;\nUSE \`${schema}\`;\n`;
  }
}
registerHeaderBuilder(new MySQLHeaderBuilder());

The built-in PostgreSQLHeaderBuilder generates:

• CREATE EXTENSION for pgcrypto and uuid-ossp
• Schema creation and SET search_path
• SET standard_conforming_strings = on
• Implicit INTEGER → BOOLEAN cast upgrade (for SQL Server BIT compatibility)

Extending the Pipeline

Adding a New Rule (Existing Dialect Pair)

1. Create a new file in src/rules/:

import type { IConversionRule, ConversionContext, StatementType } from './types.js';

export class MyNewRule implements IConversionRule {
  Name = 'MyNewRule';
  SourceDialect = 'tsql';
  TargetDialect = 'postgres';
  AppliesTo: StatementType[] = ['SOME_TYPE'];
  Priority = 65;  // Between existing rules
  BypassSqlglot = true;

  PostProcess(sql: string, originalSQL: string, context: ConversionContext): string {
    // Your conversion logic here
    return convertedSQL;
  }
}

2. Register it in TSQLToPostgresRules.ts:

import { MyNewRule } from './MyNewRule.js';

export function getTSQLToPostgresRules(): IConversionRule[] {
  return [
    // ... existing rules
    new MyNewRule(),  // Insert at correct priority position
  ];
}

3. Add tests in src/__tests__/MyNewRule.test.ts

Adding Rules for a New Dialect Direction

To support a new conversion direction (e.g., T-SQL → MySQL):

1. Create rules implementing IConversionRule with SourceDialect = 'tsql' and TargetDialect = 'mysql'
2. Register rules with RuleRegistry.RegisterAll(myMySQLRules)
3. Add a TypeMap branch in TypeResolver.getTypeMap() for the new target dialect
4. Register a DialectHeaderBuilder for the new target via registerHeaderBuilder()
5. Create a convenience function like getTSQLToMySQLRules() for easy consumption

Adding New Type Mappings

Update type mappings in:

• TypeResolver — centralized type resolution (preferred for new dialects)
• CreateTableRule — for CREATE TABLE column types
• ExpressionHelpers.convertCastTypes() — for CAST expressions
• ExpressionHelpers.convertConvertFunction() — for CONVERT() function
• PostProcessor.postProcess() — for final cleanup pass

Verification & Cross-Database Audit

Database Verification

import { ConversionPipeline } from '@memberjunction/sql-converter';

const result = await new ConversionPipeline().Run({
  source: 'input.sql',
  sourceIsFile: true,
  sourceDialect: 'tsql',
  targetDialect: 'postgres',
  verify: true,
  targetConnection: 'postgresql://user:pass@localhost:5432/testdb',
});

Audit Command

npx mj sql-audit --source-connection "mssql://..." --target-connection "postgresql://..." --schema __mj

Compares source and target databases:

• Missing objects (tables, views, functions, triggers, indexes)
• Row count mismatches

Testing

Running Tests

# All tests
cd packages/SQLConverter && npm run test

# Watch mode
cd packages/SQLConverter && npm run test:watch

Test Organization

src/__tests__/
  StatementClassifier.test.ts    # 60+ tests
  SubSplitter.test.ts            # 13 tests
  ExpressionHelpers.test.ts      # 100+ tests
  BatchConverter.test.ts         # 22 tests
  PostProcessor.test.ts          # 50+ tests
  SQLFileSplitter.test.ts        # 24 tests
  DatabaseAuditor.test.ts        # 12 tests
  CreateTableRule.test.ts        # 31 tests
  ViewRule.test.ts               # 15 tests
  ProcedureToFunctionRule.test.ts # 48 tests
  FunctionRule.test.ts           # 20+ tests
  TriggerRule.test.ts            # 13 tests
  InsertRule.test.ts             # 20 tests
  AlterTableRule.test.ts         # 13 tests
  ConditionalDDLRule.test.ts     # 20+ tests
  CreateIndexRule.test.ts        # 15+ tests
  GrantRule.test.ts              # 15+ tests
  ExtendedPropertyRule.test.ts   # 8 tests
  TSQLToPostgresRules.test.ts    # 10+ tests
  ConversionPipeline.test.ts     # 14 tests
  integration.test.ts            # Full pipeline against real PG

Adding Test Cases

Follow the established pattern:

import { describe, it, expect } from 'vitest';
import { MyRule } from '../rules/MyRule.js';
import { createConversionContext } from '../rules/types.js';

const rule = new MyRule();
const context = createConversionContext('tsql', 'postgres');

function convert(sql: string): string {
  return rule.PostProcess!(sql, sql, context);
}

describe('MyRule', () => {
  describe('metadata', () => {
    it('should have the correct name, priority, and applies-to types', () => {
      expect(rule.Name).toBe('MyRule');
      expect(rule.Priority).toBe(65);
    });
  });

  describe('feature area', () => {
    it('should convert X to Y', () => {
      const result = convert('INPUT SQL');
      expect(result).toContain('EXPECTED OUTPUT');
    });
  });
});

Dependencies

• @memberjunction/sqlglot-ts — Python sqlglot wrapper for transpilation fallback
• pg (dev) — PostgreSQL client for integration tests
• vitest (dev) — Test framework

License

ISC