Show HN: Xmysql – One command to serve REST APIs for any MySql database

https://github.com/o1lab/xmysql

by o1lab • 9 years ago

72 49 9 years ago

github.com

tlack• 9 years ago

I couldn't have used this many times over the years. Kudos

o1lab8• 9 years ago

Thank you.

raja• 9 years ago

pREST is something similar for postgres written in Go. (I haven't tried it) https://postgres.rest / https://github.com/prest/prest

dewey• 9 years ago

Similar but with Go and for Postgres if you are looking for something like that. Used it in the past and works well for some quick prototyping: https://postgres.rest/

daurnimator2• 9 years ago

Or take it all the way to production with postgrest. https://postgrest.com/

dmichulke• 9 years ago

There is no java client lib?

(At least it's not mentioned in the docs)

rhombocombus• 9 years ago

Came here to say this. Postgrest is a fantastic product and a huge time saver, both in terms of reduced superfluous code requirements and overall performance.

o1lab8• 9 years ago

  xmysql now supports 
  + group by
  + group by, order by
  + aggregate
  + single file upload, multiple file upload and download file
  + where clause on list of resource
  + where clause on list of nested resource

https://github.com/o1lab/xmysql

treve• 9 years ago

It's not really REST if there's no link in sight. Why not just call it a HTTP API or a Webservice?

Waterluvian• 9 years ago

The REST train left the station a long time ago without HATEOAS on it. Let's just move on; I don't think there's much value left in that worn out debate.

jbenner-radham3• 9 years ago

What are you referring to when you say “no link in sight” in regards to REST?

axlee• 9 years ago

Basically, instead of having a "User" response built like this:

  User {

    name: "Bob"

    id: 1

  }

You would have something like this:

  User {

    name: "Bob"

    href: "http://www.example.com/api/users/1"

  }

nilved• 9 years ago

Hypermedia as the Engine of Application State

JimDabell• 9 years ago

REST APIs must be hypertext-driven: http://roy.gbiv.com/untangled/2008/rest-apis-must-be-hyperte...

jack9• 9 years ago

REST does not require a specific response. The original paper used specific verb as an example present in the wild, which has oft been misinterpreted as required usage.

Formatted summary - http://www.ics.uci.edu/%7Efielding/pubs/dissertation/rest_ar... - "The most frequent form of request semantics is that of retrieving a representation of a resource (e.g., the "GET" method in HTTP), which can often be cached for later reuse."

A critique of HTTP REST - http://cafe.elharo.com/web/why-rest-failed/ - "Representational State Transfer (REST) is the explicit architecture of HTTP and the World Wide Web" which is a notably incorrect statement.

Other interesting articles:

http://www.artima.com/lejava/articles/why_put_and_delete.htm...

http://roy.gbiv.com/untangled/2009/it-is-okay-to-use-post

oneweekwonder2• 9 years ago

Reminds of ArrestDB[0]. A one-file PHP "plug-n-play" RESTful API for SQLite, MySQL and PostgreSQL databases. ArrestDB provides a REST API that maps directly to your database stucture with no configuation.

[0] https://github.com/alixaxel/ArrestDB

softwarelimits3• 9 years ago

Which also seems to have no permission system and because of that is of very limited use.

artpar• 9 years ago

Checkout daptin[1] if you are looking for a full-fledged solution.

[1] https://github.com/daptin/daptin

oneweekwonder2• 9 years ago

Not xmysql or myself talked about permission, from xmysql:

## When NOT to use ?
- If you are in need of a full blown MVC framework, ACL, Authorisation etc - Not this.

I personally don't know if I want to build permissions in my one page micro curd api. If you are going for basics I believe access is bettered controlled by the web server(ldap/basic auth).

samspenc• 9 years ago

One of the most amazing pieces of software I've worked with recently is Kibana, which is a web-based visualization software that is part of the open-source Elasticsearch stack.

My understanding of why Kibana could be built and become such a powerful visualization tool is because of the easy-to-use and powerful REST APIs that Elasticsearch shipped with out of the box.

Hopefully a REST API like this for MySQL will make it easy to build similar powerful tools around MySQL! It's definitely something that would have helped with the PHP / MySQL applications I built years ago.

jensvdh• 9 years ago

Have you tried SuperSet?

enobrev• 9 years ago

I don't love kibana, but with the ease of dumping structured logs from rsyslog to elasticsearch, it's hard not to use it.

I hadn't heard of SuperSet[1] until this post. Seems pretty interesting. Looks like there's potential for ElasticSearch support[2] as well.

1: https://github.com/apache/incubator-superset

2: https://github.com/apache/incubator-superset/issues/600#issu...

Karrot_Kream• 9 years ago

Postgrest does the same thing for Postgres https://postgrest.com/

setr2• 9 years ago

I don't get it; this doesn't seem to offer anything more than say, having a raw sql query in a post body, that gets thrown against the db. Except this looks a lot more annoying to write

I'm imagining auth and permissions and such will still primarily be handled by the db in either case

mseebach• 9 years ago

I suppose the use case is for when it's not practical or desirable to do the legwork of setting up a full connection, with libraries and everything.

You could imagine a "(web)serverless" web app with Javascript querying the DB directly over REST. Or embedded devices saving telemetry directly in a DB.

setr2• 9 years ago

Im still suggesting the webapp exists, just in the form of webapp.com/db, to which you POST with the field query=SELECT%20*%20FROM%20table

So the one page takes any arbitrary query, and forwards the query on the client's behalf, and returns the dataset to the client.

Im not sure how the provided api is better than what ive suggested; they seem to me equivalent, but xmysql a lot more annoying to write, and xmysql is doing a lot more work for what looks like no benefit

skhro87• 9 years ago

similar but for GraphQl: https://github.com/postgraphql/postgraphql

joshribakoff• 9 years ago

softwarelimits3• 9 years ago

no permission system?

o1lab8• 9 years ago

as mentioned in another comment, the scope of this repo is limited. I will try to make it explicit by having 'when to use' section before. Thank you.

oneeyedpigeon2• 9 years ago

Is there a reason parameter names begin with underscore? Or why the page parameter is abbreviated to "p"? Why is a non-standard syntax used for multiple values rather than just parameter arrays? E.g. instead of:

/api/payments?_fields=customerNumber,checkNumber

why not:

/api/payments?fields[]=customerNumber&fields[]=checkNumber

Kudos for "sort=-{field}" though (I think); any commentary on that design choice? I've used e.g. "sortdir=desc" in the past; not sure which is superior.

o1lab8• 9 years ago

Thank you for your input.

parameters with underscore: I'd imagined to 've 'where' parameters in query fields like ?columnName=columnValue. Hence started with underscore - however I dropped where clause as I was soon sure that I was only covering small group within where clause and operators.

abbreviation of page to _p - I think I picked up that from hackernews where paging query parameter is p :).

fields: second one seems easy to use - I think I borrowed it from google youtube apis. They have concept of id,snippet etc which is higher level of abstracting ids, primary-info (respectively) in response

sort: may be I extended the concept of fields to sort. Since sort can 've only two directions.

dalore• 9 years ago

Why not a standard syntax like this:

``` /api/payments?fields=customerNumber&fields=checkNumber ```

You don't need [] to look for multiples.

oneeyedpigeon2• 9 years ago

As another commenter mentioned, there isn't really a standard here. I thought "field[]" was a standard, of course — that's the way PHP handles parameter arrays. In contrast, reusing the parameter name without the trailing "[]" does not work (only the last value is provided, as a scalar).

I guess I got my answer :)

userbinator2• 9 years ago

Or why the page parameter is abbreviated to "p"?

It's probably the most common convention I've seen (at least for those sites that still support pagination, as opposed to that horrible "infinite scroll"...)

The underscores are a little unusual, however; I don't think it's a bad thing, unless you're deliberately trying to hide the fact that you're using a specific backend. I've seen other prefices for parameters too with other sites in the past.

Why is a non-standard syntax used for multiple values rather than just parameter arrays?

There is no standard. I believe [] came from PHP, but I've seen many different ways to express "multiple values" in query strings.

onion-soup• 9 years ago

How is this different from sending SQL to a remote MysQL server? It is literally wrapping existing interface with obscure query strings in URL

userbinator2• 9 years ago

Somewhat less prone to SQL injection attacks (I haven't looked at the code, so I may be wrong, but this does limit what can be queried if implemented correctly.)

stephenr• 9 years ago

Ah, NodeJS. Where people write userland SQL clients with string substitution and call it "prepared queries".

o1lab8• 9 years ago

  xmysql now supports
  + group by
  + group by, order by
  + aggregate
  + single file upload, multiple file upload and download file

o1lab8• 9 years ago

  > xmysql now supports
  + where clause on list of resource
  + where clause on list of nested resource

Show HN: Xmysql – One command to serve REST APIs for any MySql database

Related Stories