Develop for MySQL and SQLite3 Concurrently

by Trees4theForest   Last Updated August 09, 2017 19:05 PM

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.



Answers 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
Samuel
August 09, 2017 20:15 PM

Related Questions


how to drop tables in SQLite

Updated May 05, 2016 08:02 AM

How to connect back-end with front-end

Updated September 14, 2016 09:02 AM

Should I use a plain text database, or SQLite?

Updated May 31, 2016 08:02 AM