Exposing MySQL database/table using REST

问题:

What would be the easiest way to expose a MySQL database using a REST interface?

The only guidelines that I can find on the Internet is just using NetBeans. I am not a big fan of NetBeans...

I'd like to retrieve that and be able to update data on the database using this REST interface.

FYI, this is for an iPhone application that I am currently working on.

回答1:

As dorkitude commented, it depends on what languages you can use / want to use.

I found the project PHPRestSQL which seems to do what you want: http://phprestsql.sourceforge.net/ - haven't tried it myself though.

回答2:

Another option is restSQL, an ultra-lightweight persistence framework. http://restsql.org. It supports MySQL and PostgreSQL and runs in a standard Java EE container, e.g. Apache Tomcat.

restSQL is a very unconventional data access layer. restSQL is not an object-oriented view of the database. It presents flat or hierarchical "views" of relational database tables. These views are query-able and updatable through a simple REST-based HTTP or Java API. The HTTP interface is based on REST principles, which use HTTP’s built-in features, rather than abstracting away from them.

回答3:

MySQL 5.7.4 and above work with the HTTP plugin, which offers a HTTP REST API.

SQL: SELECT * FROM simple ORDER BY id
URL: http://127.0.0.1:8080/sql//SELECT+%2A+FROM+simple+ORDER+BY+id

However, it currently comes with a NOT FIT FOR PRODUCTION warning.

Blog announcement.

Docs.

回答4:

NetBeans is just an IDE. It has nothing to do with REST. If you are using Java, take a look at Restlet or Jersey. I personally prefer Jersey, but both works.

回答5:

2017 Update:

PHP: Another good player in the field is https://slimframework.com

Python: http://python-eve.org/

For people finding this in 2015, potential solutions that are still working/under development as of Oct are:

Haskell (binary): https://github.com/begriffs/postgrest

Java: http://restsql.org/

PHP: https://github.com/moddity/Rester/

回答6:

Seems MySQL is getting REST interface natively, so we can query the DB, without a need for server... :-)

http://www.infoq.com/news/2014/09/MySQL-REST

回答7:

Another option is Slash db which "automatically generates a REST API from relational databases."

As of June 2017, it works for SQL databases without adjustment, but other kinds of data stores require a custom developed plugin.

I've only read about this service (not a user). I have no affiliation with them.

回答8:

If a Java backend is ever pursued, have a look at an answer I posted for Need to write a RESTful JSON service in Java that outlines the steps you can take to connect to the database and create a RESTful Web Service using the following.

• IDE: Eclipse IDE for Jave EE Developers (Kepler), comes with Maven built in
• Database: MySQL (also makes use of MySQL Workbench)
• Application Server: GlassFish 4.0
• Java EE 7
  • Jersey for JAX-RS (default implementation in GlassFish) RESTful service layer.
  • EclipseLink for JPA (default implementation in GlassFish) Persistence layer.
• Any REST Client for testing: (eg Postman)

回答9:

My recommendation in 2020 would be using metamug console. Metamug console is language agnostic and builds APIs with a MySQL connection and no extra setup.

Resource Editor

The console comes with a resource editor that helps you map requests to sql statements. You can add parameter validation and parameters into the queries.

Documentation

It generates resource documentation.

Other Features you may need.

• It can handle complex JSON requests along with multipart and URL-encoded data
• Implements standard status codes and error handling so you don't have to sweat.
• Comes with built-in query testing to fail fast development.
• Role-based per resource authentication.

You can ask questions with metamug tag

Disclaimer: I'm part of the dev team. And we have been working on metamug console from last 3 years.

You can easily export the apis build with metamug console to be used in production and the exported webapp is built on top of open source mason library

回答10:

In 2019, you may use PHP-CRUD-API. It is a single file PHP script that adds a REST API to a MySQL 5.6 InnoDB database. PostgreSQL 9.1 and MS SQL Server 2012 are also fully supported.

Disclaimer: I wrote it.

回答11:

For just GET requests, DBSlayer looks promising. Here is a google one that also sounds interesting. I have not used it though/

回答12:

Chiming in I came across loopback.io and have been toying with it. It's a great tool for easily creating api end points for mysql. You can install a mysql connector and then it discovers all your mysql tables and you can create them into models. I don't really know much programming but it's easy enough where I got an api for my sql working in a day. I have 19 tables and it easily created my end points.

http://loopback.io

回答13:

You should also consider using DataDirect's Hybrid Connectivity Services for this. They can help you quickly restify your database . To get started, you refer to the following product pages:

1. REST API (OData) for MySQL Community Edition
2. REST API (OData) for MySQL Enterprise Edition

Disclaimer: I work for DataDirect, but please note that I genuinely believe that you should be looking at these solutions.

回答14:

One of the easiest ways to create REST APIs for any MySql database is to use Xmysql

• Xmysql with one command you can get literally thousands of APIs within seconds (based on num of tables).
• Xmysql not only generates CRUD but also some sophisticated aggregate APIs too.
• Xmysql has a really well written documentation that accompanies in the repo.
• There is docker support as well

回答15:

You may want to have a look at jongo. It's a REST API for JDBC.