Building a simple timesheet app with SlashDB, Go and Vue

Building a simple timesheet app with SlashDB, Go and Vue

As a proof of concept we are building a time tracking app using APIs from SlashDB for the backend.

Contents

The app itself will allow users to register, login/logout, add new projects, add tasks to those projects and it will automatically sum-up the time a given project has taken. Here’s a little preview of the frontend:

Technology stack

Our application follows a standard three-tier architecture composed out of HTML5 front-end, RESTful web services for the middle tier and a relational database for the persistence layer.

Frontend  in Vue

After my personal experience with Angular 1.x (and a bit of React), I choose Vue – a small and simple front-end js framework. It fits somewhere between those two frameworks (but a lot closer to React) and only focuses on the V(iew) part of MVC. Similar to Angular, we have things like template directives (i.e. ng-for=”item in items” -> v-for=”item in items”) – so no cumbersome JSX transpilation. And similar to React, we get one-way data flow i.e. always parent -> child node communication (harder to achieve the level of Angular app craziness). In general it’s easier to understand what our app is doing at a given time, also we get the awesome dev tools 🙂

Business logic and API proxy in Go

We will experiment with Go to wrap around SlashDB’s automatically generated REST APIs. Go (Golang for search engines) is a programming language developed by Google, which is gaining popularity as a tool for building microservices. It has most of the “batteries” included, allowing us to build a simple authentication layer between our data and the frontend. Also, Go is a compiled language, so we can build statically linked binaries for easy distribution and even embed our assets (CSS, JS etc) into that binary.

Data access API from SlashDB

SlashDB automatically generates a REST API from relational databases for ready and writing, making it easy to access and modify our data. This will save a lot of work coding our own data access APIs.

Persistence layer with MySQL

MySQL is a popular open-source RDBMS and it will be more than sufficient for our purposes here. It is worth noting that since SlashDB also supports PostgreSQL, SQLite, Oracle, SQL Server and IBM DB2 we could have picked any of these without the need to rethink our application architecture or even worry about client libraries.

What the developer needs to implement is, the frontend GUI and a way to do authentication/authorization for the user. The RESTful API part is provided to us for free by SlashDB.

Implementation

We’ll walk the stack from the bottom up to explain the implementation of each layer. The complete code is available on GitHub at https://github.com/SlashDB/timesheet-demo.

Database model

Installation and configuration of a MySQL server is outside of scope of this article, so we will just skip that part.

Once the database server is running,  we’ll need to create a new database named timesheet, a user with read/write privileges, and 3 tables.

For the purposes of this application we will only need the following tables:

  • Project – holds the list of projects
  • User – list of users of the application
  • Timesheet – time records of Users’ time spend working on Projects

Below is code is for MySQL, but it gives a general idea about the table layout and is easy enough to adjust for other SQL dialects. This code is available in timesheet.sql file in the repository, which also contains some sample data.


DROP TABLE IF EXISTS timesheet;


DROP TABLE IF EXISTS project;
CREATE TABLE `project` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` varchar(150) DEFAULT NULL,
  `timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `project_id_uindex` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS user;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(35) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  `passwd` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id_uindex` (`id`),
  UNIQUE KEY `user_username_uindex` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `timesheet` (
  `user_id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `duration` double NOT NULL,
  `accomplishments` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`user_id`,`project_id`,`date`),
  FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
  FOREIGN KEY (`project_id`) REFERENCES `project` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Automatic data API with SlashDB

Now that the database is in place let’s build or rather generate the REST API. SlashDB is available free of charge for development purposes and we recommend using the docker image for quick way to get started.

$ docker pull slashdb/slashdb
$ mkdir timesheet-docker
$ cd timesheet-docker
$ wget -c https://downloads.slashdb.com/latest/default-slashdb-configs.zip
$ unzip default-slashdb-configs.zip
$ docker run -d -p 8000:80 -v $PWD/slashdb:/etc/slashdb -v $PWD/slashdb:/var/log/slashdb slashdb/slashdb

Once that’s done, point your browser to http://localhost:8000/ and follow the initial setup steps.

Next, we will need to connect SlashDB to our MySQL database.

Configure Database Endpoint

We will be following  instructions from our SlashDB user guide to add the new database configuration.

Click on the Configure -> Databases, then click the New button, and a pop-up dialog will appear. Fill out the form as follows.

For simplicity, we named the Database ID endpoint timesheet. We also enabled the Auto Discover feature and provided login credentials for our MySQL user. The complete configuration should look something like this.


Click Save, then Close, and your newly configured database will appear on the Database Configuration screen. Click the On/Off button to Connect. Make sure that it has connected without errors (green Connected status), and we are all set.

It is also good idea to create a new user for remote access to our resources, so once again we will be following the docs, to add a new user named – you guessed it – timesheet.
Since our proxy authorization/authentication app will be connecting to SlashDB pro grammatically it makes sense to set an API key for that user too. In this example we will simply put timesheet-api-key here, but normally this should be a string of random characters. Under Database Mappings associate this user with the MySQL login.

Click Save, then Close.

Log out of the admin account and log back in using your newly created timesheet user to examine your database API endpoints.

Click on Data Discovery in the main menu.

Then click on the HTML button next to your timesheet database, and our three tables will appear.

The project endpoint, http://localhost:8000/db/timesheet/project.html should now display the contents of the Project table in HTML.

The same URL ending with .json will return the contents in that format:  http://localhost:8000/db/timesheet/project.json.

That’s it – now you have your data provided as an RESTful API, courtesy of SlashDB.

GoLang proxy authorization/authentication app

With the API endpoints in place we can proceed to adding business logic for our app.

Important: This part requires you understanding the basics of programming in Go, its setup (i.e. $GOPATH/$GOROOT) and it’s basic tooling (i.e. go get/build/install). For a complete reference on that please visit Go’s wiki page.

The basic idea for this layer is to proxy all requests from the frontend to the SlashDB RESTful API and to do resource authorization on the fly. Also, we need to provide a way for users to sign up and login.

In my setup, this app will have these four endpoints:

/                - the SlashDB proxy
/app/          - the frontend app itself
/app/reg/    - user registration provider
/app/login/ - user login/token provider

In the spirit of keeping it simple, as a method of of providing a kind of stateless session, we’ll use JSON Web Token (JWT). The /app/login/ endpoint will check user credentials and if everything’s OK, provide a JWT token.

First, things first, so let’s install everything we’ll need:

$ go get golang.org/x/crypto/pbkdf2
$ go get github.com/dgrijalva/jwt-go/...
$ go get github.com/jteeuwen/go-bindata/...
$ go get github.com/elazarl/go-bindata-assetfs/...

Pro Tip: you can just run the build.sh script from our repository to install all requirements and compile the app.

Setting up authorization/authentication proxy

Using Go’s builtin httputil.ReverseProxy, we create a lightweight reverse proxy:


// for the full code, view auth.go source file
func setupProxy() {
    // get address for the SlashDB instance and parse the URL
    url, err := url.Parse(pa.SdbInstanceAddr)
    if err != nil {
        log.Fatalln(err)
    }

    // create a reverse proxy
    proxy := httputil.NewSingleHostReverseProxy(url)
    // for simplicity's sake disable https verification
    proxy.Transport = &http.Transport{
        TLSClientConfig: &tls.Config{InsecureSkipVerify: true},
    }

    proxyHandler := func(w http.ResponseWriter, r *http.Request) {
        // set API key for easy proxy-to-SDB communication
        q := r.URL.Query()
        q.Set(pa.ParsedSdbAPIKey, pa.ParsedSdbAPIValue)
        r.URL.RawQuery = q.Encode()
        ...
    }
    // bind the proxy handler to "/"
    http.HandleFunc("/", authorizationMiddleware(proxyHandler))
}

So now, when requesting something from the default localhost:8000 we will get redirected to the root of the selected SlashDB instance.

For example:

http://localhost:8000/db/timesheet/project/project_id/1.json → http://demo.slashdb.com/db/timesheet/project/project_id/1.json

and the response will be transparently returned to the us.

The authorizationMiddleware function applies all the authorization logic to the proxied requests i.e. it extracts the JWT token, checks if it’s valid and depending on the user permissions, allows or prohibits access to the resource.


func authorizationMiddleware(fn func(http.ResponseWriter, *http.Request), secret []byte) func(w http.ResponseWriter, r *http.Request) {
    return func(w http.ResponseWriter, r *http.Request) {
        token, err := request.ParseFromRequest(r, request.OAuth2Extractor, func(token *jwt.Token) (interface{}, error) {
            // we simply check the token claims, but this is a good place
            // to parse the r.URL.Path or other request parameters
            // and determine if a given user can access requested data
            // i.e. check if user of ID = 8 can read /db/timesheet/project/project_id/2/ etc.
            mc := token.Claims.(jwt.MapClaims)
            _, ok := mc["id"]
            if !ok {
                return nil, fmt.Errorf("token lacks 'id' claim")
            }
            _, ok = mc["username"]
            if !ok {
                return nil, fmt.Errorf("token lacks 'username' claim")
            }

            ...
            return secret, nil
        })

        if err != nil || !token.Valid {
            http.Error(w, http.StatusText(http.StatusUnauthorized), http.StatusUnauthorized)
            return
        }
        // else call the original handler function
        fn(w, r)
    }
}

In my example it’s only a simple function, but of course, depending on the use case, we can implement any kind of authentication logic there.

/app/

The frontend app is being served from a static template and all other features are generated and managed by the Vue app.


afs := &assetfs.AssetFS{Asset: Asset, AssetDir: AssetDir, AssetInfo: AssetInfo, Prefix: ""}
http.HandleFunc("/app/", func(w http.ResponseWriter, r *http.Request) {
    indexTmpl := template.New("index.html")
    data, err := afs.Asset("index.html")
    ...
    _, err = indexTmpl.Parse(string(data))
    ...
    indexTmpl.Execute(w, pa)
})

/app/static/

This little Go app also servers our static content like so:


afs := &assetfs.AssetFS{Asset: Asset, AssetDir: AssetDir, AssetInfo: AssetInfo, Prefix: ""}
http.HandleFunc("/app/", func(w http.ResponseWriter, r *http.Request) {
    indexTmpl := template.New("index.html")
    data, err := afs.Asset("index.html")
    ...
    _, err = indexTmpl.Parse(string(data))
    ...
    indexTmpl.Execute(w, pa)
})

Pro Tip: during development, a lot of changes will happen in the assets, so it would be nice not to have to rebuild the binary every time we change anything.
It’s good that go-bindata has a debug mode, running:


go-bindata -debug ./assets/... index.html

That will generate a bindata.go file, but it will mock assets using automatically generated function calls that load files from the HDD. When done debugging, simply rerun ./build.sh

/app/reg/

Before we can login we will need a user, and for that we need to implement a way to register one. We also need to generate a password hash, store user info in the DB – so once again, SlashDB API automation comes in handy here.

Here we simply make a POST request to SlashDB-s /db/timesheet/user.json providing necessary info – no SQL required and if everything goes OK, well get a 201 (Created) status code and the URL of the newly created user resource i.e. /db/timesheet/user/user_id/10.


...
encodedPass := genPassword(un+passwd, nil)
payload := map[string]string{
  "username": un,
  "passwd":   encodedPass,
  "email":    email,
}
data, err := json.Marshal(payload)
...
// by default:
// pa.SdbInstanceAddr = "https://demo.slashdb.com"
// pa.SdbDBName = "timesheet"
// pa.ParsedSdbAPIKey = "apikey"
// pa.ParsedSdbAPIValue = "timesheet-api-key"
req, _ = http.NewRequest(
  "POST", 
  pa.SdbInstanceAddr + "/db/" + pa.SdbDBName + "/user.json?" + pa.ParsedSdbAPIKey + "=" + pa.ParsedSdbAPIValue,   
  bytes.NewReader(data)
)
ureq, err := defaultClient.Do(req)
...

if ureq.StatusCode != http.StatusCreated {
  // something went wrong, provide some useful response to the user or just return what SlashDB has returned
}
w.WriteHeader(http.StatusCreated)
w.Write([]byte(fmt.Sprintf("User %q was created successfully!", un)))

 

/app/login/

This little app’s session relies on the JWT token, so for the login we need to authorize the user, generate the token and send it back to the user. This is done via loginHandler function and the token itself is generated, based on the user input (received via form data or URL params), in the genJWTToken function.


var defaultSecret = []byte("timesheet app secret")

func genJWTToken(username string, id int, secret []byte) (string, error) {
    token := jwt.NewWithClaims(jwt.SigningMethodHS512, jwt.MapClaims{
        "username": username,
        "id":       id,
        "exp":      time.Now().Add(time.Hour * 24).Unix(),
    })
    if len(secret) == 0 {
        secret = defaultSecret
    }
    return token.SignedString(defaultSecret)
}

Then, if everything goes according to plan, we return the new token to the user.


tc := struct {
  Token string `json:"accessToken"`
}{st}

td, err := json.Marshal(tc)
if err != nil {
  log.Printf("data: %v, error: %v", tc, err)
  http.Error(w, http.StatusText(http.StatusInternalServerError), http.StatusInternalServerError)
  return
}
w.Write(td)

On the frontend side, we set the Authorization header and store the token in localStorage for reuse in subsequent requests i.e.


storeAuthInfo: function (authInfo) {
    // set authorization token for all the request done by the Vue app
    Vue.http.headers.common['Authorization'] = 'Bearer ' + authInfo.accessToken;
    this.authInfo = authInfo;
    this.userId = authInfo.payload.id;
    this.userName = authInfo.payload.username;
    localStorage.setItem(this.lsAuthInfoKey, JSON.stringify(authInfo));
}

Thanks to this we can make further requests, without explicitly managing this token by hand.

For instate when requesting user data.


var userId = 'id taken form JWT token';
this.$http.get('http://localhost:8000/db/timesheet/user_id/' + userId + '.json'))
    .then(function(resp) {
        // do something on successful response
    }, function(errResp) {
        // do something else on an error
    });

We just take care of handling responses/errors and don’t bother with anything else.

The same goes for POST, PUT and DELETE requests, which are all supported by the SlashDB’s generated REST API.

Screenshots from completed app

The registration view

The main view

Command line arguments for the app

In the init() function in main.go file, using the GoLang building lib flag package, we have added some command line arguments to this small proxy app. They allow for easy customization without recompiling the app (i.e. after deploying the binary to somewhere or giving it to someone).
Here’s a quick overview of the available switches and their default values.

$ ./timesheet:
  -net-interface string
        network interface to serve on (default "localhost")
  -port uint
        local port to serve on (default 8000)
  -sdb-address string
        SlashDB instance address (default "https://demo.slashdb.com")
  -sdb-apikey string
        SlashDB user API key, key and value separated by single ':' (default "apikey:timesheet-api-key")
  -sdb-dbname string
        SlashDB DB name i.e. https://demo.slashdb.com/db/>>timesheet<< (default "timesheet")

Building the app and bundling assets

Just run:

$ ./build.sh

and you’ll end up with linux/max/win binaries – with all the assets bundled inside.

Requirements and code download

If you want to play around with the code, it’s available here, and the pre-build binaries are here.

Back To Top