Skip to main content
Sistema de Ventas uses both MySQL and PostgreSQL databases. This guide walks through the complete database setup process.

​
Database Architecture

The system uses database-per-service pattern with:
  • MySQL: 8 databases for most microservices
  • PostgreSQL: 1 database for Cliente service

​
MySQL Databases

ServiceDatabase NamePurpose
AuthjeaauthUser authentication and authorization
CatalogojeacatalogoProduct catalog and categories
ClientejeaclienteCustomer management (uses PostgreSQL)
InventariojesinventarioInventory management
VentajeaventaSales transactions
ComprajeacompraPurchase orders
PagosjeapagosPayment methods and processing
ProveedorjeaproveedorSupplier management

​
MySQL Setup

​
Step 1: Start MySQL Service

sudo systemctl start mysql
sudo systemctl status mysql

​
Step 2: Secure MySQL Installation

sudo mysql_secure_installation
  • Set root password (or keep empty for local development)
  • Remove anonymous users: Yes
  • Disallow root login remotely: Yes (No for development)
  • Remove test database: Yes
  • Reload privilege tables: Yes

​
Step 3: Create Databases Using SQL Script

The project includes an initialization script at sql-scripts/01-init-databases.sql: 
# Navigate to project root
cd /path/to/Sistema-ventas-ms

# Execute initialization script
mysql -u root -p < sql-scripts/01-init-databases.sql
If you’re using MySQL without a password (local development), omit the -p flag:
mysql -u root < sql-scripts/01-init-databases.sql

​
Step 4: Manual Database Creation (Alternative)

If you prefer to create databases manually: 
-- Connect to MySQL
mysql -u root -p

-- Create all databases
CREATE DATABASE IF NOT EXISTS jeaauth CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS jeacatalogo CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS jeacliente CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS jesinventario CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS jeaventa CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS jeacompra CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS jeapagos CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS jeaproveedor CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Verify databases
SHOW DATABASES;

-- Apply privileges
FLUSH PRIVILEGES;

​
Step 5: Load Sample Data

Load example data for testing: 
mysql -u root -p < sql-scripts/02-datos-ejemplo.sql
This script inserts:

Proveedores

5 sample suppliers with RUC, contact info

Formas de Pago

8 payment methods (Efectivo, Transferencia, etc.)

Categorías

5 product categories (Electrónicos, Oficina, etc.)

Productos

10 sample products with pricing

Usuarios

3 test users (admin, vendedor, compras)

​
Step 6: Verify MySQL Setup

mysql -u root -p < sql-scripts/03-verificar-datos.sql
Or manually verify: 
mysql -u root -p

-- Check databases
SHOW DATABASES;

-- Check sample data
USE jeaproveedor;
SELECT * FROM proveedor WHERE estado = true;

USE jeapagos;
SELECT * FROM forma_pago;

USE jeacatalogo;
SELECT p.*, c.nombre as categoria_nombre 
FROM producto p 
JOIN categoria c ON p.categoria_id = c.id 
WHERE p.estado = true;

USE jeaauth;
SELECT username, email, activo FROM usuario WHERE activo = true;

​
PostgreSQL Setup

​
Step 1: Start PostgreSQL Service

sudo systemctl start postgresql
sudo systemctl status postgresql

​
Step 2: Create Cliente Database

# Switch to postgres user
sudo -u postgres psql

-- Create database
CREATE DATABASE "cliente-jea";

-- Create user (optional)
CREATE USER ventasapp WITH PASSWORD '123456';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE "cliente-jea" TO ventasapp;

-- Connect to database
\c cliente-jea

-- Verify
\l
The default configuration uses:
  • Username: postgres
  • Password: 123456
Update these in config-data/jea-cliente-service.yml for production.

​
Connection Configuration

​
MySQL Connection Strings

Services use these connection patterns (from config-data/*.yml): 
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/auth-jea
    username: root
    password: ""
    driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true

​
PostgreSQL Connection String

Cliente Service
spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/cliente-jea
    username: postgres
    password: 123456
    driver-class-name: org.postgresql.Driver
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect

​
Update Database Credentials

1

Navigate to Config Data

cd config-data
2

Edit Service Configuration

Update database credentials in each service’s YAML file:
# Example: Edit auth service config
nano jea-auth-service.yml
3

Update MySQL Password

spring:
  datasource:
    username: root
    password: "your_mysql_password"  # Update this
4

Update PostgreSQL Password

# In jea-cliente-service.yml
spring:
  datasource:
    username: postgres
    password: "your_postgres_password"  # Update this

​
Database Migration Strategy

The system uses JPA auto-DDL with hibernate.ddl-auto: updateTables are created automatically when services start.

​
DDL Configuration

All services use: 
jpa:
  hibernate:
    ddl-auto: update  # Creates/updates tables automatically
  show-sql: true      # Shows SQL queries in logs
For production, consider:
  • Setting ddl-auto: validate or ddl-auto: none
  • Using Flyway or Liquibase for migrations
  • Manual schema management

​
Troubleshooting

Issue: Connection refused errorSolutions:
# Check MySQL is running
sudo systemctl status mysql

# Start MySQL
sudo systemctl start mysql

# Check port 3306 is listening
sudo netstat -tlnp | grep 3306
Issue: Access denied for user 'root'@'localhost'Solutions:
# Reset root password
sudo mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpassword';
FLUSH PRIVILEGES;

# Or allow empty password
ALTER USER 'root'@'localhost' IDENTIFIED BY '';
Issue: password authentication failed for userSolutions:
# Edit pg_hba.conf
sudo nano /etc/postgresql/13/main/pg_hba.conf

# Change from 'peer' to 'md5' for local connections
local   all             all                                     md5

# Restart PostgreSQL
sudo systemctl restart postgresql
Issue: Database creation failsSolutions:
-- Drop existing database
DROP DATABASE IF EXISTS jeaauth;

-- Recreate
CREATE DATABASE jeaauth CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

​
Docker Database Setup (Alternative)

For development, you can use Docker: 
docker run -d \
  --name mysql-ventas \
  -e MYSQL_ROOT_PASSWORD=root123 \
  -e MYSQL_ALLOW_EMPTY_PASSWORD=yes \
  -p 3306:3306 \
  -v mysql-data:/var/lib/mysql \
  mysql:8.0

# Initialize databases
docker exec -i mysql-ventas mysql -u root < sql-scripts/01-init-databases.sql
docker exec -i mysql-ventas mysql -u root < sql-scripts/02-datos-ejemplo.sql

​
Next Steps

Backend Setup

Configure and start Spring Boot microservices

Frontend Setup

Set up Angular frontend application

Build docs developers (and LLMs) love

Get started for freeTalk to us