Tuql: Automatically create a GraphQL server from a SQLite database | Hacker News Reader

Tuql: Automatically create a GraphQL server from a SQLite database

github.com

143 pointsthunderbong32 comments

GraphQL is really intended to model UI concepts rather than directly exposing your database schema.

First of all, your GraphQL schema is basically append-only due to older clients that may remain in the wild. So you don't want to expose implementation details that may change.

Second, you want to write client code that handles mutations. This is easier if the data the client receives is organized in a UI-centric way. I'll give you a simple example that came up at work recently: a single conceptual category (a "user account") that, due to implementation details, was spread across two different tables with different columns. Because the GraphQL schema in this case mapped each table to its own GraphQL type, somebody was then able to write client code that only handled one type and not the other, causing an inconsistent UI.

I would suggest thinking carefully about your GraphQL schema, treating it as an API, and not auto-generating it. Of course, you want it to be convenient to construct, just not fully automatic and thoughtless.

I’ve been kicking around a project idea for years that would be a niche fit for these considerations. It’s effectively an extremely niche reference corpus of very field-specific data, effectively represented by JSON documents and having a few relationships that don’t require transactions. It wouldn’t be editable by clients at all. Every client would be nothing more than a consumer, and the database would be versioned and only edited by the maintainer.

That is certainly not a common use of web based APIs, to have an application that is read only every client, but the reason I came to this thread was because I’ve considered SQLite for this idea in the past.

This is what I constantly tell people. Use GraphQL to efficiently create permutations of access so the data is shaped for the client more or less exactly how they need it. To that end, I often recommend that at least one UI developer is involved in approving schema changes and regularly dogfoods the GQL setup before its deployed.

I have been met with a lot of resistance around this notion for some reason.

To be honest, I fought the same argument with OpenAPI (Swagger) too.

API developers seemingly just want to chuck their schema over the wall and walk away

Yeah this dynamic definitely exists. I think this is because there's a big mismatch between what frontend wants ("we'd prefer a single, super fast endpoint with no params") and what backend wants ("we automatically exposed our database tables complete with RBAC, query away"). Backend engineering has more or less become all implementing this mapping layer, whether it's with graphql resolvers, db views, or whatever, but the work to be done is like 90% here.

I think part of this is that when an API developer tries to write frontend code, they'll naturally use the underlying schema as their frontend domain model because they're already thinking in those terms.

I definitely suffer from that problem when trying to work out what a good API for frontend would be, and frontend people often have their own blind spots which makes working together to find something actually good a tricky process.

Then again, getting REST/GraphSQL/API-of-choice designs 'actually good' is hardly a trivial problem at the best of times, so how much of this is developer biases and how much inherent difficulty isn't something I'd be confident trying to estimate.

I imagine a lot of people don't share this particular opinion. The problem GraphQL was created to solve is precisely the ability to compose ad-hoc queries to feed into UI, without being tied to a particular data model or mapping. It allows applications to change at a faster pace without requiring centralized API modifications.

If you create those UI-centric models when exposing data through your GraphQL schemas, you just moved the modelling work elsewhere but haven't actually facilitated anything, and it's still centralized. At this point you're better off embracing the 'BFF' architecture and skipping GraphQL altogether.

There may be a useful middle ground (for example, ensuring a single User type), but it's a slippery slope to stand on.

I think if you look at it from the otherside it makes more sense.

A problem that is common is that we send these JSON blobs over the wire that aren't purpose fit. So, with GraphQL, we can construct a query graph that makes the JSON blobs slimmer and more purpose fit, by constructing the queries to return the data we desire for some particular business reasons we need to represent in the UI, for instance.

I got the argument with mutations need to match things more closely, but I'd argue you can cross compose mutations w/ resolvers too.

I think this is what they're getting at. Just throwing the database schema over the wall via GraphQL isn't that much better than REST, and takes zero advantage of abilty to use revolvers to construct purpose built queries

Could you explain that “two tables” issue again? I’m trying to think of a way that DB constraints (I’m using Postgres, fwiw) couldn’t handle denormalization, but imo there’s a constraint for everything. Force a 1:1 relationship, or add a check constraint so you literally cannot denormalize.

If your use case is read-only I suggest taking a look at roapi[1]. It supports multiple read frontends (GraphQL, SQL, REST) and many backends like SQLite, JSON, google sheets, MySQL, etc.

[1] https://github.com/roapi/roapi

Is there anything similar that I could deploy to vercel? My usecase is API produced from JSON source, and I dont want a server for it, only static assets.

Devil's advocate: why can't you deploy Dockerfile of roapi to Vercel (or fly.io)?

What is the defacto "hobbyist tinker-er" standard for "I want to deploy this Docker thing into the cloud and play with it on the Internet"? There's got to be some one-click deploy solution where it goes to a Github repo, picks up a Helm chart, deploys it to a k8s cluster or something?

You can deploy a docker container to heroku, digitalocean app platform etc. no helm config required.

I think fly.io is intending to be a good choice for that use case.

I say 'intending' because I've not tried it rather than to imply any informed opinion about where they are in the process of achieving said goal.

Datasette was designed for Vercel from the start. It's a really great way of turning data into a read-only API, with both JSON and GraphQL endpoints.

https://datasette.io/

Demo here: https://datasette.simonwillison.net/simonwillisonblog

GraphQL demo here: https://datasette.simonwillison.net/graphql?query=%7B%0A%20%...

To deploy a SQLite database to Vercel with a GraphQL API:

    brew install datasette
    datasette install datasette-publish-vercel
    datasette publish vercel mydatabase.db \
      --project my-new-vercel-project-name \
      --install datasette-graphql

Here's a tutorial on how to get data into that SQLite database in the first place: https://datasette.io/tutorials/clean-data

You might like what we're building with Seafowl [0], which is an open-source, single binary database you can deploy to the edge for executing cache-friendly queries (so e.g. you can deploy to fly.io free tier, load your data into it, and put Cloudflare in front of it to cache query results like any other HTTP response).

Here's an example [1] multi-page site with Next.js and Seafowl.

[0] https://seafowl.io/docs/getting-started/introduction

[1] https://github.com/splitgraph/madatdata/tree/main/examples/r...

Impressive how little code is involved here! This is really neat.

The biggest feature I can see that's missing is pagination - it looks like this doesn't have a way to retrieve e.g. ten results, then pass a next token to get back the next set.

Here's how I implemented pagination in my similar datasette-graphql plugin (which also gives you a GraphQL API for an existing SQLite database): https://github.com/simonw/datasette-graphql#pagination

> The primary key column is named id or thing_id or thingId, where thing is the singular form of the table name.

Is it naming relations a plural word a common thing in practice?

I thought best-practice was to name relations either singular (as each tuple represents one entry) or uninflected (still singular for most words), specially when you're not a fluent speaker of the language being used to name the relations of the database.

Plurals are often irregular for commonly used words, and the fact that this requires a external dependency ( https://github.com/plurals/pluralize ) to cover for some "common plurals" is telling that supporting this feature is a complex thing indeed - that would not be required in the first place with singular everywhere.

>> The primary key column is named id or thing_id or thingId, where thing is the singular form of the table name.

> Is it naming relations a plural word a common thing in practice?

It's unfortunately what rails ActiveRecord does, and other solutions inspired by Rails.

If one keep the domain models and tables in a consistent language (ie translate everything to English) - it is somewhat consistent, and the code reads somewhat pleasantly like a DSL.

In practice I find that business logic/data modelling needs to/should be done in local language and then this becomes a bit of an annoyance and point of friction.

https://guides.rubyonrails.org/active_record_basics.html#nam...

I took a quick peek at the code and, unless I missed looking at big file somewhere, looks like it’s only around 550 lines of code.

That’s great if so. I haven’t looked at lines of code in any other graphql library but I’d guess there’d be far more.

pretty sure this is wrapping other libraries. sequelize and graphql

Aha, yes, you're right. I see those in the package.json.

I knew GraphQL would eventually come down to this, it was already apparent in the idea. Instead of replacement for REST services, GraphQL is really more like a way to expose a database schema from any relational database in a standard way. Regardless of whether the database is SQLite, Postgres, or a proprietary product like Oracle or DB2, slap a generate GraphQL front end on it now it's available remotely without needing a client driver.

Slap them into entity-normalized subgraphs and now you can compose all your databases, indexes, queues, etc. into one supergraph where you can seamlessly resolve entity fields across all your data stores.

Very cool, though I'm not sure how production-ready this is, given that there doesn't seem to be a way to add authorization for certain fields / queries.

Also, does the generated schema include the primary keys? Otherwise caching in the frontend might turn out to be difficult.

Which graphql frameworks do handle auth? It’s a genuine question, I don’t know the space but had to rule out graphql in its early days for a seeming lack in this area. But I’ve got a project now where it might be a great fit.

Authorization is usually not handled by a GraphQL framework. You typically have a business layer between your GraphQL API and your database. (And probably some basic JWT authentication layer in front of your API. Notice the difference between authorization & authentication.)

Sorry, yes, I meant authorization. So you're saying to just effectively run a filter in the business layer based on allowed role/user/whatever. Seems pretty straightforward but I wonder if I'm missing something.

> run a filter in the business layer based on allowed role/user/whatever

Yup, this is the way I've seen it implemented everywhere so far.

Hasura has authz this (but ive only used it it toy situations) authn is jwt or custom integration https://hasura.io/docs/latest/auth/authorization/permissions...

Edit: roles via jwt token https://hasura.io/docs/latest/auth/authentication/jwt/

You could use WunderGraph in front of your GraphQL and REST APIs to add auth, e.g. using OpenID Connect: https://docs.wundergraph.com/docs/auth/cookie-based-auth/ope...

Hey - I'm the author of tuql (if you can still call it authorship, I haven't updated this library in a long time :) )

I originally wrote this to speed up prototyping / development projects, I'd never recommend shipping this anywhere near production.

Good one

Reply on news.ycombinator.com