The Pain of Backend Development
If you've ever built a mobile or web app that interacts with a database, you know the struggle—designing API endpoints, handling authentication, writing business logic, and ensuring scalability. Sometimes, all you need is a simple way to query the database and retrieve structured JSON responses without going through the entire process of backend development.
That’s exactly why many years ago I built the Database Gateway API—a solution that allows developers to interact directly with PostgreSQL and MSSQL databases via a secure API, without writing a full-fledged backend.
The Idea Behind Database Gateway API
The goal was simple: Why should you have to build an entire backend when all you need is an API for your database?
I needed a lightweight yet powerful solution that could:
- Eliminate the need for a dedicated backend in simple applications.
- Enable mobile and web apps to access database data seamlessly.
- Provide an easy way to deploy APIs for database interactions with minimal setup.
- Integrate with any existing system, whether it’s an enterprise accounting platform or a retail point-of-sale (POS) system.
This led to the creation of the Database Gateway API—a framework-agnostic API layer that acts as a bridge between your database and applications.
How It Works
The Database Gateway API is a standalone service that connects to your PostgreSQL or MSSQL database and exposes SQL queries as API endpoints. It takes care of request parsing, security, and response formatting, allowing you to focus on building your application instead of writing backend logic.
Key Features:
✅ Direct SQL Query Execution: Supports SELECT, INSERT, UPDATE, and DELETE operations via API requests.
✅ Automatic JSON Responses: Converts database query results into well-structured JSON.
✅ Easy Integration with Mobile & Web Apps: No need for a complex backend—just plug it into your frontend.
✅ Secure & Configurable: Set up role-based access, API keys, and rate limiting.
✅ Supports Complex Queries & Joins: Fetch relational data easily, just like using SQL directly.
✅ Minimal Deployment Overhead: Run it as a standalone service or containerized in Docker.
Real-World Impact: 29 Deployments & 150+ Devices
This API has been successfully deployed across 29 businesses, powering over 150 devices, primarily in shops and malls. It integrates seamlessly with mobile apps and existing accounting systems, allowing businesses to:
- Sync sales data in real-time, eliminating manual record-keeping.
- Improve customer experience by linking mobile apps to inventory and POS systems.
- Enable effortless data exchange between different applications without writing additional backend logic.
Why You Should Use It
If you’re a developer building a mobile app, web dashboard, or prototype, the Database Gateway API can save you weeks of development time by handling database queries and responses automatically.
Instead of spinning up a full backend, setting up ORM models, and writing CRUD endpoints, you simply install the gateway, configure it with your database, and start making API requests.
✅ Instant API – Set up in minutes and start making SQL queries right away.
✅ Effortless Integration – Works with mobile, web, and desktop apps.
✅ JSON-Formatted Responses – Your queries return well-structured JSON, ready to use.
✅ Perfect for Prototyping – Quickly test database interactions without a full backend.
✅ Optimized for Performance – Execute fast queries with minimal setup.
🚀 How It Works
🔍 Querying Your Database
Need to fetch data? Just send a POST request with your SQL query:
POST 127.0.0.1:8000/api/v1/make-db-request
{
"query_string": "SELECT * FROM tbl_mg_materials",
"base64_columns": ["group_code", "image_pict", "firm_id_guid"]
}
Pro tip: Use base64_columns to encode image BLOBs or sensitive data!
📊 The Response
The API returns a structured JSON response:
{
"data": [...],
"status": 1,
"total": 2,
"message": "db query result"
}
⚠️ Important Considerations
🔴 Security First! – This API executes raw SQL, so make sure to restrict access and validate inputs.
🔴 Database Changes? – Schema updates might require adjustments to your API queries.
🔴 Use with Caution – Best for internal tools, rapid prototyping, and trusted environments.
Want to try it out? The Database Gateway API is open-source and available for anyone to use and contribute to. You can integrate it into your next project and cut down on backend development time significantly.
Check out the repository: [Db-Gateway-Go]
Let me know if you have questions or ideas for improvements—happy coding! 🚀
Top comments (0)