Convert Your Database to RESTful API Without Code
Tags: database, webdev, mysql, postgres
Published: 2021-02-13 18:37:23

Creating an API for a database is one of the most headache part of any application. Today, I'll introduce a platform allows you to querying your database instantly, through an API with the most essential operations you may think of.

[dbSauce](https://dbsauce.com/) is a platform empowers you with an essential tool that adds a REST API to a MySQL/MariaDB, PostgreSQL, or SQL Server to supercharge your building apps workflow.

We're going to be using [dbSauce](https://dbsauce.com/) for converting our database to an API. First thing you need to do is having an account, so, if you don't already have an account, go ahead and [sign up for a free account here](https://app.dbsauce.com/register).

Once you have your dbSauce account, go ahead and add a new connection in the dashboard. A <code>Connection</code> is an entity that represents a data source for our API, to be capable of accepting and responding to requests made by clients.

## Requirements
* An account, and you can get started with free 14-day trial.
* Database with remote access capability.

## API Features
* Transform the complex query statement into a simple HTTP request.
* Inputs validation using a wide variety of convenient validation rules.
* Authentication integration with [Auth0 API](http://auth0.com/).
* Supports a JSON object as input.
* Supports a JSON array as input (batch operations).
* Supports POST variables as input (x-www-form-urlencoded).
* File uploads are supported through the FileReader API.
* Support both JSON and XML for output.
* Validate inputs 
* Support for reading joined results from multiple tables.
* Search support on multiple criteria.
* Pagination, sorting, and column selection.
* Relationships detection with nested results.
* Binary fields supported with base64 encoding.
* Spatial/GIS fields and filters supported with WKT and GeoJSON.
* Support for reading database structure in JSON.
* Support for modifying database structure using REST endpoint.
* Support for execute custom SQL query directly.

## Discovering API
For any developer, CRUD operations are one of the most basic term, so let's find out, how to use dbSauce API to perform the CRUD operations.

For instance, let's say we have a blog, and our blog posts saved in a table called <code>posts</code>.

### Create Post
To create a row in a database table post a JSON object whose keys are the names of the columns you would like to create. Missing properties will be set to default values when applicable.

```shell
POST: /db/posts
```

#### Request

```json
{
  "title": "Hello, world",
  "content": "This is a test post!",
  "author_id": 1
}
```

#### Response

```json
{
  "success": true,
  "data": {
    "id": 10,
    "title": "Hello, world",
    "content": "This is a test post!",
    "author_id": 1
  }
}
```

## Read Post

To read a specific record from a table, the request will be formed as:

```shell
GET: /db/posts/10
```

Where <code>10</code> is the value of the primary key of the record that you want to read.

#### Response

```json
{
  "success": true,
  "data": {
    "id": 10,
    "title": "Hello, world",
    "content": "This is a test post!",
    "author_id": 1
  }
}
```

## Update Post

To update a specific record in a table, the request will be formed as:

```shell
PUT: /db/posts/10
```

Where <code>10</code> is the value of the primary key of the record that you want to update.

#### Request

```json
{
  "content": "Hello, dbSauce!"
}
```

#### Response

```json
{
  "success": true,
  "data": {
    "id": 10,
    "title": "Hello, world",
    "content": "Hello, dbSauce!",
    "author_id": 1
  }
}
```

## Delete Post

If you want to delete a specific record from a table, the request will be formed as:

```shell
DELETE: /db/posts/10
```

Where <code>10</code> is the value of the primary key of the record that you want to delete.

#### Response

```json
1
```

<code>1</code> is the number of deleted rows.

To find out more about what the API offers, check out the [documentations](https://docs.dbsauce.com/).

**Regards**