The Model Layer in Express.js

Most other frameworks require you to follow a strict file structure in order to implement this MVC architecture, but not Express.js. You can design your file structure however you want. As a demonstration, this is the structure we are going to use:

1.
2├── models
3├── controllers
4├── views
5├── libs
6├── routes
7├── package.json
8├── package-lock.json
9└── index.js

In this case, models, controllers, and views each contain the code for their corresponding layer. libs contains the extra libraries you might need. The routes directory contains the routers defined for the app, and finally, index.js is the entry point of our app.

Preparing the database

In this lesson, we are going to start with the model layer, which is in charge of interacting with the database. Previously we used a .txt file to store information, but that is not going to be enough this time, so first of all, you'll need a proper database.

For demonstration purposes, we are going with SQLite. It is a lightweight, single file-based SQL database. It is a great option for beginners to create their first practice project, because to create a SQLite database, all you need to do is install the corresponding command line tool.

If you are on Windows:

1winget install -e --id SQLite.SQLite

If you are on macOS:

1brew install sqlite

If you are on Ubuntu:

1sudo apt install sqlite3

And then, create a .sqlite file.

1touch database.sqlite

1.
2├── controllers
3├── database.sqlite  <===
4├── index.js
5├── libs
6├── models
7├── package-lock.json
8├── package.json
9├── routes
10└── views

To view the SQLite database, you can use a VSCode extension such as SQLite Viewer, or any other tools you are familiar with.

Lastly, there is one more thing we must do, that is to install the sqlite3 NPM package in our project. This package allows JavaScript to work with SQLite databases.

1npm install sqlite3

To use this package, go to libs and create a new file named db.js.

1import sqlite3 from "sqlite3";
2
3const db = new sqlite3.Database("./database.sqlite");
4
5export default db;

This example makes a connection to the SQLite database by creating a new instance of Database object, which gives you access to various methods that allow you to interact with the database. We will talk more about them later.

Of course, you can initialize a Database from inside the model itself, but this setup allows you to share the same Database instance across multiple models, which is much more convenient.

Next, go to the models directory and create a user.js file. This will be our user model. It is in charge of interacting with the corresponding users table.

models/user.js

1import db from "../libs/db.js";

Inside the user model, you'll need to import the Database instance we just created.

Initializing the users table

The first thing we need to do is to make sure a users table has ben initialized inside database.sqlite.

models/user.js

1db.serialize(() => {
2  db.run(
3    `CREATE TABLE IF NOT EXISTS users (
4      id INTEGER PRIMARY KEY AUTOINCREMENT,
5      username TEXT,
6      email TEXT
7    )`
8  );
9});

db.serialize(() => {}) makes sure that everything in the callback function is executed in sequence, and db.run() executes the given SQL query, which is a command language used to interact with relational databases such as SQLite. We did not discuss how to write SQL in this course, but the language itself is quite intuitive.

1CREATE TABLE IF NOT EXISTS users (
2  id INTEGER PRIMARY KEY AUTOINCREMENT,
3  username TEXT,
4  email TEXT
5)

In this case, a new users table will be created if it doesn't exist already. The users table has the following structure.

1+---------+----------------+-------------------+
2|   id    |      email     |      username     |
3+---------+----------------+-------------------+
4| INTEGER |      TEXT      |       TEXT        |
5+---------+----------------+-------------------+

The User class

Next, your users table needs a schema, meaning the table should have a fixed structure. In our case, you should make sure every user item has an id, a username, and an email. We are using a JavaScript class to organize our code:

models/user.js

1class User {
2  constructor(id, username, email) {
3    this.id = id;
4    this.username = username;
5    this.email = email;
6  }
7
8  static getAll() {. . .} // Get all user items in the table.
9  static getById() {. . .} // Get a single user item based on id.
10  static create() {. . .} // Create a new user, and save it in the database.
11  static update() {. . .} // Update an existing user item.
12  static delete() {. . .} // Remove a user item from the database.
13}

Besides the constructor function, we are also going to create five utility methods. Of course, you may need other methods depending on the specific requirements of your project, such as getByUsername(), getByEmail(), and so on. After going over this lesson, you should be able to create them with ease.

Creating a new user

Right now, our database is empty, so let's start by talking about how to add new records to the database.

models/user.js

1static create(username, email, callback) {
2  const sql = "INSERT INTO users (username, email) VALUES (?, ?)";
3  db.run(sql, [username, email], function (err) {
4    callback(null, this.lastID);
5  });
6}

The create() method accepts three arguments, username, email, and callback. The callback is a function that should be executed after the new user has been inserted into the database.

Inside create(), we are using the run() method under Database to insert the new user record. The run() method has the following syntax:

1db.run(sql, [param1, param2, ...], callback);

The first argument, sql, accepts an SQL query.

1INSERT INTO users (username, email) VALUES (?, ?)

This command says we are going to insert a new user into the users table. The columns username and email each correspond to a placeholder (?). Their values are provided in the second argument of run() in the form of an array.

Wait, our schema defines the users table to have an id, a username, and an email, so where is the id? The answer is quite simple, they are automatically created. When you insert a new record, it will be added to the end of the table, and the id will simply increment by 1.

Lastly, the run() method accepts a callback function, which will run after the SQL query has been executed. If something goes wrong, an error object will be passed to the callback function (err). If not, err will be null.

We did not implement an error handling mechanism in this example for simplicity, but you should definitely do that in production.

Inside the callback function, the function callback(), which is passed to the create() method, will be executed.

Create the corresponding route

So what exactly is this callback(), and why do we need it here? To answer this question, we need to think about how you may use this User.create() method. Go back to the entry point of our app, index.js, and add a new router for our create() method:

index.js

1import User from "./models/user.js";
2
3app.use(express.json()); // Allows Express.js to parse the request body
4
5app.post("/users", (req, res) => {
6  const { username, email } = req.body;
7  User.create(username, email, (err, userId) => {
8    res.status(201).json({ id: userId, message: "User created successfully" });
9  });
10});

First of all, we need to retrieve the username and email from the request body. This step requires us to use the express.json() function, which we are going to discuss later.

The username and email are then passed to the create() method, so that create() can save them to the database. The third argument will become the callback() function like this:

In this example, we left out error handling, so null is assigned to the argument err, but you should include it in your project, especially in a production environment.

You may have noticed something strange here. What exactly is this.lastID? We did not define a lastID in the constructor function, so where did it come from?

This is a common mistake for beginners to JavaScript, due to the fact that this exists outside of an object, which is a special feature in JavaScript.

As we've discussed previously in the JavaScript Methods lesson, this is context dependent, meaning the this keyword in different functions points to different things. And in this case, this exists in the context of the callback function.

And it has a lastID property that stores the id of the item that has just been saved.

One more thing to be noted is that you cannot use arrow functions here if you wish to access this.lastID, because arrow functions do not have a this keyword.

Testing the create() method

Finally, let's test if our setup works. At this point, this should be your user.js model:

1import db from "../libs/db.js";
2
3db.serialize(() => {
4  db.run(
5    `CREATE TABLE IF NOT EXISTS users (
6      id INTEGER PRIMARY KEY AUTOINCREMENT,
7      username TEXT,
8      email TEXT
9    )`
10  );
11});
12
13class User {
14  constructor(id, username, email) {
15    this.id = id;
16    this.username = username;
17    this.email = email;
18  }
19
20  static create(username, email, callback) {
21    const sql = "INSERT INTO users (username, email) VALUES (?, ?)";
22    db.run(sql, [username, email], function (err) {
23      callback(null, this.lastID);
24    });
25  }
26}
27
28export default User;

And this should be your index.js:

1import express from "express";
2import User from "./models/user.js";
3
4const app = express();
5const port = 3001;
6
7app.use(express.json());
8
9app.post("/users", (req, res) => {
10  const { username, email } = req.body;
11  User.create(username, email, (err, userId) => {
12    res.status(201).json({ id: userId, message: "User created successfully" });
13  });
14});
15
16app.listen(port, () => {
17  console.log(`App listening on port ${port}. Visit http://localhost:${port}.`);
18});

Start the server by running the following command:

1node index.js

1App listening on port 3001. Visit http://localhost:3001.

A new users table should be created because the db.serialize() function will be executed first. But now, the table is still empty.

Next, open Thunder Client and make a new POST request to http://localhost:3001/users. Inside the request body, you must provide the username and email, so that they can be accessed on the server side.

1{
2  "username": "John",
3  "email": "john@example.com"
4}

Click send, and a new user record should be added to the database.

Getting a single user by id

Next, let's talk about how to retrieve the user record we just created.

models/user.js

1static getById(id, callback) {
2  const sql = "SELECT * FROM users WHERE id = ?";
3  db.get(sql, [id], (err, row) => {
4    const user = new User(row.id, row.username, row.email);
5    callback(null, user);
6  });
7}

In this case, we use the db.get() method to get a single row from the users table. The method works just like db.run(), as it also accepts an SQL query, an array of parameters, and a callback function.

The only difference is that if the SQL query executes without error, the retrieved row will be passed to the callback function. In our example, we use the retrieved data to initialize a new instance of User, which is then passed to the function callback().

As for the corresponding route handler:

index.js

1app.get("/users/:id", (req, res) => {
2  const id = req.params.id;
3  User.getById(id, (err, user) => {
4    res.json(user);
5  });
6});

Getting all users

models/user.js

1static getAll(callback) {
2  const sql = "SELECT * FROM users";
3  db.all(sql, (err, rows) => {
4    const users = rows.map(
5      (row) => new User(row.id, row.username, row.email)
6    );
7    callback(null, users);
8  });
9}

The db.all() method is used to retrieve multiple rows from the database.

index.js

1app.get("/users", (req, res) => {
2  User.getAll((err, users) => {
3    res.json(users);
4  });
5});

Updating an existing user

models/user.js

1static update(id, username, email, callback) {
2  const sql = "UPDATE users SET username = ?, email = ? WHERE id = ?";
3  db.run(sql, [username, email, id], function (err) {
4    callback(null);
5  });
6}

index.js

1app.put("/users/:id", (req, res) => {
2  const id = req.params.id;
3  const { username, email } = req.body;
4  User.update(id, username, email, () => {
5    res.json({ message: "User updated successfully" });
6  });
7});

The corresponding record in the database should be updated.

Deleting a user

models/user.js

1static delete(id, callback) {
2  const sql = "DELETE FROM users WHERE id = ?";
3  db.run(sql, [id], function (err) {
4    callback(null);
5  });
6}

index.js

1app.delete("/users/:id", (req, res) => {
2  const id = req.params.id;
3  User.delete(id, () => {
4    res.json({ message: "User deleted successfully" });
5  });
6});