This might be an obvious newbie issue, but it seems really weird to me. I created a database, and started postgraphile. I then opened GraphiQL and added a row to a table using a mutation. Querying the data returns it correctly.

However, if I add data directly via an SQL insert, somehow it is always ignored by GraphiQL - it only returns data that was added by GraphiQL. I've inspected the table via SQL and I can't see anything to distinguish the rows. I even logged the query that GraphiQL runs and verifies that it returns all the data as JSON (including the data added via SQL) and there's no obvious different between them. Yet GraphiQL somehow filters out the data that I added via SQL.

Is this expected? If not I can try and give reproduction instructions. It's super weird though so if it is expected you should probably document it at the start of the documentation somewhere.

Some examples to get us started can be found here

Here's what I use on some projects (most error codes have been omitted because they won't make sense for your app):

Error codes

Errors from PostGraphile may come through with one of these error codes.

Error code rules

Some error codes are reserved by
PostgreSQL,
so error codes we use must match these criterea:

• 5 alphanumeric (capitals) letters
• First character must be a letter
• First character cannot be F, H, P or X.
• Third character cannot be 0 or P.
• Fourth character must be a letter.
• Must not end 000

Rewritten, the above rules state:

• Char 1: A-Z except F, H, P, X
• Char 2: A-Z0-9
• Char 3: A-Z0-9 except 0, P
• Char 4: A-Z
• Char 5: A-Z0-9

Common

• SELF0: you can't do that to yourself (e.g. remove admin from yourself)
• ERROR: invalid arguments
• UNSUB: error unsubscribing - perhaps your token was wrong?

graphile/crystal#588 (comment)

locally i get the graphql context call in 20ms
on lambda, 200ms+ every time...same exact code
...
It's memory allocation! upgraded from 128mb to 512mb, we flying!
-- https://gitter.im/postgraphql/postgraphql?at=5a560f5f6117191e614b64a7

Replaces graphile/crystal#874

• Note that disabling GraphiQL does NOT prevent people from inspecting your schema/documentation/etc - they can just run it themselves
• Beware various security issues, break down by types of auth: JWT, cookies, etc
• Prevent DOS attacks - analyse incoming requests for complexity and only allow through if simple enough; require limits for all collections; etc etc. Simplest workaround is to just whitelist the queries you're using
• Ensure logging isn't too much!
• Ensure Row Level Security is enabled on every table

More to come

See: graphile/graphile-engine#69

It's in the console but would help to set expectations.

HT @chadfurman:

You say:

We can detect one-to-many and many-to-one relations. One-to-one and many-to-many relations are exposed in the same way but do not always give the best API currently.

Looks like the example only includes one-to-many. One-to-one is less interesting than many-to-many -- it's very common to have a join-table and an example of how that looks would be useful.

https://www.graphile.org/postgraphile/relations/#relations

Current best solution is to do a computed column that returns setof; ref: graphile/crystal#91 (comment)

I'd like to expand support in future so that we have explicit support for many-many and the join table additional fields go on the node edges. This shouldn't be too hard - we'd simply require that the join table had a dual primary key where both key columns reference different tables.

https://twitter.com/bradleyayers/status/1012516146438856704

This issue will probably never close; it's a perennial reminder that the TODOs in the documentation need addressing.

Help doing this is very welcome; you can just fix them one at a time if you like!

Some things are simple "check this example works" - so if you can check it and it works you can delete the text and submit that as a PR 👍

Others are adding examples - which you'd also be very welcome to submit as a PR 👍

Basically what I'm trying to say is:

PRs welcome!

Place to add community plugins and related projects until we come up with something better. Just reference this ticket from the appropriate github issue / repo

https://www.graphile.org/postgraphile/extending/

Currently reads:

Note that the return types of added fields (e.g. JSONType above) do not need to be implemented via Graphile Build's newWithHooks - you can use standard GraphQL objects too. (However, if you do not use newWithHooks then the objects referenced cannot be extended via plugins.)

We do not use newWithHooks in the example (deliberately); but the wording above can be interpreted as if we did. Need to use less ambiguous wording here.

HT @chadfurman:

The example doesn't use "newWithHooks" but instead uses a standard GraphQL object? Maybe a better way to say that would be, "Note that above we used a standard GraphQL object for the return type of our added field. Alternatively, you could use newWithHooks and here's an example..."

HT @chadfurman:

A brief sentence or two on why you're using GraphQLObjectType:fields:field now and not just GraphQLObjectType:fields

It's because we want to use addArgDataGenerator which only makes sense within an individual field and not for all fields of the object; though any plugin which modifies an existing field rather than adding a new one should use ...:fields:field because it makes the intent clearer and gives better controls and less chance of things going wrong.

Should probably link to the All Hooks page, and probably update the wording that's currently:

• GraphQLObjectType:fields to add additional fields to this object type (is ran asynchronously and gets a reference to the final GraphQL Object as Self in the context)
• GraphQLObjectType:fields:field: to add any root-level attributes to an individual field, e.g. add a description

In the documentation I’ve noticed the length of first_name and last_name is validated with a check

I’ve usually have a column with a maximum length on the type, eg: character varying(100)

So I’m wondering, is there differences/benefits between those 2 methods ?

thank you (and sorry for being off topic)

Or add support for them 😁

This is a good reference point: graphile/crystal#485 (comment)

HT @chadfurman

https://www.graphile.org/postgraphile/evaluating/#schema-driven-apis

https://www.graphile.org/postgraphile/extending/#wrapping-an-existing-resolver

HT @chadfurman:

why do we have isRootMutation and fieldName? Why not just fieldName? Would the fieldName ever not be a root mutation?

Reason is that since we're hooking GraphQLObjectType there's no need for it to be a mutation; there could be a regular Query field somewhere deep down with the name createLink and we don't want to modify that too. I think all mutation overrides should use the isRootMutation check - maybe we could even simplify it to just isMutation...?

Reference: graphile/crystal#488

Could you please further explain following points with an example.

Then I modify the JWT role based on whether or not the user is an admin (appname_admin) or not (appname_visitor). I also set a JWT claim for admin for when security definer is used somewhere (since that effectively changes the role) so I can use a current_user_is_admin() method in my DB logic.

(Authenticator is the default role that PostGraphQL assumes, I grant that role only enough permissions to introspect the schema and no more, plus of course the ability to switch to one of the relevant roles based on the JWT)

HT @chadfurman:

Performance implications and limitations of complex RLS policies? Can I do a join table lookup here? nested selects?

https://www.graphile.org/postgraphile/security/

Also expose this via the CLI and via .postgraphilerc

https://www.graphile.org/postgraphile/best-practices/

• Don't expose more than you need to
• Adding indexes (--no-ignore-index)
• Adding permissions
• Creating roles
• Using namespaces
• Using @omit

Throughout the docs, we have ES2018 syntax. It would be good to provide a reference for people working with Node 6 LTS.

https://www.graphile.org/postgraphile/postgresql-schema-design/

If you are going to use UUIDs as the primary key, it is recommended you use uuid_generate_v1mc to generate the ids. This is because uuid_generate_v1mc is time based which means the ids will be mostly sequential which is good for your primary key index.

This is refuted by Bill Moran on this PostgreSQL mailing list thread:

https://www.postgresql.org/message-id/20151222124018.bee10b60b3d9b58d7b3a1839%40potentialtech.com

There's no substance to these claims. Chasing the links around we finally
find this article:
http://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/
which makes the reasonable argument that random primary keys can cause performance robbing fragmentation on clustered indexes. But Postgres doesn't have clustered indexes, so that article doesn't apply at all. The other authors appear to have missed this important point.

One could make the argument that the index itself becomming fragmented could cause some performance degredation, but I've yet to see any convincing evidence that index fragmentation produces any measurable performance issues (my own experiments have been inconclusive).

It would be good to find a canonical source of truth for this one way or the other.

See also: graphile/crystal#578

I'm building a platform where clients submit orders for writing and copywriters fill them. We're likely to use HTML rather than text to capture formatting in the orders and attempts. Naturally I want to sanitize that HTML both before it reaches the database and, for extra safety, before it is returned to the user.

There is, to my knowledge, no HTML sanitization feature in Postgres (please correct me if I'm wrong.) There is, however, a sanitize-html NPM package.

What I'd like to do is create a type, HTML, that is for all intents and purposes a string/text. Before that text is written to the database, or rendered in a query response, it is passed through sanitizeHtml(text) or something similar.

I'll happily package this as a plugin and contribute it back, but either having it documented or knowing which areas of the docs to focus on would be helpful. Thanks!

https://www.graphile.org/postgraphile/extending/#wrapping-an-existing-resolver

HT @chadfurman:

link to docs on addArgDataGenerator, pgQuery.queryBuilder

https://www.graphile.org/postgraphile/extending/

HT @chadfurman

E.g. as compared to Prisma, how/where React, Express, Postgres sit.

PostGraphile's GraphiQL doesn't currently support subscriptions.

HT @mattmeye

https://www.graphile.org/postgraphile/computed-columns/

HT @chadfurman

The triangle element that goes before sections creates a scroll bar.

Think it's cause of the 100vw size which doesn't take into account the scroll bar.

In response to graphile/crystal#438

First, test / verify this works. Second, update stored procedure docs to explain how to only make some paraemters required (i.e. not strict) vs all params required (STRICT)

https://www.graphile.org/graphile-build/look-ahead/#when-initially-defining-the-object-fields

Where does resolveAlias come from?

https://www.graphile.org/graphile-build/look-ahead/#seeing-which-sub-fields-were-requested

This 4th argument is the one we're interested in because it contains a number of goodies.

Perhaps show an example of what resolveInfo is. Maybe like "here is resolveInfo and here is parseResolveInfo(resolveInfo)".

I would also move https://www.graphile.org/graphile-build/#quick-to-start to the top.

I am following the pattern described in here: https://www.graphile.org/postgraphile/testing-jest/#testing-the-graphql-middleware

Using this, the tests never end and cause a Jest did not exit one second after the test run has completed.

So even if I have nothing in the tests, not even calling the runGraphQLQuery, the test suite hangs in the end. After some investigation, this seems to be due to createPostGraphileSchema: if I run only this, the tests never end.
Looking at the DB connections, it seems there is a connection that remains open on an introspection query, waiting for an event Client: ClientRead

if I run the tests with the flag --forceExit the test suite successfully ends. However, I would prefer not doing so as this could hide other issues in the future.

I was wondering if you has the same behavior and how you did get around this.

If you follow the JWT example on the Security page, you will always receive a http 401 error with: {"errors":[{"message":"jwt expired"}]} when the jwt token is sent. The 'jwt expired' comes from node's jwt.verify. Reason:
The exp field should contain the number of seconds since the epoch ( (also known as POSIX time or unix time).

86400 is 2 january 1970

Btw, I use:

• postgraphile 4.0.0-alpha2.27
• node v8.6.0

To fix it, I did this:

create or replace function auth.authenticate(
  email text,
  password text
) returns auth_private.jwt_token as $$
declare
  account auth_private.person_account;
  expire_unixtime integer;
begin
  select a.* into account
    from auth_private.person_account as a
    where a.email = authenticate.email;

  select cast(extract(epoch from current_timestamp) as integer) + 86400 
    into expire_unixtime;

  if account.password_hash = crypt(password, account.password_hash) then
    return (
      'person_role',
      expire_unixtime,
      account.person_id,
      account.is_admin,
      account.username
    )::auth_private.jwt_token;
  else
    return null;
  end if;
end;
$$ language plpgsql strict security definer;

Here's another proposal for a very simple recipe. Again, feel free to do what you want with this.

Creating a viewer/"logged in user" root field

A lot of API:s use the "viewer" convention, putting a field on the root query that references the currently logged in user. If you have your authentication set up properly and a have a way of getting the currently logged in user's user id, you can do this very easily with Postgraphile:

create function my_schema.viewer()
  returns my_schema.users as $$
  select *
  from my_schema.users
  where id = current_user_id(); /* current_user_id is a function that returns the logged in user's id, usually by extracting it from the auth token used */
$$ language sql stable;

This will put a field (viewer) on your root query that returns the logged in user. Very convenient! Check out the docs on security for handling authorization and adding a current_user_id function.

postgraphql/postgraphql.github.io#4 (comment)

The postgres query string library doesn't support sslmode=require instead requiring ssl=1.

Example: postgresql://username:[email protected]/dbname?ssl=1

Ref: graphile/crystal#357

BENJIE'S NOTE: this post was originally written by @calebmer on the PostGraphQL repo (since renamed to PostGraphile); since then we have built a website (https://graphile.org) but many of these points are still salient so we'll keep this issue open until they're dealt with.

PostGraphQL needs a documentation/marketing website. As a frontend engineer I’d love to build it myself, but at the same time PostGraphQL needs improvements in areas like performance. Here is an outline for what I want in the site (of course these specifications are subject to change):

• An attractive landing page.
• An easily accessible GraphiQL interface (this will probably need to be hosted on Heroku).
• A super basic getting started article.
• Forum example tutorial.
• Helpful articles:
  • Example Queries.
  • Documenting your schema (with Postgres COMMENT).
  • Deploying PostGraphQL with Docker.
  • Deploying PostGraphQL with PM2.
  • Extending your database with triggers and LISTEN/NOTIFY.
  • Writing JavaScript or Ruby in your Postgres logic.
  • Understanding graph database terms in GraphQL.
  • Backing up your Postgres database.
  • Why you should use PostGraphQL (persuasive, stop overengineering, etc.)
• Documentation.

…and here’s a quick outline for documentation:

• Tables.
  • Queries.
    • Relationships.
    • Unique keys.
  • Mutations.
• Procuedures.
  • Mutations.
  • Queries.
  • Connections.
  • Computed fields.
• Custom types.
  • Domain types.
  • Enum types.
  • Composite types.
• Authentication/Authorization.
• PostGraphQL as a CLI tool.
• PostGraphQL as HTTP middleware, basic examples of the web techs would be awesome:
  • http
  • express
  • connect
  • koa

consider updating the docs for testing.

https://github.com/graphile/graphile.github.io/blob/develop/src/pages/postgraphile/testing-jest.md

here's a small improvement I've made.

https://gist.github.com/lifeiscontent/02307f35e10fd60044af012889298fbf

e.g. demonstrate settings you want to be period-less (e.g. role, statement_timeout which affect PostgreSQL directly) and settings you want to have periods in (e.g. jwt.claims.user_id etc which don't affect PostgreSQL directly but your own application code can use).

This page: https://www.graphile.org/postgraphile/usage-library/#pgsettings-function

This file: https://github.com/graphile/graphile.github.io/blob/develop/src/pages/postgraphile/usage-library.md

Here's a recipe for single mutations that create multiple rows in different tables. Feel free to review and change as you see fit. I hope it's understandable, and thank you for the help with understanding how this works.

Single mutations that create multiple rows in different tables

Occasionally you'll want to create a bunch of rows in different tables in a single mutation. Here's an example of how to do that.

Pretend we're registering quiz entries, and we want to store each answer in its own table as we want to be able to operate on the answers independently later.

This means we want:

1. A mutation that takes input data for inserting one quiz entry and multiple answers.
2. A function that inserts a new quiz entry, inserts an answer for each answer provided in the input data, and connects each answer to the created quiz entry.
3. Finally, we want the function to return the inserted quiz entry itself.

Here's the setup:

create table quiz_entry (
  id serial primary key,
  user_id text not null references users(id),
  created_at timestamp default now() not null,
  updated_at timestamp default now() not null,
  quiz_id integer not null references quiz(id) /* This table models the quiz taken */
);

create table quiz_entry_answer (
  id serial primary key,
  user_id text not null references users(id),
  quiz_entry_id integer not null references quiz_entry(id),
  question text not null,
  answer integer,
  created_at timestamp default now() not null,
  updated_at timestamp default now() not null
);

/**
 * This type is used for input in the mutation
 */

create type quiz_entry_input as (
  question text,
  answer integer
);

create function add_quiz_entry(
  quiz_id integer,
  answers quiz_entry_input[]
)
  returns quiz_entry as $$
  declare
    q quiz_entry;
    a quiz_entry_answer;
  begin
    insert into quiz_entry(user_id, quiz_id)
      values(current_user_id(), quiz_id)
      returning * into q;

    foreach a in array answers loop
      insert into quiz_entry_answer(user_id, quiz_entry_id, question, answer)
        values (current_user_id(), quiz_id, a.question, a.answer);
    end loop;
    return q;
  end;
$$ language plpgsql volatile strict;

There! This will create a mutation that takes a quiz id and a series of answers in the shape of [{ question: string, answer: number }]. It'll add a new quiz entry, add all answers, connect them, and then finally return the created quiz entry.

https://www.graphile.org/postgraphile/why-nullable/

Currently the snippet (incorrectly) marks all relations as non-nullable. Should only apply if the columns themselves are non-nullable.

Something like this: (needs testing)

module.exports = function NonNullRelationsPlugin(builder) {
  builder.hook('GraphQLObjectType:fields:field', (field, build, context) => {
    const { isPgForwardRelationField, pgFieldIntrospection } = context.scope;
    if (isPgForwardRelationField) {
      const linkedAttributeNums = pgFieldIntrospection.keyAttributeNums;
      const relationIsNotNull = pgFieldIntrospection.class.attributes.filter(
        attr => linkedAttributeNums.indexOf(attr.num) >= 0
      ).every(attr => attr.isNotNull || attr.type.domainIsNotNull);

      if (relationIsNotNull) {
        return {
          ...field,
          type: new build.graphql.GraphQLNonNull(field.type),
        };
      }
    }

    return field;
  });
};

HT @enisdenjo

Discussion: graphile/crystal#47

https://www.graphile.org/postgraphile/filtering/

HT @chadfurman