In order to provide the data that we have stored in our database to our application, we will create a set of REST endpoints in our Express server that will connect to our database, execute a SQL query, and return data as JSON objects. In order to work with an Sqlite3 database, we will use the sqlite3 node library, which can be installed as follows:

npm install sqlite3
npm install @types/sqlite3 --saveDev   

In order to serve data to our application, we will create a new Express handler in the express/routes directory named dataRoutes.ts as follows:

import * as express from 'express'; 
import { serverLog } from '../main'; 
 
var router = express.Router(); 
 
router.get(`/boards`, async (req: any, res: any, next: any) => { 
    serverLog(`GET /boards`); 
    res.json({ result: 'success' }) 
}); 
 
export { router }; 

Here, we have a standard Express handler that is serving GET requests from the path named /boards. For the time being, this handler is simply logging a message using our serverLog function, and returning a JSON payload with a single property named result. As we have seen before, we must re-export the router from this file as seen on the last line of the preceding code snippet. We can then register this handler with our Express server in our express/main.ts file as follows:

... existing code 
 
app.use('/', userRoutes.router); 
app.use('/api', dataRoutes.router); 
 
... existing code 

Here, we have added a new app.use function call to register all routes from the dataRoutes.ts file with the Express server, under a base URL path named /api. This means that in order to access any of our REST endpoints, we will need to add the /api path to the beginning of the URL. Using a base URL in this way allows us to differentiate standard Express handlers from REST handlers. Let's now connect to our database within the /boards handler and return all the rows found as follows:

router.get(`/boards`, async (req: any, res: any, next: any) => { 
 
    serverLog(`GET /boards`); 
    let db = new Database('./database/board_sales_db.db'); 
 
    let boardsArray: any[] = []; 
 
    db.each(`select  
       b.id, b.name, b.short_description,  
       b.long_description, b.img 
       from Board b  
    `, (err: Error, row: any) => { 
            let board = { 
                id: row.id, name: row.name, 
                short_description: row.short_description, 
                long_description: row.long_description, 
                img: row.img 
            }; 
            boardsArray.push(board); 
 
        }, (err: Error, count: number) => { 
            // complete 
 
            if (err) { 
                serverLog(`err : ${err}`); 
                res.status(503).send(err); 
            } else { 
                console.log(` => returning array`); 
                res.json(boardsArray); 
            } 
        }); 
 
}); 

Here, we have started to use the Sqlite3 API in order to connect to a database and return results. Our handler has been updated to create a local variable named db that is a new instance of the Database class. This db variable represents a connection to the Sqlite3 database, and has a single constructor argument. This argument is the path to the Sqlite3 database file itself. Our handler then creates a new array named boardsArray, which is set to an empty array.

We then use the each function of the Database class to execute a SQL query. The each function has three parameters. The first parameter is of type string, and is the SQL command to execute. The second parameter is a function that will be executed for each result that is returned by the database, and the third parameter is a function that will be executed once all results are returned. The API for Sqlite3 that we are working with is using a standard callback mechanism. In other words, the second parameter that we have defined in our call to db.each is a callback function that will be invoked for each record returned by the database. The third parameter is a further callback function that will be invoked once all records have been read.

This double callback design is actually quite handy. Within the first callback, we know that we are dealing with only a single database record. We are therefore able to construct a JavaScript object that represents this single record, and add it to our array of records to return. Within the second callback, we know that all records have been processed from the database. If there was some sort of error, we return a response with a status of 503, and include an error message. If all goes well, we return the entire contents of the boardsArray local variable as a JSON structure.

Browsing to the URL of localhost:9000/api/boards will now return a JSON structure with the data from our database, as seen in the following screenshot:

There are a number of endpoints that we will need in order to provide the necessary data for our application. We will not discuss each of these in detail, as the implementation of each route handler is the same. In other words, connect to the database, execute a SQL query, and return the results. The only difference between each of these handlers is the handler route, the SQL query that is executed, and the corresponding JSON data structure that is returned. For the sake of brevity, we will only list each of the endpoint routes, and the SQL query that is used. Please refer to the sample code of this chapter for full implementations.

Our list of manufacturers will be served by the endpoint named /api/manufacturers, and consists of the following SQL query:

select id, name, logo from Manufacturer 

Our list of board types will be served by the endpoint named /api/board-types, and will run the following SQL query:

select id, name from BoardType 

Linking a particular board to a manufacturer is resolved by the /board-manufacturers endpoint, which will run the following query:

select id, board_id, manufacturer_id from BoardManufacturer 

Again, please refer to the sample code for the full implementations.