typescriptnestjstypeormcockroachdbnestjs-typeorm

Cockroachdb transactions running in readonly mode with typeorm mifgrations


This is my dataSource.ts file

import { DataSource } from 'typeorm';
import { Step } from './src/recipe/entity/step.entity';
import { Chemical } from './src/recipe/entity/chemical.entity';
import { Recipe } from './src/recipe/entity/recipe.entity';

export const dataSource = new DataSource({
    type: "cockroachdb",
    timeTravelQueries: true,
    url: "xxxx-xxxxxxx-xxxxx",
    ssl: true,
    synchronize: false,
    logging: true,
    entities: [Step,Chemical,Recipe],
    migrations: [__dirname + '/src/**/migrations/*{.ts,.js}'],
    extra: {
      enableExperiments: ['sequence-ddl'],
  
}});


async () => {await dataSource.initialize()
  .then(() => {
    console.log('Data Source has been initialized!');
    // async (): Promise<void> =>{
    //   await dataSource.query(`SET default_transaction_use_follower_reads = 'off';`);
    //   // Your migration code here
    // }

    console.log('Follower reads setting has been disabled.');
    
    })
  .catch((err) => {
    console.error('Error during Data Source initialization', err);
  });}

and this is my migration file which is auto generated from typeorm cli:

import { MigrationInterface, QueryRunner, Transaction } from "typeorm";
import { writeHeapSnapshot } from "v8";

export class Migration011720100667518 implements MigrationInterface {
    name = 'Migration011720100667518'
    public async set_trans_off(queryRunner: QueryRunner): Promise<void>{
        
    }
    
    public async up(queryRunner: QueryRunner): Promise<void> {
        // // await queryRunner.query().
        // await queryRunner.startTransaction('READ WRITE');
        await queryRunner.query(`SET default_transaction_read_only "off"`);
        await queryRunner.query(`SET default_transaction_use_follower_reads = 'off';`);
        await queryRunner.query(`ALTER TABLE "step" DROP CONSTRAINT "FK_e43ba0c1168808a120724d0bd85"`);
        await queryRunner.query(`DROP INDEX "step"@"IDX_e43ba0c1168808a120724d0bd8" CASCADE`);
        await queryRunner.query(`ALTER TABLE "step" RENAME COLUMN "chemicalId" TO "created_at"`);
        await queryRunner.query(`CREATE TABLE "step_chemicals_chemical" ("stepId" int8 NOT NULL, "chemicalId" int8 NOT NULL, CONSTRAINT "PK_2509c715e13ddba6b71791087a8" PRIMARY KEY ("stepId", "chemicalId"))`);
        await queryRunner.query(`CREATE INDEX "IDX_fc0f013cbac13b0a0241573785" ON "step_chemicals_chemical" ("stepId") `);
        await queryRunner.query(`CREATE INDEX "IDX_604ee43998e40a8af82e001fcd" ON "step_chemicals_chemical" ("chemicalId") `);
        await queryRunner.query(`ALTER TABLE "recipe" ADD "isdeleted" bool NOT NULL`);
        await queryRunner.query(`ALTER TABLE "recipe" ADD "created_at" timestamptz NOT NULL DEFAULT current_timestamp()`);
        await queryRunner.query(`ALTER TABLE "chemical" ADD "created_at" timestamptz NOT NULL DEFAULT current_timestamp()`);
        await queryRunner.query(`ALTER TABLE "step" DROP COLUMN "created_at"`);
        await queryRunner.query(`ALTER TABLE "step" ADD "created_at" timestamptz NOT NULL DEFAULT current_timestamp()`);
        await queryRunner.query(`ALTER TABLE "step_chemicals_chemical" ADD CONSTRAINT "FK_fc0f013cbac13b0a0241573785a" FOREIGN KEY ("stepId") REFERENCES "step"("id") ON DELETE CASCADE ON UPDATE CASCADE`);
        await queryRunner.query(`ALTER TABLE "step_chemicals_chemical" ADD CONSTRAINT "FK_604ee43998e40a8af82e001fcd4" FOREIGN KEY ("chemicalId") REFERENCES "chemical"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`ALTER TABLE "step_chemicals_chemical" DROP CONSTRAINT "FK_604ee43998e40a8af82e001fcd4"`);
        await queryRunner.query(`ALTER TABLE "step_chemicals_chemical" DROP CONSTRAINT "FK_fc0f013cbac13b0a0241573785a"`);
        await queryRunner.query(`ALTER TABLE "step" DROP COLUMN "created_at"`);
        await queryRunner.query(`ALTER TABLE "step" ADD "created_at" int8`);
        await queryRunner.query(`ALTER TABLE "chemical" DROP COLUMN "created_at"`);
        await queryRunner.query(`ALTER TABLE "recipe" DROP COLUMN "created_at"`);
        await queryRunner.query(`ALTER TABLE "recipe" DROP COLUMN "isdeleted"`);
        await queryRunner.query(`DROP INDEX "step_chemicals_chemical"@"IDX_604ee43998e40a8af82e001fcd" CASCADE`);
        await queryRunner.query(`DROP INDEX "step_chemicals_chemical"@"IDX_fc0f013cbac13b0a0241573785" CASCADE`);
        await queryRunner.query(`DROP TABLE "step_chemicals_chemical"`);
        await queryRunner.query(`ALTER TABLE "step" RENAME COLUMN "created_at" TO "chemicalId"`);
        await queryRunner.query(`CREATE INDEX "IDX_e43ba0c1168808a120724d0bd8" ON "step" ("chemicalId") `);
        await queryRunner.query(`ALTER TABLE "step" ADD CONSTRAINT "FK_e43ba0c1168808a120724d0bd85" FOREIGN KEY ("chemicalId") REFERENCES "chemical"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`);
    }

}

I've tried many things but still stuck on this error. I have tried to change the configuration of datasource, explicitly running queries to set transaction read-only variables off but nothing works it be appreciated if someone guide me through this.

query: SELECT \* FROM current_schema()
query: SET default_transaction_use_follower_reads = 'on';
query: SET enable_experimental_alter_column_type_general = true
query: SELECT \* FROM "information_schema"."tables" WHERE "table_schema" = 'public' AND "table_name" = 'migrations'
query: CREATE SEQUENCE "migrations_id_seq"
query failed: CREATE SEQUENCE "migrations_id_seq"
error: error: cannot execute CREATE SEQUENCE in a read-only transaction
Error during migration run:
QueryFailedError: cannot execute CREATE SEQUENCE in a read-only transaction
at CockroachQueryRunner.query (C:\\Users\\SAAD\\Desktop\\AKT_FE\\akhtarbackend\\akhtar_backend\\node_modules\\typeorm\\src\\driver\\cockroachdb\\CockroachQueryRunner.ts:396:23)
at processTicksAndRejections (node:internal/process/task_queues:95:5)
at async CockroachQueryRunner.executeQueries (C:\\Users\\SAAD\\Desktop\\AKT_FE\\akhtarbackend\\akhtar_backend\\node_modules\\src\\query-runner\\BaseQueryRunner.ts:660:13)
at async CockroachQueryRunner.createTable (C:\\Users\\SAAD\\Desktop\\AKT_FE\\akhtarbackend\\akhtar_backend\\node_modules\\typeorm\\src\\driver\\cockroachdb\\CockroachQueryRunner.ts:694:9)
at async MigrationExecutor.createMigrationsTableIfNotExist (C:\\Users\\SAAD\\Desktop\\AKT_FE\\akhtarbackend\\akhtar_backend\\node_modules\\src\\migration\\MigrationExecutor.ts:505:13)
at async MigrationExecutor.executePendingMigrations (C:\\Users\\SAAD\\Desktop\\AKT_FE\\akhtarbackend\\akhtar_backend\\node_modules\\src\\migration\\MigrationExecutor.ts:195:9)
at async DataSource.runMigrations (C:\\Users\\SAAD\\Desktop\\AKT_FE\\akhtarbackend\\akhtar_backend\\node_modules\\src\\data-source\\DataSource.ts:403:13)
at async Object.handler (C:\\Users\\SAAD\\Desktop\\AKT_FE\\akhtarbackend\\akhtar_backend\\node_modules\\src\\commands\\MigrationRunCommand.ts:76:13) {
query: 'CREATE SEQUENCE "migrations_id_seq"',
parameters: undefined,
driverError: error: cannot execute CREATE SEQUENCE in a read-only transaction
at Parser.parseErrorMessage (C:\\Users\\SAAD\\Desktop\\AKT_FE\\akhtarbackend\\akhtar_backend\\node_modules\\pg-protocol\\src\\parser.ts:369:69)  
at Parser.handlePacket (C:\\Users\\SAAD\\Desktop\\AKT_FE\\akhtarbackend\\akhtar_backend\\node_modules\\pg-protocol\\src\\parser.ts:188:21)  
at Parser.parse (C:\\Users\\SAAD\\Desktop\\AKT_FE\\akhtarbackend\\akhtar_backend\\node_modules\\pg-protocol\\src\\parser.ts:103:30)
at TLSSocket.\<anonymous\> (C:\\Users\\SAAD\\Desktop\\AKT_FE\\akhtarbackend\\akhtar_backend\\node_modules\\pg-protocol\\src\\index.ts:7:48)  
at TLSSocket.emit (node:events:519:28)
at TLSSocket.emit (node:domain:488:12)
at addChunk (node:internal/streams/readable:559:12)
at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
at TLSSocket.Readable.push (node:internal/streams/readable:390:5)
at TLSWrap.onStreamRead (node:internal/stream_base_commons:191:23) {
length: 122,
severity: 'ERROR',
code: '25006',
detail: undefined,
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'relational.go',
line: '181',
routine: 'buildRelational'
},
length: 122,
severity: 'ERROR',
code: '25006',
detail: undefined,
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'relational.go',
line: '181',
routine: 'buildRelational'
}

Solution

  • I think you want to try setting timeTravelQueries to false. In your dataSource.ts:

    import { DataSource } from 'typeorm';
    import { Step } from './src/recipe/entity/step.entity';
    import { Chemical } from './src/recipe/entity/chemical.entity';
    import { Recipe } from './src/recipe/entity/recipe.entity';
    
    export const dataSource = new DataSource({
        type: "cockroachdb",
        - timeTravelQueries: true,
        + timeTravelQueries: false,
        url: "xxxx-xxxxxxx-xxxxx",
        ssl: true,
        synchronize: false,
        logging: true,
        entities: [Step,Chemical,Recipe],
        migrations: [__dirname + '/src/**/migrations/*{.ts,.js}'],
        extra: {
          enableExperiments: ['sequence-ddl'],
      
    }});
    

    It is unfortunate that enabling timeTravelQueries would mean that you cannot run schema migrations; you might be interested in opening up an issue for that -- here is an example issue filed https://github.com/typeorm/typeorm/issues/10287