javascript function to check if a mysql table exists and return an integer

Question

I have a javascript function which is intended to output a 0 if a mysql table doesn't exist and a 1 if it does. But I have not been able to understand why it does not function as intended.

function TableExistsOrNot (checktable){
    try{
        console.log("the function was entered");
        var sqlcheck = 'SELECT id FROM ?? ORDER BY id DESC LIMIT 1';
        con.query(sqlcheck,[checktable],function (err, result) {
            if (result > 0){
                console.log(result);
                console.log("table exists");
                return 1;
            }
            if(result === undefined){
                console.log("result was undefined");
                return 0.0;
            }

        });
    }

    catch (err) {
    console.log(err);
    console.log("error locating table");
    return 0;
    }

}

When I console log the result it just says undefined. And if I try to assign the result of the function to a variable it also says undefined.

edit based on the feedback I have added code for async nature, installed and required express. But this has not solved the problem. See the code below. What is strange is console.log(a); in the else statement is logging 5. But the last line console.log(datatablepresent); which should be equal to 'a' is logging Promise { <pending> }

function checktable(tablename) {
    try{
        let sql = 'SELECT id FROM ?? ORDER BY id DESC LIMIT 1';
        return new Promise((resolve, reject) => {
        con.query(sql,[tablename],(err, result) => {
            if (err) {
                console.log("entered err");
            }
            else {
                resolve(result);
                console.log("entered else");
                var a = result[0].id;
                if (a>1){
                    console.log(a);
                    return a;
                }
                
            }
        });
        });
    }
    catch (err) {
        console.log(err);
        console.log("error locating table");
        //return 0;
    }
}
    

app.get("/", async function (req, res) {
    console.log("entered callback");
    const result = await checktable();
    res.send(result);
    console.log(result);
});

datatablepresent = checktable('beta5');
console.log(datatablepresent);

All the tables information is stored in `mysql` database. But on current database you can use ```show tables LIKE 'xxx';``` — fela, Mar 11 '23 at 20:12
Does this answer your question? [How do I return the response from an asynchronous call?](https://stackoverflow.com/questions/14220321/how-do-i-return-the-response-from-an-asynchronous-call) — Konrad, Mar 11 '23 at 20:12
No if I return the result as shown in the link it says undefined. How can I make this return a 0, when undefined? — Publius, Mar 11 '23 at 20:26
Does this answer your question? [How to properly return a result from mysql with Node?](https://stackoverflow.com/questions/31875621/how-to-properly-return-a-result-from-mysql-with-node) — Henry Woody, Mar 11 '23 at 20:53

O. Jones · Answer 1 · 2023-03-13T15:21:00.130

Here is the right query to find out if a table exists. Use the table -- actually the system view -- named TABLES information_schema.

It's too hard to issue a query and cope with a sometimes-expected error when you can just get a number. What you want to do is simply ask the system. And you're presenting the name of the table as an ordinary string value to go into a placeholder.

SELECT COUNT(*) table_exists 
FROM information_schema.TABLES 
 WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
  AND TABLE_NAME = ?;

You give the table name as the substitution parameter. The query always returns one row and one column. The column, named table_exists in the result set, has the value 1 if the table does exist, and 0 otherwise.

Then you have to structure the query and error handler correctly, and handle the async nature of the query correctly. The easiest way to do that is with the mysql2 package and an async function.

const mysql = require('mysql2/promise');

const table_exists_query = `
  SELECT COUNT(*) table_exists 
    FROM information_schema.TABLES 
   WHERE TABLE_SCHEMA = DATABASE()
     AND TABLE_TYPE = 'BASE TABLE'
     AND TABLE_NAME = ?;`

async function TableExistsOrNot (checktable) {
  try {
    console.log("the function was entered")
    const [rows, fields] = await con.query(table_exists_query, [checktable])
    return result[0].table_exists > 0 ? true : false
  }
  catch (error) {
    console.error ('should not happen', error)
    return false
  }
}


const exists = await TableExistsOrNot( 'my_table_name' )

Edit You can put all your Javascript code in an async function, like this.

async function allMyCode () {
  ...
  const exists = await TableExistsOrNot( 'my_table_name' )
  ...

}

allMyCode().catch(console.error)

Or you can do use a promises' then clause to call async code from a non-async context:

TableExistsOrNot( 'my_table_name' )
 .then(function (exists) {
     if (exists) console.log ('the table exists')
  })

If you're not quite sure about this async / await / promises stuff I respectfully suggest you learn about it as soon as feasible. — O. Jones, Mar 11 '23 at 22:22
I can't get this to work. If I include the await in the last line `const exists = await TableExistsOrNot( 'my_table_name' )` compiling fails. I see `SyntaxError: await is only valid in async functions and the top level bodies of modules` — Publius, Mar 12 '23 at 03:05
one more thing I don't understand is if I remove `async` and `await` to test the functionality without the async nature. I get the error: `TypeError: object is not iterable (cannot read property Symbol(Symbol.iterator))` — Publius, Mar 12 '23 at 04:45
You *must* figure out how to use promises / async / await to write effective Javascript code. Explaining that is beyond the scope of a Stack Overflow answer. That being said, see my edit. — O. Jones, Mar 13 '23 at 15:13

Jordy · Answer 2 · 2023-03-14T01:17:55.887

You can simply check whether the table exists or not in the particular database just with

SHOW TABLES LIKE "<table_name>"

If the table exists, it will return:

[ RowDataPacket { 'Tables_in_<db_name> (<table_name>)':'<table_name>' } ]

If not, it will return:

[]

So it can return 1 or 0 (exist or not) with the value of result.length.

async function checktable(tablename) {
    try {
        let sql = `SHOW TABLES LIKE "${tablename}"`;
        return new Promise((resolve, reject) => {
            con.query(sql, (err, result) => {
                if (err) {
                    console.log("entered err");
                    reject(err);
                } else {
                    console.log("entered else");
                    // check if tablename already exists
                    resolve(result.length);
                }
            });
        });
    } catch (err) {
        return err;
    }
}

app.get("/", async function(req, res) {
    console.log("entered callback");
    const result = await checktable('item');
    res.json(result);
    console.log(result);
});

javascript function to check if a mysql table exists and return an integer

2 Answers2