Getting Started

Introduction

Apisentris is a platform that turns your any database schemas into RESTful API.

Custom API servers suffer problems. Writing business logic often duplicates, ignores or hobbles database structure. Object-relational mapping is a leaky abstraction leading to slow imperative code. Apisentris philosophy establishes a single declarative source of truth: the data itself.

Configuration

MySQL Connection

Setup MySQL for remote access on Ubuntu server

For mysql version 5.6 and below

Uncommented in /etc/mysql/my.cnf and assigned to your IP address then restart mysql.

        
sudo service mysql restart
        
      

For mysql version 5.7 and above

Uncommented in /etc/mysql/mysql.conf.d/mysqld.cnf and assigned to your IP address

        
#Replace xxx with your IP Address
bind-address = xxx.xxx.xxx.xxx
        
      

Or add a bind-address = 0.0.0.0 if you don't want to specify the IP.

Then stop and restart MySQL with the new config entry.

        
sudo service mysql restart
        
      

Once running go to the terminal and enter the following command.

        
lsof -i -P | grep :3306
        
      

That should come back something like this with your actual IP in the xxx's

        
mysqld  1046  mysql  10u  IPv4  5203  0t0  TCP  xxx.xxx.xxx.xxx:3306 (LISTEN)
        
      

If the above statement returns correctly you will then be able to accept remote users. However for a remote user to connect with the correct priveleges you need to have that user created in both the localhost and '%' as in.

        
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'username'@'172.105.121.64' IDENTIFIED BY 'password';
        
      

then,

        
GRANT ALL ON *.* TO 'username'@'localhost';
GRANT ALL ON *.* TO 'username'@'172.105.121.64';
        
      

and finally,

        
FLUSH PRIVILEGES;
EXIT;
        
      

Setup MySQL for remote access on webhosting

Go to cPanel and access Remote MySQL

Add our IP as whitelisted 172.105.121.64 and save.

PostgreSQL Connection

When connecting a Postgres database, you have to modify the pg_hba.conf file to accept connections from our IPs. You can read this doc about the pg_hba.conf file in general: http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

An example of a possible lines to add in the pg_hba.conf file is the following:

#TYPE   DATABASE    USER           CIDR-ADDRESS           METHOD

host    $db_name     $userName      35.221.156.87        md5

host    $db_name     $userName      35.221.156.87        md5

host    $db_name     $userName      35.221.156.87        md5

Please note that the database and user values should be adapted to your database. The method to use can vary according to your database.

MongoDB Connection

MongoDB requires a Connection String and Database Name to connect to the APIs. If you're MongoDB Atlas user, you can find MongoDB connection string on your dashboard page by following this documentation.

Connection string usually can be found in this format:

            
mongodb+srv://<username>:<password>@<host>
// or
mongodb://<username>:<password>@<host>
            
          

NOTE:

When entering your password, make sure that any special characters are URL encoded.

If you have MongoDB running on your own server (linux), please follow this step so your MongoDB will be accessible remotely.

Setup User

First ssh into your server and enter the mongo shell by typing mongo. For this example, I will set up a user named absyah and give that user read & write access to the demo_db database.

            
use demo_db

db.createUser({
    user: 'absyah',
    pwd: 'mysecretpwd',
    roles: [{ role: 'readWrite', db:'demo_db'}]
})
            
          

Enable auth and open MongoDB access up to all IPs

Edit your MongoDB config file. On Ubuntu:

            
sudo vim /etc/mongod.conf
            
          

Look for the net line and comment out the bindIp line under it, which is currently limiting MongoDB connections to localhost:

NOTE:

Warning: do not comment out the bindIp line without enabling authorization. Otherwise you will be opening up the whole internet to have full admin access to all mongo databases on your MongoDB server!

            
# network interfaces
net:
  port: 27017
#  bindIp: 127.0.0.1  <- comment out this line
            
          

Scroll down to the #security: section and add the following line. Make sure to un-comment the security: line.

            
security:
  authorization: 'enabled'
            
          

Open port 27017 on your instance

Restart mongo daemon (mongod)

            
sudo service mongod restart
            
          

Make sure you can still log in with mongo while ssh’d into the box.

If anything goes wrong, look at the log: tail -f /var/log/mongodb/mongod.log (note: non-Ubuntu machines will keep the log in another directory…)

Input your connection string to Apisentris

Your connection string will be: mongodb://absyah:[email protected], change 123.45.67.89 to your server's public IP. Default MongoDB port is 27017

Google BigQuery Connection

Google Cloud requires a Project ID and Service Account Credentials to connect to the APIs. You will use the Project ID and JSON key file to connect to most services with google-cloud-storage.

If you are not running this client on Google Compute Engine, you need a Google Developers service account.

  1. Visit the Google Developers Console.

  2. Create a new project or click on an existing project.

  3. Activate the slide-out navigation tray and select API Manager. From here, you will enable the APIs that your application requires.

    Enable the APIs that your application requires

    Note: You may need to enable billing in order to use these services.

  4. Select Credentials from the side navigation.

    You should see a screen like one of the following.

    Create a new service account

    Create a new service account With Existing Keys

    Find the "Add credentials" drop down and select "Service account" to be guided through downloading a new JSON key file.

    If you want to re-use an existing service account, you can easily generate a new key file. Just select the account you wish to re-use, and click "Generate new JSON key":

    Re-use an existing service account

    The key file you download will be used by Apisentris to authenticate API requests.

Authentication

As the development of security measures, many APIs are content with just having an API key or username passed in as a parameter when an endpoint is called. The danger with that scheme is that the API token might be stolen for abuse.

Apisentris has authentication method which is combination of basic authentication and whitelisting client references to keep API authentication simplicity without sacrificing the security.

Basic Authentication

Each connection has its own client id and access token. Simply those identifier should be included as request header in every request.

Header Data Type
client_id Integer
access_token String
Content-Type String. Default: application/json

Whitelist Client

This method will ensure whether clients has privilege to make a request to the APIs or not. Just keep in mind, if there is no whitelisted clients so it will be considered all incoming requests will be granted to receive the API responses.

Go to the connection details page then add whitelist. It is accepting both IP addresse and Domain Name Server. Nothing to do with your API request after that, Apisentris will take care everything.

Get Resources

All tables in the active schema are available for querying. They are exposed in one-level deep routes. For instance the full contents of a table articles is returned at:

GET api/v1/articles


Response:

        
[
  {
    "id":1,
    "title":"Some Title",
    "content": "Long text description."
  },
  {
    "id":2,
    "title":"Some Another Title",
    "content": "Long text description."
  }

]
        
      

Ordering

The reserved word order_by reorders the response rows.

GET api/v1/people?order_by=age

It uses a comma-separated list of columns and directions.

GET api/v1/people?order_by=age.desc,height.asc

Limit and Pagination

Apisentris provides a limit and offset clause that is used to specify the number of records to return. The limit and offset clause makes it easy to code multi page results or pagination response, and is very useful on large tables. Returning a large number of records can impact on performance.

Assume we wish to select all records from 1 - 30 (inclusive) from a table called articles. The request would then look like this.

GET api/v1/articles?limit=30&offset=0

Filtering

You can filter result rows by adding conditions on columns, each condition is a query string parameter. For instance, to return people aged under 13 years old:

GET api/v1/people?age=lt.13

Multiple parameters can be logically conjoined by:

GET api/v1/people?age=lt.13&gender=eq.male

These clauses are available:

Abbreviation Meaning
eq equals
gt greater than
gte greater than or equal
lt less than
lte less than or equal
neq not equal
like LIKE operator (use * in place of %)
in on of list of values e.g. ?age=in.(12,13,14)

Selecting

When certain columns are wide (such as those holding binary data), it is more efficient for the server to withhold them in a response. The client can specify which columns are required using the select parameter.

GET api/v1/users?select=firstname,lastname,email

Response:

        
[
  {
    "firstname":"John",
    "lastname": "Doe"
    "email": "[email protected]"
  },
  {
    "firstname":"John",
    "lastname":"Lark",
    "email": "[email protected]"
  }

]
        
      

Association

In Apisentris, an association is a connection between two tables that make common operations simpler and easier.

Given a user (with id 1) has many books, so the endpoint will be:

GET api/v1/users/1/books

Response:

      
[
  {
    "id":1,
    "title":"Book",
    "user_id": 1
  },
  {
    "id":2,
    "title":"Book 2",
    "user_id": 1
  },
]
      
    

In the example above, books's foreign key should be user_id. But you can specify foreign key in fk query string if it has custom column name.

GET api/v1/users/1/books?fk=myuser_id

NOTE:

Limit, pagination, ordering, and filtering are applicable in this endpoint.

Embedded Resources

In addition to providing RESTful routes for each table, Apisentris allows related resources to be included together in a single API call. This reduces the need for multiple API requests. The server uses foreign keys to determine which tables and views can be returned together. For example, consider a database of books with their authors and their user - has many authors (plural) and belongs to user (singular).

GET api/v1/users/1/books?has_many=authors&belongs_to=user

Response:

      
[
  {
    "id":1,
    "title":"Book",
    "user_id": 1,
    "user": {
      "name": "Tony Doe",
      "email": "[email protected]"
    },
    "authors": [
      {"name": "Deni"}
    ]
  },
  {
    "id":2,
    "title":"Book 2",
    "user_id": 2,
    "user": {
      "name": "John Doe",
      "email": "[email protected]"
    },
    "authors": [
      {"name": "Zoro"}, {name: "Dito"}
    ]
  },
]
      
    

You can filter embedded query result rows by adding conditions on columns, each condition is a query string parameter. Multiple query strings separated by comma.

GET api/v1/users/1/books?has_many=authors(name=*like*.deni,active=eq.1)

NOTE:

All supported filters are applicable to embedded resources which are mentioned on these filtering, limit and pagination, and ordering section

Embedded Filtering

Prefix the query parameters with the name of the embedded resource.For instance, to select all users that having "Harry Potter" book:

GET api/v1/people?books.name=like.*harry*

We can combining the endpoint with other query string as well:

GET api/v1/people?has_many=books&books.name=like.*harry*&limit=2&offset=0&name=like.*john*

Response:

      
[
  {
    "id":1,
    "name":"John Doe",
    "books": [
      {"name": "Harry Potter and the Chamber of Secret"},
      {"name": "Lord of the Ring"}
    ]
  },
  {
    "id":11,
    "name":"John Cena",
    "books": [
      {"name": "Harry Potter and the Goblet of Fire"},
      {"name": "The Lean Startup"},
      {"name": "Rich Dad Poor Dad"}
    ]
  },
]
      
    

Custom Query

The Apisentris grammar limits the kinds of queries clients can perform. It prevents arbitrary, potentially poorly constructed and slow client queries. The most common causes for custom endpoints are:

  • Table unions
  • More complicated joins than those provided by Resource Embedding
  • Geo-spatial queries that require an argument
  • Sophisticated full-text search



POST api/v1/query


Request Body:

      
{
  "query": "SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name"
}
      
    

Request Body (for MongoDB):

      
{
  "query": {
    "collection": "demo_db", // collection name
    "operator": "text_search", // operator, see table below
    "command": { // query
      "$text": {
        "$search" : "ardian"
      }
    }
  }
}
      
    


These operators are available for MongoDB custom query:

Operator Similar to
create_index db.collection.createIndex
text_search db.collection.find with $text query
geospatial_search db.collection.find with Geospatial Query Operators
aggregate db.collection.aggregate

Get Resource Details

Apisentris uses RESTful as its architectural style and communication approach. Primary key should be defined in the table as resource identifier.

GET api/v1/articles/1


It will return single JSON object for the convenience of your client side. e.g.

        
{
  "id": 1,
  "name":"My First Article",
  "content":"Long text content",
  "published":true
}
        
      

Creating Resource

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.

POST api/v1/articles


Request Body:

        
{
   "resource": {
        "title":"Some Title",
        "content": "Long text description."
   }
}
        
      

Response:

        
{
  "id":1,
  "title":"Some Title",
  "content": "Long text description."
}
        
      

NOTE:

JSON object should be inside resource key.

Updating Resource

To update a row in a table, use the PATCH verb and RESTful uri followed with table's primary key.

PATCH api/v1/articles/1


Request Body:

        
{
   "resource": {
        "title":"Updated Title",
        "content": "Updated content."
   }
}
        
      

Response:

        
{
  "id":1,
  "title":"Updated Title",
  "content": "Updated content."
}
        
      

NOTE:

JSON object should be inside resource key.

Deleting Resource

To delete row in a table, use the DELETE verb and RESTful uri followed with table's primary key.

DELETE api/v1/articles/1

User Auth

An external service can provide user management and coordinate with the Apisentris using JWT. It’s also possible to support login and user signup entirely through Apisentris.

Register

POST api/v1/auth

Request Body:

        
{
   "auth": {
        "email":"[email protected]",
        "password":"somepassword",
        "password_confirmation":"somepassword",
   }
}
        
      

Success Response:

        
{
  "id":1,
  "email":"[email protected]"
}
        
      

Error Response:

        
{
    "password_confirmation": [
        "doesn't match Password"
    ],
    "email": [
        "has already been taken"
    ]
}
        
      

Login

POST api/v1/auth

Request Body:

        
{
   "auth": {
        "email":"useremai[email protected]",
        "password":"somepassword"
   }
}
        
      

Success Response:

        

{
  "jwt": "jwt_token_with_payload"
}
        
      

Error Response:

Simply it will return 404 error code in the response



NOTE:

JWT will be expired in 1 day.

Permission

Recall from the authentication request should has headers with the connection credentials.

To give your user permission to access your resource, you must include Authorization header with Bearer followed with generated jwt as its value.

Example Authorization header value:

Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJleHAiOjE1Mzc0OTAzNTIsImlkI
jo2LCJlbWFpbCI6InVzZXJlbWFpbEBkb21haW4uY29tIiwiY29ubmVjdGlvbl9pZCI6MTd9.7cr1KpA0CeOjiRRi4NwDWSfHCQDGqtdt5CVy1tlN_Ew

Complete header should be:

Header Data Type
Authorization JWT String
client_id Integer
access_token String
Content-Type String. Default: application/json

Update User

PATCH api/v1/auth/:id

Request Body:

        
{
   "auth": {
        "email":"[email protected]",
        "password":"update_somepassword",
        "password_confirmation":"update_somepassword",
   }
}
        
      

Success Response:

        
{
  "id":1,
  "email":"[email protected]"
}
        
      

Error Response:

        
{
    "password_confirmation": [
        "doesn't match Password"
    ],
    "email": [
        "has already been taken"
    ]
}
        
      

Delete User

DELETE api/v1/auth/:id

Success Response: http code 200