Better-sqlite3: Attaching additional databases

[imtbl]

Is it possible to attach additional databases to the current connection and join tables across them? If so, could you provide a quick example?

If it's currently not possible, do you think it's something you might consider adding in the future?

See https://stackoverflow.com/a/6824831 for an example of how it's done with plain queries.

I assume just executing the statement attach 'database2.db' as db2; won't work?

Thanks! :)

[imtbl]

So, I've actually tried the following since I had a bit of time:

const Database = require('better-sqlite3');

const options = {
  readonly: true,
  fileMustExist: true
};

const dbPath = './master.db';
const additionalDbPath = './additional.db';

const db = new Database(dbPath, options);
db.prepare(`ATTACH '${additionalDbPath}' AS additionalDb;`).run();

let row = db.prepare('SELECT * FROM example').get();
console.log(row);

row = db.prepare('SELECT * FROM additionalDb.example').get();
console.log(row);

// SqliteError: no such table: additionalDb.example

It seems like the ATTACH seems to work (or doesn't throw an error at least), but I'm not sure how to check if the additional DB actually got attached (there is no way to run the .databases command/keyword from the library, right?).

The first statement works fine and queries from the master database. The second statement throws an SqliteError. But even if that one had worked I'm still not sure how to specify the master database in a query since I've never assiged a name to it. E.g.:

SELECT * FROM master.example AS a
  INNER JOIN additionalDb.example AS b
  ON b.id = a.foreign_id;

Or would it be enough to just run

SELECT * FROM example AS a
  INNER JOIN additionalDb.example AS b
  ON b.id = a.foreign_id;

since SQLite assumes that I want to use the master database when I don't specify a name?

Sorry, it's probably a stupid question/issue that's easily solved, but it's my first time working with SQLite. :)

[JoshuaWise]

You can absolutely use the ATTACH statement to attach multiple databases into a single connection. You can't name the first database on a connection but it is implicitly named main (this is an sqlite feature, not a feature of this library).

Here's an example of this all working:

const Database = require('better-sqlite3');

const foo = new Database('./foo.db');
const bar = new Database('./bar.db');

// First I'll provision each database
foo.prepare("CREATE TABLE data (a INTEGER, b INTEGER)").run();
bar.prepare("CREATE TABLE data (c INTEGER, d INTEGER)").run();

// And now I'll add example data
foo.prepare("INSERT INTO data VALUES (12, 34)").run();
bar.prepare("INSERT INTO data VALUES (56, 78)").run();

// I don't need to close `bar`, but I might as well
bar.close();
foo.prepare("ATTACH './bar.db' AS other").run();

// These two statements are the same
console.log(foo.prepare("SELECT * FROM data").get());
// => { a: 12, b: 34 }
console.log(foo.prepare("SELECT * FROM main.data").get());
// => { a: 12, b: 34 }

// This is querying bar.db
console.log(foo.prepare("SELECT * FROM other.data").get());
// => { c: 56, d: 78 }

// And I can query both databases at the same time
console.log(foo.prepare("SELECT * FROM main.data INNER JOIN other.data").get());
// => { a: 12, b: 34, c: 56, d: 78 }

[JoshuaWise]

You can't use the .databases command, but you can query the built-in SQLITE_MASTER table.

console.log(foo.prepare("SELECT * FROM main.SQLITE_MASTER").get());

Which will yield something like:

{
  type: 'table',
  name: 'data',
  tbl_name: 'data',
  rootpage: 2,
  sql: 'CREATE TABLE data (a INTEGER, b INTEGER)'
}

[imtbl]

Perfect, thank you! :)

Closed.

[tacomanator]

As a follow up note, can this be achieved with in memory databases somehow? as in...

const Database = require('better-sqlite3');

const foo = new Database('./foo.db');
const bar = new Database(':memory');

foo.prepare("ATTACH DATABASE ... AS bar").run(...);

[JoshuaWise]

@tacomanator, you can do that if you use the in-memory database as your "main" database.

const Database = require('better-sqlite3');

const foo = new Database('./foo.db');
const bar = new Database(':memory:');

bar.prepare("ATTACH DATABASE ? AS foo").run(foo.name);

Alternatively, you could use a _named_ in-memory databases and access it with a URI filename.

function toURI(name) {
  // This function is defined here: https://www.sqlite.org/uri.html#the_uri_path
  if (process.platform === 'win32') {
    name = name.replace(/\\/g, '/').replace(/^[a-z]:\//i, '/$&');
  }
  return 'file:'
    + name
      .replace(/#/g, '%23')
      .replace(/\?/g, '%3f')
      .replace(/\/\/+/g, '/')
    + '?mode=memory&cache=shared';
}

const Database = require('better-sqlite3');

const foo = new Database('./foo.db');
const bar = new Database('bar', { memory: true });

foo.prepare("ATTACH DATABASE ? AS bar").run(toURI(bar.name));