OData REST API for MySQL

January 24, 2020

Skyvia Connect allows you to easily expose MySQL data via OData service — RESTful API for quick, real-time data access and manipulation. OData is a widely accepted open standard built on HTTP, ATOM/XML and JSON, intended for data access over the Internet with no firewall configuration.

With Skyvia you do not need to build web API manually. Instead of building RESTful API with Node.js and MySQL, you can simply create an OData endpoint for quick data access.

Skyvia Connect is a universal cloud-based solution, which requires no coding, no technical skills. You do not need to develop MySQL restful web service, care about security, hosting and domain, obtain respective certificates, deploy, administer or maintain it. Our cloud-based solution will do it for you.

You can use it to expose any kind of data in a fast and convenient way – both from cloud and on-premise servers. To connect to on-premise servers, use our new Agent application developed by Skyvia team exactly for these needs.

What Is Agent? How Can We Configure It?

Agent is a secure tunnel application developed to connect your Skyvia account to local, on-premise databases with ease and comfort and to establish secure communication bypassing firewall.

Try its functions by installing Agent application to your PC and creating MySQL connection you want to work with. Follow our easy step-by-step tutorial on how to do it in Skyvia.

Creating an Agent in Skyvia

To create an agent, perform the following steps:

  1. Click +NEW in the top menu and select Agent from the list on the left.

    Agent icon

  2. When the Agent editor page opens, click Download.exe to download an Agent application

    Agent Page

  3. Click the downloaded file to install the Agent application.
  4. When you do it, the Skyvia Agent window pops up. Click the INSTALL button. By default, agent will be installed to C:\Program Files (x86)\Skyvia Agent.

    Agent Install button

  5. After you have downloaded the Skyvia agent, you need to download the security key. To do it, click Download Key at the top right corner.
  6. When the key file is downloaded to the Downloads folder, move it to the folder where the Agent is installed. Note that the key file name should always be skyvia_security_agent.key.
  7. Then, in the Skyvia Agent folder, click Skyvia.Agent.Client to start the program.
  8. If you have done everything correctly, on the Agent editor page you will see Connected sign and green light next to it.

    Connected Sign

  9. Optionally test the agent connection by clicking the Test agent button.

Creating a Connection in Skyvia

To create a connection, perform the following steps:

  1. Click +NEW in the top menu.
  2. Select Connection from the list on the left. The Select Connector page will open.

    Select Connector Page

  3. Select MySQL from available connections.
  4. When the Connection editor page opens, select Agent under Connection Mode and fill in all the required parameters.

    MySQL Connection

Creating an Endpoint in Skyvia

To create MySQL OData endpoint, perform the following steps:

  1. Click +NEW in the top menu and click on OData Endpoint under Connect.

    Endpoint button

  2. On the page that opens, select MySQL connection from the created ones and you are transferred to the Choose Endpoint Editor Mode page.
  3. Click Advanced mode to design your OData endpoint on a diagram.
  4. On the Endpoint editor page, drag-n-drop tables you need and manage connections between them.

    Endpoint window

  5. In Skyvia, you can optionally allow only authenticated users to access endpoint data and optionally limit IP addresses, from which the data of the endpoint can be accessed. Click Users on the toolbar and add a user to enable authentiсation for your endpoint. The same you can do with IPs.

When you have already created the endpoint, you can copy its URL from the Overview tab and use it for different purposes as well as in your OData consumer applications. You may also test the endpoint URL in the browser.

Overview tab of Endpoint

Having made a request in the browser, you will receive a response containing entity sets. In our example, we have received a response with 5 entity sets in JSON format — products, orders, orderdetails, productcategories and companies.

Window with Entity Sets

To receive all companies and see their properties, like CompanyID, CompanyName, Web, Email, Address, City, Region, Country, etc., we add the corresponding name of the entity set, companies, in the URL bar and check the displayed data.

Window with Entity Sets 2

You may use the endpoint URL in any other OData consumer applications, such as Excel or Salesforce Connect, etc. You determine it yourself based on your business needs and actual demand.

We welcome you to try OData REST API for MySQL and enjoy full functionality of our platform. Give us your feedback and share your thoughts!

Comments