I want to insert with mikro-orm, but it dont find my table :c (TableNotFoundException)

Question

So

Console:

yarn dev
yarn run v1.22.10
$ nodemon dist/index.js
[nodemon] 2.0.7
[nodemon] to restart at any time, enter `rs`
[nodemon] watching path(s): *.*
[nodemon] watching extensions: js,mjs,json  
[nodemon] starting `node dist/index.js`     
[discovery] ORM entity discovery started, using ReflectMetadataProvider
[discovery] - processing entity Post
[discovery] - entity discovery finished, found 1 entities, took 21 ms
[info] MikroORM successfully connected to database postgres on postgresql://postgres:*****@127.0.0.1:5432
[query] begin
[query] insert into "post" ("created_at", "title", "updated_at") values ('2021-04-05T21:04:23.126Z', 'my first post', '2021-04-05T21:04:23.126Z') returning "_id" [took 12 ms]
[query] rollback
TableNotFoundException: insert into "post" ("created_at", "title", "updated_at") values ('2021-04-05T21:04:23.126Z', 'my first post', '2021-04-05T21:04:23.126Z') returning "_id" - relation "post" does not exist
    at PostgreSqlExceptionConverter.convertException (P:\.Projektek\lireddit-server\node_modules\@mikro-orm\postgresql\PostgreSqlExceptionConverter.js:36:24)
    at PostgreSqlDriver.convertException (P:\.Projektek\lireddit-server\node_modules\@mikro-orm\core\drivers\DatabaseDriver.js:194:54)
    at P:\.Projektek\lireddit-server\node_modules\@mikro-orm\core\drivers\DatabaseDriver.js:198:24
    at processTicksAndRejections (internal/process/task_queues.js:93:5)
    at async PostgreSqlDriver.nativeInsert (P:\.Projektek\lireddit-server\node_modules\@mikro-orm\knex\AbstractSqlDriver.js:150:21)
    at async ChangeSetPersister.persistNewEntity (P:\.Projektek\lireddit-server\node_modules\@mikro-orm\core\unit-of-work\ChangeSetPersister.js:55:21)
    at async ChangeSetPersister.executeInserts (P:\.Projektek\lireddit-server\node_modules\@mikro-orm\core\unit-of-work\ChangeSetPersister.js:24:13)
    at async UnitOfWork.commitCreateChangeSets (P:\.Projektek\lireddit-server\node_modules\@mikro-orm\core\unit-of-work\UnitOfWork.js:496:9)
    at async UnitOfWork.persistToDatabase (P:\.Projektek\lireddit-server\node_modules\@mikro-orm\core\unit-of-work\UnitOfWork.js:458:13)
    at async PostgreSqlConnection.transactional (P:\.Projektek\lireddit-server\node_modules\@mikro-orm\knex\AbstractSqlConnection.js:53:25)
    at async UnitOfWork.commit (P:\.Projektek\lireddit-server\node_modules\@mikro-orm\core\unit-of-work\UnitOfWork.js:183:17)
    at async SqlEntityManager.flush (P:\.Projektek\lireddit-server\node_modules\@mikro-orm\core\EntityManager.js:486:9)
    at async SqlEntityManager.persistAndFlush (P:\.Projektek\lireddit-server\node_modules\@mikro-orm\core\EntityManager.js:438:9)

previous error: insert into "post" ("created_at", "title", "updated_at") values ('2021-04-05T21:04:23.126Z', 'my 
first post', '2021-04-05T21:04:23.126Z') returning "_id" - relation "post" does not exist
    at Parser.parseErrorMessage (P:\.Projektek\lireddit-server\node_modules\pg-protocol\dist\parser.js:278:15)   
    at Parser.handlePacket (P:\.Projektek\lireddit-server\node_modules\pg-protocol\dist\parser.js:126:29)        
    at Parser.parse (P:\.Projektek\lireddit-server\node_modules\pg-protocol\dist\parser.js:39:38)
    at Socket.<anonymous> (P:\.Projektek\lireddit-server\node_modules\pg-protocol\dist\index.js:10:42)
    at Socket.emit (events.js:315:20)
    at Socket.EventEmitter.emit (domain.js:467:12)
    at addChunk (internal/streams/readable.js:309:12)
    at readableAddChunk (internal/streams/readable.js:284:9)
    at Socket.Readable.push (internal/streams/readable.js:223:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23) {
  length: 166,
  severity: 'ERROR',
  code: '42P01',
  detail: undefined,
  hint: undefined,
  position: '13',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'd:\\pginstaller_13.auto\\postgres.windows-x64\\src\\backend\\parser\\parse_relation.c',
  line: '1376',
  routine: 'parserOpenTable'
}

Index.ts:

import { MikroORM } from "@mikro-orm/core";
import { __prod__ } from "./constants";
import { Post } from "./entities/Post";
import mikroConfig from "./mikro-orm.config";

const main = async () => {
  const orm = await MikroORM.init(mikroConfig);
  await orm.getMigrator().up;
  const post = orm.em.create(Post, { title: "my first post" });
  await orm.em.persistAndFlush(post);
};

main().catch((err) => {
  console.error(err);
});

Post.ts:

import { Entity, PrimaryKey, Property } from "@mikro-orm/core";

@Entity()
export class Post {
  @PrimaryKey()
  _id!: number;

  @Property({ type: "date" })
  createdAt = new Date();

  @Property({ type: "date", onUpdate: () => new Date() })
  updatedAt = new Date();

  @Property({ type: "text" })
  title!: string;
}

mikro-orm.config.ts:

import { __prod__ } from "./constants";
import { Post } from "./entities/Post";
import { MikroORM } from "@mikro-orm/core";
import path from "path";

export default {
  migrations: {
    path: path.join(__dirname, "./migrations"),
    pattern: /^[\w-]+\d+\.[tj]s$/,
  },
  entities: [Post],
  dbName: "postgres",
  debug: !__prod__,
  type: "postgresql",
  password: "hellothere",
} as Parameters<typeof MikroORM.init>[0];

And the migration I created with npx mikro-orm migration:create:

import { Migration } from '@mikro-orm/migrations';

export class Migration20210405205411 extends Migration {

  async up(): Promise<void> {
    this.addSql('create table "post" ("_id" serial primary key, "created_at" timestamptz(0) not null, "updated_at" timestamptz(0) not null, "title" text not null);');
  }

}

After that im compiling it to js btw, but I guess the problem will be somewhere at my code or idk plz help me, I can give you more info just plz help, I've been trying to fix this bug for 5 hours :/

Btw Im doin Ben Awad's 14 hour fullstack tutorial if its matter.

score 6 · Answer 1 · answered Jun 12 '21 at 17:22

The TableNotFoundException happens when you try to add data before initializing the table's schema (or structure).

Passing the --initial as in Mosh's Answer did not work for me, possibly because I am passing a username and password in ./mikro-orm.config.ts.

I used Mikro-ORM's SchemaGenerator to initialize the table as seen here in the official docs.

Add the following lines before adding data to post in your main function in index.ts:

const generator = orm.getSchemaGenerator();
await generator.updateSchema();

The main function in index.ts should now look like this:

const main = async () => {
  const orm = await MikroORM.init(mikroConfig);    
  await orm.getMigrator().up;

  const generator = orm.getSchemaGenerator();
  await generator.updateSchema();

  const post = orm.em.create(Post, { title: "my first post" });
  await orm.em.persistAndFlush(post);
};

updateSchema creates a table or updates it based on .entities/Post.ts. This could cause issues when the Post file is updated, I haven't run in to any while following Ben's tutorial. Although, I'd still recommend creating ./create-schema.ts and running it when needed as shown in the official docs.

I tried this, but now when i run yarn dev, the terminal just hangs - it doesnt ever complete the action — Mel, Aug 21 '21 at 01:45

score 3 · Accepted Answer · answered Apr 22 '21 at 20:32

I have had the same issue. This is what I did:

I deleted the migrations folder as well as the dist folder
I ran npx mikro-orm migration:create --initial

After that, I restarted yarn watch and yarn dev and it worked for me.

Notice the --initial flag. I would recommend to check the official documentation. The migrations table is used to keep track of already executed migrations. When you only run npx mikro-orm migration:create, the table will not be created and therefore MikroORM is unable to check if the migration for the Post entity has already been performed (which includes creating the respective table on the database).

Ben does not use the --initial flag in his tutorial, he might have already ran it prior to the tutorial.

I tried this, in addition to deleting the dist folder and the src migrations folder, i deleted node_modules. I then reinstalled node modules and tried this, but i get an error that says: Error: Initial migration cannot be created, as some migrations already exist — Mel, Aug 21 '21 at 01:50

score 2 · Answer 3 · edited Jul 01 '21 at 20:59

I had a similar problem myself (Also doing Ben Awad's tutorial).

I used Mikro-ORM's schema generator to initialize the table like in Fares47's Answer, but the problem still persisted.

It wasn't until I set my user to have Superuser permissions that it started working.

I am using postgresql for my data base which I downloaded using homebrew. If you have a similar set up here is what I did:

Start up psql in your terminal using psql postgres. If you want, you can view your users and check their permissions by typing \du in the shell. Then, to change the permissions for a user use the command ALTER ROLE <username> WITH SUPERUSER;. Make sure you include a semi-colon or else it will not run the command.

Check this article out for more info on psql commands.

Tarcísio Carvalho · Answer 4 · 2021-07-10T13:22:22.013

1

I have the same problem i solved by install the ts-node on project

npm i -D ts-node

and set useTsNode on package.json as true.

The problem is the mikro-orm cli only add ts files paths in configPaths if the property useTsNode is true and ts-node is installed.

orther problem that i have is the regex in pattern property in mikro-orm.config.ts was wrong because a typo.

edited Jul 10 '21 at 13:22

answered Jul 10 '21 at 13:09

Tarcísio Carvalho

11
3

I thought Ben said not to use this. I tried this, and I get the same problem as when I try Fare's suggestion on this post. – Mel Aug 21 '21 at 01:52

score 1 · Answer 5 · answered Sep 13 '21 at 23:28

If any of the suggested steps didnt solve it for you, simply...

quit yarn watch and yarn dev run this command from the command line

npx mikro-orm migration:up

now restart watch and dev and it you should be good.

from https://mikro-orm.io/docs/migrations/#migration-class

score 0 · Answer 6 · answered Feb 06 '22 at 09:01

I also experienced this. And like Fares47 said it's possibly because I passed the username and password in ./mikro-orm.config.ts.

And my solution is simply execute the sql command that generated in ./src/migrations/Migration<NUMBERS>.ts file in postgresql terminal. Here is the command that I execute in the database,

create table "post" ("id" serial primary key, "created_at" timestamptz(0) not null, "updated_at" timestamptz(0) not null, "title" text not null);

Just like what they suggested in the doc,

A safe approach would be generating the SQL on development server and saving it into SQL Migration files that are executed manually on the production server.

I want to insert with mikro-orm, but it dont find my table :c (TableNotFoundException)

6 Answers6

Linked