Consume etherscan.io API, store in DB, serve from web app

Question

I didn't make it to the next stage in this interview process, because another applicant did better than me, which wasn't surprising given that this was my first foray into Node.js.

I'd like some tips on how I could have made this a better submission, and also more generally I'd like to know how I could do things more idiomatically.

Here are the instructions I was given:

Using node, an http server of your choice (express recommended) and a DB of your choice (mongo or postgres recommended), make a web app. It will have at least three functions. These functions may be written as a standard JSON API, REST API, or GraphQL endpoints.

1. Accept an Ethereum address as input, and then:
   1. query https://etherscan.io/apis and collects all transactions associated with that address.
      1. store the transactions in the DB.
      2. store the address balances in the DB.
2. Return transactions of stored ETH address, and accept some form of search params (which params are up to you).
3. Return stored address balances by ETH address, and any other information about the address you see fit.

Be prepared to explain design choices that you made. Frameworks, libraries, directory structures, coding contentions, et al.

And below are the files I submitted. I've pre-pended each code segment with its file name, and all files are in the same top-level folder.

index.js:

const express = require('express')
const app = express()

const etherscan = require('etherscan-api').init('(my api key redacted)')

const { Client } = require('pg')
const postgres = new Client()
postgres.connect().catch((error) => { console.log('connecting to postgres: ' + error) })

const functions = require('./functions.js')

app.put('/ethtrancache/:address(0x[0-9a-fA-F]{40})/', handleAddAddress)
app.get('/ethtrancache/:address(0x[0-9a-fA-F]{40})/', handleGetBalance)
app.delete('/ethtrancache/:address(0x[0-9a-fA-F]{40})/', handleDeleteAddress)
app.get('/ethtrancache/:address(0x[0-9a-fA-F]{40})/transactions', handleGetTransactions)

async function handleDeleteAddress (request, response) {
  try {
    await functions.deleteAddress(postgres, request.params['address'])
  } catch (err) {
    response.send(
      { result: 'failure', msg: 'error deleting existing address: ' + err })
    return
  }
  response.send({result: 'success'})
}

async function handleAddAddress (request, response) {
  try {
    await functions.addAddress(postgres, etherscan, request.params['address'])
  } catch (err) {
    response.send({ result: 'failure', msg: 'error adding address: ' + err })
    return
  }
  response.send({ result: 'success' })
}

async function handleGetBalance (request, response) {
  var balance
  try {
    balance = await functions.getBalance(postgres, request.params['address'])
  } catch (err) {
    response.send({ result: 'failure', msg: 'error getting balance: ' + err })
    return
  }
  response.send({ result: 'success', balance: balance })
}

async function handleGetTransactions (request, response) {
  var transactions
  try {
    transactions = await functions.getTransactions(postgres, request.params['address'], request.query.other_address)
  } catch (err) {
    response.send({ result: 'failure', msg: 'error getting transactions: ' + err })
    return
  }
  response.send({ result: 'success', transactions: transactions })
}

app.listen(3000, () => console.log('listening on port 3000'))

functions.js:

async function addAddress (postgres, etherscan, address) {
  /*
  "Accept an Ethereum address as input, and then:
  1. query https://etherscan.io/apis and collects all transactions
  associated with that address.
      1. store the transactions in the DB.
      2. store the address balance in the DB."
  */

  // clear out existing balance and transactions
  try {
    await deleteAddress(postgres, address)
  } catch (err) { throw new Error('error deleting existing address: ' + err) }

  /* scrape and store eth balance */

  var balance

  try {
    balance = await etherscan.account.balance(address)
  } catch (err) { throw new Error('err getting eth balance: ' + err) }

  try {
    await postgres.query(
      'INSERT INTO eth_balances(address, balance) VALUES(LOWER($1), $2)',
      [address, balance['result']])
  } catch (err) { throw new Error('error storing eth balance: ' + err) }

  /* scrape and store transactions */

  var txlist

  try {
    txlist = await etherscan.account.txlist(address)
  } catch (err) { throw new Error('error getting transactions: ' + err) }

  try {
    for (var i = 0; i < txlist.result.length; i++) {
      await postgres.query(
        'INSERT INTO transactions(to_address, txn_id, from_address, value)' +
            ' VALUES(LOWER($1), LOWER($2), LOWER($3), $4)',
        [ txlist.result[i].to,
          txlist.result[i].hash,
          txlist.result[i].from,
          txlist.result[i].value ])
    }
  } catch (err) { throw new Error('error storing transactions: ' + err) }
}

async function deleteAddress (postgres, address) {
  try {
    await postgres.query(
      'DELETE FROM eth_balances WHERE address = LOWER($1)',
      [address])
    await postgres.query(
      'DELETE FROM transactions WHERE from_address = LOWER($1) OR to_address = LOWER($1)',
      [address])
  } catch (err) { throw new Error('PostgreSQL error: ' + err) }
}

async function getBalance (postgres, address) {
  /*
  Return stored address balance by ETH address
  */
  try {
    var result = await postgres.query(
      'SELECT balance FROM eth_balances WHERE address = LOWER($1)',
      [address])

    if (result.rows.length === 0) { throw new Error('no such address') }

    return result.rows[0].balance
  } catch (err) { throw new Error('error getting eth_balance: ' + err) }
}

async function getTransactions (postgres, address, otherAddress) {
  /*
  Return transactions of stored ETH address, and accept some form of search
  params (which params are up to you).
  */

  var query =
    'SELECT * FROM transactions WHERE ' +
      'from_address = LOWER($1) OR to_address = LOWER($1)'

  var values = [address]

  if (otherAddress !== undefined) {
    query += ' AND ( from_address = LOWER($2) OR to_address = LOWER($2) )'
    values.push(otherAddress)
  }

  query += ';'

  try {
    var result = await postgres.query(query, values)
  } catch (err) { throw new Error('error getting transactions: ' + err) }

  return result.rows
}

module.exports = {
  addAddress: addAddress,
  deleteAddress: deleteAddress,
  getBalance: getBalance,
  getTransactions: getTransactions
}

package.json:

{
  "name": "assignment",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "lint": "node_modules/.bin/eslint *.js"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "etherscan-api": "^8.0.4",
    "express": "^4.16.3",
    "pg": "^7.4.3"
  },
  "devDependencies": {
    "eslint": "^4.19.1",
    "eslint-config-standard": "^11.0.0",
    "eslint-plugin-import": "^2.11.0",
    "eslint-plugin-node": "^6.0.1",
    "eslint-plugin-promise": "^3.7.0",
    "eslint-plugin-standard": "^3.1.0"
  }
}

initDatabase.sh:

#!/bin/sh

# tested with PostgreSQL version 9.3.22

. ./pgenv
dropdb -h $PGHOST -U $PGUSER $PGDATABASE || exit 1
createdb -h $PGHOST -U $PGUSER $PGDATABASE || exit 1
psql -h $PGHOST -U $PGUSER $PGDATABASE -f createTables.psql

pgenv: (This I filled in with the credentials for a PostgreSQL instance I spun up on AWS.)

export PGHOST=
export PGUSER=
export PGPASSWORD=
export PGDATABASE=assignment

createTables.psql:

create table eth_balances(address text PRIMARY KEY, balance bigint);
create table transactions(to_address text, txn_id text UNIQUE, from_address text, value bigint);

Answer 1

I must say it a very interesting assignment to do: what I see missing from your assignment is not much bigger things in development aspects but it all have a huge impact on Code maintainability and infrastructure cost sometimes:

• your code is not modularized, from the first sight i can see that you should have maintained the Postgres connection-related configuration in another file with its associated getter and setter functions same for creation of etherscan instance.
• There should be separate module for app entry point where you will create/ listen server through express and another for app routes.
• There should be any logging module used like winston, pino, morgon.
• Looking at your addAddress function, i see you are triggering multiple insert queries (one for every transaction), which can be accomplised by only a single query using bulk insert operations.
• avoid using var type variables if the values if the variable is not going to be changed.

Answer 2

Really interesting interview assignment, I may give it a go myself!

The first thing that jumps out at me is the organization of the code. Instead of a file named functions.js, I would be more specific and break it up even further if need be...(addressController.js, transactionController.js, etc.) The model-view-controller (MVC) pattern will really serve you well as your app hypothetically begins to scale.

As far as the way you handle the actual data, I suggest you think about your data and try to narrow in on the fundamental data types or entities that you are working with and how they should/could be modeled. For example, inside a models directory, you could have things like User.js, Transaction.js model files, in which you would define your model schemas (the properties of your data objects). Using an ORM makes this much easier, for PostgreSQL I reccommend sequelize. Moreover, a database visualizer desktop app like Postico will be very helpful when working with your stored data.

Finally, it's cool that you are using async with await, but I personally think using Promsies (or even the Async library) would be cleaner in this case, since it looks like you are working with many different async operations. Promises can be chained, andasync.parrallel or async.waterfall are elegant ways to perform multiple related asynchronous operations.

Also, for your module.exports you can use destructuring assignment and can be written like so:

module.exports = {
  addAddress,
  deleteAddress,
  getBalance,
  getTransactions,
}

Finally, I suggest you reconsider your use of var throughout your code, I would only use it if necessary and try to be consistent with ES6 let and const. These are the things that jump out at me immediately, overall though I think you did a great job!