
Show HN: XgeneCloud – Instant REST and GraphQL APIs on Any SQL Database - xgenecloud
https://github.com/xgenecloud/xgenecloud
======
gingerlime
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?)

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

------
nkprabhath
Interesting, [https://xgenecloud.com/demos](https://xgenecloud.com/demos) was
useful.

will be adopting in few usecases soon

------
danr4
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](https://github.com/o1lab/xmysql)

~~~
o1lab
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](https://news.ycombinator.com/item?id=15580914)

------
Reubend
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?

~~~
xgenecloud
>> 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...](https://github.com/o1lab/xmysql/commit/3b279855a2562765975f7271490e4483254d7c96)

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

~~~
xgenecloud
>> 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](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](https://github.com/xgenecloud/xgenecloud/issues/4)

------
1MachineElf
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"

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

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

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

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

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

~~~
o1lab
Thank you! We are fixing this.

------
artificial
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!"

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

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

------
shireboy
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?

~~~
xgenecloud
We provide simple role based ACL mechanism (toggleable from CLI/GUI)
[https://docs.xgenecloud.com/en/v0.5/rest-apis/rest-
acl](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...](https://docs.xgenecloud.com/en/v0.5/rest-apis/rest-
acl#workflow)

------
eatonphil
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/](https://eatonphil.github.io/dbcore/)

------
pune2th
Is support for NoSQL on roadmap?

~~~
o1lab
NoSQL is on our roadmap.

Which NoSQL you had in mind ?

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

~~~
xgenecloud
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](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.

------
syats
Related project: [https://sciencedb.github.io/](https://sciencedb.github.io/)

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

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

~~~
o1lab
Which SQL database are you using ?

It will take about two minutes to try

------
doug78
Really! Where do we check it?

~~~
o1lab
Please click on the link below :)

Github :
[https://github.com/xgenecloud/xgenecloud](https://github.com/xgenecloud/xgenecloud)

Website : [https://xgenecloud.com](https://xgenecloud.com)

Usage: takes just two minutes

    
    
       npm install -g xc-cli
       xc app.install
       xc app.open
    
       xc new <project_name>

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

~~~
xgenecloud
Yes, this will be perfect.

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

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

~~~
bxparks
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?

~~~
xgenecloud
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](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.

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

------
ashwincf
Interesting one. I will check out for our project.

------
macca321
deleted

~~~
macca321
>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](https://twitter.com/mamund/status/767212233759657984?lang=en)

~~~
RileyJames
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](https://medium.com/good-api/api-maturity-fb25560151a3)

