CannotExecuteNotConnectedError when running migration on Postgres

[assapir]

Issue Description

When trying to run a migration on Postgres DB, the migrations fail to run with CannotExecuteNotConnectedError: Cannot execute operation on "default" connection because connection is not yet established. at DataSource.destroy (/home/assaf/Code/private/f3tw/src/data-source/DataSource.ts:284:19) at Object.handler (/home/assaf/Code/private/f3tw/src/commands/MigrationRunCommand.ts:73:46)

Expected Behavior

All migrations should run as expected.

Actual Behavior

No migrations are running, and an error is thrown:

> typeorm-ts-node-esm -d src/data-source.ts "migration:run"

query: SELECT * FROM current_schema()
typeorm-ts-node-esm migration:run

Runs all pending migrations.

Options:
  -h, --help         Show help                                         [boolean]
  -d, --dataSource   Path to the file where your DataSource instance is defined.
                                                                      [required]
  -t, --transaction  Indicates if transaction should be used or not for
                     migration run. Enabled by default.     [default: "default"]
  -v, --version      Show version number                               [boolean]

CannotExecuteNotConnectedError: Cannot execute operation on "default" connection because connection is not yet established.
    at DataSource.destroy (/home/assaf/Code/private/f3tw/src/data-source/DataSource.ts:284:19)
    at Object.handler (/home/assaf/Code/private/f3tw/src/commands/MigrationRunCommand.ts:73:46)

Steps to Reproduce

1. Create a migration
2. try to run it with npm run typeorm migration:run

my datasource:

import "reflect-metadata";
import { DataSource } from "typeorm";

export const TestAppDataSource = new DataSource({
  type: "postgres",
  host: "localhost",
  port: 5432,
  username: "postgres",
  password: "test",
  database: "postgres",
  logging: process.env.NODE_ENV !== "production",
  entities: ["src/entity/*.{js,ts}"],
  migrations: ["src/migration/*.{ts, ts}"],
});

From looking at the code in src/commands/MigrationRunCommand.ts it seems like it does call
await dataSource.initialize() so I have no idea where that problem is coming from.

My Environment

Might be related to the fact that I am using npm workspace and running inside one of the projects?
It does work when using typeorm-ts-node-commonjs but not typeorm-ts-node-esm

Dependency	Version
Operating System	Arch linux
Node.js version	17.8.0
Typescript version	4.6.3
TypeORM version	0.3.5

Additional Context

Relevant Database Driver(s)

DB Type	Reproducible
aurora-mysql	no
aurora-postgres	no
better-sqlite3	no
cockroachdb	no
cordova	no
expo	no
mongodb	no
mysql	no
nativescript	no
oracle	no
postgres	yes
react-native	no
sap	no
sqlite	no
sqlite-abstract	no
sqljs	no
sqlserver	no

Are you willing to resolve this issue by submitting a Pull Request?

• ✖️ Yes, I have the time, and I know how to start.
• ✅ Yes, I have the time, but I don't know how to start. I would need guidance.
• ✖️ No, I don’t have the time, but I can support (using donations) development.
• ✖️ No, I don’t have the time and I’m okay to wait for the community / maintainers to resolve this issue.

[igortrinidad]

Same issue here @assapir , if you find a solution please share with us 🙏

[igortrinidad]

I didn't found a solution @assapir but i could finally run the migrations.

Isn't the best solution ever but at least we could go to sleep without this exception on mind lol 😆

import dbCreateConnection from "./dbCreateConnection"
import AppDataSource from "./AppDataSource"

export default ( async () => {
  await dbCreateConnection()
  await AppDataSource.runMigrations()
})()

[girishsuri15]

++ Same Issue

[memo-567]

Same here with YunoHost Misskey:

typeorm migration:run

Runs all pending migrations.
 
Options:
   -h, --help         Show help                                         [boolean]
   -d, --dataSource   Path to the file where your DataSource instance is defined.
                                                                       [required]
   -t, --transaction  Indicates if transaction should be used or not for
                      migration run. Enabled by default.     [default: "default"]
   -v, --version      Show version number                               [boolean]
 
 CannotExecuteNotConnectedError: Cannot execute operation on "default" connection because connection is not yet established.
     at DataSource.destroy (/var/www/misskey/packages/backend/node_modules/typeorm/data-source/DataSource.js:169:19)
     at Object.handler (/var/www/misskey/packages/backend/node_modules/typeorm/commands/MigrationRunCommand.js:67:34)
 error Command failed with exit code 1.

[ghost]

same issue with misskey migrations.
edit: in the case of misskey, running npm run clean-all and then installing the packages again made it possible to run the migrations as normal.

[tdnghia98]

I have the same problem. However creating new table using queryRunner runs fine

public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`CREATE TABLE "city" ("id" SERIAL NOT NULL, "postCode" character varying NOT NULL, "communeCode" character varying NOT NULL, "communeLabel" character varying NOT NULL, "name" character varying NOT NULL, CONSTRAINT "PK_b222f51ce26f7e5ca86944a6739" PRIMARY KEY ("id"))`);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`DROP TABLE "city"`);
    }

Only when using Enitity objects I have this issue

public async up(queryRunner: QueryRunner): Promise<void> {
    const manager = AppDataSource.manager;

    const repository = manager.getRepository(City);
    await repository.save(
      allCities['default'].map((rawCity) => {
        const city = new City();
        city.postCode = rawCity.codePostal;
        city.communeCode = rawCity.codeCommune;
        city.communeLabel = rawCity.nomCommune;
        city.name = rawCity.libelleAcheminement;
        return city;
      }),
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    const manager = AppDataSource.manager;

    const repository = manager.getRepository(City);
    repository.clear();
  }

[kareemarafa]

Same issue

[nicholasoxford]

Same issue

[aftabaig]

I had to revert back typeorm to previous version to make it work

[lucasgama335]

Same issue

[nicholasoxford]

@aftabaig are you saying typeorm 0.2.*?

[aftabaig]

@aftabaig are you saying typeorm 0.2.*?

Yes ^0.2.45 to be exact

[lucasgama335]

Issue Description

When trying to run a migration on Postgres DB, the migrations fail to run with CannotExecuteNotConnectedError: Cannot execute operation on "default" connection because connection is not yet established. at DataSource.destroy (/home/assaf/Code/private/f3tw/src/data-source/DataSource.ts:284:19) at Object.handler (/home/assaf/Code/private/f3tw/src/commands/MigrationRunCommand.ts:73:46)

Expected Behavior

All migrations should run as expected.

Actual Behavior

No migrations are running, and an error is thrown:

> typeorm-ts-node-esm -d src/data-source.ts "migration:run"

query: SELECT * FROM current_schema()
typeorm-ts-node-esm migration:run

Runs all pending migrations.

Options:
  -h, --help         Show help                                         [boolean]
  -d, --dataSource   Path to the file where your DataSource instance is defined.
                                                                      [required]
  -t, --transaction  Indicates if transaction should be used or not for
                     migration run. Enabled by default.     [default: "default"]
  -v, --version      Show version number                               [boolean]

CannotExecuteNotConnectedError: Cannot execute operation on "default" connection because connection is not yet established.
    at DataSource.destroy (/home/assaf/Code/private/f3tw/src/data-source/DataSource.ts:284:19)
    at Object.handler (/home/assaf/Code/private/f3tw/src/commands/MigrationRunCommand.ts:73:46)

Steps to Reproduce

1. Create a migration
2. try to run it with npm run typeorm migration:run

my datasource:

import "reflect-metadata";
import { DataSource } from "typeorm";

export const TestAppDataSource = new DataSource({
  type: "postgres",
  host: "localhost",
  port: 5432,
  username: "postgres",
  password: "test",
  database: "postgres",
  logging: process.env.NODE_ENV !== "production",
  entities: ["src/entity/*.{js,ts}"],
  migrations: ["src/migration/*.{ts, ts}"],
});

From looking at the code in src/commands/MigrationRunCommand.ts it seems like it does call await dataSource.initialize() so I have no idea where that problem is coming from.

My Environment

Might be related to the fact that I am using npm workspace and running inside one of the projects? It does work when using typeorm-ts-node-commonjs but not typeorm-ts-node-esm

Dependency Version
Operating System Arch linux
Node.js version 17.8.0
Typescript version 4.6.3
TypeORM version 0.3.5

Additional Context

Relevant Database Driver(s)

DB Type Reproducible
aurora-mysql no
aurora-postgres no
better-sqlite3 no
cockroachdb no
cordova no
expo no
mongodb no
mysql no
nativescript no
oracle no
postgres yes
react-native no
sap no
sqlite no
sqlite-abstract no
sqljs no
sqlserver no

Are you willing to resolve this issue by submitting a Pull Request?

• ✖️ Yes, I have the time, and I know how to start.
• ✅ Yes, I have the time, but I don't know how to start. I would need guidance.
• ✖️ No, I don’t have the time, but I can support (using donations) development.
• ✖️ No, I don’t have the time and I’m okay to wait for the community / maintainers to resolve this issue.

I found the problem. import dotenv into your file that contains the dataSource. i also retrieve some environment variables and for some reason when dotenv does not matter the username comes as null.

My code:
Configuration file: (@src/config/database.ts)

export default {
    /*
    |--------------------------------------------------------------------------
    | Default Database Connection Name
    |--------------------------------------------------------------------------
    |
    | Here you may specify which of the database connections below you wish
    | to use as your default connection for all database work. Of course
    | you may use many connections at once using the Database library.
    |
    */
    driver: process.env.DB_CONNECTION || 'postgres',
    debug: process.env.DB_DEBUG === 'true',

    /*
    |--------------------------------------------------------------------------
    | Database Connections
    |--------------------------------------------------------------------------
    |
    | Here are each of the database connections setup for your application.
    | Of course, examples of configuring each database platform that is
    | supported by App is shown below to make development simple.
    |
    |
    */
    connections: {
        postgres: {
            driver: 'postgres',
            host: process.env.DB_HOST || 'localhost',
            port: Number(process.env.DB_PORT) || 5432,
            database:
                process.env.APP_ENV === 'test'
                    ? process.env.DB_DATABASE_TEST || 'api_base_test'
                    : process.env.DB_DATABASE || 'api_base',
            username: process.env.DB_USERNAME || 'postgres',
            password: process.env.DB_PASSWORD || 'root',
        },
    },

    /*
    |--------------------------------------------------------------------------
    | Redis Databases
    |--------------------------------------------------------------------------
    |
    | Redis is an open source, fast, and advanced key-value store that also
    | provides a richer body of commands than a typical key-value system
    | such as APC or Memcached. Laravel makes it easy to dig right in.
    |
    */
    redis: {
        host: process.env.REDIS_HOST || 'localhost',
        port: process.env.REDIS_PORT || 6379,
        username: process.env.REDIS_USERNAME || '',
        password: process.env.REDIS_PASSWORD || '',
    },
};

Configuration file: (@src/config/index.ts)

import 'dotenv/config';
import app from './app';
import auth from './auth';
import cors from './cors';
import database from './database';
import filesystem from './filesystem';
import mail from './mail';
import upload from './upload';

export const ConfigService = {
    app,
    auth,
    cors,
    database,
    filesystem,
    mail,
    upload,
};

dataSource:

import { DataSource } from 'typeorm';

import { ConfigService } from '@src/config';

export const AppDataSource = new DataSource({
    type: 'postgres',
    host: ConfigService.database.connections.postgres.host,
    port: ConfigService.database.connections.postgres.port,
    username: ConfigService.database.connections.postgres.username,
    password: ConfigService.database.connections.postgres.password,
    database: ConfigService.database.connections.postgres.database,
    synchronize: false,
    logging: ConfigService.database.debug,
    entities: ['./src/modules/**/entities/typeorm/*.ts'],
    migrations: ['./src/database/typeorm/migrations/*.ts'],
});

if it doesn't work for you, go to your node_modules/typeorm/commands/MigrationRunCommand.js folder and console log the dataSource to verify the connection data is correct. I discovered my problem this way.

[Johann150]

I found today that this error may be because of another completely unrelated error. I added a console.log in node_modules/typeorm/commands/MigrationRunCommand.js suggested by lucasgama335, but instead I logged the error in the catch statement and found that I had accidentally used a require in an ecmascript module.

[acSpock]

I recommend everyone first attempt what lucasgama335 and Johann150 did and add a console.log(err) in the catch block of the node_modules/typeorm/commands/MigrationRunCommand.js on line 65. For me, my issue was I was technically still connected in datagrip to an external DB through an ssh tunnel that was bound to the same local port as my typeorm connection and I was getting an auth error that wasn't surfacing.

error: password authentication failed for user "my-local-db"

[christian-1609]

@acSpock; Hi, How do you manage to connect using ssh tunnel? could you help me with information? I hope you can support me

[ledlamp]

i had the same issue setting up misskey. i resolved it by upgrading from postgresql 10 to 13.

[bluuewhale]

After doing some research, I've figured out that the error message comes from the line which loads exported DataSource instance from given path.

dataSource = await CommandUtils.loadDataSource(
    path.resolve(process.cwd(), args.dataSource as string),
)

In my case, the problem was that the target database was not initialized.

error: database "development" does not exist
    at Parser.parseErrorMessage (/Users/doghyungko/Documents/git-projects/coxwave/showcase/.yarn/cache/pg-protocol-npm-1.5.0-390f8d9ed8-b839d12caf.zip/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (/Users/doghyungko/Documents/git-projects/coxwave/showcase/.yarn/cache/pg-protocol-npm-1.5.0-390f8d9ed8-b839d12caf.zip/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (/Users/doghyungko/Documents/git-projects/coxwave/showcase/.yarn/cache/pg-protocol-npm-1.5.0-390f8d9ed8-b839d12caf.zip/node_modules/pg-protocol/src/parser.ts:103:30)
    at Socket.<anonymous> (/Users/doghyungko/Documents/git-projects/coxwave/showcase/.yarn/cache/pg-protocol-npm-1.5.0-390f8d9ed8-b839d12caf.zip/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (node:events:526:28)
    at Socket.emit (node:domain:475:12)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {

---

@assapir
You're absolutely right. The command fails before it reaches the initialize function.

From looking at the code in src/commands/MigrationRunCommand.ts it seems like it does call
await dataSource.initialize() so I have no idea where that problem is coming from.

You'll be able to figure out the error message if you manually add some error printing commands to the catch scope.

[shohelrana-dev]

i had the same issue

[bregoh]

Guys, if you ever face migration issues. Latest TypeOrm version failed to emphasize the current changes on the documentation.

<npx | yarn> <typeorm-ts-node-commonjs | typeorm-ts-node-esm> <migration command> -d <path_to_your_datasource>

for example:

yarn typeorm-ts-node-esm migration:generate <migration_path> -d src/data-source.ts

or

npx typeorm-ts-node-esm migration:run -d src/data-source.ts

for reference: https://typeorm.io/migrations#running-and-reverting-migrations

[lukasfehling]

I had the same issue today and after some debugging like @acSpock told us I finally got an error message that helped me figuring out what seems to be the problem.

Including console.log(err) didn't helped but commenting the dataSource.destroy() method out I got the espected result and an error:

I then found this article that solved my problems (Seems like I just had to define a password for connecting to an mysql database...)
https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server

Hope that helps some of you.

[Valeronlol]

check this, it might helps.

[bcla22]

I was able to fix my issue with migration:show with the advice from this reply.

I had custom path aliases configured in my tsconfig.json such that I could "pretty-import" modules within the project, i.e. import { User } from '@/user/user.entity. Turns out Typeorm doesn't like this, and couldn't resolve the module references while using typeorm-ts-node-commonjs. The error I was able to log from node_modules/typeorm/commands/MigrationShowCommand.js was

Error: Cannot find module '@/users/user.entity'
Require stack:
- /Users/{user}/dev/{ns}/_sandbox/{app}/src/auth/entities/token.entity.ts
- /Users/{user}/dev/{ns}/_sandbox/{app}/node_modules/typeorm/util/ImportUtils.js
- /Users/{user}/dev/{ns}/_sandbox/{app}/node_modules/typeorm/commands/CommandUtils.js
...

This explains why I could use the Typeorm CLI fine from the built JS datasource, but using the TS datasource it was unable to resolve those references.

[lmatosevic]

This error is very misleading, you would believe that something is wrong with database credentials or connection options, but in my case, it was due to additional "paths" defined in my tsconfig.json file.

The solution was to install ts-node and tsconfig-paths packages and add this config to the tsconfig.json:

{
  "compilerOptions": {
    ...
    "paths": {
      "@modules": [
        "src/modules"
      ],
      ...
  },
  "ts-node": {
    "require": [
      "tsconfig-paths/register"
    ]
  }
}

[connor-g-swyftx]

This gist reproduces the issue:

https://gist.github.com/gillepsi-swyftx/d595a281ec08c019ea12abbfa20ea058

Most of the CLI commands currently run dataSource.destroy() in a catch {} block when an error occurs during the command (see here). This call to destroy() is throwing, which masks the underlying issue. The error caught in the catch {} block should be logged regardless of whether destroy() throws. I'll follow up with a PR to address this.

[connor-g-swyftx]

Raised #9135 to move the log entry above the call to dataSource.destroy().

[rgembalik]

After a couple of hours of investigation I also came to this conclusion: If you see the error below after writing some migrations:

CannotExecuteNotConnectedError: Cannot execute operation on "default" connection because connection is not yet established

and it even shows when running typeorm migration:show

then you probably have problem with connection or your migration code! (EDIT: or, as it turns out, entity code, including incorrect column type).

Unfortunately, typeorm (or typeorm cli, or anything related) hides all compilation errors from migrations. The best way to learn what is wrong with your migration is to run:

npx ts-node
> import { MIGRATION_CLASS_NAME } from './src/migration/MIGRATION_FILENAME'
> MIGRATION_CLASS_NAME

This will show you actual errors in the code syntax and will help you solve them. In my case, it was just a typo in a variable name, but cli always reported CannotExecuteNotConnectedError, so I spent way too long time digging into my connectivity params.

[inapeace0]

same here today, 😥
I deployed the app on heroku, and there was no such issue, but when I run it on AWS EC2 and RDS PostgreSQL, had it.
I am not sure why....

[forrestwilkins]

Updating TypeORM from ^0.3.6 to ^0.3.12 resolved the issue for me. I was seeing the Cannot execute operation on "default" error because I hadn't yet added a new entity to my datasource config. I was able to see what the actual error was after updating to the latest version.