Tagged template literals and the hack that will never go away

Tagged template literals were added to Javascript as part of ES 2015. While a fair bit has been written about them, I’m going to argue their significance is underappreciated and I’m hoping this post will help change that. In part, it’s significant because it strikes at the root of a problem people had otherwise resigned themselves to living with: SQL injection.

Just so we are clear, before ES 2015, combining query strings with untrusted user input to create a SQL injection was done via concatenation using the plus operator.

let query = 'select * from widgets where id = ' + id + ';'

As of ES 2015, you can create far more stylish SQL injections using backticks.

let query = `select * from widgets where id = ${id};`

By itself this addition is really only remarkable for not being included in the language sooner. The backticks are weird, but it gives us some much-needed multiline string support and a very Rubyish string interpolation syntax. It’s pairing this new syntax with another language feature known as tagged templates that has a real potential to make an impact on SQL injections.

> let id = 1
// define a function to use as a "tag"
> let sql = (strings, ...vars) => console.log(strings, vars)
[Function: sql]
// pass our tag a template literal
> sql`select * from widgets where id = ${id};`
[ 'select * from widgets where id = ', ';' ] [ 1 ]

What you see above is just a function call, but it no longer works like other languages. Instead of doing the variable interpolation first and then calling the sql function with select * from widgets where id = 1;, the sql function is called with an array of strings and the variables that are supposed to be interpolated.

You can see the difference how things are evaluated by by adding brackets. The string is interpolated before being passed to the sql function, entirely loosing the distinction between the variable (which we probably don’t trust) and the string (that we probably do). The result is printing out the injected string along with the empty array that was supposed to hold the variables.

> sql(`select * from widgets where id = ${id};`)
select * from widgets where id = 1; []

This loss of context is the heart of matter when it comes to SQL injection (or injection attacks generally). The moment the strings and variables are combined you have a problem on your hands.

So why not just use parameterized queries or something similar? It’s generally held that good code expresses the programmers intent. I would argue that our SQL injection example code perfectly expresses the programmers intent; they want the id variable to be included in the query string. As a perfect expression of the programmers intent, this should be acknowledged as “good code”… as well as a horrendous security problem.

let query = sql(`select * from widgets where id = ${id};`)

When the clearest expression of a programmers intent is also a security problem what you have is a systemic issue which requires a systemic fix. This is why despite years of security education, developer shaming and “push left” pep-talks SQL injection stubbornly remains “the hack that will never go away”. It’s also why you find Mike Samuel from Google’s security team as the champion of the “Template Strings” proposal.

You can see the fruits of this labour by noticing library authors leveraging this to deliver a great developer experience while doing the right thing for security. Allan Plum, the driving force behind the Arangodb Javascript driver leveraging tagged template literals to let users query ArangoDB safely.

The aql (Arango Query Language) function lets you write what would in any other language be an intent revealing SQL injection, safely returns an object with a query and some accompanying bindvars.

aql`FOR thing IN collection FILTER thing.foo == ${foo} RETURN thing`
{ query: 'FOR thing IN collection FILTER thing.foo == @value0 RETURN thing',
  bindVars: { value0: 'bar' } }

Mike Samuel himself has a number of node libraries that leverage Tagged Template Literals, among them one to safely handle shell commands.

sh`echo -- ${a} "${b}" 'c: ${c}'`

It’s important to point out that Tagged Template Literals don’t entirely solve SQL injections, since there are no guarantees that any particular tag function will do “the right thing” security-wise, but the arguments the tag function receives set library authors up for success.

Authors using them get to offer an intuitive developer experience rather than the clunkiness of prepared statements, even though the tag function may well be using them under the hood. The best experience is from safest thing; It’s a great example of creating a “pit of success” for people to fall into.

const ps = new sql.PreparedStatement(/* [pool] */)
ps.input('param', sql.Int)
ps.prepare('select * from widgets where id = @id;', err => {
    // ... error checks
    ps.execute({id: 1}, (err, result) => {
        // ... error checks
        ps.unprepare(err => {
            // ... error checks
        })
    })
})

It’s an interesting thought that Javascripts deficiencies seem to have become it’s strength. First Ryan Dahl filled out the missing IO pieces to create Node JS and now missing features like multiline string support provide an opportunity for some of the worlds most brilliant minds to insert cutting edge security features along-side these much needed fixes.

I’m really happy to finally see language level fixes for things that are clearly language level problems, and excited to see where Mike Samuel’s mission to “make the easiest way to express an idea in code a secure way to express that idea” takes Javascript next.

Advertisements

Basic HTTPS on Kubernetes with Traefik

Back in February of 2018 Google’s Security blog announced that Chrome would be start displaying “not secure” for websites starting in July. In doing so they cemented HTTPS as part of the constantly-rising baseline expectations for modern web developers.

These constantly-rising baseline expectations are written into a new generation of tools like Traefik and Caddy. Both are written in Go and both leverage Let’s Encrypt to automate away the requesting and renewal, and by extension the unexpected expiration, of TLS certificates. Kubernetes is another modern tool aimed at meeting some of the other modern baseline expectations around monitoring, scaling and uptime.

Using Traefik and Kubernetes together is a little fiddly, and getting a working deployment on a cloud provider even more so. The aim here is to show how to use Traefik to get Let’s Encrypt based HTTPS working on the Google Kubernetes Engine.
An obvious prerequisite is to have a domain name, and to point it at a static IP you’ve created.

Let’s start with creating our project:

mike@sleepycat:~$ gcloud projects create --name k8s-https
No project id provided.

Use [k8s-https-212614] as project id (Y/n)?  y

Create in progress for [https://cloudresourcemanager.googleapis.com/v1/projects/k8s-https-212614].
Waiting for [operations/cp.6673958274622567208] to finish...done.

Next lets create a static IP.

mike@sleepycat:~$ gcloud beta compute --project=k8s-https-212614 addresses create k8s-https --region=northamerica-northeast1 --network-tier=PREMIUM
Created [https://www.googleapis.com/compute/beta/projects/k8s-https-212614/regions/northamerica-northeast1/addresses/k8s-https].
mike@sleepycat:~$ gcloud beta compute --project=k8s-https-212614 addresses list
NAME       REGION                   ADDRESS        STATUS
k8s-https  northamerica-northeast1  35.203.65.136  RESERVED

Because I am easily amused, I own the domain actually.works. In my settings for that domain I created an A record pointing at that IP address. When you have things set up correctly, you can verify the DNS part is working with dig

mike@sleepycat:~$ dig it.actually.works

; <<>> DiG 9.13.0 <<>> it.actually.works
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 62565
;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 4096
;; QUESTION SECTION:
;it.actually.works.		IN	A

;; ANSWER SECTION:
it.actually.works.	3600	IN	A	35.203.65.136

;; Query time: 188 msec
;; SERVER: 192.168.0.1#53(192.168.0.1)
;; WHEN: Tue Aug 07 23:02:11 EDT 2018
;; MSG SIZE  rcvd: 62

With that squared away, we need to create our Kubernetes cluster. Before we can do that we need to get a little administrative stuff out of the way. First we need to get our billing details and link them to our project.

mike@sleepycat:~$ gcloud beta billing accounts list
ACCOUNT_ID            NAME                OPEN  MASTER_ACCOUNT_ID
0X0X0X-0X0X0X-0X0X0X  My Billing Account  True
mike@sleepycat:~$ gcloud beta billing projects link k8s-https-212614 --billing-account 0X0X0X-0X0X0X-0X0X0X
billingAccountName: billingAccounts/0X0X0X-0X0X0X-0X0X0X
billingEnabled: true
name: projects/k8s-https-212614/billingInfo
projectId: k8s-https-212614

Then we’ll need to enable the Kubernetes engine for this project.

mike@sleepycat:~$ gcloud services enable container.googleapis.com --project k8s-https-212614
Waiting for async operation operations/tmo-acf.74966272-39c8-4b7b-b973-8f7fa4dac4fd to complete...
Operation finished successfully. The following command can describe the Operation details:
 gcloud services operations describe operations/tmo-acf.74966272-39c8-4b7b-b973-8f7fa4dac4fd

Let’s create our cluster. Because both Kubernetes and Google move pretty quickly, it’s good to check the current Kubernetes version for your region with something like gcloud container get-server-config --region "northamerica-northeast1". In my case that shows “1.10.5-gke.3” as the newest so I’ll use that for my cluster. If you are interested in beefier machines explore your options with gcloud compute machine-types list --filter="northamerica-northeast1" but for this I’ll slum it with a f1-micro.

mike@sleepycat:~$ gcloud container --project=k8s-https-212614 clusters create "k8s-https" --zone "northamerica-northeast1-a" --username "admin" --cluster-version "1.10.5-gke.3" --machine-type "f1-micro" --image-type "COS" --disk-type "pd-standard" --disk-size "100" --scopes "https://www.googleapis.com/auth/compute","https://www.googleapis.com/auth/devstorage.read_only","https://www.googleapis.com/auth/logging.write","https://www.googleapis.com/auth/monitoring","https://www.googleapis.com/auth/servicecontrol","https://www.googleapis.com/auth/service.management.readonly","https://www.googleapis.com/auth/trace.append" --num-nodes "3" --enable-cloud-logging --enable-cloud-monitoring --addons HorizontalPodAutoscaling,HttpLoadBalancing,KubernetesDashboard --enable-autoupgrade --enable-autorepair

Creating cluster k8s-https...done.
Created [https://container.googleapis.com/v1/projects/k8s-https-212614/zones/northamerica-northeast1-a/clusters/k8s-https].
To inspect the contents of your cluster, go to: https://console.cloud.google.com/kubernetes/workload_/gcloud/northamerica-northeast1-a/k8s-https?project=k8s-https-212614
kubeconfig entry generated for k8s-https.
NAME       LOCATION                   MASTER_VERSION  MASTER_IP    MACHINE_TYPE  NODE_VERSION  NUM_NODES  STATUS
k8s-https  northamerica-northeast1-a  1.10.5-gke.3    35.203.64.6  f1-micro      1.10.5-gke.3  3          RUNNING

You will notice that kubectl (which you obviously have installed already) is now configured to access this cluster.
As part of the Traefik setup we are about to do we will need to change some RBAC rules. To do that we will need to create a cluster admin role and load that into our cluster.

mike@sleepycat:~$ cat cluster-admin-rolebinding.yaml 
---
apiVersion: rbac.authorization.k8s.io/v1beta1
kind: ClusterRoleBinding
metadata:
  name: owner-cluster-admin-binding
roleRef:
  apiGroup: rbac.authorization.k8s.io
  kind: ClusterRole
  name: cluster-admin
subjects:
- apiGroup: rbac.authorization.k8s.io
  kind: User
  name: <your_username@your_email_you_use_with_google_cloud.whatever>
---

mike@sleepycat:~$ kubectl apply -f cluster-admin-rolebinding.yaml

With that done we can apply the rest of the config I’ve posted in a snippet here with kubectl apply -f https.yaml.
It’s a fair bit of yaml, but a few things are worth pointing out.

First, we are running a single pod with my helloworld image. It’s just the output of create-react-app that I use for testing stuff.
If you look at the traefik-ingress-service, you will notice we are telling Google we want the service mapped to the static IP we created earlier using loadBalancerIP.

---
apiVersion: v1
kind: Service
metadata:
  name: traefik-ingress-service
  namespace: kube-system
spec:
  loadBalancerIP: 35.203.65.136
  ports:
  - name: http
    port: 80
    protocol: TCP
  - name: https
    port: 443
    protocol: TCP
  - name: admin
    port: 8080
    protocol: TCP
  selector:
    k8s-app: traefik-ingress-lb
  type: LoadBalancer
---

When looking at the traefik-ingress-controller itself, it’s worth noting the choice of kind: Deployment instead of kind: DaemonSet. This choice was made for simplicity’s sake (only a single pod will read/write to my certs-claim volume so no Multi-Attach errors), and means that I will have a single pod acting as my ingress controller. Read more about the tradeoffs here.

Here is the traefik-ingress-controller in it’s entirety. It’s a long chunk of code, but I find this helps see everything in context.

Special note about the args being passed to the container; make sure they are strings. You can end up with some pretty baffling errors if you don’t. Other than that, it’s the full set of options to get you TLS certs and automatic redirects to HTTPS.

---
apiVersion: extensions/v1beta1
kind: Deployment
metadata:
  labels:
    k8s-app: traefik-ingress-lb
  name: traefik-ingress-controller
  namespace: kube-system
spec:
  template:
    metadata:
      labels:
        k8s-app: traefik-ingress-lb
    spec:
      containers:
      - args:
        - "--api"
        - "--kubernetes"
        - "--logLevel=DEBUG"
        - "--debug"
        - "--defaultentrypoints=http,https"
        - "--entrypoints=Name:http Address::80 Redirect.EntryPoint:https"
        - "--entrypoints=Name:https Address::443 TLS"
        - "--acme"
        - "--acme.onhostrule"
        - "--acme.entrypoint=https"
        - "--acme.domains=it.actually.works"
        - "--acme.email=mike@korora.ca"
        - "--acme.storage=/certs/acme.json"
        - "--acme.httpchallenge"
        - "--acme.httpchallenge.entrypoint=http"
        image: traefik:1.7
        name: traefik-ingress-lb
        ports:
        - containerPort: 80
          hostPort: 80
          name: http
        - containerPort: 443
          hostPort: 443
          name: https
        - containerPort: 8080
          hostPort: 8080
          name: admin
        securityContext:
          capabilities:
            add:
            - NET_BIND_SERVICE
            drop:
            - ALL
        volumeMounts:
        - mountPath: /certs
          name: certs-claim
      serviceAccountName: traefik-ingress-controller
      terminationGracePeriodSeconds: 60
      volumes:
      - name: certs-claim
        persistentVolumeClaim:
          claimName: certs-claim

The contents of the snippet should be all you need to get up and running. You should be able to visit your domain and see the reassuring green of the TLS lock in the URL bar.

A TLS certificate from Let's Encrypt

If things aren’t working you can get a sense of what’s up with the following commands:

kubectl get all --all-namespaces
kubectl logs --namespace=kube-system traefik-ingress-controller-...

Where to go from here

As you can see, there is a fair bit going on here. We have DNS, Kubernetes, Traefik and the underlying Google Cloud Platform all interacting and it’s not easy to get a minimal “hello world” style demo going when that is the case. Hopefully this shows enough to give people a jumping off point so they can start refining this into a more robust configuration. The next steps for me will be exploring DaemonSets and storing the acme.json in a way that multiple copies of Traefik can access, maybe a key/value like consul. We’ll see what the next layer of learning brings.

Minimum viable Kubernetes

I remember sitting in the audience at the first Dockercon in 2014 when Google announced Kubernetes and thinking “what kind of a name is that?”. In the intervening years, Kubernetes, or k8s for short, has battled it out with Cattle and Docker swarm and emerged as the last orchestrator standing.

I’ve been watching this happen but have been procrastinating on learning it because from a distance it looks hella complicated. Recently I decided to rip off the bandaid and set myself the challenge of getting a single container running in k8s.

While every major cloud provider is offering k8s, so far Google looks to be the easiest to get started with. So what does it take to get a container running on Google Cloud?

First some assumptions: you’ve installed the gcloud command (I used this) with the alpha commands, and you have a GCP account, and you’ve logged in with gcloud auth login.

If you have that sorted, let’s create a project.

mike@sleepycat:~$ gcloud projects create --name projectfoo
No project id provided.

Use [projectfoo-208401] as project id (Y/n)?  

Create in progress for [https://cloudresourcemanager.googleapis.com/v1/projects/projectfoo-208401].
Waiting for [operations/cp.4790935341316997740] to finish...done.

With a project created we need to enable billing for it, so Google can charge you for the compute resources Kubernetes uses.

                                                                                                                                      
mike@sleepycat:~$ gcloud alpha billing projects link projectfoo-208401 --billing-account 0X0X0X-0X0X0X-0X0X0X
billingAccountName: billingAccounts/0X0X0X-0X0X0X-0X0X0X
billingEnabled: true
name: projects/projectfoo-208401/billingInfo
projectId: projectfoo-208401

Next we need to enable the Kubernetes Engine API for our new project.

mike@sleepycat:~$ gcloud services --project=projectfoo-208401 enable container.googleapis.com
Waiting for async operation operations/tmo-acf.445bb50c-cf7a-4477-831c-371fea91ddf0 to complete...
Operation finished successfully. The following command can describe the Operation details:
 gcloud services operations describe operations/tmo-acf.445bb50c-cf7a-4477-831c-371fea91ddf0

With that done, we are free to fire up a Kubernetes cluster. There is a lot going on here, more than you need, but it’s good to be able to see some of the options available. Probably the only ones to care about initially are the zone and the machine-type.

mike@sleepycat:~$ gcloud beta container --project=projectfoo-208401 clusters create "projectfoo" --zone "northamerica-northeast1-a" --username "admin" --cluster-version "1.8.10-gke.0" --machine-type "f1-micro" --image-type "COS" --disk-type "pd-standard" --disk-size "100" --scopes "https://www.googleapis.com/auth/compute","https://www.googleapis.com/auth/devstorage.read_only","https://www.googleapis.com/auth/logging.write","https://www.googleapis.com/auth/monitoring","https://www.googleapis.com/auth/servicecontrol","https://www.googleapis.com/auth/service.management.readonly","https://www.googleapis.com/auth/trace.append" --num-nodes "3" --enable-cloud-logging --enable-cloud-monitoring --addons HorizontalPodAutoscaling,HttpLoadBalancing,KubernetesDashboard --enable-autoupgrade --enable-autorepair
This will enable the autorepair feature for nodes. Please see
https://cloud.google.com/kubernetes-engine/docs/node-auto-repair for more
information on node autorepairs.

This will enable the autoupgrade feature for nodes. Please see
https://cloud.google.com/kubernetes-engine/docs/node-management for more
information on node autoupgrades.

Creating cluster projectfoo...done.                                                                                                                                                                         
Created [https://container.googleapis.com/v1beta1/projects/projectfoo-208401/zones/northamerica-northeast1-a/clusters/projectfoo].
To inspect the contents of your cluster, go to: https://console.cloud.google.com/kubernetes/workload_/gcloud/northamerica-northeast1-a/projectfoo?project=projectfoo-208401
kubeconfig entry generated for projectfoo.
NAME        LOCATION                   MASTER_VERSION  MASTER_IP     MACHINE_TYPE  NODE_VERSION  NUM_NODES  STATUS
projectfoo  northamerica-northeast1-a  1.8.10-gke.0    35.203.8.163  f1-micro      1.8.10-gke.0  3          RUNNING

With that done we can take a quick peek at what that last command created: a Kubernetes cluster on three f1-micro VMs.

mike@sleepycat:~$ gcloud compute instances --project=projectfoo-208401 list
NAME                                       ZONE                       MACHINE_TYPE  PREEMPTIBLE  INTERNAL_IP  EXTERNAL_IP    STATUS
gke-projectfoo-default-pool-190d2ac3-59hg  northamerica-northeast1-a  f1-micro                   10.162.0.4   35.203.87.122  RUNNING
gke-projectfoo-default-pool-190d2ac3-lbnk  northamerica-northeast1-a  f1-micro                   10.162.0.2   35.203.78.141  RUNNING
gke-projectfoo-default-pool-190d2ac3-pmsw  northamerica-northeast1-a  f1-micro                   10.162.0.3   35.203.91.206  RUNNING

Let’s put those f1-micro‘s to work. We are going to use the kubectl run command to run a simple helloworld container that just has the basic output of create-react-app in it.

mike@sleepycat:~$ kubectl run projectfoo --image mikewilliamson/helloworld --port 3000
deployment "projectfoo" created

The result of that is the helloworld container, running inside a pod, inside a replica set inside a deployment, which of course is running inside a VM on Google Cloud. All that’s needed now is to map the port the container is listening on (3000) to port 80 so we can talk to it from the outside world.

mike@sleepycat:~$ kubectl expose deployment projectfoo --type LoadBalancer --port 80 --target-port 3000
service "projectfoo" exposed

This creates a LoadBalancer service, and eventually we get allocated our own IP.

mike@sleepycat:~$ kubectl get services
NAME         TYPE           CLUSTER-IP     EXTERNAL-IP   PORT(S)        AGE
kubernetes   ClusterIP      10.59.240.1    <none>        443/TCP        3m
projectfoo   LoadBalancer   10.59.245.55   <pending>     80:32184/TCP   34s
mike@sleepycat:~$ kubectl get services
NAME         TYPE           CLUSTER-IP     EXTERNAL-IP      PORT(S)        AGE
kubernetes   ClusterIP      10.59.240.1    <none>           443/TCP        4m
projectfoo   LoadBalancer   10.59.245.55   35.203.123.204   80:32184/TCP   1m

Then we can use our newly allocated IP and talk to our container. The moment of truth!

mike@sleepycat:~$ curl 35.203.123.204
<!DOCTYPE html><html lang="en"><head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1,shrink-to-fit=no"><meta name="theme-color" content="#000000"><link rel="manifest" href="/manifest.json"><link rel="shortcut icon" href="/favicon.ico"><title>React App</title><link href="/static/css/main.c17080f1.css" rel="stylesheet"></head><body><noscript>You need to enable JavaScript to run this app.</noscript><div id="root"></div><script type="text/javascript" src="/static/js/main.61911c33.js"></script></body></html>

After you’ve taken a moment to marvel at the layers of abstractions involved here, it’s worth remembering that you probably don’t want this stuff hanging around if you aren’t really using it, otherwise you’re going to regret connecting your billing information.

mike@sleepycat:~$ gcloud container --project projectfoo-208401 clusters delete projectfoo
The following clusters will be deleted.
 - [projectfoo] in [northamerica-northeast1-a]

Do you want to continue (Y/n)?  y

Deleting cluster projectfoo...done.                                                                                                                                                                         
Deleted [https://container.googleapis.com/v1/projects/projectfoo-208401/zones/northamerica-northeast1-a/clusters/projectfoo].
mike@sleepycat:~$ gcloud projects delete projectfoo-208401
Your project will be deleted.

Do you want to continue (Y/n)?  y

Deleted [https://cloudresourcemanager.googleapis.com/v1/projects/projectfoo-208401].

You can undo this operation for a limited period by running:
  $ gcloud projects undelete projectfoo-208401

There is a lot going on here, and since this is new territory, much of it doesn’t mean lots to me yet. What’s exciting to me is finally being able to get a toe-hold on an otherwise pretty intimidating subject.

Having finally started working with it, I have to say both the kubectl and gcloud CLI tools are thoughtfully designed and pretty intuitive, and Google’s done a nice job making a lot of stuff happen in just a few approachable commands. I’m excited to dig in further.

Customizing your R command line experience

I’ve come to appreciate how powerful R is for working with data, but I find it has some pretty awkward and clunky defaults that make every interaction with the command line kind of aggravating.

mike@sleepycat:~$ R

R version 3.4.3 (2017-11-30) -- "Kite-Eating Tree"
Copyright (C) 2017 The R Foundation for Statistical Computing
Platform: x86_64-pc-linux-gnu (64-bit)

R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.

  Natural language support but running in an English locale

R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.

Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.

> install.packages("tidyverse")
Warning in install.packages("tidyverse") :
  'lib = "/usr/lib/R/library"' is not writable
Would you like to use a personal library instead?  (y/n)
>
Save workspace image? [y/n/c]: n

Here you can see a few things that I’m not a fan of. First up, uppercase commands are just weird. Second, holy moly that’s a lot of blah blah to give me a repl.

Next, this warning about the non-writable directory; /usr will always be read only and owned by root. Why even try to write there?

Accepting the offer to use a “personal library” translates to creating a directory like ~/R/x86_64-pc-linux-gnu-library/3.4, which is another annoyance. Why isn’t this a hidden directory? Why must I be forced to look at this R directory every time I see my home folder?

Finally, if I’m doing something more than a quick experiment, I’ll write a script in a text file so I can track changes to it with git rather than saving my work in some opaque .RData file. Given that, having R always asking to save my workspace is deeply irritating.

We can pick off a few of these problems with a simple alias, by adding the following to your ~/.bashrc file:

alias r="R --no-save --quiet"

This immediately gives us a far more civilized experience getting in and out of R: I can launch R with a lowercase r command and then exit without a fuss with Ctrl+d (signaling the end of input). The --quiet kills the introductory text while --no-save gets rid of the “Save workspace image?” nag.

mike@sleepycat:~$ r
> 
mike@sleepycat:~$

This is a good start but goofy stuff like where to save your libraries will need to be solved another way: your Rprofile. While starting up R looks for certain configuration files one of them being ~/.Rprofile.

I’ve created a hidden folder called .rlibs folder in my home directory which my Rprofile then sets as my chosen place to save libraries among a few other things likes setting a default mirror and loading and saving my command history.

Here is what’s working for me:

# Load utils so we can use it here.
library(utils)

# Save R libraries into my /home/$USER/.rlibs instead of somewhere that
# requires root privileges:
.libPaths('~/.rlibs')

# Stop asking me about mirrors, and always use the https
# cran mirror at muug.ca:
local({r <- getOption("repos")
      r["CRAN"] <- "https://muug.ca/mirror/cran/"
      options(repos=r)})

# Some reasonable defaults:
options(stringsAsFactors=FALSE)
options(max.print=100)
options(editor="vim")
options(menu.graphics=FALSE)
Sys.setenv(R_HISTFILE="~/.Rhistory")
Sys.setenv(R_HISTSIZE="100000")

# Run at startup
.First <- function(){
  # Load my history if this is an interactive session
  if (interactive()) utils::loadhistory(file = "~/.Rhistory")
  # Load the packages in the tidyverse without warnings.
  # suppressMessages(library(tidyverse))
}

# Run at the end of your session
.Last <- function(){
  if (interactive()) utils:::savehistory(file = "~/.Rhistory")
}

This little foray into R configuration has made R really nice to use from the command line. Hopefully this will be a decent starting point for others as well.

GraphQL i18n

One of the things I love about GraphQL is that it is “self documenting”, but of course, here in Canada the obvious question that follows is “in both languages?”. Since GraphQL is one of the core technologies for me I really wanted to figure out a decent answer when people ask about i18n, but it’s never been clear to me how to handle it.

Since I’m familiar with GraphQL-js, I’ll be using that here but Apollo Server is on my list to explore and may have some different answers. I’ll also be using my favourite i18n library Lingui here, but any other i18n library could be easily substituted.

Just to be clear, the issue at hand is not i18n for the data (that you’d handle with something like ArangoDB’s TRANSLATE) it’s i18n for the description attributes that can be attached to all your schema objects.
Once description strings are added, anyone (and “anyone” could and probably will include developer tools, IDEs or developers themselves) can introspect on the schema by querying the __schema or __type fields to find the descriptions with a query like this:

{
  __schema {
    queryType {
      fields {
        name
        description
        args {
          name
          description
        }
      }
    }
  }
}

To help think this through lets create a basic schema that just returns the current time and returns the DateTime type from above and use it to explore i18n.

const DateTime = new GraphQLObjectType({
  name: 'DateTime',
  description: 'An example date/time object.',
  fields: () => ({
    date: {
      description: 'The current date in DD/MM/YYYY format.',
      type: GraphQLString,
    },
    time: {
      description: 'The current time in HH:MM:SS AM/PM format.',
      type: GraphQLString,
    },
  }),
})

const query = new GraphQLObjectType({
  name: 'Query',
  fields: {
    now: {
      description: 'Returns current time and date values.',
      type: DateTime, // what the resolve function will produce
      resolve: (root, args, context) => {
        let now = new Date()
        let time = now.toLocaleDateString(context.language, {
          timeZone: 'America/Toronto',
        })
        let date = now.toLocaleTimeString(context.language, {
          timeZone: 'America/Toronto',
        })
        return { date, time }
      },
    },
  },
})

With our query type and it’s return type created, we just need to wrap that in a schema and pass it to the express-graphql middleware. It will mount the schema on the url we specify and pass the request object to all our resolvers as the third argument (aka “the context”).

Adding the requestLanguage middleware from the express-request-language library before express-graphql means that incoming requests will be checked for the Accept-Language header, and the best matching language of the languages you specify will be attached to the request as request.language. Remember that express-graphql is passing the request to our resolvers as context so that means that we access the request.language as context.language.

const schema = new GraphQLSchema({ query })

let server = express()

server
  .use(
    requestLanguage({
      languages: ['en', 'fr'], // First locale becomes the default
    }),
  )
  .use('/graphql', graphqlHTTP({ schema }))

server.listen(3000)

With this basic setup, we can already see the outline of the issue: our schema and types with their accompanying descriptions are defined once when the script is run, but the language we want is whatever is appropriate for each request.

It probably won’t surprise you that our middleware can been passed a function that it will execute per request that will produce the configuation needed. With that in place we have a pretty clear path forward.

server
  .use(
    requestLanguage({
      languages: ['en', 'fr'], // First locale becomes the default
    }),
  )
  .use(
    '/graphql',
    graphqlHTTP((request, response, graphQLParams) => {
      return {
        schema: new GraphQLSchema({
          query: // define a schema and types using the request language and pass it in
        }),
      }
    }),
  )

My first step was to define a function that recieves an i18n object and returns a schema.

const createSchema = i18n => {
  // Define a type that describes the data
  const DateTime = new GraphQLObjectType({
    name: 'DateTime',
    description: i18n.t`An example date/time object.`,
    fields: () => ({
      date: {
        description: i18n.t`The current date in DD/MM/YYYY format.`,
        type: GraphQLString,
      },
      time: {
        description: i18n.t`The current time in HH:MM:SS AM/PM format.`,
        type: GraphQLString,
      },
    }),
  })

  const query = new GraphQLObjectType({
    name: 'Query',
    fields: {
      now: {
        description: i18n.t`Returns current time and date values.`,
        type: DateTime, // what the resolve function will produce
        resolve: (root, args, context) => {
          let now = new Date()
          let time = now.toLocaleDateString(context.language, {
            timeZone: 'America/Toronto',
          })
          let date = now.toLocaleTimeString(context.language, {
            timeZone: 'America/Toronto',
          })
          return { date, time }
        },
      },
    },
  })

  return query
}

With that defined I can use it to produce a schema, but because lingui works by scanning for and extracting things like i18n.t`...` from my code, I have to remember not to rename that otherwise lingui extract won’t find my translations. Additionally I don’t want variable shadowing, so I import i18n under a different name and rename it to what Lingui expects only when I go to create the schema:

const express = require('express')
const graphqlHTTP = require('express-graphql')
const { GraphQLSchema, GraphQLObjectType, GraphQLString } = require('graphql')
const { i18n: internationalization, unpackCatalog } = require('lingui-i18n') // import i18n as something else
const requestLanguage = require('express-request-language')

internationalization.load({  // Load our language files
  fr: unpackCatalog(require('./locale/fr/messages.js')),
  en: unpackCatalog(require('./locale/en/messages.js')),
})

// Our function that creates the schema
const createSchema = i18n => {
...
}

let server = express()

server
  .use(
    requestLanguage({
      languages: internationalization.availableLanguages.sort(), // First locale becomes the default
    }),
  )
  .use(
    '/graphql',
    graphqlHTTP(async (request, response, graphQLParams) => {
      internationalization.activate(request.language)
      return {
        schema: new GraphQLSchema({
          query: createSchema(internationalization),
        }),
      }
    }),
  )

server.listen(3000)

Getting Lingui properly integrated took a little fiddling. It brings along it’s own copy of babel which doesn’t seem to see your other babel plugins but does read your .babelrc.
Configuring my projects babel using only command line options, solved the clashes with lingui’s babel, and then making sure Lingui would only look for translations in the src folder was all I needed to get Lingui in working along-side the usual “transpile to dist” workflow (the finished code is available here).
After running Lingui’s lingui extract and doing my translations I’m now able to hit my endpoint and see the translated descriptions:

# Ask for English!
mike@sleepycat:~$ curl -H "Accept-Language: en" -H "Content-Type: application/graphql" -d "{ __schema {queryType { fields { name description } } } }" "localhost:3000/graphql"
{"data":{"__schema":{"queryType":{"fields":[{"name":"now","description":"Returns current time and date values."}]}}}}
# Ask for French!
mike@sleepycat:~$ curl -H "Accept-Language: fr" -H "Content-Type: application/graphql" -d "{ __schema {queryType { fields { name description } } } }" "localhost:3000/graphql"
{"data":{"__schema":{"queryType":{"fields":[{"name":"now","description":"Renvoie les valeurs actuelles de date et d'heure."}]}}}}

Performance

Obviously defining the schema and types before processing each request is going to cost something but it would be good to know what. This is a question some load testing with wrk2 can give us insight into (with the caveat that both the server and the load testing program were running on the same laptop, so take this with a large grain of salt).

First, a version without the schema per request:

mike@sleepycat:~$ wrk2 -t4 -c100 -d30s -R2000 --latency "http://localhost:3000/graphql?query=%7B%0A%20%20now%20%7B%0A%20%20%20%20date%0A%20%20%20%20time%0A%20%20%7D%0A%7D"
Running 30s test @ http://localhost:3000/graphql?query=%7B%0A%20%20now%20%7B%0A%20%20%20%20date%0A%20%20%20%20time%0A%20%20%7D%0A%7D
  4 threads and 100 connections
  Thread calibration: mean lat.: 3309.908ms, rate sampling interval: 11272ms
  Thread calibration: mean lat.: 3310.066ms, rate sampling interval: 11280ms
  Thread calibration: mean lat.: 3308.845ms, rate sampling interval: 11280ms
  Thread calibration: mean lat.: 3310.191ms, rate sampling interval: 11280ms
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency    11.83s     3.26s   17.47s    57.94%
    Req/Sec   214.00      0.00   214.00    100.00%

And now the one with our i18n schema per request:

mike@sleepycat:~$ wrk2 -t4 -c100 -d30s -R2000 --latency "http://localhost:3000/graphql?query=%7B%0A%20%20now%20%7B%0A%20%20%20%20date%0A%20%20%20%20time%0A%20%20%7D%0A%7D"
Running 30s test @ http://localhost:3000/graphql?query=%7B%0A%20%20now%20%7B%0A%20%20%20%20date%0A%20%20%20%20time%0A%20%20%7D%0A%7D
  4 threads and 100 connections
  Thread calibration: mean lat.: 3196.913ms, rate sampling interval: 11296ms
  Thread calibration: mean lat.: 3196.537ms, rate sampling interval: 11288ms
  Thread calibration: mean lat.: 3115.362ms, rate sampling interval: 10493ms
  Thread calibration: mean lat.: 3196.062ms, rate sampling interval: 11288ms
  Thread Stats   Avg      Stdev     Max   +/- Stdev
    Latency    11.91s     3.35s   17.81s    57.65%
    Req/Sec   207.00      0.00   207.00    100.00%

So generating our schema/types per request drops us from 214 requests per second down to 207. Clearly it’s not free, but in this little example it’s pretty reasonable and in this world of microservices, there are a fair number of services that aren’t much bigger than this example. That said, a ~3% drop for something so simple is probably something you would want to watch carefully. A larger schema with more imports might well be far more costly. Clearly this little performance test is far from rigorous, but it’s nice to have some vague sense of the impact.

i18n and GraphQL

In the end this blog post is as much a question as it is an answer. I’m pretty certain their are futher refinements to be made here and that there are ways to avoid some or maybe all of the performance penalty highlighted above.
I’m also pretty curious about what it would look like to get a similar thing happening with Apollo Server.
Hopefully this will help other people who are trying to do i18n with GraphQL and maybe surface better options.

Javascript i18n with Lingui

Living in a country with two official lanaguage means that you don’t get far into a project before the question of internationalization (aka i18n to anyone who has to type it more than a few times) comes up.

There are a few options for dealing with this in Javascript, but it’s taken a while to find one I like. First, I expect to use the same library on the server and on the client, and I expect to be able to use it with libraries like React.

React-Intl works OK on the client side, but using the underlying Intl on the server looks under-documented and deeply clunky. I18next is reasonable on the server and has integrations with most client side frameworks. While it’s a decent choice, there is something about the way it works which rubs me the wrong way.

i18next.init({
  lng: 'en',
  fallbackLng: 'en',
  resources: {
    en: {
      translation: {
        person: {
          firstName: "First name",
          lastName: "Last name",
	}
      },
    },
    fr: {
      translation: {
        person: {
          firstName: "Prénom",
          lastName: "Nom de famille",
	}
      },
    },
  }
})

The above code shows how to use it. It’s a pretty standard setup (very familiar if you’ve ever done i18n in Rails), some singleton object (you can make others if you need to) with an internal collection of messages which are stored as a JSON object.

One of the things that I dislike about this approach is that the translations stored in that JSON object tend to accumulate and hang around long after the code that needs that message is gone.

The other thing I find doesn’t sit well with me is the way you access those messages: i18n.t('mutation.fields.purchase.args.expiryYear').

What you are looking at is a function call that assumes the existence of an object like {translation:{fields:{purchase:{args:{expiryYear: "Expiry year"}}}}}. This an example of structural coupling, my code depends on the structure of that object. This sort of thing is normally considered an anti-pattern, a violation of the “law of Demeter”, but it’s pretty common among i18n libraries. I have to decide on the structure to start with, and after that, changing that structure (say if you decide you didn’t make the right decision about how to structure it originally) is going to break a lot of things.

Poking around I stumbled on a library that takes a different approach: Lingui.

Lingui is interesting because it builds a nice translation workflow by leveraging the now ubiquitous infrastructure of Babel.

Aside from the core code in lingui-i18n (and other packages dealing with React) the heart of lingui’s approach are two babel plugins: babel-plugin-lingui-extract-messages and babel-plugin-lingui-transform-js.

We can install what we need for the server side like this.

yarn add lingui-cli lingui-i18n

The babel-plugin-lingui-extract-messages plugin does what is advertised on the tin. First we need a little test code to extract.

const { i18n } = require('lingui-i18n')

i18n.t`I do like a bit of gorgonzola.`
i18n.t`Not even Wensleydale?`

Then we need to create some locales using the helper supplied by lingui-cli:

[mike@longshot lingui]$ lingui add-locale en fr
Added locale en.
Added locale fr.

(use "lingui extract" to extract messages)
[mike@longshot lingui]$ tree
.
├── index.js
├── locale
│   ├── en
│   │   └── messages.json
│   └── fr
│       └── messages.json
├── package.json
└── yarn.lock

Next we use babel-plugin-lingui-extract-messages via the Lingui CLI commandlingui extract to scan our code for those internationalized strings and extract them into translation files.

[mike@longshot lingui]$ lingui extract
Extracting messages from source files…
Collecting all messages…
Writing message catalogues…
Messages extracted!

Catalog statistics:
┌──────────┬─────────────┬─────────┐
│ Language │ Total count │ Missing │
├──────────┼─────────────┼─────────┤
│ en       │      2      │    2    │
│ fr       │      2      │    2    │
└──────────┴─────────────┴─────────┘

(use "lingui add-locale <language>" to add more locales)
(use "lingui extract" to update catalogs with new messages)
(use "lingui compile" to compile catalogs for production)

Lingui prints out a nice summary of the state of my translations.
A look at the translation files shows how Lingui can solve that coupling problem; it generates an object with the content of the strings used as the keys. This way my translations are looked up by content, rather than via their location in some stucture. Since Lingui defaults to showing the message id (which is actually the English content string from the source) we’ll just edit the French messages file.

[mike@longshot lingui]$ cat locale/fr/messages.json 
{
  "I do like a bit of gorgonzola.": {
    "translation": "Je aime un peu de gorgonzola.",
    "origin": [
      [
        "index.js",
        3
      ]
    ]
  },
  "Not even Wensleydale?": {
    "translation": "Pas même Wensleydale?",
    "origin": [
      [
        "index.js",
        4
      ]
    ]
  }
}

With that done we need to compile the json into JS files for use with lingui compile. The missing piece now is the how those i18n.t tagged template literals are going to produce our translated strings at runtime, and the answer is babel-plugin-lingui-transform-js.

Since a picture is worth a thousand words, I think the best way to explain it is this:

[mike@longshot lingui]$ cat index.js | babel --plugins lingui-transform-js
const { i18n } = require('lingui-i18n');

i18n._('I do like a bit of gorgonzola.');
i18n._('Not even Wensleydale?');

As you can see, all the calls to i18n.t`` are replaced with calls to i18n._(). This is underscore function is the low level api that Lingui uses to actually give you the translated strings.

Now that we know that, let’s take a look at what using the library looks like.

[mike@longshot lingui]$ node
> var { i18n, unpackCatalog } = require('lingui-i18n')
undefined
> i18n.load({fr: unpackCatalog(require('./locale/fr/messages.js')), en: unpackCatalog(require('./locale/en/messages.js'))})
undefined
> i18n.availableLanguages
[ 'fr', 'en' ]>
> i18n.activate('en')
undefined
> i18n._('I do like a bit of gorgonzola.')
'I do like a bit of gorgonzola.'
> i18n.activate('fr')
undefined
> i18n._('I do like a bit of gorgonzola.')
'Je aime un peu de gorgonzola.'

Lingui has some more tricks up it’s sleeve like pluralization, but one of the things I’m happiest about is that this approach also solves that “unused messages” problem that I mentioned.

If we delete our “Not even Wensleydale?” message and run lingui extract again we can see the benefits of this static analysis style approach: Lingui knows when there is nothing referencing a message, and marks it as obsolete.

[mike@longshot lingui]$ cat locale/fr/messages.json 
{
  "I do like a bit of gorgonzola.": {
    "translation": "Je aime un peu de gorgonzola.",
    "origin": [
      [
        "index.js",
        3
      ]
    ]
  },
  "Not even Wensleydale?": {
    "translation": "Pas même Wensleydale?",
    "origin": [
      [
        "index.js",
        4
      ]
    ],
    "obsolete": true
  }
}

Better still, Lingui will clean out the obsolete messages for you with lingui extract --clean.

[mike@longshot lingui]$ lingui extract --clean
Extracting messages from source files…
Collecting all messages…
Writing message catalogues…
Messages extracted!

Catalog statistics:
┌──────────┬─────────────┬─────────┐
│ Language │ Total count │ Missing │
├──────────┼─────────────┼─────────┤
│ en       │      1      │    1    │
│ fr       │      1      │    0    │
└──────────┴─────────────┴─────────┘

(use "lingui add-locale <language>" to add more locales)
(use "lingui extract" to update catalogs with new messages)
(use "lingui compile" to compile catalogs for production)
[mike@longshot lingui]$ cat locale/fr/messages.json 
{
  "I do like a bit of gorgonzola.": {
    "translation": "Je aime un peu de gorgonzola.",
    "origin": [
      [
        "index.js",
        3
      ]
    ]
  }
}

For me this is pretty much the holy grail for i18n. Here I’ve focused on using Lingui without any other libraries, but it’s just as awesome with React. With locale files that can be plausibly handed over to a translator, and tooling that both find and remove translations Lingui has become my goto i18n library.

Setting up Oracle-XE on Arch linux

Currently I’m working on a project that needs to pull data from an Oracle database. My normal development setup is to install the database locally and develop the application TDD style with a test database, so it seemed reasonable to do the same with Oracle as well. Although, the fact that this became fodder for a blog post suggests it wasn’t as easy as I expected.

First up was the basic decision about what to install. The Oracle database itself is a multi-gigabyte monster, seemingly designed to sell support contracts so I was glad to find discover an Express Edition exists. Last released in 2014, and aimed at whatever “easy development” means in the world of Oracle, apparently “Applications developed with XE may be immediately used with other editions of the Oracle Database”. This sounds like the right thing to me.

So “easy development” obviously begins with logging into your Oracle account and downloading the Oracle XE zip file.

Next we want to package this up so that it can be cleanly installed and removed from our system. The Oracle-XE package on the AUR uses the zip file we just downloaded to build a package we can install, so lets get that happening

[mike@longshot ~]$ git clone https://aur.archlinux.org/oracle-xe.git
Cloning into 'oracle-xe'...
remote: Counting objects: 22, done.
remote: Compressing objects: 100% (13/13), done.
remote: Total 22 (delta 8), reused 22 (delta 8)
Unpacking objects: 100% (22/22), done.
[mike@longshot ~]$ cd oracle-xe                           
[mike@longshot oracle-xe]$ cp ~/Downloads/oracle-xe-11.2.0-1.0.x86_64.rpm.zip .
[mike@longshot oracle-xe]$ ls
listener.ora  oracle_env.csh  oracle_env.sh  oracle.install  oracle-xe  oracle-xe-11.2.0-1.0.x86_64.rpm.zip  oracle-xe.conf  oracle-xe.service  PKGBUILD
[mike@longshot oracle-xe]$ makepkg
==> Making package: oracle-xe 11.2.0_1.0-4 (Tue Oct 17 14:51:00 EDT 2017)
==> Checking runtime dependencies...
==> Checking buildtime dependencies...
==> Retrieving sources...
  -> Found oracle-xe-11.2.0-1.0.x86_64.rpm.zip
  -> Found oracle_env.csh
  -> Found oracle_env.sh
  -> Found oracle-xe
  -> Found oracle-xe.conf
  -> Found listener.ora
  -> Found oracle-xe.service
==> Validating source files with md5sums...
    oracle-xe-11.2.0-1.0.x86_64.rpm.zip ... Passed
    oracle_env.csh ... Passed
    oracle_env.sh ... Passed
    oracle-xe ... Passed
    oracle-xe.conf ... Passed
    listener.ora ... Passed
    oracle-xe.service ... Passed
==> Extracting sources...
  -> Extracting oracle-xe-11.2.0-1.0.x86_64.rpm.zip with bsdtar
==> Starting build()...
==> Entering fakeroot environment...
==> Starting package()...
==> Tidying install...
  -> Removing libtool files...
  -> Purging unwanted files...
  -> Removing static library files...
  -> Compressing man and info pages...
==> Checking for packaging issue...
==> Creating package "oracle-xe"...
  -> Generating .PKGINFO file...
  -> Generating .BUILDINFO file...
  -> Adding install file...
  -> Generating .MTREE file...
  -> Compressing package...
==> Leaving fakeroot environment.
==> Finished making: oracle-xe 11.2.0_1.0-4 (Tue Oct 17 14:54:35 EDT 2017)
[mike@longshot oracle-xe]$ ls
listener.ora  oracle_env.csh  oracle_env.sh  oracle.install  oracle-xe  oracle-xe-11.2.0_1.0-4-x86_64.pkg.tar.xz  oracle-xe-11.2.0-1.0.x86_64.rpm.zip  oracle-xe.conf  oracle-xe.service  pkg  PKGBUILD  src
[mike@longshot oracle-xe]$ sudo pacman -U oracle-xe-11.2.0_1.0-4-x86_64.pkg.tar.xz 
[sudo] password for mike: 
loading packages...
resolving dependencies...
looking for conflicting packages...

Packages (1) oracle-xe-11.2.0_1.0-4

Total Installed Size:  564.61 MiB

:: Proceed with installation? [Y/n] y
(1/1) checking keys in keyring                                                                                                 [#############################################################################] 100%
(1/1) checking package integrity                                                                                               [#############################################################################] 100%
(1/1) loading package files                                                                                                    [#############################################################################] 100%
(1/1) checking for file conflicts                                                                                              [#############################################################################] 100%
(1/1) checking available disk space                                                                                            [#############################################################################] 100%
:: Processing package changes...
(1/1) installing oracle-xe                                                                                                     [#############################################################################] 100%

creating group "dba" ...done

creating user "oracle" ...done

change directory rights ...done

set sticky bit to oracle executable ...done

creating /etc/sysconfig ...done

creating /var/log/oracle ...done


add your user to the "dba" group in order to use the oracle tools

:: Running post-transaction hooks...
(1/2) Arming ConditionNeedsUpdate...
(2/2) Updating the desktop file MIME type cache...
[mike@longshot oracle-xe]$ sudo usermod -aG dba $USER

Above we built and installed the Oracle-XE package, and added the dba group to the current users existing groups.

To get a sense of what we just installed it’s good to look at what that package put into the /etc directory.

[mike@longshot node_oracle]$ pacman -Ql oracle-xe | grep "etc"
oracle-xe /etc/
oracle-xe /etc/ld.so.conf.d/
oracle-xe /etc/ld.so.conf.d/oracle-xe.conf
oracle-xe /etc/profile.d/
oracle-xe /etc/profile.d/oracle_env.csh
oracle-xe /etc/profile.d/oracle_env.sh
oracle-xe /etc/rc.d/
oracle-xe /etc/rc.d/oracle-xe
oracle-xe /etc/systemd/
oracle-xe /etc/systemd/system/
oracle-xe /etc/systemd/system/oracle-xe.service
[mike@longshot node_oracle]$ cat /etc/ld.so.conf.d/oracle-xe.conf
/usr/lib/oracle/product/11.2.0/xe/lib
[mike@longshot node_oracle]$ cat /etc/profile.d/oracle_env.sh 
export ORACLE_HOME=/usr/lib/oracle/product/11.2.0/xe
export ORACLE_SID=XE
export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
export PATH=$PATH:$ORACLE_HOME/bin

Here we can see that this package installed an entry in our library search path (/etc/ld.so.conf.d/oracle-xe.conf), added some env vars for us (/etc/profile.d/oracle_env.sh), added a run script (/etc/rc.d/oracle-xe) and a systemd service (/etc/systemd/system/oracle-xe.service).

In theory we should be able to install our node driver and have it work.

Installing node-oracledb

Oracle has thoughtfully released a Node.js driver, which can be installed with npm install oracledb. This driver installs and compiles a bunch of stuff with node-gyp and expects some libraries and headers to be available for that process. Let’s see!

[mike@longshot node_oracle]$ npm i oracledb

> oracledb@1.13.1 install /home/mike/projects/play/node_oracle/node_modules/oracledb
> node-gyp rebuild

node-oracledb ERR! Error: Cannot find $OCI_LIB_DIR/libclntsh.so
node-oracledb ERR! Error: See https://github.com/oracle/node-oracledb/blob/master/INSTALL.md

gyp: Call to 'INSTURL="https://github.com/oracle/node-oracledb/blob/master/INSTALL.md"; ERR="node-oracledb ERR! Error:"; if [ -z $OCI_LIB_DIR ]; then OCI_LIB_DIR=`ls -d /usr/lib/oracle/*/client*/lib/libclntsh.* 2> /dev/null | tail -1 | sed -e 's#/libclntsh[^/]*##'`; if [ -z $OCI_LIB_DIR ]; then if [ -z "$ORACLE_HOME" ]; then if [ -f /opt/oracle/instantclient/libclntsh.so ]; then echo "/opt/oracle/instantclient/"; else echo "$ERR Cannot find Oracle library libclntsh.so" >&2; echo "$ERR See $INSTURL" >&2; echo "" >&2; fi; else if [ -f "$ORACLE_HOME/lib/libclntsh.so" ]; then echo $ORACLE_HOME/lib; else echo "$ERR Cannot find \$ORACLE_HOME/lib/libclntsh.so" >&2; echo "$ERR See $INSTURL" >&2; echo "" >&2; fi; fi; else if [ -f "$OCI_LIB_DIR/libclntsh.so" ]; then echo $OCI_LIB_DIR; else echo "$ERR Cannot find \$OCI_LIB_DIR/libclntsh.so" >&2; echo "$ERR See $INSTURL" >&2; echo "" >&2; fi; fi; else if [ -f "$OCI_LIB_DIR/libclntsh.so" ]; then echo $OCI_LIB_DIR; else echo "$ERR Cannot find \$OCI_LIB_DIR/libclntsh.so" >&2; echo "$ERR See $INSTURL" >&2; echo "" >&2; fi; fi;' returned exit status 0 while in binding.gyp. while trying to load binding.gyp
gyp ERR! configure error 
gyp ERR! stack Error: `gyp` failed with exit code: 1
gyp ERR! stack     at ChildProcess.onCpExit (/home/mike/.nodenv/versions/8.7.0/lib/node_modules/npm/node_modules/node-gyp/lib/configure.js:336:16)
gyp ERR! stack     at emitTwo (events.js:125:13)
gyp ERR! stack     at ChildProcess.emit (events.js:213:7)
gyp ERR! stack     at Process.ChildProcess._handle.onexit (internal/child_process.js:200:12)
gyp ERR! System Linux 4.13.8-1-hardened
gyp ERR! command "/home/mike/.nodenv/versions/8.7.0/bin/node" "/home/mike/.nodenv/versions/8.7.0/lib/node_modules/npm/node_modules/node-gyp/bin/node-gyp.js" "rebuild"
gyp ERR! cwd /home/mike/projects/play/node_oracle/node_modules/oracledb
gyp ERR! node -v v8.7.0
gyp ERR! node-gyp -v v3.6.2
gyp ERR! not ok 
npm WARN node_oracle@1.0.0 No description
npm WARN node_oracle@1.0.0 No repository field.

npm ERR! code ELIFECYCLE
npm ERR! errno 1
npm ERR! oracledb@1.13.1 install: `node-gyp rebuild`
npm ERR! Exit status 1
npm ERR! 
npm ERR! Failed at the oracledb@1.13.1 install script.
npm ERR! This is probably not a problem with npm. There is likely additional logging output above.

npm ERR! A complete log of this run can be found in:
npm ERR!     /home/mike/.npm/_logs/2017-10-19T21_01_24_226Z-debug.log

Not being a C/C++ programmer, these moments are pretty perplexing. It looks a lot like ORACLE_HOME=/usr/lib/oracle/product/11.2.0/xe OCI_LIB_DIR=$ORACLE_HOME/lib OCI_INC_DIR=$ORACLE_HOME/xdk/include npm i oracledb should work, but it doesn’t.

ldconfig -N -v | grep libclntsh.so prints out libclntsh.so.11.1 -> libclntsh.so.11.1 so the library seems to be findable, just not by the driver.

Plan B

It turns out that the headers and libraries we need are also available in Oracle’s instantclient. This would mean more downloading/packaging silliness except someone has gone to the effort to package these instantclient libraries and providing the as a pacman repo. Since the world is a beautiful place and everyone is friends on the internet I am going to pull my packages from them by adding these lines to my pacman.conf:

[mike@longshot node_oracle]$ tail -n 3 /etc/pacman.conf 
[oracle]
SigLevel = Optional TrustAll
Server = http://linux.shikadi.net/arch/$repo/$arch/

Then we update and install.

[mike@longshot node_oracle] sudo pacman -Sy
[mike@longshot node_oracle]$ sudo pacman -S oracle-instantclient-sdk oracle-instantclient-basic

Looking at the contents pacman -Ql oracle-instantclient-sdk shows bunch of files being put into /usr/include, while pacman -Ql oracle-instantclient-basic shows our much sought after libclntsh.so going into /usr/lib. It looks like we finally have some plausible values for OCI_LIB_DIR and OCI_INC_DIR.

[mike@longshot node_oracle]$ OCI_LIB_DIR=/usr/lib OCI_INC_DIR=/usr/include npm i oracledb

> oracledb@1.13.1 install /home/mike/projects/play/node_oracle/node_modules/oracledb
> node-gyp rebuild

make: Entering directory '/home/mike/projects/play/node_oracle/node_modules/oracledb/build'
  CXX(target) Release/obj.target/oracledb/src/njs/src/njsOracle.o
  CXX(target) Release/obj.target/oracledb/src/njs/src/njsPool.o
  CXX(target) Release/obj.target/oracledb/src/njs/src/njsConnection.o
  CXX(target) Release/obj.target/oracledb/src/njs/src/njsResultSet.o
  CXX(target) Release/obj.target/oracledb/src/njs/src/njsMessages.o
  CXX(target) Release/obj.target/oracledb/src/njs/src/njsIntLob.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiEnv.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiEnvImpl.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiException.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiExceptionImpl.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiConnImpl.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiDateTimeArrayImpl.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiPoolImpl.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiStmtImpl.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiUtils.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiLob.o
  CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiCommon.o
  SOLINK_MODULE(target) Release/obj.target/oracledb.node
  COPY Release/oracledb.node
make: Leaving directory '/home/mike/projects/play/node_oracle/node_modules/oracledb/build'
npm notice created a lockfile as package-lock.json. You should commit this file.
npm WARN node_oracle@1.0.0 No description
npm WARN node_oracle@1.0.0 No repository field.

+ oracledb@1.13.1
added 2 packages in 11.894s

Talking to Oracle-XE from Node

After installing XE, instandclient-basic and sdk, the full set of environmental variable that made this thing work are:

export ORACLE_HOME=/usr/lib/oracle/product/11.2.0/xe
export ORACLE_SID=XE
export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
export PATH=$PATH:$ORACLE_HOME/bin
export OCI_INC_DIR=/usr/include
export OCI_LIB_DIR=/usr/lib

Next up I want to configure XE (which seems to need those vars set). Below I’ll use sudo -E to ensure that all of those variable still exist when I do sudo:

[mike@longshot node_oracle]$ sudo -E /etc/rc.d/oracle-xe configure
[sudo] password for mike: 

Oracle Database 11g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 11g Express 
Edition.  The following questions will determine whether the database should 
be starting upon system boot, the ports it will use, and the passwords that 
will be used for database accounts.  Press <Enter> to accept the defaults. 
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:

Specify a port that will be used for the database listener [1521]:

Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of 
different passwords for each database account.  This can be done after 
initial configuration:
Confirm the password:

Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:y

Starting Oracle Net Listener...Done
Configuring database...Done
Starting Oracle Database 11g Express Edition instance...Done
Installation completed successfully.

In theory XE is configured and running (in the future you’ll probably want to start it with systemctl start oracle-xe), and the node-oracle README suggests that we run one of the examples to test it. What they don’t mention is that the example is based on sample data in an “hr” account that needs to be enabled first.

[mike@longshot oracle-xe]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 20 14:20:30 2017

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> connect system/yourpassword as sysdba
Connected.
SQL> ALTER USER hr ACCOUNT UNLOCK;          
User altered.
SQL> ALTER USER hr IDENTIFIED BY password;         
User altered.

The example script reads it’s config from a file so I created that using the terrible password I assigned to the hr account above:

[mike@longshot node_oracle]$ cat dbconfig.js 
module.exports = {
    user: "hr",
    password: "password",
    connectString: "localhost/XE",
};

So now we should be able to run the example:

[mike@longshot node_oracle]$ node select1.js 
[ { name: 'DEPARTMENT_ID' }, { name: 'DEPARTMENT_NAME' } ]
[ [ 180, 'Construction' ] ]

What’s next

The next logical step here is to start exploring the capabilities of the Node driver. There is also the Simple-oracledb package which is suddenly sounding very interesting to me.
Hopefully this will save someone else some time.