NOTE:
When entering your password, make sure that any special characters are URL encoded.
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.
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;
Go to cPanel and access Remote MySQL
Add our IP as whitelisted 172.105.121.64 and save.
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 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>
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.
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'}]
})
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:
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'
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…)
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 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.
Visit the Google Developers Console.
Create a new project or click on an existing project.
Activate the slide-out navigation tray and select API Manager. From here, you will enable the APIs that your application requires.
Note: You may need to enable billing in order to use these services.
Select Credentials from the side navigation.
You should see a screen like one of the following.
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":
The key file you download will be used by Apisentris to authenticate API requests.
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.
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 |
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.
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."
}
]
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
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
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) |
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]"
}
]
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
Limit, pagination, ordering, and filtering are applicable in this endpoint.
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)
All supported filters are applicable to embedded resources which are mentioned on these filtering, limit and pagination, and ordering section
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"}
]
},
]
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:
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 |
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
}
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."
}
JSON object should be inside resource
key.
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."
}
JSON object should be inside resource
key.
To delete row in a table, use the DELETE verb and RESTful uri followed with table's primary key.
DELETE
api/v1/articles/1
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.
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"
]
}
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
JWT will be expired in 1 day.
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 |
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
api/v1/auth/:id
Success Response: http code 200