Error: ER_NO_DB_ERROR: No database selected (Node.js + MySql) when always using database qualified object references

Question

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?

Community · Accepted Answer · 2020-06-20 09:12:55Z

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 :/

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 21, 2019 at 19:50

Leafgard

516 bronze badges

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

3 Comments

Neoheurist Over a year ago

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;

Leafgard Over a year ago

Take a look at my edit! Maybe you can find something there

Neoheurist Over a year ago

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.

Neoheurist · Accepted Answer · 2019-08-22 17:55:57Z

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.

answered Aug 22, 2019 at 17:55

Neoheurist

3,4947 gold badges41 silver badges59 bronze badges

Comments

mmaismma · Accepted Answer · 2021-05-04 13:57:54Z

1

The solution is more easy:

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

edited May 4, 2021 at 13:57

mmaismma

7836 silver badges20 bronze badges

answered May 4, 2021 at 6:07

Namroy

1091 silver badge3 bronze badges

Comments

holabisii · Accepted Answer · 2022-09-22 15:00:15Z

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

answered Sep 22, 2022 at 15:00

holabisii

655 bronze badges

Comments

user19067318 · Accepted Answer · 2022-06-06 15:58:30Z

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);
}));

answered Jun 6, 2022 at 15:58

user19067318

Collectives™ on Stack Overflow

Error: ER_NO_DB_ERROR: No database selected (Node.js + MySql) when always using database qualified object references

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related