QueryFailed error when trying to synchronise

[keithdarragh]

I'm currently in the process of testing a typescript app using Typeorm. Tried to create a few simple examples at first but it seems that pg-mem is throwing an error when trying to write the test. Application code works fine. Have I implemented wrong/is there a simple fix?

Thanks!

Stack trace

QueryFailedError: column "columns.table_name" does not exist

    🐜 This seems to be an execution error, which means that your request syntax seems okay,
    but the resulting statement cannot be executed → Probably not a pg-mem error.

    *️⃣ Failed SQL statement: SELECT columns.*, pg_catalog.col_description(('"' || table_catalog || '"."' || table_schema || '"."' || table_name || '"')::regclass::oid, ordinal_position) AS description, ('"' || "udt_schema" || '"."' || "udt_name" || '"')::"regtype" AS "regtype", pg_catalog.format_type("col_attr"."atttypid", "col_attr"."atttypmod") AS "format_type" FROM "information_schema"."columns" LEFT JOIN "pg_catalog"."pg_attribute" AS "col_attr" ON "col_attr"."attname" = "columns"."column_name" AND "col_attr"."attrelid" = ( SELECT "cls"."oid" FROM "pg_catalog"."pg_class" AS "cls" LEFT JOIN "pg_catalog"."pg_namespace" AS "ns" ON "ns"."oid" = "cls"."relnamespace" WHERE "cls"."relname" = "columns"."table_name" AND "ns"."nspname" = "columns"."table_schema" ) WHERE ("table_schema" = 'public' AND "table_name" = 'address');

    👉 You can file an issue at https://github.com/oguimbal/pg-mem along with a way to reproduce this error (if you can), and  the stacktrace

Typeorm entity:

 @Entity()
export class Address {
  @PrimaryGeneratedColumn('uuid')
  id: number;

  @Column()
  number: string;

  @Column()
  line1: string;

  @Column()
  line2: string;

  @Column()
  city: string;

  @Column()
  postcode: string;

  @Column()
  country: string;
}

Test

describe('address model', () => {
  let connection: Connection;
  beforeEach( async () => {
    const db = newDb();
    console.log('creating connection');
    connection = await db.adapters.createTypeormConnection({
        type: 'postgres',
        entities: [ Address ]
    });

    console.log('sync?')
    await connection.synchronize();
  });
  it('should be created', () => {
    console.log('hi')
    const addressRepository = connection.getRepository(Address);
    const address = addressRepository.create({
      number: '30',
      line1: 'Main Street', 
      city: 'London',
      postcode: 'N113SE',
    });

    expect(address.number).toEqual('30');
  });
});

[vsSanti]

Hi @keithdarragh, I think I've solved this problem, at least for me.

I was running the project with pg-mem using Node 12, and I was getting an error when running jest saying that current_database() was not a Postgres function or something like this. What I ended up doing was changing my Node version to a most recent one (in my case, version 16).

I hope this helps you.

[oguimbal]

Thanks for the feedback 🙂 Appreciate it.

I'll try to investigate when I have more time.

Happy to know you circumvented this issue.

[adolfo-f]

+1 I'm seeing exactly the same issue but updating to the latest node did not help. I get the error when running the example here https://github.com/oguimbal/pg-mem/blob/master/samples/typeorm/simple.ts. Thanks a lot.

[oguimbal]

That may also be a Typorm version issue. I know that 0.2.29 works. Have you installed this version ? (cat node_modules/typeorm/package.json | grep version ... the minor version counts, I know that 0.2.30 has issues)

[adolfo-f]

Thanks @oguimbal yes its the same issue. The error goes away when downgrading to 0.2.29.

[TylerSustare]

I was able to fix the issue by using the registerFunction for my use case (TypeORM with NestJS) I was getting the error like others in this thread.

    QueryFailedError: ERROR: function current_database() does not exist
    HINT: 🔨 Please note that pg-mem implements very few native functions.

                👉 You can specify the functions you would like to use via "db.public.registerFunction(...)"

    🐜 This seems to be an execution error, which means that your request syntax seems okay,
    but the resulting statement cannot be executed → Probably not a pg-mem error.

    *️⃣ Failed SQL statement: SELECT * FROM current_database();

    👉 You can file an issue...

But by setting up my tests like the following, everything is working splendidly on Node v14.15.0 and "pg-mem": "^1.9.17"

import { Test, TestingModule } from '@nestjs/testing';
import { IMemoryDb, newDb } from 'pg-mem';
import { Connection, Repository } from 'typeorm';
import { UsersService } from './users.service';
import { User } from './users.entity';
import { usersFixtures } from './fixtures';

describe('UsersService', () => {
  let db: IMemoryDb;
  let got: Connection;
  let users: Repository<User>;
  let service: UsersService;

  beforeAll(async () => {
    //==== create a memory db
    db = newDb({
      autoCreateForeignKeyIndices: true,
    });

    //==== define current_database
    db.public.registerFunction({
      implementation: () => 'test',
      name: 'current_database',
    });

    //==== create a Typeorm connection
    got = await db.adapters.createTypeormConnection({
      type: 'postgres',
      entities: [User],
    });
    //==== create tables
    await got.synchronize();
    users = got.getRepository(User);

    //==== create entities
    for (const user of usersFixtures()) {
      const u = users.create(user);
      await users.save(u);
    }
  });

  afterAll(async () => {
    await got.close();
  });

  const mockFindOne = jest.fn((id) => users.findOne(id));

  const fakeUserService: Partial<UsersService> = {
    findOne: mockFindOne,
  };

  beforeEach(async () => {
    const module: TestingModule = await Test.createTestingModule({
      providers: [{ provide: UsersService, useValue: fakeUserService }],
    }).compile();

    service = module.get<UsersService>(UsersService);
  });

  afterEach(() => {
    mockFindOne.mockClear();
  });

  it('should be defined', () => {
    expect(service).toBeDefined();
  });

  describe('find', () => {
    it('should find one', async () => {
      const user = await service.findOne(1);

      expect(user).toMatchObject({
        email: 'a',
        password: 'b',
      });

      const user2 = await service.findOne(2);
      expect(user2).toMatchObject({
        email: 'bono',
        password: 'u2sux',
      });
      expect(mockFindOne).toBeCalledTimes(2);
    });
  });
});

[gareth-cheeseman]

To add to the information on this, my experience is that:

• typeorm version 0.2.29 works
• from typeorm versions 0.2.30 – 0.2.34 I get QueryFailedError: column "columns.table_name" does not exist
• from typeorm versions 0.2.35 - 0.2.41 I get QueryFailedError: ERROR: function current_database() does not exist despite having registered the function, as described in the pg-mem docs (and above).

I understand that typeorm 0.2.29 is listed as strict peer dependency, possibly for these reasons.

[oguimbal]

You're right.

This might also be usefull to workaround this problem.

[gareth-cheeseman]

@oguimbal thanks for the reply (and the great tool!). Unfortunately that workaround doesn't help for my situation. Ideally I need to get it working with the later versions (0.2.37 +) of typeorm as there are typeorm fixes that are needed in an application. And with those later versions I get no more error than the function isn't there despite registering it.

[dparish]

This is a test I had written to validate that my migrations were working. This in no way represents a best practice, just my attempt to flush things out until I settle on a better pattern.

The two registered functions were needed. version is used internally by typeorm gen_random_uuid is emulating what postgres does.

import {beforeEach, describe, it} from 'vitest';
import {DataType, newDb} from 'pg-mem'
import {DataSource} from 'typeorm';
import {migrations} from './migrations';
import {randomUUID} from 'node:crypto';

describe('runMigrations', () => {
   let datasource: DataSource
   beforeEach(async () => {
      const db = newDb({
         autoCreateForeignKeyIndices: true,
      });
      db.public.registerFunction({
         name: 'version',
         returns: DataType.text,
         implementation: () => '17.2',
      })
      db.public.registerFunction({
         name: 'gen_random_uuid',
         returns: DataType.text,
         implementation: () => randomUUID(),
      })
      db.createSchema('test');
      datasource = await db.adapters.createTypeormDataSource({
         type: 'postgres',
         entities: [/* your entities here ! */],
         database: 'test',
         migrations
      })
      await datasource.initialize();
   })
   it('should run migrations', async () => {
      await datasource.runMigrations();
   })
});

migrations is just an exported array of the migrations....

Versions: "typeorm": "^0.3.20", "pg": "^8.13.1", "pg-mem": "^3.0.3",