Postgraphile: Quick questions before using it

[mytototo]

Hey! I've been playing with PostGraphQL v2 today to see if I can use it for a project. For now it looks like it's a good fit. But I have a few questions:

1. I need to limit access to some data depending on the user. For example, a user can only access his projects. What solution do you advise to have ACL using PostGraphQL?
2. Can we use default GraphQL mutations? I tried but no way to make them work. The only way I've found for mutations is using the PostgreSQL procedures like written in the doc.
3. Regarding the PostgreSQL procedures, how should we version them? What's your experience about it? I never used it.
4. Can we have automatic lifecycle events? For example, trigger a createProject mutation every time a createUser mutation is called.

Awesome project!
Thank you for your time.

[calebmer]

Hey @mytototo, glad you are interested :blush:

Before I answer your questions, I wasn’t planning on releasing this yet but you have a couple questions which this could answer. With the PostGraphQL 2 release I also want to release a Postgres schema design tutorial which will go over in depth the process of designing a schema for PostGraphQL. Here’s the preview, tell me what you think, tell me what it’s lacking. It should be done by next Monday. I’ll reference sections applicable to you in the answers below 👍

1. Of course! Postgres 9.5+ has rich Row Level Security (RLS) functionality which you can leverage with PostGraphQL. I haven’t gotten to this point in the article I’m writing yet, but there’s a great example of RLS in the Relay example (#66) @ferdinandsalis is working on.
2. I assume what you mean by this is mutations where the input is flattened out as arguments and the output is returned directly instead of in a payload field? We follow the Relay Input Object Mutations specification when it comes to mutations, but that doesn’t mean you have to use Relay! The thing about Relay specifications, and this includes the connection specification, is that they aren’t just Relay requirements, but also best practice GraphQL. In this case, it is actually incredibly handy that there is only one argument in the input argument because you can just pass in a JavaScript object instead of coordinating arguments in the GraphQL syntax. It may look like query X($a: Int, $b: Int, $c: Int...), but instead it is just query X($input: CreatePersonInput). As for the payload it is incredibly helpful to have the capability to add extra fields. This can allow mutations to return multiple things. I’d love to hear what your idea for what mutations should look like is, and I’d be happy to add a flag if enough people want it. However, I really do think the Relay specification nailed it for mutations.
3. You’d version your procedures similar to how you version the rest of your schema. When you want to change an implementation, just drop and replace! The CREATE FUNCTION command also lets you specify create or replace function …. In a migration file you can just create or replace your functions as needed. In your “down” migration you’d probably want to copy/paste the old implementation. To be honest, there is no great solution for this, and the SQL migration tool landscape is fragmented. I’d be happy to endorse a solution from the community!
4. As for automatic lifecycle events, these come native in Postgres. Just use a trigger :blush:. In the article I’m writing there is a section addressing triggers. The command you would use is called CREATE TRIGGER. If you would like to do extra work in a Node.js worker, you could also use the Postgres NOTIFY feature with a package like pg-pubsub. The advantage to this approach is that you get _all_ events generated by Postgres, not just those that go through PostGraphQL. It may also be easier to scale the services separately. I do foresee PostGraphQL adding lifecycle events in the future if we want to support MySQL, but that’s a rather long ways away.

Hope that helps :+1:

[mytototo]

Thank you very much for your quick and well detailed answer @calebmer.

1. If I understand correctly, you advise me to use the PostgreSQL RLS with PostGraphQL, right? I saw the example but it seems a bit abstract for me. Do you have a minimalist example only using PostGraphQL without any front-end?
2. I looked for a query using something like CreatePersonInput but I couldn't find one working...
3. Looks like a good option you're right. Thanks!
4. Awesome. I'll take a look at it.

Thank you very much.

[bendiy]

@calebmer On 2, I was trying to figure out mutations yesterday in GraphiLQ and couldn't get anything to work. Reading the GraphQL docs wasn't working (query X($a: Int, $b: Int, $c: Int...)). Now that you pointed out the Relay link, https://facebook.github.io/relay/graphql/mutations.htm, I might be able to get it to work.
Please inlucde some mutations examples in the forum_example docs:
https://github.com/calebmer/postgraphql/tree/master/examples/forum#sample-queries

[bendiy]

@mytototo For 1, take a look at this example: http://postgrest.com/examples/blog/

[calebmer]

Thanks @mytototo and @bendiy.

For 1, definitely look at the PostgREST example @bendiy linked, it’s great. Also check out the Postgres documentation for the CREATE POLICY command :+1:

As for example mutations in 2, the queries in the postgraphql/src/postgraphql/__tests__/fixtures/queries directory (yes I know, a little hard to find…) are great working examples. Note that these are tests, so they aren’t always pretty :wink:. I’ll definitely find a place to put good example queries.

I’ll paste some from the tests here though for now:

Updates

mutation {
  a: updatePerson(input: {
    __id: "WyJwZW9wbGUiLDFd",
    personPatch: {
      name: "John Smith Sr."
      about: "An older John Smith"
    }
  }) { ...updatePersonPayload }
  b: updatePerson(input: {
    clientMutationId: "hello"
    __id: "WyJwZW9wbGUiLDJd",
    personPatch: {
      name: "Sarah Smith"
      email: "[email protected]"
    }
  }) { ...updatePersonPayload }
  c: updatePerson(input: {
    clientMutationId: "world"
    __id: "WyJwZW9wbGUiLDJd"
    personPatch: {
      about: "Now with an “H.”"
    }
  }) { ...updatePersonPayload }
  d: updatePersonById(input: {
    id: 3
    personPatch: {
      name: "Best Pal"
      about: "I have taken over Budd’s account. Hehehe."
    }
  }) { ...updatePersonPayload }
  e: updatePersonByEmail(input: {
    email: "[email protected]"
    personPatch: {
      about: "Make art not friends."
    }
  }) { ...updatePersonPayload }
  f: updateCompoundKey(input: {
    __id: "WyJjb21wb3VuZF9rZXlzIiwxLDJd"
    compoundKeyPatch: {
      personId1: 2
      extra: true
    }
  }) { ...updateCompoundKeyPayload }
  g: updateCompoundKeyByPersonId1AndPersonId2(input: {
    clientMutationId: "hello"
    personId1: 2
    personId2: 2
    compoundKeyPatch: {
      personId1: 3
      extra: false
    }
  }) { ...updateCompoundKeyPayload }
  h: updateCompoundKeyByPersonId1AndPersonId2(input: {
    clientMutationId: "world"
    personId1: 4
    personId2: 3
    compoundKeyPatch: {
      extra: false
    }
  }) { ...updateCompoundKeyPayload }
}

fragment updatePersonPayload on UpdatePersonPayload {
  clientMutationId
  person {
    __id
    id
    name
    email
    about
  }
}

fragment updateCompoundKeyPayload on UpdateCompoundKeyPayload {
  clientMutationId
  compoundKey {
    __id
    personId1
    personId2
    extra
    personByPersonId1 {
      id
      name
    }
    personByPersonId2 {
      id
      name
    }
  }
}

Deletes

mutation {
  a: deletePost(input: { __id: "WyJwb3N0cyIsMV0=" }) { ...deletePostPayload }
  b: deletePost(input: { __id: "WyJwb3N0cyIsMl0=", clientMutationId: "hello" }) { ...deletePostPayload }
  c: deletePost(input: { __id: "WyJwb3N0cyIsMjAwMF0=" }) { ...deletePostPayload }
  d: deletePost(input: { __id: "WyJwb3N0cyIsM10=", clientMutationId: "world" }) { ...deletePostPayload }
  e: deletePostById(input: { id: 6 }) { ...deletePostPayload }
  f: deletePostById(input: { id: 9, clientMutationId: "hello" }) { ...deletePostPayload }
  g: deletePostById(input: { id: 2000 }) { ...deletePostPayload }
  h: deletePostById(input: { id: 11, clientMutationId: "world" }) { ...deletePostPayload }
  i: deleteCompoundKey(input: { __id: "WyJjb21wb3VuZF9rZXlzIiw0LDNd" }) { ...deleteCompoundKeyPayload }
  j: deleteCompoundKeyByPersonId1AndPersonId2(input: { personId1: 2, personId2:3 }) { ...deleteCompoundKeyPayload }
    k: deletePersonByEmail(input: { email: "[email protected]" }) { ...deletePersonPayload }
}

fragment deletePostPayload on DeletePostPayload {
  clientMutationId
  deletedPostId
  post {
    __id
    id
    headline
    authorId
  }
}

fragment deleteCompoundKeyPayload on DeleteCompoundKeyPayload {
  clientMutationId
  deletedCompoundKeyId
  compoundKey {
    __id
    personId1
    personId2
    personByPersonId1 {
      __id
      name
    }
    personByPersonId2 {
      __id
      name
    }
  }
}

fragment deletePersonPayload on DeletePersonPayload {
  clientMutationId
  deletedPersonId
  person {
    __id
    id
    name
    email
  }
}

Creates

mutation {
  a: createType(input: {
    type: {
      id: 201
      smallint: 30
      bigint: 50
      boolean: false
      varchar: "abc"
      enum: RED
      domain: 6
      domain2: 5
      textArray: ["have", "you", "ever", "been", "down", "the", "water", "spout", "?"]
      json: "{\"x\":1,\"y\":2,\"z\":3}"
      jsonb: "{\"a\":1,\"b\":2,\"c\":3}"
      numrange: {
        start: { value: 50, inclusive: true }
      }
      daterange: {
        start: { value: "1927-11-05", inclusive: false }
        end: { value: "1927-11-07", inclusive: false }
      }
      anIntRange: {
        end: { value: 500, inclusive: false }
      }
      timestamp: "2016-10-07 16:12:21.747269"
      timestamptz: "2016-10-09 16:12:45.218676-04"
      date: "2016-10-15"
      time: "19:13:18.625699"
      timetz: "13:13:29.585176"
      interval: {
        seconds: 1
        minutes: 2
        hours: 3
        days: 4
        months: 5
        years: 6
      }
      money: 5000
      compoundType: {
        a: 123,
        b: "abc"
        c: GREEN
        d: "ec4a9fae-4ec5-4763-98eb-0327fb2dc9bf"
        fooBar: 20
      }
      nestedCompoundType: {
        a: {
          a: 456
          b: "def"
          c: BLUE
          d: "79863dcf-0433-4c3d-bc51-978326d4546f"
          fooBar: 42
        }
        b: {
          a: 789
          b: "ghi"
          c: RED
          d: "b687ee42-c515-4544-b742-525e39517e7d"
          fooBar: -8
        }
        bazBuz: 0
      }
    }
  }) {
    clientMutationId
    type {
      __id
      id
      smallint
      bigint
      boolean
      varchar
      enum
      domain
      domain2
      textArray
      json
      jsonb
      numrange {
        start { value inclusive }
        end { value inclusive }
      }
      daterange {
        start { value inclusive }
        end { value inclusive }
      }
      anIntRange {
        start { value inclusive }
        end { value inclusive }
      }
      timestamp
      timestamptz
      date
      time
      timetz
      interval { seconds minutes hours days months years }
      money
      compoundType { a b c d fooBar }
      nestedCompoundType {
        a { a b c d fooBar }
        b { a b c d fooBar }
        bazBuz
      }
    }
  }
  b: createPerson(input: {
    person: {
      id: 9000
      name: "John Smith Jr."
      email: "[email protected]"
      about: "Son of Sara and John Smith."
    }
  }) { ...createPersonPayload }
  c: createPerson(input: {
    clientMutationId: "hello",
    person: {
      id: 20
      name: "Best Pal"
      email: "[email protected]"
      about: "My archnemisis is Budd Deey."
    }
  }) { ...createPersonPayload }
  d: createCompoundKey(input: {
    clientMutationId: "world",
    compoundKey: {
      personId1: 9000
      personId2: 20
      extra: false
    }
  }) {
    clientMutationId
    compoundKey {
      __id
      personId1
      personId2
      extra
      personByPersonId1 {
        __id
        name
      }
      personByPersonId2 {
        __id
        name
      }
    }
    personByPersonId1 {
      __id
      name
    }
    personByPersonId2 {
      __id
      name
    }
  }
  e: createEdgeCase(input: {
    edgeCase: {
      notNullHasDefault: true
    }
  }) {
    edgeCase {
      notNullHasDefault
    }
  }
  f: createEdgeCase(input: {
    edgeCase: {}
  }) {
    edgeCase {
      notNullHasDefault
    }
  }
}

fragment createPersonPayload on CreatePersonPayload {
  clientMutationId
  person {
    __id
    id
    name
    email
    about
  }
  a: personEdge(orderBy: PRIMARY_KEY_ASC) { ...peopleEdge }
  b: personEdge(orderBy: PRIMARY_KEY_DESC) { ...peopleEdge }
  c: personEdge(orderBy: ID_ASC) { ...peopleEdge }
  d: personEdge(orderBy: ID_DESC) { ...peopleEdge }
  e: personEdge(orderBy: EMAIL_ASC) { ...peopleEdge }
  f: personEdge(orderBy: EMAIL_DESC) { ...peopleEdge }
  g: personEdge(orderBy: NATURAL) { ...peopleEdge }
}

fragment peopleEdge on PeopleEdge {
  cursor
  node {
    __id
    name
  }
}

[bendiy]

Using v2.0
Run the forum_example schema:
https://github.com/calebmer/postgraphql/blob/next/examples/forum/schema.sql
See bug here:
https://github.com/calebmer/postgraphql/pull/145#discussion_r83100172

1. Add data to the tables:
   forum_example.person

sql INSERT INTO person (id, first_name, last_name, about, created_at, updated_at) VALUES ('e5bc0440-7cdc-4bbf-922b-42ece0e7ed01', 'Kathryn', 'Ramirez', NULL, '2016-10-12 14:42:12.267381', NULL) ('96e1d1e2-b291-4e8a-972a-462bd8996b29', 'Johnny', 'Tucker', NULL, '2016-10-12 14:42:12.267381', NULL) ('e71aea31-14e1-4fa0-9c7c-fa0a64349cf2', 'Nancy', 'Diaz', NULL, '2016-10-12 14:42:12.267381', NULL) ('5d1ad664-cb91-482d-b94e-1c1e185da13a', 'Russell', 'Gardner', NULL, '2016-10-12 14:42:12.267381', NULL) ('7e7f2ae2-0248-4324-9be7-2fd329f01999', 'Ann', 'West', NULL, '2016-10-12 14:42:12.267381', NULL) ('dd8b54f8-e4c4-4b78-b733-bae0f94ade90', 'Joe', 'Cruz', NULL, '2016-10-12 14:42:12.267381', NULL) ('62cf9a8e-1be7-4b5f-8241-72d3985677b2', 'Scott', 'Torres', NULL, '2016-10-12 14:42:12.267381', NULL) ('c244d56f-34a5-49de-85bc-2e1b87dbe1e8', 'David', 'Bell', NULL, '2016-10-12 14:42:12.267381', NULL) ('0d6d797d-d689-46fc-80c1-b491d5171736', 'Carl', 'Ward', NULL, '2016-10-12 14:42:12.267381', NULL) ('239f5e8a-1fab-4d40-abc2-2badd03735b7', 'Jonathan', 'Campbell', NULL, '2016-10-12 14:42:12.267381', NULL) ('6b7be995-1347-4391-a44e-ea019019787c', 'Beverly', 'Kelly', NULL, '2016-10-12 14:42:12.267381', NULL) ('9b2c06d4-a6ca-4d2f-9061-07618e81faaf', 'Kelly', 'Reed', NULL, '2016-10-12 14:42:12.267381', NULL) ('7a93ebaa-46cb-4221-bc74-3edc29f500f8', 'Nicholas', 'Perry', NULL, '2016-10-12 14:42:12.267381', NULL) ('d657dbc8-ab82-4415-a52c-1eaa4118eb26', 'Carol', 'Taylor', NULL, '2016-10-12 14:42:12.267381', NULL);

forum_example.post

sql INSERT INTO post (id, author_id, headline, topic, body, created_at, updated_at) VALUES ('b95287a8-03d3-45c6-91ce-88bb33d49fb8', '239f5e8a-1fab-4d40-abc2-2badd03735b7', 'No… It’s a thing; it’s like a plan, but with more greatness.', NULL, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut ullamcorper, sem sed pulvinar rutrum, nisl dui faucibus velit, eget sodales urna mauris nec lorem. Vivamus faucibus augue sit amet semper fringilla. Cras nec vulputate eros. Proin fermentum purus posuere ipsum accumsan interdum. Nunc vitae urna non mauris pellentesque sodales vel nec elit. Suspendisse pulvinar ornare turpis ac vestibulum. Cras eu congue magna. Nulla vel sodales enim, vel semper dolor. Curabitur pellentesque dolor elit. Aenean cursus posuere dui, vitae mollis felis rhoncus ac. In at orci a erat congue consequat ut sed risus. Etiam euismod elit eu lobortis varius. Praesent lacinia lobortis nisi, vel faucibus turpis sodales in. In interdum lectus tellus, facilisis mollis diam feugiat vitae.', '2016-10-12 14:44:19.062302', NULL) ('d6b79f07-837a-4387-95f8-90a38b442866', '0d6d797d-d689-46fc-80c1-b491d5171736', 'I hate yogurt. It’s just stuff with bits in.', 'inspiration', NULL, '2016-10-12 14:44:19.062302', NULL) ('736de26d-35f8-4cdd-83a5-9fea9d416884', '0d6d797d-d689-46fc-80c1-b491d5171736', 'You hit me with a cricket bat.', NULL, NULL, '2016-10-12 14:44:19.062302', NULL) ('041ce051-5e3f-4e36-a5eb-6d6d0e0c76d8', '6b7be995-1347-4391-a44e-ea019019787c', 'Please, Don-Bot… look into your hard drive, and open your mercy file!', NULL, NULL, '2016-10-12 14:44:19.062302', NULL) ('d3ae23f0-ee30-46c2-8ee4-8ce0b7633969', '5d1ad664-cb91-482d-b94e-1c1e185da13a', 'Stop talking, brain thinking. Hush.', NULL, NULL, '2016-10-12 14:44:19.062302', NULL) ('5d4aa8ae-d861-43b2-bca0-7ed4f57147de', '0d6d797d-d689-46fc-80c1-b491d5171736', 'Large bet on myself in round one.', 'discussion', NULL, '2016-10-12 14:44:19.062302', NULL) ('66d537f4-107f-42e5-8082-1ba425277a8c', '239f5e8a-1fab-4d40-abc2-2badd03735b7', 'It’s a fez. I wear a fez now. Fezes are cool.', 'inspiration', NULL, '2016-10-12 14:44:19.062302', NULL) ('24a75ff8-ec66-4381-9437-0dcdd4fe8885', '5d1ad664-cb91-482d-b94e-1c1e185da13a', 'You know how I sometimes have really brilliant ideas?', NULL, NULL, '2016-10-12 14:44:19.062302', NULL) ('d707dc39-d7fd-4503-b9f1-3a577697e139', '239f5e8a-1fab-4d40-abc2-2badd03735b7', 'What’s with you kids? Every other day it’s food, food, food.', 'discussion', NULL, '2016-10-12 14:44:19.062302', NULL) ('f6e9be1e-1f59-41e0-9d8f-55e44363e1a9', '5d1ad664-cb91-482d-b94e-1c1e185da13a', 'They’re not aliens, they’re Earth…liens!', 'help', NULL, '2016-10-12 14:44:19.062302', NULL) ('b2cbf864-f1b8-4602-b585-fae1fa236cf5', '6b7be995-1347-4391-a44e-ea019019787c', 'You’ve swallowed a planet!', NULL, NULL, '2016-10-12 14:44:19.062302', NULL) ('590d8e09-b679-4ba2-be31-4ddd9cc5546b', '0d6d797d-d689-46fc-80c1-b491d5171736', 'Is that a cooking show?', 'inspiration', NULL, '2016-10-12 14:44:19.062302', '2016-10-13 11:04:37.403943');

1. Start PostGraphQL server pointing it at the forum_example schema.
2. Open GraphiQL.
3. Query all posts:
   Query:

graphql query { allPosts { nodes { __id id authorId headline topic body } } }

Results:

graphql { "data": { "allPosts": { "nodes": [ { "__id": "WyJwb3N0cyIsIjA0MWNlMDUxLTVlM2YtNGUzNi1hNWViLTZkNmQwZTBjNzZkOCJd", "id": "041ce051-5e3f-4e36-a5eb-6d6d0e0c76d8", "authorId": "6b7be995-1347-4391-a44e-ea019019787c", "headline": "Please, Don-Bot… look into your hard drive, and open your mercy file!", "topic": null, "body": null }, { "__id": "WyJwb3N0cyIsIjI0YTc1ZmY4LWVjNjYtNDM4MS05NDM3LTBkY2RkNGZlODg4NSJd", "id": "24a75ff8-ec66-4381-9437-0dcdd4fe8885", "authorId": "5d1ad664-cb91-482d-b94e-1c1e185da13a", "headline": "You know how I sometimes have really brilliant ideas?", "topic": null, "body": null }, { "__id": "WyJwb3N0cyIsIjU5MGQ4ZTA5LWI2NzktNGJhMi1iZTMxLTRkZGQ5Y2M1NTQ2YiJd", "id": "590d8e09-b679-4ba2-be31-4ddd9cc5546b", "authorId": "0d6d797d-d689-46fc-80c1-b491d5171736", "headline": "Is that a cooking show?", "topic": "INSPIRATION", "body": null }, ... ] } } }

1. Mutate a post headline using the GraphiQL QUERY VARIABLES section at the bottom:
   Query:

graphql mutation ($input: UpdatePostInput!) { updatePost(input: $input) { clientMutationId post { headline } } }

QUERY VARIABLES:

json { "input": { "clientMutationId": "something-random-here", // Note: Get this __id from your allPosts query. It might be different. "__id": "WyJwb3N0cyIsIjU5MGQ4ZTA5LWI2NzktNGJhMi1iZTMxLTRkZGQ5Y2M1NTQ2YiJd", "postPatch": { "headline": "This headline mutated" } } }

Result:

json { "data": { "updatePost": { "clientMutationId": "something-random-here", "post": { "headline": "This headline mutated" } } } }

1. Alternatively, do it all in the query:
   Query:

graphql mutation { updatePost(input: { __id: "WyJwb3N0cyIsIjU5MGQ4ZTA5LWI2NzktNGJhMi1iZTMxLTRkZGQ5Y2M1NTQ2YiJd", clientMutationId: "green-slime" postPatch: { headline: "This headline mutated" } }) { clientMutationId post { headline } } }

Result:

json { "data": { "updatePost": { "clientMutationId": "green-slime", "post": { "headline": "This headline mutated" } } } }

[calebmer]

The documentation article is complete if you want to see it go here: https://github.com/calebmer/postgraphql/blob/787bbe4ee842e0d931989df88acbdb3a88e1c380/examples/forum/tutorial.md

Hopefully it definitely answers some of your questions with examples :blush:

I’m going to close this issue, but feel free to open more as you go :+1:

[mytototo]

Thank you all for your answers and support, I appreciate. Your examples definitely helped me a lot. It was a good starting point. However, I'm still wondering: is it possible to perform a mutation using the database id instead of the __id key?

[ferdinandsalis]

Yes, it is possible. For example a person table with a primay key of id will expose a updatePersonById mutation. This is analogous for delete mutations. Does that answer you question?

[mytototo]

Oh I didn't see this one! Thanks a lot, this is what I was looking for.