7 ms·
Show HN: Xmysql – One command to serve REST APIs for any MySql database
- tlack 9y agoI couldn't have used this many times over the years. Kudos
- o1lab 9y agoThank you.
- raja 9y agopREST 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
- dewey 9y agoSimilar 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 9y agoOr take it all the way to production with postgrest. https://postgrest.com/ https://postgrest.com/
- dmichulke 9y agoThere is no java client lib? (At least it's not mentioned in the docs)
- daurnimator 9y agoWhy 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/
- rhombocombus 9y agoCame 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.
- o1lab 9y agoxmysql 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 https://github.com/o1lab/xmysql
- treve 9y agoIt's not really REST if there's no link in sight. Why not just call it a HTTP API or a Webservice?
- Waterluvian 9y agoThe 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-radham 9y agoWhat are you referring to when you say “no link in sight” in regards to REST?
- axlee 9y agoBasically, 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 9y agoThis kind of reminds me of hateoas. [1] [1] https://spring.io/understanding/HATEOAS https://spring.io/understanding/HATEOAS
- o1lab 9y agoThank you for your input. I will consider this feature going ahead.
- jbenner-radham 9y agoIsn’t that HATEOAS and not REST specifically? I don’t remember anything in Roy Fielding’s dissertation specifying any type of response body.
- nilved 9y agoHypermedia as the Engine of Application State
- oneweekwonder 9y agoReminds 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 9y agoWhich also seems to have no permission system and because of that is of very limited use.
- artpar 9y agoCheckout daptin[1] if you are looking for a full-fledged solution. [1] https://github.com/daptin/daptin https://github.com/daptin/daptin
- softwarelimits 9y agoThanks for the hint, looks interesting!
- oneweekwonder 9y agoNot 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 9y agoOne 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 9y agoHave you tried SuperSet?
- enobrev 9y agoI 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#issuecomment-307960932 https://github.com/apache/incubator-superset/issues/600#issu...
- o1lab 9y agoThank you for your inputs. Kibana and superset look awesome. I'll have a look at them.
- Karrot_Kream 9y agoPostgrest does the same thing for Postgres https://postgrest.com/ https://postgrest.com/
- setr 9y agoI 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 9y agoI 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 9y agoIm 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 9y agoam 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)
- rco8786 9y ago> 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.
- skhro87 9y agosimilar but for GraphQl: https://github.com/postgraphql/postgraphql https://github.com/postgraphql/postgraphql
- joshribakoff 9y agoAlso related https://github.com/stems/join-monster https://github.com/stems/join-monster
- softwarelimits 9y agono permission system?
- o1lab 9y agoas 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.
- oneeyedpigeon 9y agoIs 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.
- o1lab 9y agoThank 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 9y agoWhy not a standard syntax like this: ``` /api/payments?fields=customerNumber&fields=checkNumber ``` You don't need [] to look for multiples.
- oneeyedpigeon 9y agoAs 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 :)
- userbinator 9y agoOr 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 9y agoHow is this different from sending SQL to a remote MysQL server? It is literally wrapping existing interface with obscure query strings in URL
- userbinator 9y agoSomewhat 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 9y agoAh, NodeJS. Where people write userland SQL clients with string substitution and call it "prepared queries".
- o1lab 9y agoxmysql now supports + group by + group by, order by + aggregate + single file upload, multiple file upload and download file
- o1lab 9y ago> xmysql now supports + where clause on list of resource + where clause on list of nested resource