
Show HN: Xmysql – One command to serve REST APIs for any MySql database - o1lab
https://github.com/o1lab/xmysql
======
dewey
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/](https://postgres.rest/)

~~~
daurnimator
Or take it all the way to production with postgrest.
[https://postgrest.com/](https://postgrest.com/)

~~~
dmichulke
There is no java client lib?

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

~~~
daurnimator
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/](https://editor.swagger.io/)

------
oneweekwonder
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](https://github.com/alixaxel/ArrestDB)

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

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

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

~~~
softwarelimits
Thanks for the hint, looks interesting!

------
Karrot_Kream
Postgrest does the same thing for Postgres
[https://postgrest.com/](https://postgrest.com/)

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

~~~
setr
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

~~~
ruslan_talpa
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)

------
samspenc
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
Have you tried SuperSet?

~~~
enobrev
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](https://github.com/apache/incubator-superset)

2: [https://github.com/apache/incubator-
superset/issues/600#issu...](https://github.com/apache/incubator-
superset/issues/600#issuecomment-307960932)

~~~
o1lab
Thank you for your inputs.

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

------
skhro87
similar but for GraphQl:
[https://github.com/postgraphql/postgraphql](https://github.com/postgraphql/postgraphql)

~~~
joshribakoff
Also related [https://github.com/stems/join-
monster](https://github.com/stems/join-monster)

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

~~~
dalore
Why not a standard syntax like this:

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

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

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

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

------
o1lab

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

~~~
o1lab

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

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

~~~
o1lab
Thank you.

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

~~~
userbinator
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
Ah, NodeJS. Where people write userland SQL clients with string substitution
and call it "prepared queries".

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

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

~~~
axlee
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"
    
      }

~~~
gabrielcsapo
This kind of reminds me of hateoas. [1]

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

------
softwarelimits
no permission system?

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

