CODEWITHSUNDEEP
node.jsgoogle-cloud-platformgoogle-cloud-spannergcloud-node
Chipintoza
Chipintoza

Reputation: 295

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

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?

Upvotes: 2

Views: 3148

Answers (3)

Noir Alsafar
Noir Alsafar

Reputation: 11

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'

Upvotes: 1

JJ Geewax
JJ Geewax

Reputation: 10579

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

Upvotes: 0

Dominic Preuss
Dominic Preuss

Reputation: 697

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

Upvotes: 1

Related Questions