#Adding a New SQL Dialect

This repository supports two runtime models:

• built-in runtime dialects in src/dialects/ (netezza, sqlite)
• optional runtime dialects delivered from sibling extensions/<dialect>/ packages

The most important rule is:

Keep portable SQL semantics in core, and keep driver/runtime specifics in the dialect runtime package.

That means traits, SQL authoring, and shared naming/qualification behavior belong in core-owned files, while connection classes, system queries, DDL generation, and extension activation stay with the runtime dialect package.

#Architecture Overview

graph TD
    A["Extension entry point"] --> B["ConnectionManager"]
    B --> C["DatabaseDialectRegistry"]
    C --> D["DatabaseDialect"]
    D --> E["traits"]
    D --> F["metadataProvider"]
    D --> G["sqlAuthoring"]
    D --> H["runtime connection"]
    H --> I["query execution / DDL"]
    F --> J["schema browsing / completion"]
    G --> K["formatter / validator / LSP"]

    style D fill:#e1f5ff
    style E fill:#fff9c4
    style F fill:#fff9c4
    style G fill:#fff9c4

The core extension owns portable SQL semantics and the registry/contract system. Built-in dialects also own runtime code in src/dialects/*, while optional dialect runtimes live in extensions/<dialect>/src/ and register themselves through the public API.

#1. Decide whether the dialect is built-in or optional

Use a built-in runtime dialect only when the core extension can ship the driver/runtime dependency safely.

Use an optional runtime dialect when the driver is native, large, platform-specific, or otherwise unsuitable for the core VSIX. In that case:

• core still owns the SQL semantics (traits.ts, sql/authoring.ts)
• the sibling extension owns runtime registration and driver-specific implementation

#2. Create the core-owned dialect metadata

Create a dialect folder under src/dialects/<dialect>/ for the parts that must be available before optional extensions activate.

Typical structure:

src/dialects/<dialect>/
├── traits.ts
└── sql/
    └── authoring.ts

Built-in runtime dialects usually also have:

src/dialects/<dialect>/
├── index.ts
├── connectionForm.ts
├── advancedFeatures.ts
├── metadata/
│   └── provider.ts
├── runtime.ts
├── traits.ts
└── sql/
    └── authoring.ts

#3. Define dialect traits

Traits drive identifier formatting, qualification rules, and path completion behavior.

import { createDatabaseDialectTraits } from '../../contracts/database';

export const myDialectTraits = createDatabaseDialectTraits({
    identifiers: {
        quoteStyle: 'double',
        unquotedIdentifierPattern: /^[A-Z_][A-Z0-9_]*$/
    },
    qualification: {
        twoPartNameStyle: 'schema-object',
        twoPartContainerPreference: 'database-over-schema',
        supportsThreePartName: true,
        databaseOnlyReferenceStyle: 'double-dot'
    },
    completion: {
        singleDotPathNamespace: 'schema',
        supportsDoubleDotPath: false
    },
    objects: {
        supportsIndexes: true
    }
});

Current meanings:

• identifiers.quoteStyle: SQL identifier quoting style used by the formatter/helpers
• identifiers.unquotedIdentifierPattern: names that do not require quoting for that dialect
• qualification.twoPartNameStyle: whether A.B should be treated as schema.object or database.object
• qualification.supportsThreePartName: whether database.schema.object is emitted/treated as native notation
• qualification.databaseOnlyReferenceStyle: how database + object should be rendered when no schema is provided
• completion.singleDotPathNamespace: how db. / schema. style completion paths should be interpreted
• completion.supportsDoubleDotPath: whether DB..TABLE completion paths are valid
• objects.supportsIndexes: currently a capability-style flag for future shared object handling

#4. Register the traits in the static core map

Add the new traits entry in src/core/dialectTraits.ts.

import { myDialectTraits } from '../dialects/myDialect/traits';

const DIALECT_TRAITS_BY_KIND = {
    // ...existing kinds
    myDialect: myDialectTraits
};

Also update SUPPORTED_DATABASE_KINDS and aliases in src/contracts/database/index.ts when introducing a new kind.

#5. Add SQL authoring in the shared authoring package

SQL authoring is loaded eagerly from core so formatter, validator, completion, and the LSP keep working before optional extensions activate. Pure authoring for optional dialects is kept in @justybase/dialect-utils; this prevents core from importing a companion runtime while allowing all products to use the same implementation.

For a built-in dialect, add a core-owned authoring module:

src/dialects/<dialect>/sql/authoring.ts

For an optional dialect, add the platform-neutral implementation under:

packages/dialect-utils/src/authoring/<dialect>.ts

If the authoring module has substantial keyword, signature, or quality-rule data, keep those leaf modules below the same directory. The extension-side src/sql/authoring.ts (or *SqlAuthoring.ts) should be a thin re-export for source compatibility only.

Then register it in:

• src/core/sqlAuthoringRegistry.ts

If the runtime dialect lives in extensions/<dialect>, the extension-side *SqlAuthoring.ts should be only a thin re-export or adapter.

#6. Add or update the runtime dialect package

For a built-in runtime dialect, wire the runtime implementation directly in src/dialects/<dialect>/index.ts.

For an optional runtime dialect, add or update the sibling extension package:

extensions/<dialect>/src/
├── extension.ts
├── <dialect>Dialect.ts
├── <dialect>Connection.ts
├── <dialect>SchemaProvider.ts
├── <dialect>DdlGenerator.ts
└── ...other driver-specific files

The runtime dialect object must include traits from the core-owned dialect folder:

import { myDialectTraits } from '../../../src/dialects/myDialect/traits';

export const myDialect: DatabaseDialect = {
    kind: 'myDialect',
    displayName: 'My Dialect',
    capabilities: createDatabaseCapabilities(),
    traits: myDialectTraits,
    metadataProvider: myMetadataProvider,
    sqlAuthoring: mySqlAuthoring,
    getConnectionConstructor() {
        return MyConnection as unknown as DatabaseConnectionStaticConstructor;
    },
    createConnection(config) {
        return new MyConnection(config);
    }
};

Register optional runtime dialects from the extension entry point through the public API.

#7. Let registration validation protect you

Runtime dialect registration now validates trait consistency in src/core/factories/databaseDialectRegistry.ts.

Current validation rules include:

• twoPartNameStyle: 'database-object' requires supportsThreePartName: false
• twoPartNameStyle: 'database-object' requires singleDotPathNamespace: 'database'
• singleDotPathNamespace: 'schema-or-database' requires supportsDoubleDotPath: true
• singleDotPathNamespace: 'schema-or-database' is only valid for schema-object dialects
• unquotedIdentifierPattern must be a real RegExp and reject obviously invalid identifiers

If registration fails, fix the traits first instead of adding more special-case logic in shared callers.

#8. Understand the dialect contract

Each dialect registration must provide a valid DatabaseDialect implementation with these pillars:

• kind, displayName, and capabilities
• traits for identifiers / qualification / completion semantics
• metadataProvider query builders that return non-empty SQL strings
• sqlAuthoring assets for completion, formatting, and validation
• getConnectionConstructor() and createConnection(config) for the runtime connection

The shared metadata-provider contract is defined in src/contracts/database/metadataProvider.ts. In practice, a dialect should always cover at least:

• buildListDatabasesQuery()
• buildListSchemasQuery(database)
• buildListTablesQuery(database, schema)
• buildListViewsQuery(database, schema)
• buildListProceduresQuery(database, schema)
• buildColumnsWithKeysQuery(database, options)
• buildObjectSearchQuery(database, likePattern)
• buildViewSourceSearchQuery(database, options)
• buildProcedureSourceSearchQuery(database, options)

Trait consistency is validated during runtime registration by src/core/dialectTraitsValidator.ts. If registration fails, fix the dialect contract first instead of adding new special-case branches in shared core code.

#9. Add tests before wiring everything broadly

Required CI-safe coverage:

• src/__tests__/dialectTraits.test.ts
• src/__tests__/optionalDialects.unit.test.ts
• src/__tests__/contracts/databaseDialectContract.test.ts
• src/__tests__/sqlAuthoringRegistry.test.ts
• src/__tests__/completionEngine.test.ts

Add targeted dialect-specific tests when the dialect has special notation (for example DB..TABLE, database.table, or dialect-specific quoting behavior) or complex system-query behavior.

#10. Prefer extending traits over adding new switches

When you need new shared behavior, prefer one of these approaches:

1. extend DatabaseDialectTraits
2. update the core trait map
3. teach shared helpers to consume the new trait

Avoid sprinkling new if (databaseKind === ...) checks through shared core code unless there is no reasonable contract shape for the behavior.

#11. Pre-registration checklist

Before considering a dialect addition complete, verify:

#Traits

• twoPartNameStyle matches the database notation (schema.object vs database.object)
• supportsThreePartName is only enabled when the dialect truly supports database.schema.object
• singleDotPathNamespace matches the intended completion behavior
• unquotedIdentifierPattern matches representative unquoted identifiers for that dialect

#Metadata provider

• All required builder methods return non-empty SQL strings
• Database/schema filtering matches the dialect semantics
• System-object filtering is handled where needed

#SQL authoring

• Completion keywords are populated
• Formatter keyword sets are populated
• Validation exposes builtin functions and type metadata
• Quality rules are present when the dialect supports them

#Validation commands

npm run check-types
npm run lint
npm run build
npm run test:validate

Use targeted test runs while iterating, but finish with the full gate above.