Can you check whether a table exists via the Cloud Spanner API? (in Node.js)

Question

How can i detect table exist or not in database by table name? there is not like:

database.exist('some table name')

I write my function:

const queryTableExist = (tableName) => {
    return {
        sql: `SELECT t.table_name FROM information_schema.tables AS t WHERE t.table_catalog = '' and t.table_schema = '' and  t.table_name='${tableName}'`
    };
};
let tableExist = (tableName, cb) => {
    const query = queryTableExist(tableName);
    database.run(query, (err, rows) => {
        if (err) {
            console.log(`${err}\n${query}`);
            cb(err);
        }

        cb(err, rows.length > 0);
    })
};

is there any other idea?

Answer 1

Assuming you are using the google-cloud-node client library and the Cloud Spanner package:

You can either call call table.create() and handle the error:

var schema =
  'CREATE TABLE Singers (' +
  '  SingerId INT64 NOT NULL,' +
  '  FirstName STRING(1024),' +
  '  LastName STRING(1024),' +
  '  SingerInfo BYTES(MAX),' +
  ') PRIMARY KEY(SingerId)';

table.create(schema, function(err, table, operation, apiResponse) {
  if (err) {
    // Error handling omitted.
  }

  operation
    .on('error', function(err) {})
    .on('complete', function() {
      // Table created successfully.
    });
});

Or you can try to get a reference to the table and then check for a null object:

var instance = spanner.instance('my-instance');
var database = instance.database('my-database');
var table = database.table('my-table');

Answer 2

I think the best way to do this is to request the DDL from the database and check for a CREATE TABLE <your-table-name-here> ( ... statement with a regular expression:

var instance = spanner.instance('my-instance');
var database = instance.database('my-database');

function databaseHasTable(database, tableName) {
  return database.getSchema().then(function (data) {
    var statements = data[0];
    var matcher = new RegExp('^create table ' + tableName + ' \\(', 'i');
    var results = statements.filter(function (item) {
      return matcher.test(item);
    });
    return results.length == 1;
  });
}

// This should print out true or false depending on the table being there.
databaseHasTable(database, 'my-table').then(console.log);

Answer 3

Google Spanner API currently does not support exists function to discover which table exists. So you'll need to query it yourself at the moment.

the highest performance way to get your answer

Get all tables that exist

SELECT
    t.table_name
FROM
    information_schema.tables AS t
WHERE
    t.table_catalog = '' AND t.table_schema = ''

Find out if certain tables exist

SELECT
    t.table_name
FROM
    information_schema.tables AS t
WHERE
    t.table_catalog = '' AND t.table_schema = '' AND
    t.table_name = 'yourTableNameA' OR
    t.table_name = 'yourTableNameB' OR
    t.table_name = 'yourTableNameC'