vis.it API

In order to be able to access our data from anywhere in the world, we decided to create our own API (Application Programming Interface). API’s purpose is to make all queries towards the database simple and, more importantly, available.

What about hosting?

Hosting is an important thing to mention while talking about our API. In order to be able to access our API’s available options, the API itself must be running on someone’s machine. Also, in order to be able to access our data, the database must be saved and available somewhere. Both hosting problems were solved by using Heroku. Heroku is a platform as a service that enables developers to build, run, and operate applications entirely in the cloud. What that means is – we can create an application which will communicate with our database and then run it on Heroku which makes it available 24/7. Another thing that had to be taken care of is the database. Heroku also offers the possibility of creating a database through its ClearDB add-on which can then enable us to host our database (we are provided with hostname, username and password which we use to connect to it).

How does it work?

The application hosted on Heroku which communicates with the database is very simple. We used NodeJS environment and several node modules which make connecting and communicating with database extremely easy.

Modules

• require("express")

  • Enables us to define our API’s routing table which can then be used to perform different actions based on HTTP methods and the URL itself
  • Enables us to read HTTP request information and to create HTTP responses

• require("body-parser")

  • Enables us to easily handle (parse) JSON and URL data

• require("cors")

  • Enables us to define our application’s routes that express uses
  • For example, app.get('/api', function (req, res) {res.send(hello: "World"})

• require("mysql")

  • Enables us to connect and communicate with databases

  • For example:

    • var mysql = require('mysql');
      var con = mysql.createConnection({
      	host: "localhost",
        	user: "yourusername",
        	password: "yourpassword"
      });
      
      con.connect(function(err) {
        if (err) throw err;
        console.log("Connected!");
      });
      

• require("dotenv")

  • Enables us to use environment variables

API routes

For efficient use of API, we defined a couple of routes towards our API. Of course, as our project develops and more features are added to it, the number of routes will most probably grow.

Roughly, we must define a route for each type of database query. For example, we will have different API routes for reading and inserting vis.it’s users from/to the database. We will also create different API routes for updating user’s general information and updating user’s trip data. However, we will not go to the extent in which we would create a different route for reading each user’s information – we will rather send user’s username through the URL. These examples may seem straightforward, but the decisions about routing will certainly become more complex relative to the project’s stage.

Routes we currently use:

• /getUsers
  • Gets all information from all users
  • It will probably be deleted as there is no need for getting all user data in vis.it Android application
• /getUser
  • Gets all information from a specific user’s database entry
  • Used in UserInterfaceFragment
• /updateUser/:username
  • Updates general information of a user with a specific username (for example - /updateUser/ivan updates data of user whose username is "ivan")
  • Used in UserInterfaceFragment
• /updatePassword/:username
  • Updates password of a user with a specific username (for example - /updateUser/ivan updates the password of user whose username is "ivan")
  • Used in ChangePasswordFragment
• /login
  • Extracts the username from POST HTTP request
  • Returns positive feedback ("user_found") and password of a user if the username exists
  • Returns negative feedback ("user_not_found") if the username does not exist
  • Used in LoginFragment
• /register
  • Extracts new user’s details from POST HTTP request
  • Checks if the username from POST request already exists:
    • If it does – it returns the feedback "username_unavailable"
    • If it does not – it inserts the user into "users" database table and returns the feedback "user_registered"
    • Used in RegisterFragment
• /insertTrip
  • Extracts trip details from POST HTTP request, inserts the data to “trips” database table and returns feedback "trip_inserted"
  • Used for saving a trip
• NOTE:
  • All the routes return the feedback "database_error" if there was an error in execution of any database queries
  • In that way, we will be able to display a message to the user even if things go wrong

Route example

I will briefly explain how the routes work. The explanation can be applied to all other routes as all are of the same concept. I will go through the process of enabling necessary node modules, creating a database connection, defining a route, using the database connection and responding to a request. I should note that app initialization and creation of the database connection is configured only once.

Express app initialization

As I previously described, we use certain node modules that make the process of communicating with clients as well as communicating with the database much simpler. We will initialize previously required packages to previously created application "app" (const app = express()).

dotenv.config();
app.set("port", process.env.PORT || 3000); 
app.use(cors());
app.use(express.json()); //grabbing info from frontend as json
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());

Firstly, we configure dotenv package so that we can use environment variables. The following line sets the wanted port number (which is either set by PORT environment variable or is set to 3000 (in cases when process.env.PORT variable is not found)). Last four lines of code just explicitly say to the express app that we will use previously mentioned modules.

Database connection

Pretty straightforward statement which requires the database credentials for creation of an object (db) on through which execute queries.

const db = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  multipleStatements: true,
});

I think that the code is self-explanatory.

/login route

I will explain how the login route works and how the queries towards the database are executed.

Firstly, we define our route and the type of HTTP request we are expecting. We are configuring a POST request on the route /login. We are expecting the request req and are responding with the response res.

app.post("/login", (req, res) => {
  	// Route code
});

Now we can extract the username of the user that wants to log in:

const username = req.body.username;

After we extracted the username from the HTTP request, we can specify the SQL query and execute it:

const sqlSelect = "SELECT * FROM users WHERE username = ?";

The question mark is used to specify missing data that will be added in the following statement.

We execute the SQL queries by using the db object:

db.query(sqlSelect, [username], (err, result) => {
	// Code depending of error or result value
});

Basically, we are executing sqlSelect query in which the question mark is replaced with the previously extracted username (there can be multiple unknowns in the defined SQL query, and we can link the relevant data by adding the variables to query array – for example [username, password, date]). After executing the query, error and result objects are returned. If the query executed successfully, error object will be null and the result will contain query output.

According to err and result object, we continue with the program:

if (err) {
	res.send({
    	feedback: "database_error",
        password: "",
        });

    return;
} else {
	if (result[0]) {
    	res.send({
          feedback: "user_found",
          password: result[0].password,
        });
        
        return;
	} else {
        res.send({
          feedback: "user_not_found",
          password: "",
        });

        return;
    }
}

The code is pretty self-explanatory – if we encounter an error, we respond to the HTTP request with the previously mentioned feedback through a JSON object – feedback will be set to "database_error" and password will be an empty string. If we get a response and it is not empty, that means that the user was found, and we respond with the feedback "user_found" and with the user’s password. If we get an empty response, that means that no records in the database correspond to the wanted username, thus we respond with the feedback "user_not_found" and a blank password.

I think that sending feedback no matter what is extremely important so that the API’s user always get the information on how the wanted query towards the database executed.

The full code of /login route follows:

app.post("/login", (req, res) => {
  const username = req.body.username;

  const sqlSelect = "SELECT * FROM users WHERE username = ?";

  db.query(sqlSelect, [username], (err, result) => {
    if (err) {
      res.send({
        feedback: "database_error",
        password: "",
      });

      return;
    } else {
      if (result[0]) {
        res.send({
          feedback: "user_found",
          password: result[0].password,
        });

        return;
      } else {
        res.send({
          feedback: "user_not_found",
          password: "",
        });

        return;
      }
    }
  });
});

Environment variables

The API part of the project implements environment variables. They are a simple way of combining the sharing of your project’s code and securing vulnerable security details. They work by using an .env file which stores all the secret and private information (database hostname, username, password etc.). The .env file is stored locally in the project’s root folder and we access the file’s variables by typing process.env.VARIABLE_NAME. All project team members share the .env file through secure channels and the .env file extension is added to .gitignore file. By doing that, every project member can edit secret variables and the public will not be able to see any of them.

Known difficulties

Simultaneous SQL query execution

Similarly to what I previously commented concerning the Retrofit2 background execution, SQL queries also execute in the background of the main application. That can lead to serious and hard-to-find errors.

For example, in the /register API route, I used two queries – one to find out whether the username was already taken, and one to insert the user if the username is available (a couple of lines below). The problem occurred each time the results of the first query (whether username was available or not) were not ready in time for the second query. Basically, the problem is that the second query relied onto the first query’s results which sometimes were not representative due to the lack of execution speed.

How did I solve it?

Easily. I simply nested the two queries and the second query was executed only when the first query retrieved the results from the database. It is important to note that this is not a great solution for all problems of this sort! We execute queries which are automatically parallelized (some much smarter and experienced people than us thought that is the best way – and you can probably understand why) in series. While taking that into consideration, I got to the conclusion that I am not harming the performance of the execution (small, simple queries that need to be executed in series because of the nature of their purpose), that the solution was simple to understand and that it worked well. Some more complex problems of this sort will probably require more complex solutions, as long as we want to preserve the performance and still get the expected results each time.

API GitHub repository

API project is located at vis.it.api – we think of it as a separate part of the project for which it is not appropriate to be located inside of Android project files. Furthermore, if the Android and API projects were located at the same repository, it would be tricky to configure Heroku in a way that it deploys the app only on pushes related to API and to disregard all Android project files.

Missing commit history

As I didn’t know how to link our Android application to a NodeJS app that was running locally, I had to deploy the API to Heroku each time that I wanted to test it. As you might imagine, that resulted in a catastrophic commit history of which I am most ashamed of. 😞 In order to fix my error to some level at least, I will list the titles and descriptions of all relevant commits and commit to this repository with production material only.

Missing commits

• Initial setup, only /api/getUsers route available
• Fixed package.json
• Added “start: “node index.js” to scripts – used for starting the build
• Added /register, renamed to members to users, errors
  • Additionally, all removed /api from all routes (for example /api/register is now /register)
• Removed BCrypt
  • Password hashing will be done in Android application and only hashed passwords will be sent over the Internet
• Added /login
• Feedback on unavailable usernames within /register
• Chrashing fixed (SQL queries are carried out independently)
• getUser/?username=usernameExample route added
  • Gets user’s details except the password
• /getUser/:username route feedbacks added
  • Feedbacks for database_error, username_unavailable and user_registered
• Added /updateUser
• Added /updatePassword
• Added and configured environment variables
• Added /insertTrip
  • Deals with inserting trip data to trips table