Skip to main content

​
Overview

The Tandex Electronics API uses MySQL as its primary database for storing users, products, publications, and activity logs. This guide covers the complete database setup process.

​
Database Schema Overview

The database consists of several core tables and stored procedures:

​
Core Tables

Stores user authentication and profile information including:
  • User credentials (email, encrypted password)
  • User type/role (admin, regular user)
  • Personal information (name, surname)
  • Unique user identifier
Main product inventory table containing:
  • Product key/SKU (claveProducto)
  • Product line (linea)
  • Description (descripcionP)
  • Price (precio)
  • Stock levels (existencias)
  • Unit of entry (unidadEntrada)
  • WooCommerce metadata (type, descriptions, categories)
Tracks which products have been published to external platforms:
  • Product key reference (claveProductoP)
  • Platform identifier (woocomerce)
  • User who published (idUsuarioPP)
  • Publication timestamp
Audit log for tracking system activities:
  • User ID (idUsuarioB)
  • Action timestamp (fecha)
  • Type of modification (modificacion)
  • Quantity of items affected (cantidad)

​
Database Connection Setup

The API uses a connection pool for efficient database access:
dbconn.js
const mysql = require('mysql')
const server_config = require('config')

module.exports = () =>{
    let config = {
        connectionLimit : 20,
        host : server_config.get('db.host'),
        database : server_config.get('db.db'),
        user : server_config.get('db.user'),
        password : server_config.get('db.password'),
    }

    return new Database(config)
}

class Database {
    constructor(config) {
        this.connection = mysql.createPool(config)
    }

    query(sql,args){
        return new Promise((resolve,reject) => {
            this.connection.query(sql, args, (err,rows) => {
                if(err) 
                    return reject(err)
                resolve(rows)
            })
        })
    }

    close (){
        return new Promise( (resolve,reject) => {
            this.connection.end(err=>{
                if(err)
                    return reject(err)
                resolve()    
            })
        })
    }
}

​
Connection Configuration

Configure your database credentials in config/default.json:
config/default.json
{
  "db": {
    "host": "your-mysql-host",
    "db": "your-database-name",
    "user": "your-username",
    "password": "your-password"
  }
}
The connection pool is configured with a limit of 20 concurrent connections for optimal performance.

​
Running the SQL Schema

1

Create the database

First, create a new MySQL database:
CREATE DATABASE tandex_api CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
2

Import the schema

Import the backend.sql file to create all tables:
mysql -u your-username -p tandex_api < backend.sql
Or from within MySQL:
USE tandex_api;
SOURCE /path/to/backend.sql;
3

Verify the import

Check that all tables were created successfully:
USE tandex_api;
SHOW TABLES;

​
Database Schema Tables

The backend.sql file creates the following table structure: 
-- User management table
CREATE TABLE `cuenta` (
  `idCuenta` int(4) NOT NULL,
  `idUsuario` int(4) NOT NULL,
  `nivelCuenta` varchar(20) NOT NULL,
  `Correo` varchar(50) NOT NULL,
  `Contrasena` varchar(2) NOT NULL,
  `nombre` varchar(255) NOT NULL,
  `Apellido_paterno` varchar(255) NOT NULL,
  `Apellido_materno` varchar(255) NOT NULL,
  `telefono` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Product catalog table
CREATE TABLE `productos` (
  `id` int(11) NOT NULL,
  `Clave` varchar(55) NOT NULL,
  `Descripción` varchar(255) NOT NULL,
  `Existencias` int(255) NOT NULL,
  `Línea` varchar(50) NOT NULL,
  `Unidad_de_entrada` varchar(10) NOT NULL,
  `Moneda` int(10) NOT NULL,
  `Fecha_ultima_compra` varchar(50) NOT NULL,
  `Ultimo_costo` int(50) NOT NULL,
  `Nombre_de_imagen` varchar(100) NOT NULL,
  `ID_SAE` varchar(255) NOT NULL,
  `Clave_unidad` varchar(50) NOT NULL,
  `Clave_alterna` varchar(255) NOT NULL,
  `Campo_libre` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Activity log table
CREATE TABLE `bitacora` (
  `idBitacora` int(11) NOT NULL,
  `idCuenta` int(4) NOT NULL,
  `Fecha` date NOT NULL,
  `Accion` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Publication tracking table
CREATE TABLE `publicaciones` (
  `idPublicacion` int(11) NOT NULL,
  `idProducto` int(11) NOT NULL,
  `Amazon` int(1) NOT NULL,
  `MercadoLibre` int(1) NOT NULL,
  `WooComerce` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

​
Stored Procedures

The application uses several stored procedures for data validation and manipulation:

​
verificar_clave_primary_producto

Validates if a product key already exists before insertion:
Usage in cliente.js
dbconn.query('call verificar_clave_primary_producto(?)', [archivo[indice]['Clave ']])
  .then(rows => {
    console.log(rows)
    res.status(400).json({message:'clave duplicada, no se puede insertar'})
  })
  .catch(err => {
    // Product key doesn't exist, safe to insert
  })

​
update_productos

Inserts or updates product information from Excel uploads:
Usage in cliente.js
dbconn.query('call update_productos(?,?,?,?,?,?,?)',
  [
    archivo[indice]['Clave '],
    archivo[indice]['Linea '],
    archivo[indice]['Descripción '],
    archivo[indice]['Último costo '],
    '',
    archivo[indice]['Existencias '],
    archivo[indice]['Unidad de entrada ']
  ]
)

​
valida_correo_repetido

Checks if an email already exists in the user table:
Usage in cliente.js
dbconn.query('call valida_correo_repetido(?)', [email])
  .then(rows => {
    const datos = rows[0];
    const correoV = JSON.stringify(datos[0].correo);
    const correoV2 = JSON.stringify(email);
    
    if(correoV2 == correoV){
      res.status(400).json({message:'El usuario ya existe'})
    }
  })
These stored procedures must be created manually as they are not included in the backend.sql schema file. Ensure you create them before running the application.

​
Database Credentials Setup

For production environments, use environment-specific configuration: 
{
  "db": {
    "host": "localhost",
    "db": "tandex_dev",
    "user": "tandex_user",
    "password": "dev_password"
  }
}
The config package automatically loads the appropriate configuration file based on the NODE_ENV environment variable.

​
Connection Pool Benefits

  • Performance: Reuses connections instead of creating new ones for each query
  • Scalability: Handles up to 20 concurrent database operations
  • Reliability: Automatic connection management and error handling
  • Promise-based: Modern async/await compatible API

​
Troubleshooting

  • Verify MySQL is running: sudo systemctl status mysql
  • Check firewall settings allow connections on port 3306
  • Ensure the host address is correct
  • Verify username and password in config/default.json
  • Check user has appropriate database privileges
  • Test connection manually: mysql -h host -u user -p
  • Ensure the database exists: SHOW DATABASES;
  • Check database name spelling in config file
  • Create the database if missing

​
Next Steps

Build docs developers (and LLMs) love

Get started for freeTalk to us