Skip to content

Creación de Modelos

Los modelos hacen referencia a las tablas del diseño de su base de datos. Para este caso asumimos el siguiente diseño.

Antes de crear los modelos de la aplicación es necesario que configuremos nuestra base de datos.

Especificación de la base de datos

Esto nos indica que procedemos a varios procesos.

  • Instalar el motor de la base de datos que para este caso es MySql.

    https://docs.microsoft.com/en-us/windows/wsl/tutorials/wsl-database

  • Una vez instalado el motor debemos iniciar dicho motor.

    Abrimos la terminal de Ubuntu WSL y escribimos.

    sudo /etc/init.d/mysql start

    Si aparece este mensaje, quiere decir que ya esta inicializado.

    * Starting MySQL database server mysqld

Crear la base de datos indicada: 'NAME': 'almacen2022_mysql_nodejs', para ello, en la terminal de Ubuntu WSL ingresar

sudo mysql -u root -p

Y luego la contraseña.

Si aparece en la terminal

mysql> Indica que ya entró al motor de MySQL.

Luego realice:

show databases; Para mostrar las bases de datos que tiene creadas

create database almacen2022_mysql_nodejs;

Debe aparecer en pantalla, para indicar que ha sido creada.

Query OK, 1 row affected (0.01 sec)

Una vez creada la base de datos físicamente y haber levantado el servidor, procedemos a los modelos.

Primeramente instalamos las librerias que me permiten conexion con los motores de las bases de datos y tambien instalamos para utilizar el archivo .env y enviar datos externoas a las conexiones.

npm i sequelize mysql2 dotenv oracledb pg  pg-hstore tedious   @types/sequelize

Luego, creamos archivo src/database/db.ts

import { Sequelize } from "sequelize";
import dotenv from "dotenv";

dotenv.config();

const dbConfig = {
  mysql: {
    dialect: "mysql",
    host: process.env.DB_HOST || "localhost",
    username: process.env.DB_USER || "root",
    password: process.env.DB_PASSWORD || "",
    database: process.env.DB_NAME || "test",
    port: parseInt(process.env.DB_PORT || "3306")
  },
  postgres: {
    dialect: "postgres",
    host: process.env.DB_HOST || "localhost",
    username: process.env.DB_USER || "postgres",
    password: process.env.DB_PASSWORD || "",
    database: process.env.DB_NAME || "test",
    port: parseInt(process.env.DB_PORT || "5432")
  },
  mssql: {
    dialect: "mssql",
    host: process.env.DB_HOST || "localhost",
    username: process.env.DB_USER || "sa",
    password: process.env.DB_PASSWORD || "",
    database: process.env.DB_NAME || "test",
    port: parseInt(process.env.DB_PORT || "1433")
  },
  oracle: {
    dialect: "oracle",
    host: process.env.DB_HOST || "localhost",
    username: process.env.DB_USER || "oracle",
    password: process.env.DB_PASSWORD || "",
    database: process.env.DB_NAME || "test",
    port: parseInt(process.env.DB_PORT || "1521")
  },
};

// Select the database engine from the .env file
const dbEngine = process.env.DB_ENGINE || "mysql";
const config = dbConfig[dbEngine as keyof typeof dbConfig];

if (!config) {
  throw new Error(`Unsupported DB_ENGINE: ${dbEngine}`);
}

// Create the Sequelize instance
export const sequelize = new Sequelize(config.database, config.username, config.password, {
  host: config.host,
  dialect: config.dialect as any,
});

Crear archivo backend-profe/.env

PORT=3000



DB_ENGINE=mysql
DB_HOST=localhost
DB_USER=admin
DB_PASSWORD=MiNiCo57**
DB_NAME=almacen_2025_isem_node
DB_PORT=3306


# DB_ENGINE=postgres
# DB_HOST=localhost
# DB_USER=postgres
# DB_PASSWORD=MiNiCo57**
# DB_NAME=almacen_2025_isem_node
# DB_PORT=5432




# DB_ENGINE=mssql
# DB_HOST=localhost
# DB_USER=sa
# DB_PASSWORD=MiNiCo57**
# DB_NAME=almacen_2025_isem_node
# DB_PORT=1433



# DB_ENGINE=oracle
# DB_HOST=localhost
# DB_USER=ALMACENDB_ADMIN
# DB_PASSWORD=MiNiCo57
# DB_NAME=xe
# DB_PORT=1521

y se complementa el codigo en config/index.ts

import express, { Application } from "express";
import morgan from "morgan";
import { sequelize } from "../database/db";

// Load environment variables from the .env file

export class App {
  public app: Application;

  constructor(private port?: number | string) {
    this.app = express();
    this.settings();
    this.middlewares();
    this.dbConnection(); // Call the database connection method

  }

  // Application settings
  private settings(): void {
    this.app.set('port', this.port || process.env.PORT || 4000);
  }

 // Middleware configuration
 private middlewares(): void {
  this.app.use(morgan('dev'));
  this.app.use(express.json()); // leer json raw
  this.app.use(express.urlencoded({ extended: false })); //leer json form
}


// Method to connect and synchronize the database
private async dbConnection(): Promise<void> {
  try {
    await sequelize.sync({ force: true }); // Synchronize the database
    console.log("Database connected successfully");
  } catch (error) {
    console.error("Unable to connect to the database:", error);
  }
}

// Start the server
  async listen() {
    await this.app.listen(this.app.get('port'));
    console.log('Server on port', this.app.get('port'));
  }
}

Seguidamente, se procede a crear los modelos.

Nota: Se recomienda iniciar por aquellos modelos que no tengan llaves foráneas.

Modelo Client

Crear el archivo con el nombre del Modelo en src/models/Client.ts

import { DataTypes, Model } from "sequelize";
import { sequelize } from "../database/db";

export interface ClientI {
  id?: number;
  name: string;
  address: string;
  phone: string;
  email: string;
  password: string;
  status: "ACTIVE" | "INACTIVE";
}

export class Client extends Model {
  public id!: number;
  public name!: string;
  public address!: string;
  public phone!: string;
  public email!: string;
  public password!: string;
  public status!: "ACTIVE" | "INACTIVE";
}

Client.init(
  {
    name: {
      type: DataTypes.STRING,
      allowNull: true,
    },
    address: {
      type: DataTypes.STRING,
      allowNull: true,
    },
    phone: {
      type: DataTypes.STRING,
      allowNull: true,
      validate: {
        notEmpty: { msg: "Phone cannot be empty" },
        len: { args: [10, 15], msg: "Phone must be between 10 and 15 characters" },
      },
    },
    email: {
      type: DataTypes.STRING,
      allowNull: true,
      unique: true,
    },
    password: {
      type: DataTypes.STRING,
      allowNull: true,
    },
    status: {
      type: DataTypes.ENUM("ACTIVE", "INACTIVE"),
      defaultValue: "ACTIVE",
    },
  },
  {
    sequelize,
    modelName: "Client",
    tableName: "clients",
    timestamps: false,
  }
);

Enlace para conocer los tipos de datos de Sequelize

https://sequelize.org/docs/v7/other-topics/other-data-types/

Controlador Client

Crear el archivo src/controllers/client.controller.ts

import { Request, Response } from "express";
import { Client, ClientI } from "../models/Client";

export class ClientController {
  // Get all clients with status "ACTIVE"
  public async getAllClients(req: Request, res: Response) {
    try {
      const clients: ClientI[] = await Client.findAll({
        where: { status: 'ACTIVE' },
      });
      res.status(200).json({ clients });
    } catch (error) {
      res.status(500).json({ error: "Error fetching clients" });
    }
  }

  // Get a client by ID
  public async getClientById(req: Request, res: Response) {
    try {
      const { id: pk } = req.params;
      const client = await Client.findOne({
        where: { 
          id: pk, 
          status: 'ACTIVE' },
      });
      if (client) {
        res.status(200).json(client);
      } else {
        res.status(404).json({ error: "Client not found or inactive" });
      }
    } catch (error) {
      res.status(500).json({ error: "Error fetching client" });
    }
  }


}

Rutas de Cliente

Crear el archivo src/routes/client.ts

import { Router, Application } from "express";
import { ClientController } from "../controllers/client.controller";

export class ClientRoutes {
  public clientController: ClientController = new ClientController();

  public routes(app: Application): void {
    app.route("/clientes").get(this.clientController.getAllClients);
    app.route("/clientes/:id").get(this.clientController.getClientById);  

  }
}

Luego indexamos esta rutas en

En caso que no exista, crear el archivo src/routes/index.ts

import { Router } from "express";
import { ClientRoutes } from "./client";


export class Routes {
  public clientRoutes: ClientRoutes = new ClientRoutes();

}

Definir Cliente en Config

Primeramente debemos instalar el paquete de Cors para que pueda hacer peticiones desde el cliente

npm i cors

posteriormente, luego en el archivo src/config/index.ts

import { Routes } from "../routes/index";

public routePrv: Routes = new Routes();

this.routes();

private routes() {
        this.routePrv.clienteRoutes.routes(this.app);
    }

dicho archivo queda finalmente de esta manera:

import dotenv from "dotenv";
import express, { Application } from "express";
import morgan from "morgan";
import { sequelize } from "../database/db";
import { Routes } from "../routes/index";

// Load environment variables from the .env file
dotenv.config();

export class App {
  public app: Application;
  public routePrv: Routes = new Routes();

  constructor(private port?: number | string) {
    this.app = express();

    this.settings();
    this.middlewares();
    this.routes();
    this.dbConnection(); // Call the database connection method
  }

  // Application settings
  private settings(): void {
    this.app.set('port', this.port || process.env.PORT || 4000);
  }

  // Middleware configuration
  private middlewares(): void {
    this.app.use(morgan('dev'));
    this.app.use(express.json());
    this.app.use(express.urlencoded({ extended: false }));
  }

  // Route configuration
  private routes(): void {
    this.routePrv.clientRoutes.routes(this.app);

  }

  // Method to connect and synchronize the database
  private async dbConnection(): Promise<void> {
    try {
      await sequelize.sync({ force: true }); // Synchronize the database
      console.log("Database connected successfully");
    } catch (error) {
      console.error("Unable to connect to the database:", error);
    }
  }

  // Start the server
  async listen() {
    await this.app.listen(this.app.get('port'));
    console.log('Server on port', this.app.get('port'));
  }
}

Ahora hacemos lo mismo con el modelo Sale

Modelo Sale

Crear el archivo con el nombre del Modelo en src/models/Sale.ts

import { DataTypes, Model } from "sequelize";
import { sequelize } from "../database/db";

export interface SaleI {
  id?: number;
  saleDate: Date;
  subtotal: number;
  tax: number;
  discounts: number;
  total: number;
  status: "ACTIVE" | "INACTIVE";
  client_id: number;
  sale_id: number;
}

export class Sale extends Model {
  public id!: number;
  public saleDate!: Date;
  public subtotal!: number;
  public tax!: number;
  public discounts!: number;
  public total!: number;
  public status!: "ACTIVE" | "INACTIVE";
  public client_id!: number;
  public sale_id!: number;
}

Sale.init(
  {
    saleDate: {
      type: DataTypes.DATE,
      allowNull: false,
      validate: {
        notEmpty: { msg: "Sale date cannot be empty" },
        isDate: { args: true, msg: "Must be a valid date" },
      },
    },
    subtotal: {
      type: DataTypes.FLOAT,
      allowNull: false,
      validate: {
        notEmpty: { msg: "Subtotal cannot be empty" },
        isFloat: { msg: "Subtotal must be a valid number" },
      },
    },
    tax: {
      type: DataTypes.FLOAT,
      allowNull: false,
      validate: {
        notEmpty: { msg: "Tax cannot be empty" },
        isFloat: { msg: "Tax must be a valid number" },
      },
    },
    discounts: {
      type: DataTypes.FLOAT,
      allowNull: false,
      validate: {
        notEmpty: { msg: "Discounts cannot be empty" },
        isFloat: { msg: "Discounts must be a valid number" },
      },
    },
    total: {
      type: DataTypes.FLOAT,
      allowNull: false,
      validate: {
        notEmpty: { msg: "Total cannot be empty" },
        isFloat: { msg: "Total must be a valid number" },
      },
    },
    status: {
      type: DataTypes.ENUM("ACTIVE", "INACTIVE"),
      defaultValue: "ACTIVE",
    },
  },
  {
    sequelize,
    modelName: "Sale",
    tableName: "sales",
    timestamps: false,
  }
);

Controlador Sale

Crear el archivo src/controllers/sale.controller.ts

import { Request, Response } from "express";
import { Sale, SaleI } from "../models/Sale";

export class SaleController {
  // Get all sales with status "ACTIVE"
  public async getAllSales(req: Request, res: Response) {
    try {
      const sales: SaleI[] = await Sale.findAll({
        where: { status: "ACTIVE" },
      });
      res.status(200).json({ sales });
    } catch (error) {
      res.status(500).json({ error: "Error fetching sales" });
    }
  }

  // Get a sale by ID
  public async getSaleById(req: Request, res: Response) {
    try {
      const { id: pk } = req.params;
      const sale = await Sale.findOne({
        where: { 
          id: pk, 
          status: "ACTIVE" },
      });
      if (sale) {
        res.status(200).json(sale);
      } else {
        res.status(404).json({ error: "Sale not found or inactive" });
      }
    } catch (error) {
      res.status(500).json({ error: "Error fetching sale" });
    }
  }


}

Rutas de Cliente

Crear el archivo src/routes/sale.ts

import { Application } from "express";
import { SaleController } from "../controllers/sale.controller";

export class SaleRoutes {
  public saleController: SaleController = new SaleController();

  public routes(app: Application): void {
    app.route("/ventas").get(this.saleController.getAllSales); // Get all sales
    app.route("/ventas/:id").get(this.saleController.getSaleById); // Get a sale by ID

  }
}

Luego indexamos esta rutas en

En caso que no exista, crear el archivo src/routes/index.ts

import { Router } from "express";
import { ClientRoutes } from "./client";
import { SaleRoutes } from "./sale";

export class Routes {
  public clientRoutes: ClientRoutes = new ClientRoutes();
  public saleRoutes: SaleRoutes = new SaleRoutes();

}

Definir Sale en Config

Primeramente debemos instalar el paquete de Cors para que pueda hacer peticiones desde sale

posteriormente, luego en el archivo src/config/index.ts

dicho archivo queda finalmente de esta manera:

import dotenv from "dotenv";
import express, { Application } from "express";
import morgan from "morgan";
import { sequelize } from "../database/db";
import { Routes } from "../routes/index";

// Load environment variables from the .env file
dotenv.config();

export class App {
  public app: Application;
  public routePrv: Routes = new Routes();

  constructor(private port?: number | string) {
    this.app = express();

    this.settings();
    this.middlewares();
    this.routes();
    this.dbConnection(); // Call the database connection method
  }

  // Application settings
  private settings(): void {
    this.app.set('port', this.port || process.env.PORT || 4000);
  }

  // Middleware configuration
  private middlewares(): void {
    this.app.use(morgan('dev'));
    this.app.use(express.json());
    this.app.use(express.urlencoded({ extended: false }));
  }

  // Route configuration
  private routes(): void {
    this.routePrv.clientRoutes.routes(this.app);
    this.routePrv.saleRoutes.routes(this.app);

  }

  // Method to connect and synchronize the database
  private async dbConnection(): Promise<void> {
    try {
      await sequelize.sync({ force: true }); // Synchronize the database
      console.log("Database connected successfully");
    } catch (error) {
      console.error("Unable to connect to the database:", error);
    }
  }

  // Start the server
  async listen() {
    await this.app.listen(this.app.get('port'));
    console.log('Server on port', this.app.get('port'));
  }
}

Si existen relaciones adicionar las relaciones en uno de los modelos, indicando el nombre de la llave foranea, en el caso de clientes y sale lo haremos en el modelo Client.

import { Sale } from "./Sale";
....
....
....



Client.hasMany(Sale, {
  foreignKey: "client_id",
  sourceKey: "id",
});

Sale.belongsTo(Client, {
  foreignKey: "client_id",
  targetKey: "id",
});

Codigo completo

import { DataTypes, Model } from "sequelize";
import { sequelize } from "../database/db";
import { Sale } from "./Sale";

export interface ClientI {
  id?: number;
  name: string;
  address: string;
  phone: string;
  email: string;
  password: string;
  status: "ACTIVE" | "INACTIVE";
}

export class Client extends Model {
  public id!: number;
  public name!: string;
  public address!: string;
  public phone!: string;
  public email!: string;
  public password!: string;
  public status!: "ACTIVE" | "INACTIVE";
}

Client.init(
  {
    name: {
      type: DataTypes.STRING,
      allowNull: true,
    },
    address: {
      type: DataTypes.STRING,
      allowNull: true,
    },
    phone: {
      type: DataTypes.STRING,
      allowNull: true,
      validate: {
        notEmpty: { msg: "Phone cannot be empty" },
        len: { args: [10, 15], msg: "Phone must be between 10 and 15 characters" },
      },
    },
    email: {
      type: DataTypes.STRING,
      allowNull: true,
      unique: true,
    },
    password: {
      type: DataTypes.STRING,
      allowNull: true,
    },
    status: {
      type: DataTypes.ENUM("ACTIVE", "INACTIVE"),
      defaultValue: "ACTIVE",
    },
  },
  {
    sequelize,
    modelName: "Client",
    tableName: "clients",
    timestamps: false,
  }
);

Client.hasMany(Sale, {
  foreignKey: "client_id",
  sourceKey: "id",
});

Sale.belongsTo(Client, {
  foreignKey: "client_id",
  targetKey: "id",
});

De la misma forma haremos con el resto de los modelos