MariaDB REST API

Author: Kamil 'Nowik' Nowicki (kamil.nowak85@icloud.com)
Copyright © 2025

License: Apache License 2.0

---

Overview

This project is a simple REST API for a MariaDB database written in Python using FastAPI.

• The API listens on a configurable IP:port (set in config.ini).
• Normal users can only search data in the database.
• Admins can:
  • search data,
  • insert/modify/delete data in tables,
  • create new users (including other admins),
  • create new tables dynamically (with configurable columns and types).

Important: This is a generic example API. It assumes your tables use an integer primary key column named id for update/delete operations.

---

Quick start – step by step

These are the minimal steps to get the API running on a single machine (Linux/Windows) and reach the web UI.

1. Install prerequisites
   1. Install Python 3.9+.
   2. Install MariaDB server and make sure it is running.
2. Get the project files
   1. Clone or download this repository.
   2. Open a terminal and change into the project directory (where main.py and requirements.txt live).
3. Create and activate a virtual environment
   • Linux/macOS (bash/zsh):

     python3 -m venv .venv
     source .venv/bin/activate

   • Windows (PowerShell):

     python -m venv .venv
     .\.venv\Scripts\Activate.ps1

4. Install Python dependencies

   pip install -r requirements.txt

   This installs FastAPI, Uvicorn, MariaDB driver, Passlib (with bcrypt) and pins bcrypt==4.3.0.
5. Run the first-time setup wizard
   1. Make sure MariaDB is running and you know the host/port/user/password.
   2. From the project directory (venv active), run:

      python main.py

   3. If config.ini is missing or invalid, the wizard will ask you for:
      • MariaDB host, port, user, password, database name,
      • and will create required tables and an initial admin user.
6. Start the API server
   • Easiest: keep using:

     python main.py

     This reads host/port from config.ini.
   • Or using uvicorn directly (example):

     uvicorn main:app --host 0.0.0.0 --port 8000

7. Open the web UI in a browser
   • Go to: http://localhost:8000/app (or replace localhost:8000 with your host/port).
   • Log in with the admin credentials created in the wizard.
   • From there you can:
     • search tables,
     • manage users,
     • create new tables and columns,
     • insert/update/delete rows (admin only).

For more detailed information, see the sections below.

---

Table of contents

• Overview
• Quick start – step by step
• Requirements
• Installation
• Database preparation
• Configuration (config.ini)
• Running the API
• Project structure (modular layout)
• Web frontend
• API Usage
  • Authentication
  • Dynamic table creation (admin)
  • Roles
  • Example endpoints
• Security Notes
• License

Requirements

• Python 3.9 or newer (recommended)
• MariaDB server (local or remote)
• Ability to install Python packages with pip

---

Installation

1. Clone or copy the project

Place all files in a directory (for example this one: db/api).

2. Create a virtual environment (recommended)

On Linux (bash, zsh)

python3 -m venv .venv
source .venv/bin/activate

On Windows (PowerShell)

python -m venv .venv
.\.venv\Scripts\Activate.ps1

3. Install dependencies

With the virtual environment activated:

pip install -r requirements.txt

---

Database preparation

Option A – Use the built-in setup wizard (recommended)

If config.ini does not contain valid database credentials and you have not set DB_HOST / DB_USER / DB_PASSWORD / DB_NAME environment variables, running

python main.py

will start an interactive first-time setup wizard:

• asks for MariaDB host, port, user, password and database name,
• connects to MariaDB and creates the database if it does not exist,
• creates required tables:
  • users – stores API users and their hashed passwords,
  • audit_log – records admin operations,
• asks you to create an initial admin user (username + password),
• writes everything into config.ini (and generates a random secret_key if missing).

After the wizard finishes, you can start the API normally and log in with the admin user you just created.

Option B – Manual database setup

If you prefer to create the schema manually, you need a MariaDB database and a users table for authentication.

Example SQL (adjust as needed):

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL
);

CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    actor VARCHAR(255) NOT NULL,
    action VARCHAR(100) NOT NULL,
    table_name VARCHAR(255) NOT NULL,
    row_id INT NULL,
    details TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

1. Create the database (if not already existing):

CREATE DATABASE your_database_name_here CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. Create a user with permissions on that database (example):

CREATE USER 'api_user'@'%' IDENTIFIED BY 'your_strong_password';
GRANT ALL PRIVILEGES ON your_database_name_here.* TO 'api_user'@'%';
FLUSH PRIVILEGES;

3. Insert an initial admin user (you can either use the API to create it or insert a row manually with a bcrypt password hash).

---

Configuration (config.ini)

Edit config.ini before running the API:

[server]
host = 0.0.0.0
port = 8000

[database]
host = localhost
port = 3306
user = api_user
password = your_strong_password
database = your_database_name_here

[security]
secret_key = CHANGE_ME_SECRET_KEY
access_token_expire_minutes = 60

• host and port under [server] define the IP and port the API will listen on.
  • Use 0.0.0.0 to listen on all interfaces (necessary when the API is on a server and accessed from other machines).
• Under [database], set the correct MariaDB connection values.
• Under [security], set a strong, random secret_key for JWT tokens.

Security tip: You can avoid storing DB credentials in config.ini by using environment variables. The app will use these if set: DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_NAME.

---

Running the API

First-time run with setup wizard

If config.ini does not yet contain valid DB credentials, run:

python main.py

The wizard will guide you through configuring the DB connection and creating the initial admin. After that, you can run the API as a service or via uvicorn.

On Linux

With your virtual environment active and inside the project directory:

uvicorn main:app --host 0.0.0.0 --port 8000

Or to use the host/port from config.ini, you can run the module directly:

python main.py

On Windows

In PowerShell (after activating the virtual environment and going to the project directory):

uvicorn main:app --host 0.0.0.0 --port 8000

Or:

python main.py

Make sure appropriate firewall rules allow incoming connections on the configured port.

---

Project structure (modular layout)

The project is split into multiple modules for easier maintenance:

• main.py – entry point that configures the app and starts Uvicorn.
• db_utils.py – database connection helpers, schema validation, and audit logging.
• static/ – browser-based HTML+JS frontend.
• config.ini – server, database, security, and logging configuration.
• README.md, LICENSE, requirements.txt – documentation and metadata.

---

Web frontend

Once running, you can use the built-in web interface in a browser:

• http://<server-ip>:<port>/app – simple HTML+JS frontend for users and admins.

API Usage

The API root endpoint will be available at:

• http://<server-ip>:<port>/

The interactive API documentation (Swagger UI) is available at:

• http://<server-ip>:<port>/docs

Authentication

1. First, obtain a token via /login.

• Endpoint: POST /login
• Form data (x-www-form-urlencoded):
  • username
  • password

Response:

{
  "access_token": "<JWT_TOKEN>",
  "token_type": "bearer"
}

Include the token in the Authorization header as Bearer <JWT_TOKEN> for all subsequent requests.

---

Dynamic table creation (admin)

Admins can create new tables at runtime, either through the web UI or via REST.

Via web UI

1. Log in as an admin at http://<server-ip>:<port>/app.
2. In the right-hand Admin panel, scroll to Create table.
3. Fill in:
   • Table name – e.g. contacts, orders_2025 (letters/digits/_, not starting with a digit).
   • One or more column rows:
     • column name – name of the column (cannot be id, reserved for primary key).
     • type – logical type, mapped to MariaDB:
       • string → VARCHAR(length or 255)
       • text → TEXT
       • int → INT
       • bigint → BIGINT
       • float → DOUBLE
       • bool → TINYINT(1)
       • datetime→ DATETIME
       • date → DATE
       • time → TIME
     • length (for text) – optional length for string columns.
     • nullable – allow NULL values.
     • unique – add a UNIQUE constraint to the column.
     • default value – optional default value, interpreted based on type.
4. Click Create table.
5. On success, the new table appears in the Tables list and can be queried/edited like any other table.

Each created table automatically gets an id INT AUTO_INCREMENT PRIMARY KEY column so it works with the existing /tables/{table}/{row_id} endpoints.

Via REST API

• Endpoint: POST /admin/tables
• Auth: admin Bearer token
• Body (JSON):

{
  "table": "orders",
  "columns": [
    { "name": "order_no", "type": "bigint", "nullable": false, "unique": true },
    { "name": "customer_name", "type": "string", "nullable": false, "length": 200 },
    { "name": "status", "type": "string", "nullable": false, "length": 32, "default": "pending" },
    { "name": "total", "type": "float", "nullable": false, "default": "0" },
    { "name": "is_paid", "type": "bool", "nullable": false, "default": "false" },
    { "name": "order_date", "type": "date", "nullable": false },
    { "name": "order_time", "type": "time", "nullable": true },
    { "name": "created_at", "type": "datetime", "nullable": false }
  ]
}

The API validates names, types, lengths, uniqueness and default values, and will return 400 with a clear error message if something is invalid or the table already exists.

---

Roles

• User (role = "user"):
  • can call: GET /tables, GET /tables/{table_name} (search only).
• Admin (role = "admin"):
  • can call everything a user can,
  • plus admin endpoints and write operations:
    • POST /admin/users – create new user or admin,
    • GET /admin/users – list existing users,
    • POST /tables/{table_name} – insert new row,
    • PUT /tables/{table_name}/{row_id} – update row by id,
    • DELETE /tables/{table_name}/{row_id} – delete row by id.

---

Example endpoints

List tables

• Endpoint: GET /tables
• Auth: Bearer token (user or admin)

Search table

• Endpoint: GET /tables/{table_name}
• Query parameters:
  • column (optional) – column to filter by
  • value (optional) – value for equality filter
  • limit (optional, default 100, max 1000)

Examples:

• GET /tables/customers – returns first 100 rows from customers
• GET /tables/customers?column=last_name&value=Smith&limit=50

Insert row (admin only)

• Endpoint: POST /tables/{table_name}
• Body (JSON):

{
  "data": {
    "column1": "value1",
    "column2": 123
  }
}

Update row (admin only)

• Endpoint: PUT /tables/{table_name}/{row_id}
• Body (JSON): same as for insert.

Delete row (admin only)

• Endpoint: DELETE /tables/{table_name}/{row_id}

---

Security Notes

• Always use HTTPS in production (behind a reverse proxy like Nginx or Apache).
• Keep your SECRET_KEY confidential and strong.
• Restrict database user privileges according to the principle of least privilege.

---

License

This project is licensed under the Apache License, Version 2.0 – see the LICENSE file for details.