Presto backend API coding challenge

• Task
  • Specifics
  • Requirements
• Design
  • Architecture
  • Trade Offs
• Postgres
  • Configuration
  • Starting Fresh with Postgres
  • Starting Fresh with Sequelize
  • Seeding database
• Server
  • Running the server
• Client
• Database Schema
• Future Work
• License
• Acknowledgements

Utilizing your language of choice, design a RESTful web service API call that will return a list of menu items for a specific restaurant.

For this API call, the front end will target path /restaurant/:restaurantId/item.

public Observable<Response> execute(Request request) {
URL url = new URL("https://api.presto.com/restaurant/1/item");
HttpURLConnection con = url.openConnection();
}

The expected response will be parsed directly from JSON to a list of Item objects.

execute(request).subscribe(response -> {
Type listType = new TypeToken<List<Item>>(){}.getType();
List<Item> items = new Gson().fromJson(response, listType); });

This challenge is centered around the concept of communicating throughout the stack from the backend, so each part of that communication stack is required:

1. Handle the request from the front end
2. Connect to and query from a Postgres database instance
3. Send the response to the front end

The data model structure is up to you, it’s assumed that the frontend will mimic your object layout for seamless parsing. The only requirement is that the Item object must contain a list of modifiers (ex. Side item, Topping, Sauce). Hint - modifiers can also have modifiers (think dressing choices for side salad).

Scalability is paramount. The code you build should not end up being a bottleneck down the road (will this have to be rebuilt when it’s being hit 100x as frequently?).

• Framework: I am using JavaScript and Node, simply because I am very familiar with them.
• API Design: Swagger Editor to generate the API contract and swagger-codegen to generate the node server using express.
• Scalability: Node is commonly used for designing production ready scalable and concurrent backend systems. If you are writing a backend using javascript (I am), then node is your huckleberry.
• Database: Postgres, because it was specified in the challenge.
• Database Connectivity: Using Sequelize, which is the most popular (by download statistics) SQL ORM software package for node:
  • Sequelize The Sequelize library is an ORM (Object-Relational-Mapper) which provides easy access to MySQL, MariaDB, SQLite or PostgreSQL databases by mapping database entries to objects and vice versa.
  • List of most popular ORM packages according to npmjs
  • Comparison of mongoose vs sequalize over last 6 months
• Security: There is none, due to the nature of the challenge.
• Queries: Will use a Common Table Expression (CTE) query through sequelize to keep this scalable. Node will ensure this remains scalable through asyncronous promises (meaning it will not block other requests while the DB handles this query for as long as it takes). Queries from the postgres command line using the sample data are recorded here.

• I chose not to use python and django because I am unfamiliar with them and preferred to complete this challenge with tools I am proficient with.
• Using Sequelize over Mongoose because challenge calls for it. Trade-off here is lilliputian battle of SQL vs NoSQL.
• No security, never do this, ever. period.
• I stuck with the naming convention used in the coding challenge, but I am not certain that matches the database schema well.
• I used three tables to split up the data. It makes the query more complicated (needing Joins), but I think it is more extensible.

Configure the connection to your postgres server by modifying modules/restaurant/server/config/database.json. The defaults are:

  "development": {
    "username": "presto",
    "password": "presto",
    "database": "presto",
    "host": "192.168.56.102",
    "port": "5432",
    "dialect": "postgres"
  },

• Create a presto user and presto database, or sh modules/restaurant/server/scripts/database/postgres.sh

postgres@localhost:~$ createuser --echo --pwprompt --createdb --no-createrole --no-superuser presto
postgres@localhost:~$ createdb presto

This will create new models, this should be unnecessary as files are tracked in git.

cd modules/restaurant/server/scripts/database
sh ./sequelize-initialize-environment.sh
sh ./sequelize-create-models.sh

This will drop the current database and re-seed it from a known state

cd modules/restaurant/server/scripts/database
sh ./sequelize-seed-database.sh

The server stub was generated by the swagger-codegen project.

To run the server for the first time, run:

cd modules/restaurant/server
npm start

To run just the server, run:

node index.js

To view the Swagger UI interface:

open http://localhost:8080/docs

This project leverages the mega-awesome swagger-tools middleware which does most all the work.

Read the Client instructions here. Here is a shortcut:

cd modules/restaurant/client
npm install

and to test:

npm test

I am using three tables to track Restaurants, Items, and Menus:

• Restaurant - List of available restaurants, restaurantID in API.
• Items - Items available at a restaurant, associated with a Restaurant.
• Menu - Table holding relationship of items to each other. An item where Modifies is NULL is a top-level item. This table allows for a hierarchical relationship. Care must be taken (and code introduced) to prevent cyclic dependencies. Ultimately this relationship table defines the response to the API challenge.

Restaurant Table

Item Table - Associated with Restaurant Table

Menu Table

These are things I would do if I had more time:

• Use UUIDs instead of incrementing ints for primary keys
• Default string lengths (255) used for all fields, would make them reasonable
• Cannot stress implement proper security enough - https, SSL connection to DB, secure DB, API keys, API authentication, etc.
• Complete CRUD API. Use that to seed database for test environment (database provisioning/migration still done with npm install).
• Performance tests:
  • Multiple restaurants
  • X simultaneous clients
  • Y items (with modifiers)
    • random item generator
    • connection reliability (intermittent connections)
    • low bandwidth connectivity (e.g. 1 Mbps down, 0.04 Mbps up)
  • Resource graphcs under load testing
    • Memory, CPU usage
    • Perf statistics
    • SQL call duration
    • gprof
    • O(N) estimation
• Move database documentation from here to a schema definition file to avoid decoupling
• Protection against cyclic graphs, e.g.:

Menu Table: Check for cyclic association in table

(Note - Example only, not Schema accurate)

• Refactor CTE code to use native sequelize functionality, there seem to be some other modules that do this better.
• Refactor tree building code in API to be a generic graph structure builder, this would be a more reusable component. I implemented a graph search algorithm, should optimize if more time.

Licensed under the Unlicense.

• Table of contents generated with markdown-toc