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?

score 1 · Answer 1 · edited Mar 07 '17 at 01:52

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

edited Mar 07 '17 at 01:52

Misha Brukman

12,938
4
61
78

answered Mar 06 '17 at 20:31

Dominic Preuss

667
4
8

Thank you... @Dominic Preuss 'var table = database.table('my-table');' it never return null - as i understood it maybe a bug. I think while it not returning NULL.. my way is better.. – Chipintoza Mar 06 '17 at 22:04
The second solution here won't work -- there's no API call when calling `database.table(...)`. – JJ Geewax Mar 15 '17 at 15:39

Noir Alsafar · Answer 2 · 2017-03-22T21:44:05.800

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'

score 0 · Answer 3 · answered Mar 15 '17 at 15:36

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

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

3 Answers3