GATEWAY API

This is a restful api for provide database operations for another apis.

Description

Api gateway consists in a abstraction to avoid direct request in database instead all micro services dispatch a request for api gateway provide database informations and operations. The idea was inspired in elasticsearch however it was developed to use relational databases like mysql, postgresql and others.

Requeriments

You need install this softwares in your computer.

$ node v6.6.0 or higher

$ npm 3.10.3

$ mysql 5.6

$ redis 3.0

$ git client

Docker Installation

I suggest you use docker and docker-composer and then execute this instructions:

$ git clone git@github.com:renandanton/gateway-api.git

$ cd gateway-api

$ docker-compose up -d

that's all, your gateway api is ready for use.

Manual Installation

Clone the repository project:

$ git clone git@github.com:renandanton/gateway-api.git

Enter in project directory,

$ cd gateway-api

And then execute:

$ npm install

Now, change mysql configuration file in config/adpters/mysql.js:

var mysqlOptions = {
  host: 'mysql',
  port: 3306,
  user: 'root',
  password: 'root',
  database: 'my_database'
};

Do same thing in redis configuration file in config/adapters/redis.js

var redisOptions = {
  host: 'redis',
  port: 6379
};

and finally run the web server:

$ npm start

How to use

Api gateway provide routes to access database operations using http verbs based in rest design:

SQL Operation	Verb	Route
QUERY	POST	/cgs/gateway/_search
INSERT	POST	/cgs/gateway/_insert
UPDATE	PUT	/cgs/gateway/_update
DELETE	DELETE	/cgs/gateway/_delete

Insert records in database table via api gateway:

curl -H "Content-Type: application/json" -X POST -d '{"insert": {"table": "users","into": ["name","msisdn","password","email","role_id","language_id"],"values": [["Renan","13213232","ADAFDSFA3923AS232112334",    "renan@teste.com.br",1,1], ["Jessyca","78968965","OI32O34PI23JO3KLIOJ","jessyca@teste.com.br",2,2]  ]}}' http://localhost:3000/cgs/gateway/_insert

Json body data example:

{
    "insert": {
    "table": "table0",
    "into": [
        "column1",
        "column2",
        "column3",
        "column4",
        "column5",
        "column6"
    ],
    "values": [
        [
            "value1",
            "value2",
            "value3",
            "value4",
            1,
            2
        ],
        [
            "value1",
            "value2",
            "value3",
            "value4",
            3,
            2
        ]
    ]
    }
}

SQL Output:

INSERT INTO table0 (column1, column2, column3, column4, column5, column6) VALUES  ("value1","value2","value3","value4",1,2), ("value1","value2","value3","value4",3,2)  

Update records in database table via api gateway:

curl -H "Content-Type: application/json" -X PUT -d  '{"update": {"table": "users","set": {"name": "Renan Danton de souza XXX","msisdn": "13213232","password": "123456789","email": "renandanton@teste.com.br","role_id": 1,"language_id": 2},"where": {"id": 30}}}'  http://localhost:3000/cgs/gateway/_update

Json body data example:

{
    "update": {
        "table": "table0",
        "set": {
            "field1": "value1",
            "field2": "value2",
            "field3": "value3",
            "field4": "value4@teste.com.br",
            "field5": 1,
            "field6": 2
        },
        "where": [
            {
                "id": 21
            }
        ]
    }
}

SQL Output:

UPDATE table0 SET field1="value1", field2="value2", field3="value3", field4="value4@teste.com.br", field5=1, field6=2 WHERE id = 21

Delete records in database table via api gateway:

curl -H "Content-Type: application/json" -X DELETE -d '{"delete": {"from": "users","where": {"id": 47}}}' http://localhost:3000/cgs/gateway/_delete

Json body data example:

{
    "delete": {
        "from": "users",
        "where": [
            {
                "id": 47
            }
        ]
    }
}

SQL Output:

DELETE FROM users WHERE id = 47

Do queries in api gateway:

curl -H "Content-Type: application/json" -X POST -d  '{"query": {"select": ["name","email","created_at","updated_at"], "from": "users","where": {"id": 10}}}' http://localhost:3000/cgs/gateway/_search

Json body data example:

{   
    "query": {
        "select": [
          "name",
          "email",
          "created_at",
          "updated_at"
        ],
        "from": "users",
        "where": [
            {
              "id": 30
            }
        ]
    }
}

SQL Output:

SELECT name, email, created_at, updated_at FROM users WHERE id = 30

• Creating JSQL using where operator equal to:

{
    "where": [
        {
            "name": "myname"
      }
    ]
}

SQL Output:

WHERE name = 'myname'

{
    "where": [
        {
            "$eq": {
                "name": "myname"
            }
        }
    ]
}

SQL Output:

WHERE name = 'myname'

• Creating JSQL using where operator great than:

{
    "where": [
        {
            "$gt": {
                "id": 15
            }
        }  
    ]
}

SQL Output:

WHERE id > 20

• Creating JSQL using where operator great than or equal to:

{
    "where": [
        {
            "$gte": {
                "id": 19
            }
        }  
    ]
}

SQL Output:

WHERE id >= 19

• Creating JSQL using where operator less than:

{
    "where": [
        {
            "$lt": {
                "id": 17
            }
        }  
    ]
}

SQL Output:

WHERE id < 17

• Creating JSQL using where operator less than or equal to:

{
    "where": [
        {
            "$lte": {
                "id": 19
            }
        }  
    ]
}

SQL Output:

WHERE id <= 19

• Creating JSQL using where and operator:

{
    "where": [
        {
            "$eq": {
                "name": "myname"
            }
        },
        {
            "$operator": "And"
        },
        {
            "$eq": {
                "email": "myemail@example.com"
            }
        }
    ]
}

SQL Output:

WHERE name='myname' and email='myemail@example.com'

• Creating JSQL using where or operator:

{
    "where": [
        {
            "$eq": {
                "name": "myname"
            }
        },
        {
            "$operator": "Or"
        },
        {
            "$eq": {
                "name": "yourname"
            }
        }
    ]
}

SQL Output:

WHERE name='myname' or name='yourname'

• Creating JSQL using order by:

{
    "order": {
        "id": "DESC",
        "name": "ASC"
    }
}

SQL Output:

ORDER BY id DESC, name ASC

• Creating JSQL using limit:

{
    "limit": 5
}

SQL Output:

LIMIT 5

• Creating JSQL using where in:

{
    "where": [
      {
          "$in": {
            "id": [20,30]
          }
      }
    ]
}

SQL Output:

WHERE id IN (20,30)

{
    "where": [
      {
          "$in": {
            "name": ["Name1", "Name2"]
          }
      }
    ]
}

SQL Output:

WHERE id IN ("Name1", "Name2")

• Creating JSQL using where not in:

{
    "where": [
      {
          "$nin": {
            "id": [20,30]
          }
      }
    ]
}

SQL Output:

WHERE id NOT IN (20, 30)

{
    "where": [
      {
          "$nin": {
            "name": ["Name1", "Name2"]
          }
      }
    ]
}

SQL Output:

WHERE id NOT IN ("Name1", "Name2")

• Creating JSQL using where between:

{
    "where": [
      {
          "$between": {
            "id": [20,30]
          }
      }
    ]
}

SQL Output:

WHERE id BETWEEN 20 AND 30

• Creating JSQL using where like:

{
    "where": [
        {
            "$like": {
                "name": "%na%"
            }
        }
    ]
}

SQL Output:

WHERE name LIKE "%na%"

• Creating JSQL using inner joins:

{
"query": {
    "select": [
        "u.name as nome",
        "u.msisdn as telefone",
        "p.photo as foto",
        "mt.meta as objetivo"
    ],
    "from": "users as u",
    "joins": [
        {
            "profiles as p": {
                "on": "p.user_id=u.id"
            }
        },
        {
            "metas as mt": {
                "on": "mt.id=p.meta_id"
            }
        }
    ]
  }
}

SQL Output:

SELECT u.name AS nome, u.msisdn AS telefone, p.photo AS foto, mt.meta AS objetivo
FROM users AS u
INNER JOIN  profiles AS p ON p.user_id=u.id
INNER JOIN meta AS mt ON mt.id=p.meta_id

• Creating JSQL using left joins:

{
  "query": {
    "select": [
      "u.name",
    "u.email",
    "u.msisdn"
    ],
    "from": "users as u",
    "leftjoins": [
      {
        "signatures as s": {
          "on": "s.user_id=u.id"
        }
      }   
    ]
  }
}

SQL Output:

SELECT u.name,u.email,u.msisdn FROM users as u LEFT JOIN signatures as s on s.user_id=u.id

• Creating JSQL using right joins:

{
  "query": {
    "select": [
      "u.name",
      "u.email",
      "u.msisdn"
    ],
    "from": "users as u",
    "rightjoins": [
      {
        "signatures as s": {
          "on": "s.user_id=u.id"
        }
      }
    ]
  }
}

SQL Output:

SELECT u.name,u.email,u.msisdn FROM users as u RIGHT JOIN signatures as s on s.user_id=u.id

• Creating JSQL using group by and having:

{
  "query": {
    "select": [
        "r.name as role",
        "COUNT(u.id) as total_role"
    ],
    "from": "users as u",
    "joins": [
        {
            "roles as r": {
                "on": "r.id=u.role_id"
            }
        }
    ],
    "group": [
        "u.role_id"
    ],
    "having": {
        "$gte": {
            "COUNT(u.id)": 6
        }
    }
  }
}

SQL Output:

SELECT r.name AS role, COUNT(u.id) AS total_role,
FROM users AS u
INNER JOIN  roles AS r ON r.id=u.role_id
GROUP BY u.role_id
HAVING COUNT(u.id) >= 6

Testing

If you want run tests in api gateway type this command:

  $ npm test

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/renandanton/gateway-api. This project is intended to be a safe, welcoming space for collaboration, and contributors are expected to adhere to the Contributor Covenant code of conduct.

License

The gem is available as open source under the terms of the MIT License.