Show HN: Xmysql – One command to serve REST APIs for any MySql database | Hacker News Reader

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

github.com

72 pointso1lab49 comments

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/

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

There is no java client lib?

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

Why would you need a java client lib? It's a tool to create a http server from your postgres schema. You can then use your normal http client library.

Though postgrest does support openapi (previously swagger), so you can use the swagger generator to emit a java client library. https://editor.swagger.io/

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.

  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

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

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

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

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

Thanks for the hint, looks interesting!

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).

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

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

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.

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

> xmysql a lot more annoying to write

That's pretty subjective. Personally, I'd much rather write "api/foo/:id/bar?_sort=field1" than "select * from foo inner join bar on foo.id = bar.foo_id order by field1".

Not to mention that this enables people who are not as familiar with writing raw SQL to move a lot faster, which is the point when you're prototyping/hacking.

am not sure exactly what operations xmysql allows but in the context of PostgREST (which is similar) the advantage over your method is that it does not allow "any query", thus, you can safely expose this api to the open web, while in the method you propose, i can run this query (i need zero db permissions) and kill your db

SELECT crypt( encode(digest(gen_random_bytes(1024), 'sha512'), 'base64'), gen_salt('bf', 20) ) FROM generate_series(1, 1000000)

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.

Have you tried SuperSet?

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...

Thank you for your inputs.

Kibana and superset look awesome. I'll have a look at them.

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

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.

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.

Why not a standard syntax like this:

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

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

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 :)

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.

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

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

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

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

Thank you.

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

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.)

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

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

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.

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

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"

  }

This kind of reminds me of hateoas. [1]

[1] https://spring.io/understanding/HATEOAS

Thank you for your input. I will consider this feature going ahead.

Isn’t that HATEOAS and not REST specifically? I don’t remember anything in Roy Fielding’s dissertation specifying any type of response body.

Hypermedia as the Engine of Application State

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

Thank you for that link. I’ve read through Roy’s original dissertation but apparently missed some of the details. This post helps clarify things.

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.