knexFromUrl.js

const parseDbUrl = require('parse-database-url')
const isString = require('is-string')
const isNumber = require('is-number')
const unflatten = require('unflatten')

const parseBoolean = (string, defaultValue = null) => {
  // handle numbers
  if (isNumber(string)) {
    return parseFloat(string) > 0
  }

  // handle booleans
  if (!isString(string)) {
    return !!string
  }

  // handle strings
  switch (string.trim().toLowerCase()) {
    case 'true': return true
    case 'false': return false
    case 'yes': return true
    case 'no': return false
    default: return defaultValue
  }
}

// get a knex connection-object from URL
const knexFromUrl = url => {
  const config = parseDbUrl(url.replace(/\\/g, '//').replace(/ /g, '%20').replace(/^sqlite:/, 'sqlite3'))
  let { filename, driver, user, password, database, host, port, socketPath, useNullAsDefault, ssl, ...options } = config

  if (driver === 'postgres' || driver === 'postgresql') {
    driver = 'pg'
  }

  const out = { client: driver, connection: {} }

  if (filename) {
    out.connection.filename = filename.replace(/^sqlite3\/\//, '')
  }

  if (useNullAsDefault !== undefined) {
    out.useNullAsDefault = parseBoolean(useNullAsDefault)
  }

  if (ssl !== undefined) {
    out.connection.ssl = parseBoolean(ssl)
  }

  if (database) {
    out.connection.database = database
  }

  if (host) {
    out.connection.host = host
  }

  if (port) {
    out.connection.port = port
  }

  if (user) {
    out.connection.user = user
  }

  if (password) {
    out.connection.password = password
  }

  if (socketPath) {
    out.connection.socketPath = socketPath
  }

  return { ...out, ...unflatten(options) }
}

module.exports = knexFromUrl

knexFromUrl.text.js

/* global describe, test, expect */
import knexFromUrl from './knexFromUrl'

describe('knexFromUrl', () => {
  // some of these are bad URLs, or not supported by knex, but added them to tests anyway.
  const urls = [
    'postgres://uf07k1i6d8ia0v:wegauwhgeuioweg@ec2-107-21-253-135.compute-1.amazonaws.com:5431/d8r82722r2kuvn',
    'postgres://%2Fvar%2Frun%2Fpostgresql/d8r82722r2kuvn',
    'postgres://%2FUsers%2Fpostgres%2FRuN/d8r82722r2kuvn',
    'postgres://%2FUsers%2Fpostgres%2FRuN/d8r82722r2kuvn?ssl=true',
    'postgres://ieRaekei9wilaim7:wegauwhgeuioweg@[2001:db8:1234::1234:5678:90af]:5431/d8r82722r2kuvn',
    'postgres://uf07k1i6d8ia0v:wegauwhgeuioweg@ec2-107-21-253-135.compute-1.amazonaws.com:5431/d8r82722r2kuvn?currentSchema=otherschema',
    'postgres://%23user:%23password@ec2-107-21-253-135.compute-1.amazonaws.com:5431/%23database',
    'postgis://uf07k1i6d8ia0v:wegauwhgeuioweg@ec2-107-21-253-135.compute-1.amazonaws.com:5431/d8r82722r2kuvn',
    'postgis://uf07k1i6d8ia0v:wegauwhgeuioweg@ec2-107-21-253-135.compute-1.amazonaws.com:5431/d8r82722r2kuvn?currentSchema=otherschema',
    'mysqlgis://uf07k1i6d8ia0v:wegauwhgeuioweg@ec2-107-21-253-135.compute-1.amazonaws.com:5431/d8r82722r2kuvn',
    'mysql-connector://uf07k1i6d8ia0v:wegauwhgeuioweg@ec2-107-21-253-135.compute-1.amazonaws.com:5431/d8r82722r2kuvn',
    'mysql://bea6eb025ca0d8:69772142@us-cdbr-east.cleardb.com/heroku_97681db3eff7580?reconnect=true',
    'sqlite://:memory:',
    'mysql://bea6eb025ca0d8:69772142@us-cdbr-east.cleardb.com/heroku_97681db3eff7580?reconnect=true',
    'mysql://bea6eb025ca0d8:69772142@us-cdbr-east.cleardb.com/heroku_97681db3eff7580?reconnect=true',
    'oracle://scott:tiger@oraclehost:1521/hr',
    'oraclegis://scott:tiger@oraclehost:1521/hr',
    'oracle://scott:tiger@/tnsname',
    'redshift://uf07k1i6d8ia0v:wegauwhgeuioweg@ec2-107-21-253-135.compute-1.amazonaws.com:5439/d8r82722r2kuvn?currentSchema=otherschema',
    'mssql://uf07k1i6d8ia0v:wegauwhgeuioweg@ec2-107-21-253-135.compute-1.amazonaws.com/d8r82722r2kuvn?driver=ODBC Driver 13 for SQL Server',
    'mssql://uf07k1i6d8ia0v:wegauwhgeuioweg@ec2-107-21-253-135.compute-1.amazonaws.com\\insnsnss:12345/d8r82722r2kuvn?driver=ODBC Driver 13 for SQL Server',
    'sqlite3:///tmp/demodb.sqlite3?useNullAsDefault=1',
    'sqlite:///tmp/demodb.sqlite3?useNullAsDefault=false'
  ]
  urls.forEach(url => {
    test(url, () => {
      expect(knexFromUrl(url)).toMatchSnapshot()
    })
  })
})