I am developing a javascript (Node.js) desktop program that works with an existing MySQL database. I would like to (eventually) distribute to others with based on SQLite, or MySQL if they need (or possibly some other database).

What is a good strategy for writing database agnostic code? I'm baking in switches like the pseudo-code below, but I think there's a better way to abstract this out:

result = query1();

function query1(){
  DbType = getDbType(); // Returns MySQL or SQLite
  if DbType == 'MySQL'{
    query = ""; // MySQL Query
    result = mysqlconnector.doquery(query);
  }else{
  if DbType == 'SQLite'{
    query = ""; // SQLite Query
    result = sqliteconnector.doquery(query);
  }
  return result;
}

I don't think I want a full-blows ORM as they seem annoyingly restrictive.

  • How exactly? I'm missing what in those asnwers applies here? – Trees4theForest Aug 9 '17 at 19:05
  • 1
    Inversion of Control. Specifically define some interface (I'll call it IDataStoreAccessor to make this easier) that would have functions like getFoo(id) or saveFoo(foo). Then everywhere you need to access a data store, pass in an object that implements that interface. Then when you want to swap data stores you simply change whatever supplies your code with IDataStoreAccessors with a different implementation. Your code doesn't care that a switch happened and will just keep on working. Then when you decide to expand to a new data store, you just make a new accessor and away you go. – Becuzz Aug 9 '17 at 19:13
  • Use a Factory method to return the proper connector. See dofactory.com/javascript/factory-method-design-pattern – Robert Harvey Aug 9 '17 at 19:18

As @Becuzz suggested, in many OO languages you would create an interface and implement a SQLite and MySQL implementation. In JavaScript you don't have interfaces, but the principle is the same.

Create two JavaScript objects that have the same functions defined on each:

function mySqlDao(connector) {
  return {
    query1: function() {
      // query mysql
      return connector.doquery("...")
    }
  }
}

function sqliteDao(connector) {
  return {
    query1: function() {
      // query sqlite
      return connector.doquery("...")
    }
  }
}

Where you want to run a query, inject a DAO

function foo(dao) {
  return {
    bar: function() {
      const foos = dao.query1()
      // ...
    }
  }
}

And at your composition root you construct your DAO.

const dbType = getDbType()
const dao = dbType === 'MySQL' ? mySqlDao(mysqlconnector) :
  sqliteDao(sqliteconnector)
const myFoo = foo(dao) 
  • Looks promising... but still working through the logic (I'm still figuring out JS). Particularly const foo = dao => ({ is tripping me up – Trees4theForest Aug 9 '17 at 20:49
  • @Trees4theForest it assigns a function with a single parameter dao to foo. I've edited my answer to use the traditional syntax instead of arrow functions. – Samuel Aug 9 '17 at 20:57
  • Got it, that makes it a little clearer. Thanks for your patience – Trees4theForest Aug 9 '17 at 20:58

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.