Mariavel Backend - Database Management API

This is the backend API for Mariavel, built with Laravel 11. It provides a secure and robust abstraction layer for interacting with MariaDB/MySQL databases, managing schema metadata, and performing administrative operations.

🛠️ Core Responsibilities

Schema Discovery: Dynamically fetches databases, tables, and column metadata using system catalogs (information_schema).
SQL Execution: Executes raw SQL queries and returns structured data with row counts and execution metrics.
Administrative Operations: Handles bulk table maintenance (Truncate, Drop, Optimize) and technical specifications.
Database Backups: Integrates with system tools like mysqldump to generate full database exports.
Import Management: Processes SQL files and ZIP archives, handling file extraction and sequence execution.
Data Transformation: Maps database-specific types to frontend-friendly formats.

🏗️ Architecture & Structure

The backend follows a service-oriented architecture to ensure separation of concerns and database-agnostic potential:

Controller Layer

app/Http/Controllers/Api/SchemaController.php: Exposes REST endpoints for the frontend, handling request validation and response formatting.

Service Layer (Abstraction)

app/Services/DatabaseService.php: The main entry point for database operations. It orchestrates high-level tasks and manages driver interactions.

Driver Layer (Implementation)

app/Services/Database/MySqlDriver.php: Contains the concrete logic for MySQL/MariaDB. It encapsulates raw SQL generation and system command execution.

📂 Directory Structure

app/
├── Http/
│   └── Controllers/
│       └── Api/            # API Endpoints (SchemaController)
├── Services/
│   ├── Database/           # Driver implementations (MySqlDriver)
│   └── DatabaseService.php # Main database abstraction service
├── Models/                 # Eloquent models (if applicable)
└── Providers/              # Service providers for dependency injection
routes/
└── api.php                 # API route definitions
storage/
└── app/backups/            # Temporary storage for export/import files

🚥 Getting Started

Prerequisites

PHP 8.2+
Composer
MySQL/MariaDB server

Installation

Install dependencies:
```
composer install
```

Configure environment:

cp .env.example .env
php artisan key:generate

Update .env with your database credentials:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=your_database
DB_USERNAME=your_username
DB_PASSWORD=your_password

Run the development server:
```
php artisan serve
```

🔒 Security

All raw SQL execution is handled with caution.
File operations are restricted to specific storage directories.
Ensure the API is protected by authentication in production environments.

3.0 KiB Raw Permalink Blame History