MemberJunction / MemberJunction/MJ

mj migrate does not canonicalize the schema name for the platform, but the Open App engine does — PostgreSQL casing divergence

Open Beginner friendly
#3,548 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
TSQL
Stars
29
Forks
6
Avg merge
2d 1h
Merged PRs (30d)
323

Description

## Summary

The Open App install engine canonicalizes the target schema name for the platform before handing it to Skyway. `mj migrate` does not. On PostgreSQL, where unquoted DDL folds identifiers to lowercase, the two paths can therefore resolve the *same* configured schema to two *different* physical schemas.

Found while working on #3469; unrelated to that PR's subject, filed separately.

## The divergence

**Engine** — `packages/OpenApp/Engine/src/install/migration-runner.ts`, `BuildSkywayConfig`:

```ts
// Canonicalize the schema for the platform (PG folds unquoted DDL to lowercase) so Skyway's
// history table AND the `${flyway:defaultSchema}` the app's migrations resolve to both land in
// the SAME physical schema the app's (unquoted) DDL creates — no mixed-case/lowercase split.
const canonicalSchema = GetDialect(platform).CanonicalSchemaName(schemaName);
```

`canonicalSchema` is then used for **both** `Migrations.DefaultSchema` and the `flyway:defaultSchema` placeholder.

**MJCLI** — `packages/MJCLI/src/config.ts`:

```ts
placeholders['flyway:defaultSchema'] = targetSchema; // raw
...
Migrations: {
DefaultSchema: targetSchema, // raw
```

`targetSchema` is passed through unmodified. `CanonicalSchemaName` does not appear anywhere in `packages/MJCLI/src` — confirmed by grep.

## Impact

On **SQL Server**, no effect: the dialect's canonical form is the identity, and the server is case-insensitive for identifiers by default.

On **PostgreSQL**, with a schema name that is not already lowercase:

- the engine targets the folded (lowercase) schema — which is what the app's own unquoted `CREATE SCHEMA` / `CREATE TABLE` DDL actually produces
- `mj migrate` targets the raw mixed-case name

That splits the Skyway history table and the `${flyway:defaultSchema}` resolution across two schemas, so the two paths disagree about what has been applied. The engine's comment above describes precisely the failure mode it was added to prevent — MJCLI just never got the same treatment.

An all-lowercase schema name (the common case) masks it entirely, which is why it has not surfaced.

## Suggested fix

Apply `GetDialect(dialect).CanonicalSchemaName(...)` in MJCLI's config builder for both `Migrations.DefaultSchema` and the `flyway:defaultSchema` placeholder, matching the engine.

Better still, factor the Skyway config construction so both callers share one path rather than maintaining two that must be kept in agreement by hand — the same argument that motivated setting `TransactionMode` explicitly in #3469 so the engine and `mj migrate` would stop disagreeing by default. This is the second instance of the same class of drift.

Contributor guide

Open the contributing guide

Research direction

Start in packages/MJCLI/src/config.ts and compare its Skyway config builder with packages/OpenApp/Engine/src/install/migration-runner.ts, especially BuildSkywayConfig and CanonicalSchemaName. Trace how flyway:defaultSchema and Migrations.DefaultSchema are populated, then verify that mixed-case PostgreSQL schemas resolve consistently through both paths.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, typescript
Domain
backend, database
Issue type
Bug
Difficulty
2/5
Estimated time
1-3 hours
Activity status
Quiet
Clarity
Clearly specified
Newbie friendliness
74/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.