Show HN: XgeneCloud – Instant REST and GraphQL APIs on Any SQL Database | Hacker News Reader

Show HN: XgeneCloud – Instant REST and GraphQL APIs on Any SQL Database

github.com

103 pointsxgenecloud45 comments

Wow this looks insanely feature-rich and with lots of docs and videos. Amazing!

How does it compare to Hasura (haven’t used it but seems similar?)

Thank you so much.

Hasura supports generating GraphQL on Postgres.

XgeneCloud supports generating both REST APIs and GraphQL APIs on all the following databases PostgreSQL, MySQL, MSSQL, SQLite and MariaDB.

The key difference with generated APIs are - with Hasura you would need an extra service running on your backend for model-validations & business logic. This can be a container/microservice or serverless functions. With XgeneCloud, the APIs are not in a blackbox but generated express.js API source code. This would mean adding business logic is as seamless as changing that CRUD function. Since we use express.js (Thank you TJ Holowaychuk) - it is easily extendable as per your needs with npm being a great ecosystem to add middleware, rate-limiting, logging and what not. Also to note, Hasura is on Haskell language (although powerful as a language it is not mainstream language to build your backends on)

In essence,

Backend with Hasura = database + hasura (for crud) + microservice/serverless functions (for business logic)

Backend with XgeneCloud = database + xgene's API framework (regular run of the mill MVCish code with familiar controller->service->model). This means it is quite easy to comprehend, debug and scale.

The webhook pattern employed by say Firebase/Hasura/Any-API-in-a-box is good for only really simple projects or prototyping. But essentially, we believe webhook pattern for even simple CRUD operations is a major anti-pattern plaguing backend frameworks these days. Not to mention, simple things like debugging, SQL transactions become really hard on databases. Having said above, hasura has realtime capabilities which we do not have at the moment.

Serverless Support : Our APIs can be deployed as serverless functions on any cloud platform (we use express.js - which amazingly works as Serverless App). This is a big leap - so there is no more Cloud Platform specific serverless code. We support AWS Lambda, GCP Cloud Function, Azure Function App, Alibaba Function COmpute, Vercel Now.

Database Client :

We ship a fully featured GUI Database based client (much like MySQL Workbench / PgAdmin) that works on all databases - this supports autogenerating up/down SQL schema migrations automatically. It has features to bookmark queries and very intuitive :)

We also ship a full featured API Client (much like postman/insomnia) to maintain your API collections and we also support performance test of APIs via GUI interface.

edit : sentence corrections

Interesting, https://xgenecloud.com/demos was useful.

will be adopting in few usecases soon

Wow this looks really impressive. Seems like it's the author of another mysql to rest api [1]. The name is a bit clunky though, but that's the best worst problem to have.

[1] https://github.com/o1lab/xmysql

Thank you.

Yes, XgeneCloud started as a simple project (Xmysql) that was first showed on HN[1].

[1] Xmysql on HN : https://news.ycombinator.com/item?id=15580914

This looks very interesting; my understanding of the product from looking is at the website is that it provides a database client/editor, it autogenerates a serverless API from the database schema.

I have a few questions: how is authentication handled? And does the serverless model mean that a new database connection has to be established every time an API request is made (because connection pools can't be used)?

I can see from the docs that it has signup and signin endpoints, and that it provides some email verification as well. But do those tables get added to the DB? And where does the email get sent from?

>> my understanding from looking is at the website is that it provides a database client/editor

Yes, XgeneCloud comes with a fully featured GUI Database Client for schema design and migrations. No more database design from cli or code.

>> how is authentication handled?

We use session based authentication.

>> does serverless model mean that a new database connection has to be established every time an API

Yes, if there are any improvements please suggest it. We were inspired by a commit[1] made to our old solution in Xmysql - which turned REST APIs to Lambda functions.

So, we went onto extend this Serverless function support for all Cloud Platforms (AWS / GCP / Azure / Alibaba / Vercel)

>> But do those tables get added to the DB?

Yes, they gets added to Database.

>> And where does the email get sent from?

we are using nodemailer for mailing and the smtp config can be updated in the corresponding config file for the environment within server/config/ folder.

[1] : https://github.com/o1lab/xmysql/commit/3b279855a2562765975f7...

This is awesome!

Is there any way to exclude certain tables or customize what is generated in any way? It seems like it just creates a model for every single table in the database, no matter what. I would love to be able to customize the config to exclude certain tables.

As a side note: I was able to get the GraphQL server working for a small mysql database, but when I tried to hook it up to a larger database, it gave me this error: Error: Field "UagFbDataAggregate.min" can only be defined once.

>> Is there any way to exclude certain tables or customize what is generated in any way?

Yes, for this you can create the project from our GUI Desktop tool to your existing database.

Then from CLI you can just create database models with below

  xc gen.apis --model

However, I see there is an issue with our production application and workflow.

I 've created an issue to track this.

https://github.com/xgenecloud/xgenecloud/issues/3

>> Field "UagFbDataAggregate.min" can only be defined once

May be this happens when there is clashing column name with an existing column in table.

https://github.com/xgenecloud/xgenecloud/issues/4

This seems really useful and even though I may never use it, the price is low enough that I could justify maintaining a Premium membership "just in case"

Yes, Thank you. By using this you will never have to write CRUD APIs ever. That's about 60-80% of work in backend.

Also it is absolutely free to use with all features.

Interesting! How would you compare this to something like Strapi (strapi.io)? I've been using them at some point last year and I remember the product was pretty good.

Strapi, I guess, can create APIs when you create models via Strapi's own UI Modeller itself (much inline with traditional API frameworks) and it does not generate APIs automatically on existing database schemas.

With XgeneCloud, you can point to 'ANY' existing or pre-built database schemas and your APIs are ready in seconds. So XgeneCloud introspects the underlying database schema automatically and then generates models, controllers, relational controllers, auth & ACL!

And to stretch the fun factor a bit - you can also point multiple databases as input - we will still manage to generate APIs for all databases together :) - happens within seconds.

Strapi ships an UI to create-edit models, but then I am guessing it is not a complete Database Client. They are targeting headless CMS space - so their needs might be much geared to provide richer datatypes in content management rather than to provide a fully featured database client. Also, I am not sure on how they manage SQL schema migrations (if somebody can help answer that). With XgeneCloud, your schema migrations are automatically generated as and when you make schema changes - this makes the process of backend database design a really seamless and enjoyable experience. What this means is - our desktop tool can be used by any database admins to manage schema migrations easily.

And finally, XgeneCloud's APIs are also Hybrid Serverless functions : which means without changing a single line of code - generated APIs can be deployed as Serverless functions across any Cloud Platform (AWS Lambda, GCP Cloud Functions, Microsoft Azure Function App, Alibaba Function Compute and Vercel Now)!

Two major-major benefits from this :

1. Your APIs also work as Serverless Functions.

2. Then those Serverless Functions are not cloud specific.

So the same code will work on any Serverless Cloud Infrastructure. Your code is not locked in with a Cloud provider.

Plus, we have a built in API client - where you can debug and perform test generated APIs (much like postman)

So with XgeneCloud, you can cover all aspects of API lifecycle management (Database, REST API Server, GraphQL API Server, API Client and Serverless)

We support following Databases :

• MySQL

• PostgreSQL

• MSSQL

• SQL Server

• SQLite

• MariaDB

API Framework also comes with a built in GUI Database Client!

• Point & Click Schema design & migrations

• Works on MacOS, Windows & Linux

Happy to answer any questions and take your feedback.

edit : formatting

Congrats on the release!

Quick heads up: The link on graphql schema generation and stitching seems to be broken. It points to a localhost URL.

Thank you! We are fixing this.

Excellent, I'll be testing this tonight. FYI Looks like a little typo on your pricing page: "You can get DARK Theme for FREE by just getting your friends to VISIT your referral page. They don't to sign up!" Probably should read: "They don't even have to sign up!"

Excellent! Would be lovely to hear some feedback. :)

Thank you for catching the typo in pricing - we will be fixing this.

One thing I’m not finding in this or similar tools is how to do row level authorization. For example: imagine a “forms” table. Users can submit forms and see and edit “their” forms, but not those of other users. Users need GET/POST/PUT /forms based on a UserId field in forms.

A more complex scenario: employees can submit forms. Managers can see forms for their employees, but not those of other managers. Managers need GET/POST/PUT on /forms based on forms.userid AND a relationship defined in manager_employees table. Managerid is not stored in forms because it could change.

Almost every api I’ve ever written has something along those lines. How would that be accomplished in a tool like this?

We provide simple role based ACL mechanism (toggleable from CLI/GUI) https://docs.xgenecloud.com/en/v0.5/rest-apis/rest-acl

However, this simple ACL is not intended to encompass all business rules or hierarchies of every application. Hence we decided to provide each router (or controller) with its own middleware handler where these rules can be coded easily.

Explained in below diagram : https://docs.xgenecloud.com/en/v0.5/rest-apis/rest-acl#workf...

I'm working on the same kind of thing but that gives you a typesafe React UI and REST API through code generation based in your existing database.

Notable features including filtering, pagination, and jwt-based authentication.

https://eatonphil.github.io/dbcore/

Is support for NoSQL on roadmap?

NoSQL is on our roadmap.

Which NoSQL you had in mind ?

MongoDB

we'll be adding a pubilc roadmap soon.

Thanks for your input.

How about ElasticSearch? Kafka? Neo4J? Btw, I'm your first Twitter follower! ;-D

Hey vladsanchez, thanks much for following.. you deserve a yes for all the above ;) (this is after checking our number of followers on twitter! haha.. folks please follow us at https://twitter.com/XgeneCloud)

I kind of understand Elastic and Neo4j usecase.

Can you please give an usecase for Kafka - you can raise an issue detailing so in github.

However, I will have to admit that before agreeing new databases - we will be working to make existing databases concrete.

Related project: https://sciencedb.github.io/

Great work. We will definitely explore this to reduce our dependency on Firebase. This is definitely needed.

Nice idea. GraphQL on SQL is a kind of need of the hour. I will check if we can use this in our next service.

Which SQL database are you using ?

It will take about two minutes to try

Really! Where do we check it?

Please click on the link below :)

Github : https://github.com/xgenecloud/xgenecloud

Website : https://xgenecloud.com

Usage: takes just two minutes

   npm install -g xc-cli
   xc app.install
   xc app.open

   xc new <project_name>

hmm, nice idea. I could use it in my next project, may be for all hackathons!

Yes, this will be perfect.

This seems useful, is it possible to do relational queries?

Absolutely.

Example: consider `country` and `city` are two tables and are related. City has a foreign key pointing to Country.

In REST API framework : We automatically generate /api/v1/country/:id/city for REST APIs.

In GraphQL API framework :

{Country {CityList} } graphql schema will be automatically generated with resolvers as well.

So they work out of the box

Very impressive work, but I am not familiar with auto-API generators, so I have some basic questions:

What if the foreign key reference was not an parent-child relationship, instead was a one-to-many relationship between 2 first class entities? For example, Company (1) related to Contacts (many), but they should be accessed as /v1/companies/:id and /v1/contacts/:id.

What if there was a unique constraint on the foreign key reference, so that the relationship becomes a one-to-one relationship? What does the auto-generated REST endpoint look like?

How does it handle join-tables which implement many-to-many relationships? Does it generate the REST end point for the join-table instead?

How does it handle different subcollection semantics? In other words, sometimes we want to treat the entire subcollection as an atomic entity. Other times, we want to create, delete and update individual elements of the subcollection.

How does it support SQL query optimizations?

Thank you for your input.

Good & insightful questions.

>> instead was a one-to-many relationship between 2 first class entities?

This can be treated as virtual relationship between 2 entities and can be linked together in our models. This has to be done manually for now. May be we can do better with our UI tool. I've raised an issue to track this - https://github.com/xgenecloud/xgenecloud/issues/6

one-to-one relationship returns a list instead of an object as of now.

>> How does it handle join-tables which implement many-to-many relationships? Does it generate the REST end point for the join-table instead? In GraphQL : the schema is like a graph traversal so a join query is possible. In REST : no - this will have to be created manually.

>> How does it handle different subcollection semantics? Subcollections can be handled individually i.e in their own routes or in nested routes in REST framework. CUD operations for subcollections in GraphQL i.e nested CUD operations (mutations) are not possible.

>> SQL Query optimisation: N+1 query optimisations are done with data loaders.

Please let us know of any other questions.

Thanks for taking the time to respond. This is great information that points me in the right direction regarding the capabilities of the framework. I will probably need to actually use it to have more intelligent questions. I normally write my API endpoints by hand, and I am used to having complete control over the data shape and performance characteristics of the API, but I will keep this in mind in the future.

Interesting one. I will check out for our project.

deleted

>Mike Amundsen > @mamund > · > Aug 21, 2016 > remember, when designing your #WebAPI, your data model > is not your object model is not your resource model is n > not your message model

https://twitter.com/mamund/status/767212233759657984?lang=en

I was interested enough to dive deeper into this quote, but I could only find the slides related to the talk. I then found this blog post which deep dives into Mike's talk and provides some great examples, including the prior art on which his talk is based.

https://medium.com/good-api/api-maturity-fb25560151a3

edit: i didn't read the docs, there are interception mechanisms it seems

Reply on news.ycombinator.com