Access Database Using REST APIs


There are a number of services that have come onto the market recently that enable developers to connect an application to a remote database through a REST API. This can be beneficial to using standard ODBC connectors in order to overcome limitations with connectivity and enables a consistent way to access databases in any programming language. We have partnered with Espresso Logic, which has a robust set of technologies to connect your application to a SQL database using a RESTful service with JSON encoding. Currently supported databases include MySql, Sql/Server and Oracle, on-premise or in the cloud.

RESTful services can be accessed in any Codenvy language.

This page provides a brief summary of Espresso, and guides you through how to use it – from sign-up, to API creation, to accessing the API.

Note: You will need an Espresso Logic account to make this integration work, and Espresso Logic has various free & paid accounts that you must consider.

What is Espresso

Espresso is a service – you don’t have to install or configure anything. The service is provided automatically when you sign up. As summarized in this short tour, use the Espresso Logic Designer in your Browser to define your API, declare business logic applied to POST, PUT and DELETE requests, and define row/column level security.

In this quick start, we’ll provide a brief overview of Creating your API. For more information, including how to define Logic and Security, see the Getting Started Guide.

Signup

Click here to create an Espresso Account. Or, you can run the Live Demo, which enables you to explore a complete small system in a separately launched Codenvy workspace.

There are three types of services but most likely you have chosen one of the following alternatives:

  • Evaluation – This is a free, shared version and only provides partial Javascript logic
  • Developer – This is a paying version with dedicated hardware. It has no limitation on the use of JavaScript logic

Creating an Espresso Datasource API

To create your RESTful API, you Create a Project (which defines your URL), Register your Database (creates a default API with a REST resource for each Base Table), and optionally define a Custom Multi-table API with joins, projections etc.

Creating a Project

After you sign up for Espresso, you can access the Logic Designer. To create a project:

  • Access the Home screen (if you’re not already there)
  • Click Create New Project
  • Specify the Project Name

create-project

Registering Your Database

You will then see a screen like this, where you provide your database URL:
new-project
When you successfully complete this step, your API is ready:

  • All of your Base Tables are available as REST resources (per Security)
  • Your REST APIs are described here
  • Use the REST Lab to test and verify your API

Optionally, Define a Custom API

You can create a multi-table API that joins desired data by defining a REST Resource as described here.

Using the Espresso Datasource API

The Logic Designer provides a quick reference for your API.

Logic and Security

This quick reference has focused on how to create an API you can use from your Codenvy application. Much of the value of Espresso lies in simple and powerful techniques for defining Logic and Security that add semantics to your API.