Before the API can process database data, we need a database so that we can really work. In This article we use transfers to create all tables, associations, associations, and indices. Whether you use transfers or not, the article also includes a very nice diagram of BeerSnob’s database structure.
ORM is a translation between an object that we can use in the API and the data in our database. It uses ‘templates’ to translate database information into an object. In our BeerSnob example, we would like to do something
foundbeers = BeerTable.Get(ID=2). In this example, BeerTable is a model that can connect to our beer table in a database and perform functions such as retrieving data and adding new records.
Let’s create our first model; lands. This model processes the data in our country table. Create a new file in the “models” directory country.js with the following contents:
With this simple file we can create a template. Creating a model and understanding how it works is not difficult. Important points are defined below.
- In the Template table (row 5), we have added a templateName. I have decided to keep the table names in the plural (the country table contains many records of countries) and the models in the Names unit (the model only describes one country).
- The models are combined with another model (line 11). We tell ORM that if we remove the country, the city should be removed. We also specify the foreign key in the CountryId.
- The init defines some information about the columns. The ORM must know the data types and column names in the database.
- timestamps: false (line 24). If this is true, ORM will add columns that follow the created and modified timestamps (we processed them manually, but ORM could do it for you as well).
There are several options for determining the associations between models. I’ve already used some in this project so far, but we’ll cover them in more detail in the next section. I have also created templates for other tables. See the archive for more information.
Before we can start building the API, we need to install some dependencies and prepare a few things.
3.1 Install the packages.
Navigate to the root folder and:
npm install --save express body-parser
npm install --save-dev nodemon
This will install the packages required by the API. Express is a web server, the body parser allows us to parse the body of requests sent through that web server. Then we install the dev dependency; these are only used to build our application and usually only for development. Nodemon reloads the web server every time we change the code. My F5 button is very grateful for this.
3.2 Configure your application to work with Nodemon
Open pack.json, the file in the root folder. Adjust the ‘scripts’ section to match the following:
"start": "node server.js",
"dev": "nodemon server.js"
This allows us to either call
npm run start production environments, or
npm run dev run our application with nodemon, allowing a reboot.
3.3 Create folders
Create two folders in the root directory: “route” and “service”.
It’s time to put these models to work! The API allows us to receive requests and use templates to retrieve information from the database. We first design our server to receive all requests and forward them to the route. The route then checks the request and connects to the database.
4.1 Create a server for our application
Use the script below to start the server.
- Importing some packages and also loggingService. This service helps us with debugging, we do it on line 13.
- On line 9, we create our application that forwards the requests.
- Row 24; we tell the app that we want to use bodyParser; this way we can receive e.g. json information.
- Line 29: Define API rules.
- Lines 41-46: We catch some URL paths and pass them on to our routes.
- Line 59: Create a server for our application and tell it to listen on port 5000 on the localhost
4.2 Creating Routes and Processing Requests
Now that our server is up and running, it’s time to forward requests to the routes we’ve defined on line 41 until 46. I’ll show you an example of how to handle a route below. I have taken the other routes in exactly the same way; you can find our data warehouse.
From the code above, you can see how the ORM works when processing a route. Lets walk through the first route (line 11-39).
- line 11: we specify the route.
/searchmeans in our case
- Line 14: We’ll get information about our URL. req.query retrieves URL parameters (bold part
https://mikehuls.medium.com/?source=twitter. We store the value of the parameter q in a variable, also called q (for the query). In the URL
localhost:5000/api/beers/search?q=alethe value of key q is ‘ale’.
- Line 20: Do nothing if q is too short
- Line 23-30: return all beers that contain q (case insensitive)
- Line 33 res.retun is what our API returns. It returns the object with one key: ‘beers’ and a set of all the beers we have found in our database. We return with status code 200 (success).
- Line 36: If something goes wrong in the experiment block, we’ll notify the customer by returning the error (we’ll use errorService for this).
As you can see, we don’t have to write the SQL row itself; we can only call our models that translate the request and communicate with our database. It is not only very convenient, but also prevents SQL injection.
Let’s test our beer routes! There are three different paths to beers:
- GET search / search
This route uses an ORM search to query the beer table. You can pass a keyword (q), such as
localhost:5000/api/beers/search?q=heine. Then it looks for beers that are like q (‘heine’).
- GET /
This route takes the optional query parameter Id and tries to find the record if an ID is given. Otherwise, it will return all beers. Try
- POST /
This route accepts the body it catches on line 72 (body required). Try sending the json below
localhost:5000/api/beersand you will see a new record appear in the database.
"Name": "Bud Light",
"Type": "Beer flavored water"