Applying Postgres schema snapshot onto MSSQL fails

[martijnboland]

Describe the Bug

When you create a snapshot for a specific database type (e.g. postgres) and apply that to another database type (e.g. mssql), the initial apply on an empty db succeeds, but the next time, applying fails because for some reason, differences in id fields are detected and the schema applier tries to update the id fields with invalid SQL.

schema created with postgres and applied on mssql (2nd time):

ALTER TABLE [table_name] ALTER COLUMN [id] int identity(1,1) not null primary key - Incorrect syntax near the keyword 'identity'

The other way around (schema created with mssql and applied on postgres) also fails, but with a different error:

error alter table "table_name" drop column "id" - cannot drop column id of table table_name because other objects depend on it

To Reproduce

Create a snapshot on a specific database type (e.g. postgres) and apply that to a different database type (e.g. mssql). Applying the snapshot on an empty database the first time works, but succeeding applies on this database fail.

Errors Shown

No response

What version of Directus are you using?

9.10.0

What version of Node.js are you using?

16.13.1

What database are you using?

Postgres 13, MS SQL Server 2019

What browser are you using?

Chrome

How are you deploying Directus?

locally

[azrikahar]

Hi, would you be able to provide a sample snapshot? and perhaps try this again with the latest 9.14.1 version?

I'm seeing re-applying stating that the collections are marked to be deleted, which feels like it's an entirely different issue now.

That said, I do also think the particular error reported here specifically pertains to MSSQL (based on the identity(1,1) in the error). Other similar errors (like SQLite char primary key to Postgres UUID) are tracked separately, so we'll need a reproduction specifically from Postgres to MSSQL to confirm this issue 🤔

---

Schema snapshot used

```yaml version: 1 directus: 9.14.1 collections: - collection: a meta: accountability: all archive_app_filter: true archive_field: null archive_value: null collapse: open collection: a color: null display_template: null group: null hidden: false icon: null item_duplication_fields: null note: null singleton: false sort: null sort_field: null translations: null unarchive_value: null schema: comment: null name: a schema: public - collection: a_b meta: accountability: all archive_app_filter: true archive_field: null archive_value: null collapse: open collection: a_b color: null display_template: null group: null hidden: true icon: import_export item_duplication_fields: null note: null singleton: false sort: null sort_field: null translations: null unarchive_value: null schema: comment: null name: a_b schema: public - collection: b meta: accountability: all archive_app_filter: true archive_field: null archive_value: null collapse: open collection: b color: null display_template: null group: null hidden: false icon: null item_duplication_fields: null note: null singleton: false sort: null sort_field: null translations: null unarchive_value: null schema: comment: null name: b schema: public fields: - collection: a field: id meta: collection: a conditions: null display: null display_options: null field: id group: null hidden: true interface: input note: null options: null readonly: true required: false sort: null special: null translations: null validation: null validation_message: null width: full schema: comment: null data_type: integer default_value: nextval('a_id_seq'::regclass) foreign_key_column: null foreign_key_schema: null foreign_key_table: null generation_expression: null has_auto_increment: true is_generated: false is_nullable: false is_primary_key: true is_unique: true max_length: null name: id numeric_precision: 32 numeric_scale: 0 schema: public table: a type: integer - collection: a field: m2m_b meta: collection: a conditions: null display: null display_options: null field: m2m_b group: null hidden: false interface: list-m2m note: null options: null readonly: false required: false sort: null special: - m2m translations: null validation: null validation_message: null width: full schema: null type: alias - collection: a field: name meta: collection: a conditions: null display: null display_options: null field: name group: null hidden: false interface: input note: null options: null readonly: false required: false sort: null special: null translations: null validation: null validation_message: null width: full schema: comment: null data_type: character varying default_value: null foreign_key_column: null foreign_key_schema: null foreign_key_table: null generation_expression: null has_auto_increment: false is_generated: false is_nullable: true is_primary_key: false is_unique: false max_length: 255 name: name numeric_precision: null numeric_scale: null schema: public table: a type: string - collection: a_b field: a_id meta: collection: a_b conditions: null display: null display_options: null field: a_id group: null hidden: true interface: null note: null options: null readonly: false required: false sort: null special: null translations: null validation: null validation_message: null width: full schema: comment: null data_type: integer default_value: null foreign_key_column: id foreign_key_schema: public foreign_key_table: a generation_expression: null has_auto_increment: false is_generated: false is_nullable: true is_primary_key: false is_unique: false max_length: null name: a_id numeric_precision: 32 numeric_scale: 0 schema: public table: a_b type: integer - collection: a_b field: b_id meta: collection: a_b conditions: null display: null display_options: null field: b_id group: null hidden: true interface: null note: null options: null readonly: false required: false sort: null special: null translations: null validation: null validation_message: null width: full schema: comment: null data_type: integer default_value: null foreign_key_column: id foreign_key_schema: public foreign_key_table: b generation_expression: null has_auto_increment: false is_generated: false is_nullable: true is_primary_key: false is_unique: false max_length: null name: b_id numeric_precision: 32 numeric_scale: 0 schema: public table: a_b type: integer - collection: a_b field: id meta: collection: a_b conditions: null display: null display_options: null field: id group: null hidden: true interface: null note: null options: null readonly: false required: false sort: null special: null translations: null validation: null validation_message: null width: full schema: comment: null data_type: integer default_value: nextval('a_b_id_seq'::regclass) foreign_key_column: null foreign_key_schema: null foreign_key_table: null generation_expression: null has_auto_increment: true is_generated: false is_nullable: false is_primary_key: true is_unique: true max_length: null name: id numeric_precision: 32 numeric_scale: 0 schema: public table: a_b type: integer - collection: b field: id meta: collection: b conditions: null display: null display_options: null field: id group: null hidden: true interface: input note: null options: null readonly: true required: false sort: null special: null translations: null validation: null validation_message: null width: full schema: comment: null data_type: integer default_value: nextval('b_id_seq'::regclass) foreign_key_column: null foreign_key_schema: null foreign_key_table: null generation_expression: null has_auto_increment: true is_generated: false is_nullable: false is_primary_key: true is_unique: true max_length: null name: id numeric_precision: 32 numeric_scale: 0 schema: public table: b type: integer - collection: b field: name meta: collection: b conditions: null display: null display_options: null field: name group: null hidden: false interface: input note: null options: null readonly: false required: false sort: null special: null translations: null validation: null validation_message: null width: full schema: comment: null data_type: character varying default_value: null foreign_key_column: null foreign_key_schema: null foreign_key_table: null generation_expression: null has_auto_increment: false is_generated: false is_nullable: true is_primary_key: false is_unique: false max_length: 255 name: name numeric_precision: null numeric_scale: null schema: public table: b type: string relations: - collection: a_b field: a_id meta: junction_field: b_id many_collection: a_b many_field: a_id one_allowed_collections: null one_collection: a one_collection_field: null one_deselect_action: nullify one_field: m2m_b sort_field: null related_collection: a schema: column: a_id constraint_name: a_b_a_id_foreign foreign_key_column: id foreign_key_schema: public foreign_key_table: a on_delete: SET NULL on_update: NO ACTION table: a_b - collection: a_b field: b_id meta: junction_field: a_id many_collection: a_b many_field: b_id one_allowed_collections: null one_collection: b one_collection_field: null one_deselect_action: nullify one_field: null sort_field: null related_collection: b schema: column: b_id constraint_name: a_b_b_id_foreign foreign_key_column: id foreign_key_schema: public foreign_key_table: b on_delete: SET NULL on_update: NO ACTION table: a_b ```

Output of snapshotDiff in apply-snapshot when reapplying the exact same schema which is marking the collections to be deleted

```json { "collections": [ { "collection": "a", "diff": [ { "kind": "D", "path": [ "schema", "catalog" ], "lhs": "directus_db" }, { "kind": "E", "path": [ "schema", "schema" ], "lhs": "dbo", "rhs": "public" }, { "kind": "N", "path": [ "schema", "comment" ], "rhs": null } ] }, { "collection": "a_b", "diff": [ { "kind": "D", "path": [ "schema", "catalog" ], "lhs": "directus_db" }, { "kind": "E", "path": [ "schema", "schema" ], "lhs": "dbo", "rhs": "public" }, { "kind": "N", "path": [ "schema", "comment" ], "rhs": null } ] }, { "collection": "b", "diff": [ { "kind": "D", "path": [ "schema", "catalog" ], "lhs": "directus_db" }, { "kind": "E", "path": [ "schema", "schema" ], "lhs": "dbo", "rhs": "public" }, { "kind": "N", "path": [ "schema", "comment" ], "rhs": null } ] } ], "fields": [], "relations": [] } ```

[martijnboland]

Just tried with 9.14, but re-applying indeed states that all collections are going to be deleted (both pg->mssql and mssql->pg).

Added snapshots: snapshots.zip

[azrikahar]

Just tried with 9.14, but re-applying indeed states that all collections are going to be deleted (both pg->mssql and mssql->pg).

Added snapshots: snapshots.zip

Thanks for sharing the snapshots and also confirming the current state of re-applying is different now! Seems like we'll have to take another detour to resolve this new issue first 😄

[azrikahar]

The re-applying snapshot turns out to not be MSSQL specific, but it's more so between any different db vendors since their collection -> schema are indeed vendor-specific 🤔

Examples

(snapshots below are simplified to focus the main differences)

Postgres to MSSQL

When applying snapshot from PostgreSQL onto MSSQL:

collections:
  - collection: a
    meta:
      # meta properties here
    schema:
      comment: null
      name: a
      schema: public

It's diff-ing against:

collections:
  - collection: a
    meta:
      # meta properties here
    schema:
      catalog: directus_db
      name: a
      schema: dbo

but schema.catalog is unique to mssql and shouldn't be attempted to be removed, and schema.schema for mssql is by default dbo, which is different to the PostgreSQL's default public.

SQlite to Postgres

When applying snapshot from SQLite onto Postgres:

collections:
  - collection: a
    meta:
      # meta properties here
    schema:
      name: a
      sql: >-
        CREATE TABLE `a` (`id` char(36) not null, `name` varchar(255) null,
        primary key (`id`))

It's diff-ing against:

collections:
  - collection: a
    meta:
      # meta properties here
    schema:
      comment: null
      name: a
      schema: public

but sql is SQLite specific and shouldn't be applied, nor should it attempt to remove schema.schema on Postgres side.

That said, we can't flat out ignore the schema property as schema: null denotes an folder collection.

[azrikahar]

Tracking the re-apply issue in #14687, but leaving this open until we can circle back to this MSSQL specific error once #14687 gets resolved.

[rijkvanzanten]

Linear: ENG-254