[mdahlstrand]

I went through the same thoughts as you and went straight to knex for my last project. Turns out knex also has bugs and the limitations from having it support all the databases means some really useful features are not available.

For my latest project I've gone with pg, writing sql directly, and I'm not looking back. It's so nice not having to write sql through the abstraction layer of knex.

Of course if you think you'll swap your database provider down the line, maybe stay with knex. But I really can't see why I'd want to switch away from postgres at a later date.

[nawgz]

Have you looked at Hasura?

For some of my latest dev, I've just stopped worrying about writing data plumbing. I have a tool that I can use to update the schema from any arbitrary state to any arbitrary state so long as there are no column modifications, and then Hasura is able to query my entire schema via GraphQL.

You also get row, column, & table-level permissions, an easy auth hook, and the promise of schema stitching (which I must admit I haven't used yet, so I don't know if it's easy or performant).

I can't really speak to performance, but I think the Hasura guys did their best to use Postgres' internals wherever possible. Try it out!

Also, a similar tool is "Xgenecloud", but I didn't like it as much and have only invested time into Hasura.

[xgenecloud]

hey, XgeneCloud creator here. How can we improve ? which parts you didn't like

[nawgz]

I don't have great feedback for you, it's just that I downloaded and ran xgenecloud and was exceedingly confused what to do from the UI that it spawned. It didn't seem clear to me how I could get started, and my options were plentiful; I would've been ok to make a new database thru your UI, point your tool at an existing database, or anything else, but I just didn't have a clear path to querying a schema.

With Hasura, I could run it with about 2 docker commands and build a schema thru their web UI, use GraphiQL in their web UI to query the schema, and read docs to get a clear path on how I might use this artifact in production.

I guess I would just use Hasura if I was you and see what the experience is like vs XgeneCloud, maybe it will show you what I saw.

[xgenecloud]

I hear you - thank you for the feedback. I've got similar feedback from devs who are liking XgeneCloud as well. We are in really early days of our journey - I promise to improve this flow by many folds.

The way it currently works is :

* Each new project has a folder in your filesystem where GraphQL API source code is generated on pointing to database! So when we generate APIs, we also generate source code for those APIs (node.js/express.js) behind the scenes. This is a big leap on how easy it becomes to customise APIs or put some business logic in them

* Thereafter, as and when a developer changes schema (add table, add column etc) - the underlying GraphQL code also gets updated automatically.

Here is a demo of generating GraphQL on postgres & GUI based schema design:

https://youtu.be/QEq3Mjbeelg

https://youtu.be/ETEEcY4mmEg

Feel to free reach out on twitter/Discord (in profile). Happy to help you get started.

edit: sentence corrections