0

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.

Trees4theForest
  • 109
  • 1
  • Possible duplicate of [Are there any OO-principles that are practically applicable for Javascript?](https://softwareengineering.stackexchange.com/questions/180585/are-there-any-oo-principles-that-are-practically-applicable-for-javascript) – gnat Aug 09 '17 at 18:56
  • How exactly? I'm missing what in those asnwers applies here? – Trees4theForest Aug 09 '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 `IDataStoreAccessor`s 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 09 '17 at 19:13
  • Use a Factory method to return the proper connector. See http://www.dofactory.com/javascript/factory-method-design-pattern – Robert Harvey Aug 09 '17 at 19:18

1 Answers1

1

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)
Samuel
  • 9,137
  • 1
  • 25
  • 42
  • Looks promising... but still working through the logic (I'm still figuring out JS). Particularly `const foo = dao => ({` is tripping me up – Trees4theForest Aug 09 '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 09 '17 at 20:57
  • Got it, that makes it a little clearer. Thanks for your patience – Trees4theForest Aug 09 '17 at 20:58