Repository: rickbergfalk/sqlpad
Pull request #763 (2020-07-05 05:12:04)

Add .env file support
Adds .env file support. 

See #690 


Patch:
diff --git a/config-example.env b/config-example.env
new file mode 100644
index 00000000..b4b9ffcd
--- /dev/null
+++ b/config-example.env
@@ -0,0 +1,165 @@
+# Email address to give admin permissions to
+SQLPAD_ADMIN=""
+
+# Password to set for admin email address on application start. Requires `admin` setting to also be provided.
+SQLPAD_ADMIN_PASSWORD=""
+
+# Enable csv, json and xlsx downloads.
+SQLPAD_ALLOW_CSV_DOWNLOAD=true
+
+# Allows pre-approval of email domains. Delimit multiple domains by empty space.
+SQLPAD_ALLOWED_DOMAINS=""
+
+# Path to mount sqlpad app following domain. Example, if '/sqlpad' is provided queries page would be mydomain.com/sqlpad/queries
+SQLPAD_BASE_URL=""
+
+# Name used for cookie. If running multiple SQLPads on same domain, set to different values
+SQLPAD_COOKIE_NAME="sqlpad.sid"
+
+# Secret used to sign cookies
+SQLPAD_COOKIE_SECRET="secret-used-to-sign-cookies-please-set-and-make-strong"
+
+# Enable/disable automigration on SQLPad process start. Disable by setting to `false`
+SQLPAD_DB_AUTOMIGRATE="true"
+
+# Directory to store SQLPad embedded database content. This includes queries, users, query result cache files, etc.
+SQLPAD_DB_PATH=""
+
+# Default connection to select on SQLPad load if connection not previousy selected.
+SQLPAD_DEFAULT_CONNECTION_ID=''
+
+# Set to TRUE to disable authentication altogether.
+DISABLE_AUTH="false"
+
+# Specifies the role associated with users when disableAuth is set to true.
+# Acceptable values: admin, editor
+SQLPAD_DISABLE_AUTH_DEFAULT_ROLE="editor"
+
+# Set to TRUE to disable built-in user authentication. Use to restrict auth to OAuth only.
+DISABLE_USERPASS_AUTH="false"
+
+# Set to TRUE if LDAP enable, FALSE if LDAP disable.
+ENABLE_LDAP_AUTH="false"
+
+# LDAP URL, ldap and ldaps protocols are supported.
+LDAP_URL=""
+
+# Base LDAP DN to search for users in.
+LDAP_BASE_DN=""
+
+# The bind user will be used to lookup information about other LDAP users.
+LDAP_USERNAME=""
+
+# The password to bind with for the lookup user.
+LDAP_PASSWORD=""
+
+# Enable word wrapping in SQL editor.
+SQLPAD_EDITOR_WORD_WRAP="false"
+
+# Google Client ID used for OAuth setup. Authorized redirect URI for sqlpad is '[baseurl]/auth/google/callback'
+GOOGLE_CLIENT_ID=""
+
+# Google Client Secret used for OAuth setup. Authorized redirect URI for sqlpad is '[baseurl]/auth/google/callback'
+GOOGLE_CLIENT_SECRET=""
+
+# Port for SQLPad to listen on.
+SQLPAD_HTTPS_PORT="443"
+
+# IP address to bind to. By default SQLPad will listen from all available addresses (0.0.0.0).
+SQLPAD_IP="0.0.0.0"
+
+# A string of text used to encrypt sensitive values when stored on disk.
+SQLPAD_PASSPHRASE="At least the sensitive bits won't be plain text?"
+
+# Port for SQLPad to listen on.
+SQLPAD_PORT="80"
+
+# Public URL used for OAuth setup and email links. Protocol expected. Example: https://mysqlpad.com
+PUBLIC_URL=""
+
+# By default query results are limited to 50,000 records.
+SQLPAD_QUERY_RESULT_MAX_ROWS="50000"
+
+# SAML authentication - provide HTML for the sign-in link.
+SAML_LINK_HTML=""
+
+# Auto create a user record if it does not exist when new user is detected via SAML authentication
+SQLPAD_SAML_AUTO_SIGN_UP=false
+
+# Default role to assign user created when `samlAutoSignUp` is turned on. 
+# By default this is an empty-string and not used, expecting a role to be provided via header-mapping.
+SQLPAD_SAML_DEFAULT_ROLE=""
+
+# SAML authentication context URL
+SAML_AUTH_CONTEXT=""
+
+# SAML callback URL
+SAML_CALLBACK_URL=""
+
+# SAML certificate in Base64
+SAML_CERT=""
+
+# SAML Entry point URL
+SAML_ENTRY_POINT=""
+
+# SAML Issuer
+SAML_ISSUER=""
+
+# Minutes to keep a session active. Will extended by this amount each request.
+SQLPAD_SESSION_MINUTES="60"
+
+# Supply incoming Slack webhook URL to post query when saved.
+SQLPAD_SLACK_WEBHOOK=""
+
+# From email address for SMTP. Required in order to send invitation emails.
+SQLPAD_SMTP_FROM=""
+
+# Host address for SMTP. Required in order to send invitation emails.
+SQLPAD_SMTP_HOST=""
+
+# Password for SMTP.
+SQLPAD_SMTP_PASSWORD=""
+
+# Port for SMTP. Required in order to send invitation emails.
+SQLPAD_SMTP_PORT=""
+
+# Toggle to use secure connection when using SMTP.
+SQLPAD_SMTP_SECURE="true"
+
+# Username for SMTP. Required in order to send invitation emails.
+SQLPAD_SMTP_USER=""
+
+# Acquire socket from systemd if available
+SQLPAD_SYSTEMD_SOCKET="false"
+
+# HTTP server timeout as number of seconds. Extend as necessary for long running queries
+SQLPAD_TIMEOUT_SECONDS=300
+
+# Minimum level for app logs. Should be one of 'fatal', 'error', 'warn', 'info', 'debug', 'trace' or 'silent'.
+SQLPAD_APP_LOG_LEVEL="info"
+
+# Minimum level for web logs. Should be one of 'fatal', 'error', 'warn', 'info', 'debug', 'trace' or 'silent'.
+SQLPAD_WEB_LOG_LEVEL="info"
+
+# Path to root of seed data directories
+SQLPAD_SEED_DATA_PATH=""
+
+# Enable auth proxy authentication
+SQLPAD_AUTH_PROXY_ENABLED=false
+
+# Auto create a user record if it does not exist when new user is detected via auth proxy
+SQLPAD_AUTH_PROXY_AUTO_SIGN_UP=false
+
+# Default role to assign user created when `authProxyAutoSignUp` is turned on. 
+# By default this is an empty-string and not used, expecting a role to be provided via header-mapping.
+SQLPAD_AUTH_PROXY_DEFAULT_ROLE=""
+
+# Space-delimited field:header mappings to use to derive user information from HTTP headers. 
+# A mapping to `email` is required at a minimum assuming `authProxyDefaultRole` is set. 
+# Otherwise `role`, `id`, `name` and `data.<customField>` fields may be set.
+#
+# When supplying both `id` and `email`, `id` will be used for user matching instead of `email`, 
+# updating SQLPad user `email` fields when they change (assuming `id` is not changing).
+# 
+# Example: "id:X-WEBAUTH-ID email:X-WEBAUTH-EMAIL name:X-WEBAUTH-NAME role:X-WEBAUTH-ROLE data.customField:X-WEBAUTH-CUSTOM-FIELD"
+SQLPAD_AUTH_PROXY_HEADERS=""
diff --git a/docs/configuration.md b/docs/configuration.md
index 502bb016..687fb70d 100644
--- a/docs/configuration.md
+++ b/docs/configuration.md
@@ -9,7 +9,11 @@ For example:
 node server.js --config ~/sqlpad.ini
 ```
 
-For INI and JSON config file examples, see [config-example.ini](https://github.com/rickbergfalk/sqlpad/blob/master/config-example.ini) and [config-example.json](https://github.com/rickbergfalk/sqlpad/blob/master/config-example.json) in GitHub repository.
+Config file examples available in GitHub repository:
+
+- [config-example.env](https://github.com/rickbergfalk/sqlpad/blob/master/config-example.env)
+- [config-example.ini](https://github.com/rickbergfalk/sqlpad/blob/master/config-example.ini)
+- [config-example.json](https://github.com/rickbergfalk/sqlpad/blob/master/config-example.json)
 
 ?> For examples below, **key** refers to key in INI/JSON file. **Env** for environment variable.
 
diff --git a/docs/connections.md b/docs/connections.md
index f39a516b..ef02d2de 100644
--- a/docs/connections.md
+++ b/docs/connections.md
@@ -20,7 +20,7 @@ Work is under way to add multi-statement transaction support to drivers that ben
 
 ?> As of 3.2.0 connections may be defined via application configuration.
 
-### Via Environment Variable
+### Via Environment Variable & .env File
 
 When defining connections via environment variables, connection field values must be provided using an environment variable with the convention `SQLPAD_CONNECTIONS__<connectionId>__<fieldName>`. Note double underscores between `SQLPAD_CONNECTIONS`, `<connectionId>`, and `<fieldName>`.
 
diff --git a/server/config.dev.env b/server/config.dev.env
new file mode 100644
index 00000000..6f6d7c70
--- /dev/null
+++ b/server/config.dev.env
@@ -0,0 +1,12 @@
+# config for dev setup
+SQLPAD_PORT = 3010
+SQLPAD_BASE_URL = "/sqlpad"
+SQLPAD_DB_PATH = ../db
+SQLPAD_APP_LOG_LEVEL = debug
+SQLPAD_WEB_LOG_LEVEL = debug
+SERVICE_TOKEN_SECRET = secr3t
+SQLPAD_DEFAULT_CONNECTION_ID = devdbdriverid123
+
+SQLPAD_CONNECTIONS__devdbdriverid123__driver = sqlite
+SQLPAD_CONNECTIONS__devdbdriverid123__name = dev connection from config
+SQLPAD_CONNECTIONS__devdbdriverid123__filename = "./test/fixtures/sales.sqlite"
diff --git a/server/config.dev.ini b/server/config.dev.ini
deleted file mode 100644
index 6c5fe172..00000000
--- a/server/config.dev.ini
+++ /dev/null
@@ -1,13 +0,0 @@
-; INI config file for dev setup
-port = 3010
-baseUrl = "/sqlpad"
-dbPath = ../db
-appLogLevel = debug
-webLogLevel = debug
-serviceTokenSecret = secr3t
-defaultConnectionId = devdbdriverid123
-
-[connections.devdbdriverid123]
-driver = sqlite
-name = dev connection from ini
-filename = "./test/fixtures/sales.sqlite"
diff --git a/server/lib/config/from-file.js b/server/lib/config/from-file.js
index 7f2e0dd1..3e1d30bd 100644
--- a/server/lib/config/from-file.js
+++ b/server/lib/config/from-file.js
@@ -27,7 +27,11 @@ function fromFile(configFilePath) {
   const extname = path.extname(configFilePath).toLowerCase();
 
   try {
-    if (extname === '.json') {
+    if (configFilePath.includes('.env')) {
+      // Return an empty object.
+      // .env is applied to process.env and therefore processed with environment variables
+      return {};
+    } else if (extname === '.json') {
       parsedFile = JSON.parse(fileText);
     } else if (extname === '.ini') {
       parsedFile = ini.parse(fileText);
diff --git a/server/package-lock.json b/server/package-lock.json
index 6c6a273e..09c5ebd3 100644
--- a/server/package-lock.json
+++ b/server/package-lock.json
@@ -1270,6 +1270,11 @@
       "resolved": "https://registry.npmjs.org/dont-sniff-mimetype/-/dont-sniff-mimetype-1.1.0.tgz",
       "integrity": "sha512-ZjI4zqTaxveH2/tTlzS1wFp+7ncxNZaIEWYg3lzZRHkKf5zPT/MnEG6WL0BhHMJUabkh8GeU5NL5j+rEUCb7Ug=="
     },
+    "dotenv": {
+      "version": "8.2.0",
+      "resolved": "https://registry.npmjs.org/dotenv/-/dotenv-8.2.0.tgz",
+      "integrity": "sha512-8sJ78ElpbDJBHNeBzUbUVLsqKdccaa/BXF1uPTw3GrvQTBgrQrtObr2mUrE38vzYd8cEv+m/JBfDLioYcfXoaw=="
+    },
     "dottie": {
       "version": "2.0.2",
       "resolved": "https://registry.npmjs.org/dottie/-/dottie-2.0.2.tgz",
diff --git a/server/package.json b/server/package.json
index a11e9f92..9fe19853 100644
--- a/server/package.json
+++ b/server/package.json
@@ -30,7 +30,7 @@
   },
   "scripts": {
     "prepublishOnly": "cd .. && ./scripts/build.sh",
-    "start": "node-dev server.js --config \"./config.dev.ini\" | pino-pretty",
+    "start": "node-dev server.js --config \"./config.dev.env\" | pino-pretty",
     "test": "mocha test --timeout 20000 --recursive --exit --bail",
     "testmssql": "env SQLPAD_BACKEND_DB_URI='mssql://sa:SuperP4ssw0rd!@localhost:1433/dbname' npm run test",
     "testmysql": "env SQLPAD_BACKEND_DB_URI='mysql://root:root@localhost:23306/db2' npm run test",
@@ -47,6 +47,7 @@
     "cassandra-driver": "^4.5.2",
     "cryptr": "^6.0.2",
     "detect-port": "^1.3.0",
+    "dotenv": "^8.2.0",
     "express": "^4.17.1",
     "express-pino-logger": "^5.0.0",
     "express-session": "^1.17.1",
diff --git a/server/server.js b/server/server.js
index c436b9db..05f649c6 100755
--- a/server/server.js
+++ b/server/server.js
@@ -5,6 +5,7 @@ const http = require('http');
 const https = require('https');
 const minimist = require('minimist');
 const detectPort = require('detect-port');
+const dotenv = require('dotenv');
 const makeApp = require('./app');
 const appLog = require('./lib/app-log');
 const Config = require('./lib/config');
@@ -47,6 +48,17 @@ if (argv.help || cliHas('help')) {
   process.exit(0);
 }
 
+// If an .env file was passed for config, call dotenv to apply it to process.env
+// .env files are not processed like .ini/.json files
+const configFilePath = argv.config || process.env.SQLPAD_CONFIG;
+if (configFilePath && configFilePath.includes('.env')) {
+  const result = dotenv.config({ path: configFilePath });
+  if (result.error) {
+    appLog.error(result.error, 'Error loading .env file');
+    process.exit(1);
+  }
+}
+
 const config = new Config(argv, process.env);
 
 const migrateOnly = config.get('migrate') || cliHas('migrate');


Test patch:
diff --git a/server/test/fixtures/config.env b/server/test/fixtures/config.env
new file mode 100644
index 00000000..6f9d602f
--- /dev/null
+++ b/server/test/fixtures/config.env
@@ -0,0 +1,3 @@
+# This is a comment
+SQLPAD_DB_PATH = dbPath
+SQLPAD_BASE_URL = "baseUrl"
\ No newline at end of file
diff --git a/server/test/lib/config.js b/server/test/lib/config.js
index 46c5b68e..9dd9d507 100644
--- a/server/test/lib/config.js
+++ b/server/test/lib/config.js
@@ -64,6 +64,11 @@ describe('lib/config/fromFile', function () {
     assert.equal(Object.keys(config).length, 3, '3 items');
   });
 
+  it('fromFile ignores .env', function () {
+    const config = fromFile(path.join(__dirname, '../fixtures/config.env'));
+    assert.equal(Object.keys(config).length, 0, '0 items');
+  });
+
   it('Errors for old config file key', function () {
     const config = new Config(
       { config: path.join(__dirname, '../fixtures/old-config.json') },