5

What I'm trying to achieve is the ability to access tables within two databases by using database qualified table names (without specifically setting the default database with an SQL USE statement). My JavaScript application running on Node.js v10.15.3 is connecting to MySql with the NPM mysql 2.17.1 library as follows:

const mysql = require('mysql');

...

let configuration = {
    host: "localhost",
    port: 3306,
    user: "user",
    password: "password"
};

let connection = mysql.createConnection(configuration);

The connection is made but when I query using database qualified table names

let select = 'SELECT COUNT(*) AS count FROM application.table;';

connection.query(select);

I get:

(node:20332) UnhandledPromiseRejectionWarning: Error: ER_NO_DB_ERROR: No database selected

My guess is that this error was the mysql NPM library detecting that I hadn't specified a database to use, so I tried adding the following before the SELECT statement:

connection.query(`USE application;`);

...and now I get:

(node:10412) UnhandledPromiseRejectionWarning: Error: ER_NO_SUCH_TABLE: Table 'application.application.table' doesn't exist

So, the Node mysql library appears to be prepending the database name everywhere I reference any database object...

I'm actually storing the database qualified table names as JavaScript strings of the form 'application.table' or 'metadata.table' and would prefer to keep these in this format for simplicity of management (e.g. if the design required a table were to move from one database to the other we only need to change the string that references the table in one place).

Is there a Node mysql library configuration option to assure it that all database object references will be database qualified?

Improve this question

5 Answers 5

Reset to default
5

MySQL node package is designed to be used like that:

let configuration = {
    host: "localhost",
    port: 3306,
    user: "user",
    password: "password",
    database: "application" // Add database name here
};

And you can change database using changeUser function, like this:

connection.changeUser({
    database : 'metadata'
}, (err) => {
    if (err) {
      console.log('Error in changing database', err);
      return;
    }
    // Do another query
})

As far as I know, there's no way to query the database using database.table notation using MySQL Node package.

EDIT: I saw something based on your comment.. I think it is be possible to do what you want to do! Check this out:

var options = {sql: '...', nestTables: '_'};
connection.query(options, function (error, results, fields) {
  if (error) throw error;
  /* results will be an array like this now:
  [{
    table1_fieldA: '...',
    table1_fieldB: '...',
    table2_fieldA: '...',
    table2_fieldB: '...',
  }, ...]
  */
});

It seems like you can pass a delimiter nestTables or something like to use table names in queries !

By default, node-mysql will overwrite colliding column names in the order the columns are received from MySQL, causing some of the received values to be unavailable.

However, you can also specify that you want your columns to be nested below the table name like this

So I guess there's a workaround over here ! Have no more idea, sorry :/

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for the feedback... that implies that t isn't possible to join tables from different databases e.g. SELECT * FROM a.b AS x, c.d AS y WHERE x.id = y.id;
Take a look at my edit! Maybe you can find something there
Thanks for your suggestions - I've figured out a way to get my code working and I posted an answer - perhaps someone might have insight into the "why" I needed to use escapeId() for my references, but for now it's good enough that it's working. Thanks again.
3

I found that I needed to escape my query identifiers as follows:

const mysql = require('mysql');

let connection = mysql.createConnection(configuration);

let select = `SELECT COUNT(*) AS count FROM ${mysql.escapeId('application.table')};`;

connection.query(select);

Here's the documentation GitHub or NPM

While the documentation doesn't specifically cover the case of referencing database qualified tables - using escapeId() definitely overcame the error that was generating.

I'd be curious as to the magic that's apparently happening behind the scenes.

Improve this answer

Comments

1

The solution is more easy:

let configuration = {
    database: "application" //name -> database is required
};
Improve this answer

Comments

1

all the above answers helped in some way, but I was able to solve the issue by just using the . operator (database_name.table_name) database is a collection of tables and tables is a collections of fields. the err

 Error: ER_NO_SUCH_TABLE: Table 'application.application.table'

means it could not access the table name by just adding .users in my case solved the issue

connection.query(SELECT * FROM bincom_test.users ORDER BY id DESC, function(err, rows){

you can check my repo to see how I did mine. https://github.com/Olabisim/bincom_backend_test

Improve this answer

Comments

0

Don't know if you resolved you're problem but with mysql2 there is a way to connect to mysql server without selecting a specific DB you can do :

const  mysql=require("mysql2");

const {log,table}=console;

sqlServer=mysql.createConnection({
  host:"127.0.0.1",
  user:"user",
  password:"root",
  port:3306
});

sqlServer.connect((err,res)=>{
  log(err,res);
});

//Show the name all of you're db in SQLserver

sqlServer.query("SHOW DATABASES",((err,res)=>{
  //if err
  err?log(err):null;
  table(res);
}));
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.