Search code examples
sql-servertypeorm

TypeORM and SQL Server - Set `NONCLUSTERED` and `NOT ENFORCED` on primary key

Question: how can I configure a TypeORM entity's primary column with NONCLUSTERED and NOT ENFORCED in SQL Server?

CONTEXT

I'm trying to setup a connection to a SQL Server instance on Azure to be used with their Synapse service. That being the case, the connection has very specific requirements for the primary key (more details here), mainly I need to set NONCLUSTERED and NOT ENFORCED on my primary key, or have no primary key.

Since TypeORM won't accept an entity without a primary key, I'd like to configure the primary key to satisfy the requirements of Synapse's SQL server.

When configuring my entity like:

@Entity()
export class BlenderEvent {
  @PrimaryGeneratedColumn()
  public id: number;
}

I get:

[Nest] 1165402   - 2021-10-01, 3:35:15 p.m.   [TypeOrmModule] Unable to connect to the database. Retrying (1)... +3524ms
QueryFailedError: Error: Enforced unique constraints are not supported. To create an unenforced unique constraint you must include the NOT ENFORCED syntax as part of your statement.
    at QueryFailedError.TypeORMError [as constructor] (/home/gcardinal/dev/casino/mssql-nestjs-poc/node_modules/typeorm/error/TypeORMError.js:9:28)
    at new QueryFailedError (/home/gcardinal/dev/casino/mssql-nestjs-poc/node_modules/typeorm/error/QueryFailedError.js:13:28)
    at /home/gcardinal/dev/casino/mssql-nestjs-poc/node_modules/typeorm/driver/sqlserver/SqlServerQueryRunner.js:281:46
    at /home/gcardinal/dev/casino/mssql-nestjs-poc/node_modules/mssql/lib/base/request.js:438:25
    at Request.userCallback (/home/gcardinal/dev/casino/mssql-nestjs-poc/node_modules/mssql/lib/tedious/request.js:493:15)
    at Request.callback (/home/gcardinal/dev/casino/mssql-nestjs-poc/node_modules/tedious/lib/request.js:205:14)
    at Parser.onEndOfMessage (/home/gcardinal/dev/casino/mssql-nestjs-poc/node_modules/tedious/lib/connection.js:3078:22)
    at Object.onceWrapper (node:events:509:28)
    at Parser.emit (node:events:390:28)
    at Readable.<anonymous> (/home/gcardinal/dev/casino/mssql-nestjs-poc/node_modules/tedious/lib/token/token-stream-parser.js:34:12)

Same if I use @PrimaryColumn() instead of @PrimaryGeneratedColumn().

How could I accomplish this?

Solution

  • You don't. The best course of action in this situation is to prevent TypeORM from creating or modifying tables in your database with synchronize: false in your TypeORM options. It will stop it from complaining about the lack of primary key.

    If you want to have infrastructure as code for your database (as was my case), your microservice is not the right place for this.

    Thanks @David Browne for the suggestion in a comment to my question.