Database Setup

This guide walks you through setting up the MySQL database, understanding the schema, and configuring database connections for the DriveX Backend API.

Prerequisites

MySQL 8.0 or higher
Database user with CREATE, ALTER, INSERT, UPDATE, DELETE privileges
MySQL client or GUI tool (optional)

Quick Start

Create the Database

Create a new MySQL database for DriveX:

CREATE DATABASE DriveX;
USE DriveX;

Configure Connection

Update your application.properties or set environment variables:

spring.datasource.url=jdbc:mysql://localhost:3306/DriveX
spring.datasource.username=root
spring.datasource.password=your_password

The database name in the connection URL should match the database you created.

Start the Application

Start the Spring Boot application. With spring.jpa.hibernate.ddl-auto=update, Hibernate will automatically create the necessary tables based on your JPA entities.

./mvnw spring-boot:run

Database Schema

The DriveX application uses the following core tables:

Vehicles Table

Stores vehicle inventory information:

CREATE TABLE vehicles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    reference VARCHAR(50) NOT NULL,
    brand ENUM('Ferrari', 'Lamborghini', 'Porsche', 'Maserati', 
               'Bentley', 'Toyota', 'Honda', 'BMW', 'Audi', 
               'Mercedes', 'Yamaha', 'Kawasaki', 'Ducati', ...),
    model VARCHAR(20),
    hp INT,
    autonomy INT,
    average_consumption REAL,
    description VARCHAR(200),
    price REAL,
    year INT,
    mileage BIGINT,
    fuel_type VARCHAR(20),
    extras VARCHAR(500),
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

Key Fields:

Field	Type	Description
`id`	INT	Primary key, auto-increment
`reference`	VARCHAR(50)	Unique vehicle reference code (e.g., REF001)
`brand`	ENUM	Vehicle brand from predefined list
`model`	VARCHAR(20)	Vehicle model name
`hp`	INT	Horsepower
`autonomy`	INT	Range/autonomy in kilometers
`average_consumption`	REAL	Average fuel/energy consumption
`price`	REAL	Sale or rental price
`fuel_type`	VARCHAR(20)	Fuel type (Gasolina, Diésel, Eléctrico, Híbrido)

The brand field uses an extensive ENUM containing over 100 vehicle brands including cars, motorcycles, and classic vehicles.

Vehicle Images Table

Manages multiple images per vehicle:

CREATE TABLE vehicle_images (
    id INT PRIMARY KEY AUTO_INCREMENT,
    vehicle_id INT NOT NULL,
    image_url VARCHAR(255) NOT NULL,
    is_main BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
);

Features:

Multiple images per vehicle
is_main flag to mark the primary image
Cascading delete when vehicle is removed

Users Table

Stores user account information:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20),
    email VARCHAR(50),
    password_hash VARCHAR(255),
    first_name VARCHAR(20),
    last_name VARCHAR(50),
    phone_number VARCHAR(15),
    is_active BOOLEAN,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    role VARCHAR(20)
);

Key Fields:

Field	Type	Description
`password_hash`	VARCHAR(255)	Hashed password using BCrypt
`is_active`	BOOLEAN	Account activation status
`role`	VARCHAR(20)	User role (e.g., “Admin”, “User”)

Passwords are stored as BCrypt hashes using Spring Security Crypto. Never store plain-text passwords.

Rentals Table

Tracks vehicle rental reservations:

CREATE TABLE IF NOT EXISTS rentals (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    vehicle_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    price DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'RESERVED',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_rentals_user FOREIGN KEY (user_id) 
        REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_rentals_vehicle FOREIGN KEY (vehicle_id) 
        REFERENCES vehicles(id) ON DELETE CASCADE,
    INDEX idx_rentals_vehicle_dates (vehicle_id, start_date, end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Features:

Date range validation for rental periods
Composite index on vehicle and dates for efficient availability queries
Cascading deletes to maintain referential integrity
Automatic timestamp updates

Transactions Table

Records financial transactions for sales and rentals:

CREATE TABLE IF NOT EXISTS transactions (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    rental_id INT,
    vehicle_id INT,
    buyer_id INT,
    seller_id INT,
    transaction_type VARCHAR(50),
    amount DECIMAL(12,2),
    order_status VARCHAR(50),
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    rental_start_date DATE,
    rental_end_date DATE,
    CONSTRAINT fk_transactions_rental FOREIGN KEY (rental_id) 
        REFERENCES rentals(id) ON DELETE SET NULL,
    INDEX idx_transactions_rental (rental_id),
    INDEX idx_transactions_vehicle (vehicle_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Transaction Types:

Vehicle sales
Rental payments
Deposits and refunds

Order Statuses:

pending - Payment pending
paid - Payment completed
shipped - Vehicle in transit (for sales)
delivered - Transaction completed
cancelled - Transaction cancelled

JPA Configuration

The application uses Spring Data JPA with Hibernate. Configuration in application.properties:

spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect

DDL Auto Options

Option	Behavior	Use Case
`create`	Drop and recreate tables on startup	Initial development
`create-drop`	Create on startup, drop on shutdown	Testing
`update`	Update schema to match entities	Development
`validate`	Only validate schema matches entities	Production
`none`	No schema management	Production with manual migrations

Production Recommendation: Use validate or none in production. Automatic schema updates can cause data loss if entities are modified incorrectly.

Database Connection Pooling

Spring Boot automatically configures HikariCP as the connection pool. Default settings are suitable for most applications. Optional HikariCP Configuration:

# Maximum number of connections in the pool
spring.datasource.hikari.maximum-pool-size=10

# Minimum number of idle connections
spring.datasource.hikari.minimum-idle=5

# Connection timeout (ms)
spring.datasource.hikari.connection-timeout=30000

# Maximum lifetime of a connection (ms)
spring.datasource.hikari.max-lifetime=1800000

Sample Data

The database schema includes sample data for testing. The bs.sql file contains INSERT statements for:

100 vehicles covering luxury cars, standard vehicles, motorcycles, and classic cars
Diverse brands including Ferrari, Lamborghini, Toyota, Yamaha, Ducati, and more
Price range from €3,000 to €2.8M
Various fuel types: Gasoline, Diesel, Electric, Hybrid

Sample data is useful for development and testing but should not be used in production environments.

Database Migrations

For production environments, consider using database migration tools:

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-mysql</artifactId>
</dependency>

Troubleshooting

Connection Issues

Unable to connect to MySQL

Checklist:

Verify MySQL is running: systemctl status mysql or brew services list
Check host and port in connection URL
Verify username and password
Ensure database exists: SHOW DATABASES;
Check firewall rules if connecting remotely

Authentication errors

Solutions:

-- Check user privileges
SHOW GRANTS FOR 'root'@'localhost';

-- Grant necessary permissions
GRANT ALL PRIVILEGES ON DriveX.* TO 'root'@'localhost';
FLUSH PRIVILEGES;

Schema validation errors

If you encounter schema mismatch errors:

Verify spring.jpa.hibernate.ddl-auto setting
Check entity classes match database schema
Clear and regenerate schema if in development:
```
spring.jpa.hibernate.ddl-auto=create
```

Using create will drop all existing data.

Railway MySQL Deployment

The application is configured for Railway’s MySQL service:

spring.datasource.url=jdbc:mysql://mysql.railway.internal:3306/railway
spring.datasource.username=root
spring.datasource.password=${MYSQL_PASSWORD}

Railway automatically provides the internal MySQL hostname and injects credentials via environment variables.

Get Started

Core Concepts

Guides

Prerequisites

Quick Start

Database Schema

Vehicles Table

Vehicle Images Table

Users Table

Rentals Table

Transactions Table

JPA Configuration

DDL Auto Options

Database Connection Pooling

Sample Data

Database Migrations

Troubleshooting

Connection Issues

Railway MySQL Deployment

Next Steps

Configuration

Deployment

Build docs developers (and LLMs) love

Get Started

Core Concepts

Guides

​Prerequisites

​Quick Start

​Database Schema

​Vehicles Table

​Vehicle Images Table

​Users Table

​Rentals Table

​Transactions Table

​JPA Configuration

​DDL Auto Options

​Database Connection Pooling

​Sample Data

​Database Migrations

​Troubleshooting

​Connection Issues

​Railway MySQL Deployment

​Next Steps

Configuration

Deployment

Build docs developers (and LLMs) love

Prerequisites

Quick Start

Database Schema

Vehicles Table

Vehicle Images Table

Users Table

Rentals Table

Transactions Table

JPA Configuration

DDL Auto Options

Database Connection Pooling

Sample Data

Database Migrations

Troubleshooting

Connection Issues

Railway MySQL Deployment

Next Steps