Models

Database Models

Now that we have our database table structure and sample data set up, we can finally configure sequelize to query our database by defining a model representing that data. At its core, a model sis simply an abstraction that represents the structure of the data in a table of our database. We can equate this to a class in object-oriented programming - each row or record in our database can be thought of as an instance of our model class. You can learn more about models in the Sequelize Documentation

To create a model, let’s first create a models folder in our app, then we can create a file user.js that contains the schema for the User model, based on the users table.

The User model schema should look very similar to the table definition used in the migration created earlier in this example:

/**
 * @file User schema
 * @author Russell Feldhausen <russfeld@ksu.edu>
 * @exports UserSchema the schema for the User model
 */

// Import libraries
import Sequelize from 'sequelize';

const UserSchema = {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true,
    },
    username: {
        type: Sequelize.STRING,
        unique: true,
        allowNull: false,
    },
    createdAt: {
        type: Sequelize.DATE,
        allowNull: false,
    },
    updatedAt: {
        type: Sequelize.DATE,
        allowNull: false,
    },
}

export default UserSchema

At a minimum, a model schema defines the attributes that are stored in the database, but there are many more features that can be added over time, such as additional computed fields (for example, a fullName field that concatenates the giveName and familyName fields stored in the database). We’ll explore ways to improve our models in later examples.

Once we have the model schema created, we’ll create a second file named models.js that will pull together all of our schemas and actually build the sequelize models that can be used throughout our application.

/**
 * @file Database models
 * @author Russell Feldhausen <russfeld@ksu.edu>
 * @exports User a Sequelize User model
 */

// Import database connection
import database from "../configs/database.js";

// Import Schemas
import UserSchema from './user.js';

// Create User Model
const User = database.define(
    // Model Name
    'User',
    // Schema
    UserSchema,
    // Other options
    {
        tableName: 'users'
    }
)

export {
    User
}

It is also important to note that we can define the name of the table that stores instances of the model in the tableName option.

We will see why it is important to use this models.js file (instead of just defining the model itself and not just the schema in the users.js file) once we start adding relations between the models. For now, we’ll start with this simple scaffold that we can expand upon in the future.

Model Querying

Finally, we are at the point where we can actually use our database in our application! So, let’s update the route for the users endpoint to actually return a list of the users of our application in a JSON format:

/**
 * @file Users router
 * @author Russell Feldhausen <russfeld@ksu.edu>
 * @exports router an Express router
 *
 * @swagger
 * tags:
 *   name: users
 *   description: Users Routes
 */

// Import libraries
import express from "express";

// Create Express router
const router = express.Router();

// Import models
import { User } from '../models/models.js'

/**
 * Gets the list of users
 * 
 * @param {Object} req - Express request object
 * @param {Object} res - Express response object
 * @param {Function} next - Express next middleware function
 * 
 * @swagger
 * /users:
 *   get: 
 *     summary: users list page
 *     description: Gets the list of all users in the application
 *     tags: [users]
 *     responses:
 *       200: 
 *         description: a resource         
 */
router.get("/", async function (req, res, next) {
  const users = await User.findAll();
  res.json(users)
});

export default router;

The only change we need to make is to import our User model we just created in the models/models.js file, and then use the User.findAll() query method inside of our first route method. A full list of all the querying functions in sequelize can be found in the Sequelize Documentation

Now, let’s start our application and see if it works! We should make sure we have migrated and seeded the database recently before starting. If everything works correctly, we should be able to navigate to the /users path and see the following JSON output on the page:

[
  {
    "id": 1,
    "username": "admin",
    "createdAt": "2025-02-04T15:36:32.000Z",
    "updatedAt": "2025-02-04T15:36:32.000Z"
  },
  {
    "id": 2,
    "username": "contributor",
    "createdAt": "2025-02-04T15:36:32.000Z",
    "updatedAt": "2025-02-04T15:36:32.000Z"
  },
  {
    "id": 3,
    "username": "manager",
    "createdAt": "2025-02-04T15:36:32.000Z",
    "updatedAt": "2025-02-04T15:36:32.000Z"
  },
  {
    "id": 4,
    "username": "user",
    "createdAt": "2025-02-04T15:36:32.000Z",
    "updatedAt": "2025-02-04T15:36:32.000Z"
  }
]

Awesome! We have now developed a basic web application that is able to query a database and present data to the user in a JSON format. This is the first big step toward actually building a RESTful API application.

This is a good point to commit and push our work!