What would you like to be added:

A clean API to integrate Super Graph into other GoLang apps. This would be done as a http handler that can be plugged into an existing router or at a much lower level where you can provide a config object and create a new Super Graph instance to be used in your code.

Also hooks can be added for various things like onQuery, onMutation, onMutationComplete, etc, etc. This would help code using Super Graph as a library provide their own behaviour to execute during request handling.

The Super Graph GraphQL compilers (QCode and SQL) are already available as a library, this work would focus on moving more pieces of the serv package into a clean API.

Why is this needed:

Currently I run two services one for custom apis like authentication and the other Super Graph. Going ahead other custom endpoints like file upload etc would possibly also be added to the first service. It would be great if I could instead bundle it all together into a single app and also be able to augment Super Graph with my own app code.

Awesome project, thank you Vikram!

I'm thinking about something like a "forgot password" functionality. I don't think it's quite possible yet to implement it fully in GraphJin yet, but maybe with a few small changes it could be done (and it opens a lot of other possibilities too). (Please correct me if this is all already possible).

Basically, a user requests something like this:

mutation forgotPassword @script(name: "forgotPassword.js") (email: "[email protected]") {  ​
​  ok
  ​message
  requestId
}

where forgotPassword.js could be something like this:

function request(vars) {
  ​// validate the request
  ​if(!vars.email.includes(/@/)) { 
    // return an error instead of processing
    return {ok: false, message: 'email does not seem correct');
    // or throw '...'?
  }

  // check if user exists
  ​const user = graphql('query { user(email: $email) { id } }', {email: vars.email});
  if(user.id === null) { // or user === null?
    return ....;
  }

 ​ // save the request
  ​const data = { email: vars.email, code: generateRandomString(), time: new Date(), attempts: 0 };

  // graphql() function runs a GraphQL request on our database
  ​const graphqlResult = graphql('mutation { forgot_password_requests (insert: $data) { id } }', {data});

  ​// send an email
  const html = `You have requested...`
  ​http.post('mailgun.com', {html, to}); // send the email to the user

 ​ // return a custom object
 ​ return { ok: true, message: "Sent! Check your email", requestId: graphqlResult.id }; // don't do any postgres queries, just return this immediately
}

There are three things that I think are missing now in the GraphJin, that could make it hugely more useful:

1. ability to run GraphQL queries right from the request(...)
2. ability to immediately return results instead of GraphJin doing anything
3. ability to throw a custom error message, creating a more detailed validation

I think this opens huge possibilities - you could have a whole additional API without running node.js or something. Full custom business logic right in GraphJin

Then of course you'd need something that validates the code after it was sent, so it could be something like

mutation resetPassword @script(name: 'resetPassword.js') (requestId: "....", codeFromEmail: "123456abcdef", newPassword: "s3cr3t") {
  ok
}

and resetPassword.js:

function request(vars) {
  const data = graphql('query forgot_password_requests(id: $id) { code, attempts }', {id: vars.requestId};

  // TODO: check if not expired

  if(data.attempts > 3) {
    // some more logic to rate limit requests.. 
    return {ok: false, message: 'Too many attempts'};
  }

  if(data.code != vars.code) {
    // mutation to increment attempts
    return {ok: false, message: 'The code was not correct'};
  }

  return {ok: true}
}

I don't know - maybe instead of return {ok: false, message: "xyz error"} it would be better to throw an exception, but I think you get the idea.

This might actually also help with the problem that YugaByteDB guys told about - where a complex query like query getHome from older mobile clients could be intercepted by the backend via a custom request function, be processed in a more efficient manner (by running graphql() calls inside) and return the data with the same schema, but using a different way to run it.

Thank you!

checkout tests for examples.

limitations:

1. only works with variables. can not find a way to cast field args into json or struct.
2. can not use string inside cue schema (double quote " problem) inside plain graphql query, but it can be done with passing validation schema through variables.

Hello,

I'd like to suggest a feature: setting current role from the session. That would allow RLS policies to be run using the session's user name, while not needing to set any securrity models the config file.

My implementation suggestion would be hooking into sql generating and prepending the session user name like this:

conn.Query(ctx, 'SET session authorization howe; SELECT * FROM...')

This would allow reusing connections from the pool (as they already are) without more worries.

While we're at it, why not supporting session variables as well? They could be used on RLS policies, functions, triggers, etc. That woud be just as easy to implement: conn.Query(ctx, `SET supergraph.company_id=1234; SELECT * FROM...')

Of course the '1234' parameter above should be sent as a parameter in the prepared query; that was just an easier to read example.

Let's not forget to reset all those variables before running new queries; the final command should look like something this: conn.Query(ctx, `RESET ALL;SET supergraph.myvar1='xxx'; SET supergraph.myvar2='yyy'; SELECT * FROM...')

I think there should be internally a hook for before sending queries to the server, and the standalone server should have a default implementation getting vars from jwt etc., but when running the server on embedded mode, the caller should be able to customize it. On jwt, the "sub" token should probably be the role name, and the payload could set the session variables.

I didn't have a deep look into the source, but should be easy enough to implement. I could look into it if you're not willing to implement this, but think it's a good idea.

Any comments?

Thanks, Howe

I think we have a problem with filters.

Inspired by this: https://github.com/dosco/super-graph/issues/1#issuecomment-477236321

I used this in my dev.yml:

variables:
    account_id: "select account_id from users where id = $user_id"

- name: user
    tables:
        - name: players
            query:
                filters: ["{ account_id: { _eq: $account_id } }"]

And I get this error:

ERR C:/super-graph/serv/http.go:104 > failed to handle request error="ERROR: invalid input syntax for type bigint: \"select account_id from users where id = 2\" (SQLSTATE 22P02)"

SELECT "_sg_auth_info"."role", (CASE "_sg_auth_info"."role" WHEN 'user' THEN (SELECT json_build_object('player', "__sel_0"."json") as "__root" FROM (SELECT json_build_object('id', "players_0"."id", 'created_at', "players_0"."created_at", 'account_id', "players_0"."account_id", 'amount', "players_0"."amount", 'note', "players_0"."note") AS "json" FROM (SELECT "players"."id", "players"."created_at", "players"."account_id", "players"."amount", "players"."note" FROM "players" WHERE (((("players"."account_id") = 'select account_id from users where id = 2' :: bigint) AND (("players"."id") =  '2' :: bigint))) LIMIT ('1') :: integer) AS "players_0") AS "__sel_0") WHEN 'admin' THEN (SELECT json_build_object('player', "__sel_0"."json") as "__root" FROM (SELECT json_build_object('id', "players_0"."id", 'created_at', "players_0"."created_at", 'account_id', "players_0"."account_id", 'amount', "players_0"."amount", 'note', "players_0"."note") AS "json" FROM (SELECT "players"."id", "players"."created_at", "players"."account_id", "players"."amount", "players"."note" FROM "players" WHERE ((("players"."id") =  '2' :: bigint)) LIMIT ('1') :: integer) AS "players_0") AS "__sel_0") END) FROM (SELECT (CASE WHEN EXISTS (SELECT * FROM users WHERE id = 2) THEN (SELECT (CASE WHEN id = 1000 THEN 'admin' ELSE 'user' END) FROM (SELECT * FROM users WHERE id = 2) AS "_sg_auth_roles_query" LIMIT 1) ELSE 'anon' END) FROM (VALUES (1)) AS "_sg_auth_filler") AS "_sg_auth_info"(role) LIMIT 1

The query is visibly incorrect. A bug or is it my fault?

I have three three tables: film, actor, and film_actor (details below). The following GraphQL query:

query {
  actor {
    actor_id
    first_name
    last_name
    film {
      film_id
    }
  }
}

# Output 

{
  "error": {
    "error": "something wrong no remote ids found in db response",
    "data": null
  }
}

                                              Table "public.film"
      Column      |            Type             | Collation | Nullable |                Default                
------------------+-----------------------------+-----------+----------+---------------------------------------
 film_id          | integer                     |           | not null | nextval('film_film_id_seq'::regclass)
 title            | character varying(255)      |           | not null | 
 description      | text                        |           |          | 
 release_year     | year                        |           |          | 
 language_id      | smallint                    |           | not null | 
 rental_duration  | smallint                    |           | not null | 3
 rental_rate      | numeric(4,2)                |           | not null | 4.99
 length           | smallint                    |           |          | 
 replacement_cost | numeric(5,2)                |           | not null | 19.99
 rating           | mpaa_rating                 |           |          | 'G'::mpaa_rating
 last_update      | timestamp without time zone |           | not null | now()
 special_features | text[]                      |           |          | 
 fulltext         | tsvector                    |           | not null | 
Indexes:
    "film_pkey" PRIMARY KEY, btree (film_id)
    "film_fulltext_idx" gist (fulltext)
    "idx_fk_language_id" btree (language_id)
    "idx_title" btree (title)
Foreign-key constraints:
    "film_language_id_fkey" FOREIGN KEY (language_id) REFERENCES language(language_id) ON UPDATE CASCADE ON DELETE RESTRICT
Referenced by:
    TABLE "film_actor" CONSTRAINT "film_actor_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT
    TABLE "film_category" CONSTRAINT "film_category_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT
    TABLE "inventory" CONSTRAINT "inventory_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
    film_fulltext_trigger BEFORE INSERT OR UPDATE ON film FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger('fulltext', 'pg_catalog.english', 'title', 'description')
    last_updated BEFORE UPDATE ON film FOR EACH ROW EXECUTE PROCEDURE last_updated()

                                            Table "public.actor"
   Column    |            Type             | Collation | Nullable |                 Default                 
-------------+-----------------------------+-----------+----------+-----------------------------------------
 actor_id    | integer                     |           | not null | nextval('actor_actor_id_seq'::regclass)
 first_name  | character varying(45)       |           | not null | 
 last_name   | character varying(45)       |           | not null | 
 last_update | timestamp without time zone |           | not null | now()
Indexes:
    "actor_pkey" PRIMARY KEY, btree (actor_id)
    "idx_actor_last_name" btree (last_name)
Referenced by:
    TABLE "film_actor" CONSTRAINT "film_actor_actor_id_fkey" FOREIGN KEY (actor_id) REFERENCES actor(actor_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
    last_updated BEFORE UPDATE ON actor FOR EACH ROW EXECUTE PROCEDURE last_updated()

                         Table "public.film_actor"
   Column    |            Type             | Collation | Nullable | Default 
-------------+-----------------------------+-----------+----------+---------
 actor_id    | smallint                    |           | not null | 
 film_id     | smallint                    |           | not null | 
 last_update | timestamp without time zone |           | not null | now()
Indexes:
    "film_actor_pkey" PRIMARY KEY, btree (actor_id, film_id)
    "idx_fk_film_id" btree (film_id)
Foreign-key constraints:
    "film_actor_actor_id_fkey" FOREIGN KEY (actor_id) REFERENCES actor(actor_id) ON UPDATE CASCADE ON DELETE RESTRICT
    "film_actor_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
    last_updated BEFORE UPDATE ON film_actor FOR EACH ROW EXECUTE PROCEDURE last_updated()

What would you like to be added: Right now, in order to start Graphjin docker-compose pulls it and Postgres docker image as its dependency for the whole setup. Unfortunately, there is no straightforward way of setting up Graphjin using a pre-existing database

A command like

graphjin new blog --local

That will create a docker-compose with only Graphjin as a service and point graphjin to already running postgres database (barebones on my machine or remote)

Why is this needed: For adopting Graphjin in already running projects (databases) Will save time to deliver products

What would you like to be added:

A few months ago, we discussed adding a feature for Camel Case and there is a setting to control this behavior.

I'd like to see this extended to mutated variables as well.

{ "insert": { "id": "01FVPABVWGDG4VBD76N9XCBWwg", "first_name": "Elliot", "last_name": "something", "email": "fragile_something", "policy_group_id": "01FVPABVWFS242TE0FCX5J7RHH", "phone": "something", "dob": "2022-01-01" } }

I'd prefer to be able to provide camel case variables and they'd be converted into the snake case vars.

Why is this needed: Well, I can't recall the term for it. But if I'm querying the database and get a user for example, I should be able to copy the dataset returned and paste it as update variables and it should process.

Which means that the system should accept the variables for camel case when mutating the object.

What would you like to be added:

Maybe we should not throw an error when no row is found.

Why is this needed:

Today SuperGraph as a library throws in this case with:

error="sql: no rows in result set"

Maybe we should answer with this instead:

{
  "errors": [
    {
      "message": "not found",
      "path": [
        "player"
      ]
    }
  ],
  "data": null
}

I think this is more corresponding with what all the other GraphQL backends return and all the GraphQL clients expect in response.

And even if I'm not an expert in GraphQL syntax, I think this is what is foreseen by the standard.

What do you think?

Hi,

I could able to generate files using the below commands,

go get github.com/dosco/super-graph super-graph new testapp

After that, I tried to start services using docker-compose file,

ashar@testing:~/testapp$ docker logs -f testapp_testapp_api_1 restarting "./super-graph" when it changes (additional dirs: ./config) INF roles_query not defined: attribute based access control disabled ERR failed to initialize Super Graph: error fetching version: failed to connect to host=db user=postgres database=testapp_development: server error (FATAL: database "testapp_development" does not exist (SQLSTATE 3D000))

``

Upon searching I could able to see that the below command to fix the issue but getting some query issues also,

ashar@testing:~/testapp$ docker-compose run testapp_api ./super-graph db:setup Creating network "testapp_default" with the default driver Creating testapp_db_1 ... done INF created database 'testapp_development' INF 2020-06-16 10:15:01 executing 0_init.sql up -- Write your migrate up statements here

CREATE TABLE public.users ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, full_name text, email text UNIQUE NOT NULL CHECK (length(email) < 255), created_at timestamptz NOT NULL NOT NULL DEFAULT NOW(), updated_at timestamptz NOT NULL NOT NULL DEFAULT NOW() );

ERR ERROR: syntax error at or near ";" (SQLSTATE 42601)

Please let me know what is wrong.

What would you like to be added:

Document what it would take to get Super Graph working with Cockroach DB a distributed database that is designed to be compatible with Postgres. Super Graph makes use of a bunch of Postgres specific features like querying for database tables and columns, lateral joins, json functions etc. I have never tried using Super Graph with Cockroach if someone can take the time to do this and document what worked and what did not. And maybe what it would take to get this working.

Why is this needed:

Since Cockroach DB is Postgres compatible this would help Super Graph work with a massively scalable distributed database and possibly help the Cockroach DB team see what gaps can be filled in their compatibility layer.

What version of GraphJin are you using? graphjin version

v0.21.9 (Go binary install)

Have you tried reproducing the issue with the latest release?

Yes

What is the hardware spec (RAM, OS)?

16GB, Fedora 37

Steps to reproduce the issue (config used to run GraphJin).

1. Add following to 0_init.sql

CREATE TYPE employee_type AS ENUM ('Contract', 'Permenant');

CREATE TABLE employees (
  id SERIAL,
  eid VARCHAR(10) PRIMARY KEY,
  e_type employee_type NOT NULL,
  e_name VARCHAR(100) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
);

2. Create employees.csv in current directory

eid,e_type,e_name,e_gender
'00008,Contract,John

3. Add following line to seed.js

import_csv("employees", "employees.csv");

Expected behaviour and actual result.

❯ docker-compose run api db setup

FATAL   Error with copy-from: ERROR: COPY from stdin failed: unable to encode "Contract" into binary format for unknown type (OID 16394): cannot find encode plan (SQLSTATE 57014) (line no 2)

Notes

This seems to coming from this line probably a bug in an external dependency,

current solution is to avoid enum custom types and using check constraints.

What would you like to be added:

I am wondering if there is merit in adding an option to use SSE for subscription updates ?

Why is this needed:

Partly because I am having problems with web sockets and so reaching out to see if SSE would be better. It’s a far simpler protocol .

I am also curious how the backend manages what users are subscribed to what . Is this able to be inspected somehow or even better to have that data available as a change stream over SSE.

There is just a small paragraph about JWT in the docs. It doesn't do any service for one of most used ways of authentication nowadays. It lacks details about how to actually go about it. Maybe an example or a short tutorial of sort will be fine. Thank you

What would you like to be added: I really like Surreraldb and I think It will be a great match with Graphjin since it doesn't need migration and have graph support as well. It uses similar SQL to other SQL databases. Why is this needed: Faster development, Faster app response (it is written in rust), ... the only down side is that it's in beta now but works fine for me so far. thanks.

What would you like to be added: Update of multiple items via a single mutation:

mutation {
  plans(update: $data) {
    id
    name
  }
}

where data is:

[{ id: 10, name: "plan1" }, { id: 11, name: "plan2" }]

Why is this needed: I don't believe this is possible currently without implementing a custom mutation, and I think this abstraction is generically helpful.

The documentation suggests that this is possible with upsert: https://github.com/dosco/graphjin/wiki/Guide-to-GraphQL#bulk-upsert

However, I haven't got this to work successfully via the packaged Graphjin UI - the error message coming back is complaining there is a missing where clause, implying the primary key is not matched if this behaviour is indeed intended.

I'm very aware this might be my misunderstanding, so apologies in advance if that is the case

Summary:

I stumbled on this project via https://www.percona.com/resources/videos/automagical-graphql-sql-compiler and I have a use-case which would suit perfectly with this project. However, this might need the addition of a feature.

The current problem statement is to accept a graphql request via AWS Lambda (or any serverless framework), spin up the lambda, convert the graphql to plain sql, run the query, fetch/insert the result, return the reponse. I use Postgresql.

I am currently researching and have almost finalised doing this via the Postgraphile library in Javascript, making use of its --write-cache CLI option to write the schema and relationship data discovered in a storage medium (or package it in the Lambda), and then use the --read-schema CLI option to fetch this cached data, validate the graphql query against it and return the result to caller. (I am not using CLIs anymore, but the functions that are being called by the CLI, but that is just an implementation detail for now).

What would you like to be added: Option/method to save the discovered data to a file or some cache.

Why is this needed:

I would like to use graphjin to do something as explained above, however, I am unable to find any documentation to save the discovered data. In serverless framework, one does not need to discover this data for every API call that is made, and hence, this will be a very useful feature, and will allow for efficiency gain for use-cases like mine, performance, and better adoption of the library in general.