API DBLayer

A high-performance, production-ready API layer for database management. This service provides a RESTful interface to interact with multiple database systems (MySQL and PostgreSQL) with built-in rate limiting, caching, connection management, and comprehensive logging.

Features

Multi-Database Support: PostgreSQL and MySQL connection management
Dynamic API Endpoints: Create custom API endpoints mapped to database queries
Connection Testing: Validate database connections before deployment
Schema Introspection: Retrieve database schema information
Query Execution: Execute SQL queries with proper validation and sanitization
Rate Limiting: Redis-based rate limiting to prevent abuse
Caching: Intelligent caching with cache invalidation support
Request Logging: Comprehensive logging with log rotation
CORS Support: Configurable cross-origin resource sharing
Origin Validation: Whitelist-based origin verification
Subdomain Routing: Multi-tenant support via subdomain routing
Hot Reload: Development mode with automatic code reloading
Health Checks: Built-in health check endpoints
Graceful Shutdown: Proper cleanup on service termination

Architecture

The application follows a clean architecture pattern with clear separation of concerns:

├── cmd/
│   ├── server/          # Application entry point
│   └── internal/        # Internal packages
│       ├── config/      # Configuration management
│       ├── constants/   # Application constants
│       ├── handlers/    # HTTP handlers
│       ├── logger/      # Logging infrastructure
│       ├── middleware/  # HTTP middleware
│       ├── models/      # Data models
│       ├── repository/  # Data access layer
│       ├── services/    # Business logic
│       ├── types/       # Type definitions
│       └── utils/       # Utility functions

Prerequisites

Before you begin, ensure you have the following installed:

Go: Version 1.22.2 or higher
Docker: Version 20.10 or higher
Docker Compose: Version 2.0 or higher (for local development)
PostgreSQL: Version 13 or higher (for the control database)
Redis: Version 6.0 or higher (for caching and rate limiting)
MySQL (optional): If you plan to connect to MySQL databases

Environment Configuration

Create a .env file in the root directory with the following variables:

# Server Configuration
SERVER_PORT=8080
SERVER_READ_TIMEOUT=30
SERVER_WRITE_TIMEOUT=30
SERVER_IDLE_TIMEOUT=120

# PostgreSQL Connection (Control Database)
PG_URL=postgres://username:password@localhost:5432/dblayer?sslmode=disable

# Redis Configuration
REDIS_HOST=localhost
REDIS_PORT=6379
REDIS_PASSWORD=
REDIS_DB=0

# Logging Configuration
LOG_DIRECTORY=logs
LOG_LEVEL=info
LOG_MAX_SIZE=100
LOG_MAX_BACKUPS=10
LOG_MAX_AGE=30

# CORS Configuration
CORS_ALLOW_ORIGINS=*

# Security
SUPER_SECRET_KEY=your-super-secret-key-here
JWT_SECRET=your-jwt-secret-here

# Dashboard (for origin validation)
DASHBOARD_SERVER=localhost

Environment Variables Explained

Variable	Description	Default
`SERVER_PORT`	Port on which the server listens	`8080`
`SERVER_READ_TIMEOUT`	Read timeout in seconds	`30`
`SERVER_WRITE_TIMEOUT`	Write timeout in seconds	`30`
`SERVER_IDLE_TIMEOUT`	Idle timeout in seconds	`120`
`PG_URL`	PostgreSQL connection string for control database	Required
`REDIS_HOST`	Redis server host	Required
`REDIS_PORT`	Redis server port	`6379`
`REDIS_PASSWORD`	Redis password (if any)	Empty
`REDIS_DB`	Redis database number	`0`
`LOG_DIRECTORY`	Directory for log files	`logs`
`LOG_LEVEL`	Logging level (info, warn, error)	`info`
`LOG_MAX_SIZE`	Maximum size of log file in MB	`100`
`LOG_MAX_BACKUPS`	Number of log backups to retain	`10`
`LOG_MAX_AGE`	Maximum age of log files in days	`30`
`CORS_ALLOW_ORIGINS`	Allowed origins for CORS	`*`
`SUPER_SECRET_KEY`	Key for encryption/decryption	Required
`JWT_SECRET`	Secret for JWT token validation	Required
`DASHBOARD_SERVER`	Dashboard server hostname for validation	`localhost`

Installation

Clone the Repository

git clone https://github.com/scorcism/api.dblayer.dev.git
cd api.dblayer.dev

Install Dependencies

go mod download

Set Up Local Databases (Optional)

Use the provided Docker Compose file to spin up local MySQL and PostgreSQL instances:

docker-compose -f docker-compose.dev.yml up -d

This will start:

MySQL on port 3306
PostgreSQL on port 5432

Development

Local Development with Hot Reload

The project uses Air for hot reloading during development.

Using Docker (Recommended)

docker build -f Dockerfile.dev -t api.dblayer:dev .
docker run -p 8080:8080 -v $(pwd):/app api.dblayer:dev

On Windows PowerShell:

docker build -f Dockerfile.dev -t api.dblayer:dev .
docker run -p 8080:8080 -v ${PWD}:/app api.dblayer:dev

Using Local Go Installation

# Install Air
go install github.com/cosmtrek/air@latest

# Run with hot reload
air -c .air.toml

Manual Run

go run cmd/server/main.go

Running Tests

go test ./...

Linting

go vet ./...

Production Deployment

Using Docker

Build the Production Image

docker build -t api.dblayer:latest .

Run the Container

docker run -d \
  -p 8080:8080 \
  --name api.dblayer.container \
  --env-file .env \
  api.dblayer:latest

Using Docker Network

If you need to connect to other containers:

# Create a shared network
docker network create shared_network

# Run the container with network
docker run -d \
  -p 8080:8080 \
  --name api.dblayer.container \
  --network shared_network \
  --env-file .env \
  api.dblayer:latest

Using the Deployment Script

The repository includes a deployment script for easy updates:

sudo chmod +x renew_container.sh
sudo ./renew_container.sh

This script will:

Stop and remove the existing container
Remove the old image
Build a new image
Start a new container
Prune unused Docker resources

Health Check

Verify the service is running:

curl http://localhost:8080/health

Expected response:

{
  "status": "ok"
}

API Endpoints

Health Check

GET /health

Returns the health status of the service.

Dynamic API Endpoint

GET|POST|PUT|DELETE /:projectSlug/:endpointSlug

Execute a pre-configured database query through a dynamic endpoint.

Headers:

x-host: Client hostname (optional)
x-origin: Request origin (optional)
x-referer: Request referer (optional)

API Management Endpoints

All management endpoints are prefixed with /v1.

Test Connection

POST /v1/connection/test

Test a database connection configuration.

Request Body:

{
  "connection_type": "postgres",
  "config": {
    "host": "localhost",
    "port": 5432,
    "database": "mydb",
    "username": "user",
    "password": "pass"
  }
}

Get Schema

POST /v1/connection/schema

Retrieve the schema of a connected database.

Ping Connection

POST /v1/connection/ping

Verify connectivity to a database.

Run Query

POST /v1/endpoint/query-run

Execute a SQL query directly.

Headers:

x-dblayer-runquery-mode: Set to sandbox for testing

Request Body:

{
  "project_slug": "my-project",
  "endpoint_slug": "my-endpoint",
  "query": "SELECT * FROM users WHERE id = $1",
  "params": [1]
}

Revalidate Cache

POST /v1/endpoint/revalidate-cache

Invalidate cached results for an endpoint.

Application Query Execution

GET /v1/app/query-run/:slug/:resource
POST /v1/app/query-run/sandbox

Execute queries from application context.

Subdomain Routing

GET /

The root endpoint with subdomain extraction for multi-tenant support.

Project Structure

.
├── cmd/
│   ├── server/
│   │   └── main.go                  # Application entry point
│   └── internal/
│       ├── config/
│       │   └── env.go               # Environment configuration
│       ├── constants/
│       │   └── constants.go         # Application constants
│       ├── handlers/
│       │   └── api_handler.go       # HTTP request handlers
│       ├── logger/
│       │   └── logger.go            # Logging with rotation
│       ├── middleware/
│       │   └── middleware.go        # HTTP middleware
│       ├── models/
│       │   └── models.go            # Database models
│       ├── repository/
│       │   ├── repository.go        # Repository interface
│       │   ├── mysql.go             # MySQL repository
│       │   └── pg.go                # PostgreSQL repository
│       ├── services/
│       │   ├── services.go          # Business logic
│       │   ├── mysql/
│       │   │   └── main.go          # MySQL service
│       │   ├── pg/
│       │   │   └── main.go          # PostgreSQL service
│       │   └── redis/
│       │       ├── redis.go         # Redis client
│       │       └── rate_limiter.go  # Rate limiting logic
│       ├── types/
│       │   └── types.go             # Type definitions
│       └── utils/
│           ├── utils.go             # Utility functions
│           └── validate.go          # Validation utilities
├── tmp/                              # Temporary build files
├── .air.toml                         # Air configuration
├── docker-compose.dev.yml            # Development Docker Compose
├── Dockerfile                        # Production Dockerfile
├── Dockerfile.dev                    # Development Dockerfile
├── go.mod                            # Go module definition
├── go.sum                            # Go module checksums
├── renew_container.sh                # Deployment script
└── README.md                         # This file

Database Schema

All the schema are generate with drizzle orm, from dashboard.backend.dblayer.dev, Vist me for exploring all the tables.

Security Considerations

Connection String Encryption

Database connection strings are encrypted using AES encryption with the SUPER_SECRET_KEY. Never expose this key.

Origin Validation

The service validates request origins against a whitelist stored in the project configuration. Configure allowed origins in your project settings.

Rate Limiting

Redis-based rate limiting is enforced per endpoint. Configure limits in the endpoint settings.

SQL Injection Prevention

All queries use parameterized statements. Direct query execution requires proper authentication and authorization.

Authentication

JWT-based authentication is supported. Include the JWT token in the Authorization header:

Authorization: Bearer <your-jwt-token>

CORS

Configure CORS settings via the CORS_ALLOW_ORIGINS environment variable. Use specific origins in production instead of *.

Contributing

Contributions are welcome! Please follow these guidelines:

Fork the repository
Create a feature branch (git checkout -b feature/amazing-feature)
Commit your changes (git commit -m 'Add amazing feature')
Push to the branch (git push origin feature/amazing-feature)
Open a Pull Request

Code Style

Follow Go best practices and idioms
Run go fmt before committing
Ensure all tests pass
Add tests for new features
Update documentation as needed

Name		Name	Last commit message	Last commit date
Latest commit History 120 Commits
cmd		cmd
tmp		tmp
.air.toml		.air.toml
.gitignore		.gitignore
Dockerfile		Dockerfile
Dockerfile.dev		Dockerfile.dev
README.md		README.md
docker-compose.dev.yml		docker-compose.dev.yml
go.mod		go.mod
go.sum		go.sum
renew_container.sh		renew_container.sh

Folders and files

Latest commit

History

Repository files navigation

API DBLayer

Table of Contents

Features

Architecture

Prerequisites

Environment Configuration

Environment Variables Explained

Installation

Clone the Repository

Install Dependencies

Set Up Local Databases (Optional)

Development

Local Development with Hot Reload

Using Docker (Recommended)

Using Local Go Installation

Manual Run

Running Tests

Linting

Production Deployment

Using Docker

Build the Production Image

Run the Container

Using Docker Network

Using the Deployment Script

Health Check

API Endpoints

Health Check

Dynamic API Endpoint

API Management Endpoints

Test Connection

Get Schema

Ping Connection

Run Query

Revalidate Cache

Application Query Execution

Subdomain Routing

Project Structure

Database Schema

Security Considerations

Connection String Encryption

Origin Validation

Rate Limiting

SQL Injection Prevention

Authentication

CORS

Contributing

Code Style

About

Resources

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Uh oh!

Contributors

Uh oh!

Languages

Packages