Knex error loading SQL FIle - syntax error at or near "undefined"

Question

Im sure this is a simple problem but im stuck and Dr Google isn't helping.

Basically im trying to load schema SQL files in a migration using Knex in Node. The SQL files are directly from PGAdmin, no data, just schema.

Essentially, im loading the SQL files off the file system and knex.raw() applying them. I can see the SQL is loading up fine, its spitting out into the console.

My migration method looks like

exports.up = knex => {
    const schemaFilePath = path.join(__dirname, "../db/schema");
    const schemaFiles = fs.readdirSync(schemaFilePath);
    let sql;
    schemaFiles.forEach(async file => {
        sql += fs.readFileSync(path.join(schemaFilePath, file), "utf8");
    });
    return knex.raw(sql);
};

Theres several SQL files, loaded from that schema directory that look as follows. (with the commented lines etc)

-- Table: public.roles

-- DROP TABLE public.roles;

CREATE TABLE public.roles
(
    _id uuid NOT NULL DEFAULT uuid_generate_v4(),
    "displayName" citext COLLATE pg_catalog."default",
    "createdAt" timestamp with time zone NOT NULL,
    "updatedAt" timestamp with time zone NOT NULL,
    "deletedAt" timestamp with time zone,
    "_fullTextSearch" citext COLLATE pg_catalog."default",
    system boolean DEFAULT false,
....

The error I get is

    ON public.validations USING btree
    ("updatedAt" ASC NULLS LAST)
    TABLESPACE pg_default;

 - syntax error at or near "undefined"
error: syntax error at or near "undefined"
at Connection.parseE (/xxx/node_modules/pg/lib/connection.js:604:13)

It's not that particular file either as if I just delete that file, I still get the same error, just at another file.

Scrolling through the outputted logs, the SQL all looks fine to me.

Iv checked for bad characters, spaces etc. I got no where.

Hoping im just doing something wrong here.

Any ideas?

Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. wiki.postgresql.org/wiki/… — user330315
– user330315, Commented Apr 16, 2020 at 5:51
@a_horse_with_no_name It is not inheritly bad to use case sensitive indentifiers. One can use them and knex will not cause any problems since knex adds those quotes automatically :) A lot more matters what kind of attribute names one likes to have in javascript side of code. If you are willing to live with lower / snake_case js object attributes, then you can use them. EDIT: What I have seen that people do a lot is to use snake_case in DB and cameCase in JS side and that is where you really will get lot's of problems. — Mikael Lepistö
– Mikael Lepistö, Commented Apr 19, 2020 at 13:00

Mikael Lepistö · Accepted Answer · 2020-04-17 08:22:20Z

Since pg@7 executing muliple statements with knex raw should have been supported. So there is a slight change that this actually could work, but I have never tested it myself.

If you run those SQL files in one by one, does it work better?

Are you sure that concat is not messing them up?

Does that happen always after certain amount of SQL, maybe there is a limit for max query size?

I have always used command line tools to save/restore DB dumps.

One could probably execute those tools inside knex migration file and then let knex migration system to mark that migration been used already.

I have used this kind of functions to save / restore database states from node code (usually storing states of system between different e2e test setups to be able to start e2e run from the middle of the execution):

 async dump(dumpFileName, user, password, database, host) {
    return new Promise((resolve, reject) => {
      const cmd = [
        `export PGPASSWORD=${password};`,
        `pg_dump -a -O -x -F c`,
        `-f '${dumpFileName}'`,
        `-d ${database}`,
        `-h ${host}`,
        `-p 5432`,
        `-U ${user}`
      ].join(' ');

      shelljs.rm('-f', dumpFileName);
      shelljs.exec(cmd, (code, stdout, stderr) => {
        console.log(`Command ready: ${cmd}, with exit code: ${code}`);
        if (code === 0) {
          resolve(stdout);
        } else {
          reject(new Error(stderr));
        }
      });
    });
  }

  async restore(dumpFileName, user, password, database, host) {
    return new Promise((resolve, reject) => {
      const cmd = [
        `export PGPASSWORD=${password};`,
        `cat '${dumpFileName}' | `,
        `pg_restore -a -O -x -F c`,
        `-d ${database}`,
        `-h ${host}`,
        `-p 5432`,
        `-U ${user}`,
        `--disable-triggers"`
      ].join(' ');

      shelljs.exec(cmd, (code, stdout, stderr) => {
        console.log(`Command ready: ${cmd}, with exit code: ${code}`);
        if (code === 0) {
          resolve(stdout);
        } else {
          reject(new Error(stderr));
        }
      });
    });
  }

Not a perfect solution, but might work for someone...

Its only a handful of files, so I think its within the max query size. I dont really know if there is a limit for knex / javascript though. Maybe Ill try going through the one by one and introduce them until theres a probelm. Yea, not a bad idea about pg_restore, although its just another tool I need to add to the production containers then which iv rather avoid if possible. I'll try that if I get stuck Thanks for the reply

Cheyne · Accepted Answer · 2020-04-21 03:09:54Z

Hate to answer my own question, but I figured out what was happening. Thank you to all those that replied.

For anyone else trying to do the same thing, there were a few problems with that code.

First the error about being "undefined" was due to

let sql;
sql += fs.readFileSync(path.join(schemaFilePath, file), "utf8");

This generated an "undefined" at the very start of the SQL statement. Solved by

let sql = "";

Next I hit a problem with bad characters that this time, were completely invisible and was tricky to find. I was only able to find them by actually writing the contents of the string back out to the file, in which I then saw a bunch of invalid characters, which I guess are not UTF-8 compatible.

This was due to hidden files being lifted off the file system. ie

    schemaFiles.forEach(file => {
        sql += fs.readFileSync(path.join(schemaFilePath, file), "utf8");
    });

As im on a Mac, turns out it was also loading up those annoying .DS_Store files which inserted binary junk, although this wasn't visible on the screen output, only after writing the contents back out to a flat file.

I was going to try psql load it. psql gave me a better error message.

Solved this by filtering for only .sql files

     schemaFiles.forEach(file => {
        if (file.substr(file.length - 4) === ".sql") {
         code ....
        }
     })

Lastly, I had problems loading the schema files due to CONSTRAINTS and tables not being present in the schema, so I use pg_dump to get a valid schema file, excluding the knex migration tables

pg_dump --schema-only --exclude-table=knex* > db/schema.sql

Now the migration file is essentially one single line

return knex.raw(fs.readFileSync(path.join(__dirname, "../db/schema.sql"), "utf8"));

Hope this helps anyone else who is stuck

Collectives™ on Stack Overflow

Knex error loading SQL FIle - syntax error at or near "undefined"

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related