PostGraphile

https://www.graphile.org/postgraphile/
Admin
PostGraphile is an open-source tool that instantly creates a high-performance GraphQL API server by...
Tokens:130,521
Snippets:652
Trust Score:9.5
Update:1 year ago
Show doc for...
Tokens:
Raw
### PostGraphile `package.json` Start Script

Source: https://www.graphile.org/postgraphile/deploying-gcp

Configures the `start` script in `package.json` to run PostGraphile with specific flags for hosting on Google App Engine. It includes settings for host, port, CORS, GraphiQL enhancement, and the GraphQL endpoint, along with the PostgreSQL connection string.

```json
{
  "name": "myprojectname",
  "version": "1.0.0",
  "scripts": {
    "start": "postgraphile --host --port 8080 --cors --enhance-graphiql --graphql / 0.0.0.0 -c postgres://user:password@172.17.0.1:5432/str_dev"
  },
  "engines": {
    "node": "^10.15",
    "npm": "^6.9"
  },
  "license": "ISC",
  "dependencies": {
    "postgraphile": "^4.4.5"
  }
}
```

--------------------------------

### Run Postgraphile and Setup Database

Source: https://www.graphile.org/postgraphile/subscriptions

This snippet shows how to initialize the database and start the Postgraphile server. It creates a 'subs' database if it doesn't exist and then runs the Postgraphile service.

```bash
createdb subs || true
postgraphile
```

--------------------------------

### PostGraphile `package.json` Start Script (Library Mode)

Source: https://www.graphile.org/postgraphile/deploying-gcp

Configures the `start` script in `package.json` to run a Node.js script (`src/index.js`) that utilizes PostGraphile as a library. This is used when PostGraphile is integrated directly into an Express application.

```json
{
  "scripts": {
    "start": "node src/index.js"
  }
}
```

--------------------------------

### Automated SQL Setup and Event Triggering Script

Source: https://www.graphile.org/postgraphile/subscriptions

This bash script automates the database setup, table creation, function definition, and event triggering process. It includes multiple steps to insert data, send notifications, and cancel subscriptions, providing a complete end-to-end example.

```bash
#!/bin/bash
set -e
createdb subs || true
psql -1X -v ON_ERROR_STOP=1 subs << HERE
create schema if not exists app_public;
create table if not exists app_public.foo (
 id serial primary key,
 title text not null
);
create schema if not exists app_private;
create or replace function app_private.validate_subscription(topic text)
returns text as $$ 
 select 'CANCEL_ALL_SUBSCRIPTIONS'::text;
$$ language sql stable;
HERE

sleep 1

psql -1X -v ON_ERROR_STOP=1 subs << HERE
 do $$ 
 declare
   v_foo app_public.foo;
 begin
   insert into app_public.foo (title) values ('Howdy!') returning * into v_foo;
   perform pg_notify(
     'postgraphile:hello',
     json_build_object('__node__', json_build_array('foos', v_foo.id))::text
   );
 end; 
 $$ language plpgsql;
HERE

sleep 3

psql -1X -v ON_ERROR_STOP=1 subs << HERE
 do $$ 
 declare
   v_foo app_public.foo;
 begin
   insert into app_public.foo (title) values ('Goodbye!') returning * into v_foo;
   perform pg_notify(
     'postgraphile:hello',
     json_build_object('__node__', json_build_array('foos', v_foo.id))::text
   );
   perform pg_notify(
     'CANCEL_ALL_SUBSCRIPTIONS',
     json_build_object()::text
   );
 end; 
 $$ language plpgsql;
HERE
```

--------------------------------

### PostGraphile: Running in Docker

Source: https://www.graphile.org/postgraphile/functions

This example shows how to run PostGraphile using Docker. It demonstrates the basic Docker command to start a PostGraphile instance connected to a PostgreSQL database.

```Shell
docker run
  -p 5000:5000
  -e DATABASE_URL="postgres://user:password@host:port/database"
  postgraphile/postgraphile
```

--------------------------------

### Install and Run Webpack

Source: https://www.graphile.org/postgraphile/bundling-webpack

Instructions on how to install Webpack and Webpack CLI as development dependencies and how to run the Webpack build process.

```bash
yarn add --dev webpack webpack-cli
yarn webpack
```

--------------------------------

### Example JWT Claims to SQL Conversion

Source: https://www.graphile.org/postgraphile/jwt-guide

This example illustrates the conversion of a JWT with specific claims ('sub', 'role', 'user_id') into corresponding SQL commands for setting the role and claims in PostgreSQL.

```SQL
set local role user;
set local jwt.claims.sub to 'postgraphql';
set local jwt.claims.role to 'user';
set local jwt.claims.user_id to 2;
```

--------------------------------

### Install PostGraphile

Source: https://www.graphile.org/postgraphile/quick-start-guide

Provides the command to install PostGraphile globally using npm. It includes a note recommending local installs for production but suggesting global installs for beginners.

```bash
$ npm install -g postgraphile

```

--------------------------------

### PostGraphile Docker Deployment Example

Source: https://www.graphile.org/postgraphile/make-extend-schema-plugin

Shows a basic example of how to run PostGraphile using Docker, including connecting to a PostgreSQL database container.

```bash
# Example Docker run command
docker run -p 5000:5000
  -e DATABASE_URL=postgres://user:password@db_host:5432/database
  graphile/postgraphile
  --schema public
  --watch-pg
```

--------------------------------

### PostGraphile Dockerfile .dockerignore Example

Source: https://www.graphile.org/postgraphile/deploying-docker

Provides an example of a `.dockerignore` file to optimize Docker build times by excluding unnecessary files and directories.

```dockerfile
# .dockerignore
.env
.git
.github
.next
.vscode
node_modules

*Dockerfile*
*docker-compose*

**/dist
**/__tests__
```

--------------------------------

### Install Node.js

Source: https://www.graphile.org/postgraphile/quick-start-guide

Instructions for installing Node.js, a prerequisite for running PostGraphile. It specifies version requirements and provides installation methods for different operating systems.

```Shell
node --version
```

```Shell
brew install node
```

```Shell
node -v
```

--------------------------------

### PostGraphile JWT Guide Example

Source: https://www.graphile.org/postgraphile/make-extend-schema-plugin

Provides an example of how to configure and use JSON Web Tokens (JWT) with PostGraphile for authentication and authorization, often integrating with services like Auth0.

```javascript
// Example configuration for JWT authentication
app.use(postgraphile({
  connectionString: 'postgres://user:password@host:port/database',
  jwtSecret: process.env.JWT_SECRET,
  jwtPgTypeIdentifier: 'public.jwt_claims',
  // Other options...
}));
```

--------------------------------

### Start PostGraphile with Custom Role

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

Starts the PostGraphile server using a custom connection string that includes the `forum_example_postgraphile` role and its password. This ensures PostGraphile connects with specific, non-superuser privileges.

```bash
postgraphile -c postgres://forum_example_postgraphile:xyz@localhost/mydb
```

--------------------------------

### Run PostGraphile CLI Basic Example

Source: https://www.graphile.org/postgraphile/usage-cli

A basic example of running the PostGraphile CLI with a specified PostgreSQL connection string, schema, and enabling Relay and dynamic JSON features.

```bash
postgraphile -c postgres:///mydb -s public -a -j
```

--------------------------------

### PostGraphile Custom Dockerfile Example

Source: https://www.graphile.org/postgraphile/deploying-docker

An example Dockerfile for deploying a custom PostGraphile application, highlighting multi-stage builds and essential configuration considerations.

```dockerfile
# Dockerfile


```

--------------------------------

### PostGraphile Server CLI Configuration

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

Example command-line interface (CLI) arguments for starting a PostGraphile server. It specifies the database connection, schema, default role, JWT secret, and JWT token identifier.

```bash
postgraphile \
  --connection postgres://forum_example_postgraphile:xyz@localhost \
  --schema forum_example \
  --default-role forum_example_anonymous \
  --jwt-secret keyboard_kitten \
  --jwt-token-identifier forum_example.jwt_token
```

--------------------------------

### GraphQL Batch Response Example

Source: https://www.graphile.org/postgraphile/v4-new-features

Illustrates the expected JSON response format for a batch of three GraphQL queries, showing successful data responses and an example of an error response within the array.

```json
[
  {
    "data": [...] 
  },
  {
    "data": [...] 
  },
  {
    "errors": [...] 
  }
]
```

--------------------------------

### Create PostGraphile Dockerfile

Source: https://www.graphile.org/postgraphile/running-postgraphile-as-a-library-in-docker

This Dockerfile sets up a Node.js environment for PostGraphile. It copies application files, installs dependencies using npm, exposes port 8080, and defines the command to start the server.

```Dockerfile
FROM node:alpine
LABEL description="Instant high-performance GraphQL API for your PostgreSQL database https://github.com/graphile/postgraphile"

# Set Node.js app folder
RUN mkdir -p /home/node/app/node_modules
WORKDIR /home/node/app

# Copy dependencies
COPY ./src/package*.json .
RUN chown -R node:node /home/node/app

# Install dependencies
USER node
RUN npm install

# Copy application files
COPY --chown=node:node ./src .

EXPOSE 8080
CMD [ "node", "server.js" ]
```

--------------------------------

### PostGraphile GraphQL Query Example

Source: https://www.graphile.org/postgraphile/running-postgraphile-in-docker

An example GraphQL query to fetch all posts, including their titles, bodies, and the usernames of their authors.

```graphql
query {
  allPosts {
    nodes {
      id
      title
      body
      userByAuthorId {
        username
      }
    }
  }
}
```

--------------------------------

### Deploy PostGraphile to AWS Lambda

Source: https://www.graphile.org/postgraphile/deploying-lambda

This snippet provides a link to an example repository demonstrating how to deploy PostGraphile to AWS Lambda. It's a practical guide for users looking to host their PostGraphile API on a serverless platform.

```N/A
https://github.com/graphile/postgraphile-lambda-example
```

--------------------------------

### PostGraphile Configuration File Example

Source: https://www.graphile.org/postgraphile/usage-cli

An example of a `.postgraphilerc.js` file used to configure PostGraphile options programmatically. This demonstrates how to set various options, including plugins and schema details, in a JavaScript file.

```javascript
module.exports = {
  // Add your PostGraphile options here
  plugins: [
    require('@graphile-contrib/pg-simplify-inflector'),
  ],
  // Example options:
  // subscriptions: true,
  // watch: true,
  // dynamicJson: true,
  // schema: 'app_public',
  // connection: process.env.DATABASE_URL,
};

```

--------------------------------

### PostGraphile Live Query Subscription Example

Source: https://www.graphile.org/postgraphile/realtime

This example demonstrates a PostGraphile live query using the subscription operation type. It shows how to subscribe to changes in a user list based on a condition.

```GraphQL
subscription {
  allUsersList(condition: { firstName: "Alice" }) {
    id
    name
    friendsList {
      id
      name
    }
  }
}
```

--------------------------------

### Install PostGraphile CLI

Source: https://www.graphile.org/postgraphile/usage-cli

Installs the PostGraphile CLI globally using npm, making the `postgraphile` command available in the system's PATH.

```bash
npm install -g postgraphile
```

--------------------------------

### Start PostGraphile with Default Anonymous Role

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

Starts the PostGraphile server, connecting with the `forum_example_postgraphile` role and setting `forum_example_anonymous` as the default role for unauthenticated requests. This separates anonymous user access from authenticated user access.

```bash
postgraphile \
  --connection postgres://forum_example_postgraphile:xyz@localhost/mydb \
  --default-role forum_example_anonymous
```

--------------------------------

### Apollo Client Setup for Query Batching (JavaScript)

Source: https://www.graphile.org/postgraphile/v4-new-features

Shows how to configure an Apollo Client in JavaScript to use the BatchHttpLink, enabling experimental query batching support by specifying the GraphQL endpoint URI.

```javascript
import { ApolloClient } from "apollo-client";
import { BatchHttpLink } from "apollo-link-batch-http";

const link = new BatchHttpLink({ uri: "/graphql" });

const client = new ApolloClient({
  link,
  // other options like cache
});
```

--------------------------------

### GraphQL Query Test Example

Source: https://www.graphile.org/postgraphile/testing-jest

Provides a complete test case structure for running a GraphQL query against PostGraphile. It includes setup and teardown hooks, the test execution using `runGraphQLQuery`, and assertions on the query results and database state.

```javascript
const { setup, teardown, runGraphQLQuery } = require("../test_helper");

beforeAll(setup);
afterAll(teardown);

test("GraphQL query nodeId", async () => {
  await runGraphQLQuery(
    // GraphQL query goes here:
    `{ __typename }`,
    // GraphQL variables go here:
    {},

    // Any additional properties you want `req` to have (e.g. if you're using
    // `pgSettings`) go here:
    {
      // Assuming you're using Passport.js / pgSettings, you could pretend
      // to be logged in by setting `req.user` to `{id: 17}`:
      user: { id: 17 },
    },

    // This function runs all your test assertions:
    async (json, { pgClient }) => {
      expect(json.errors).toBeFalsy();
      expect(json.data.__typename).toEqual("Query");
      // If you need to, you can query the DB within the context of this
      // function - e.g. to check that your mutation made the changes you'd
      // expect.
      const { rows } = await pgClient.query(`SELECT * FROM app_public.users WHERE id = $1`, [
        17,
      ]);
      if (rows.length !== 1) {
        throw new Error("User not found!");
      }
    }
  );
});

```

--------------------------------

### Installing PostGraphile Plugins with Yarn

Source: https://www.graphile.org/postgraphile/plugins

Shows the command to install a PostGraphile plugin, specifically '@graphile/operation-hooks', using the Yarn package manager.

```bash
yarn add @graphile/operation-hooks
```

--------------------------------

### Install Docker on Ubuntu

Source: https://www.graphile.org/postgraphile/running-postgraphile-in-docker

Commands to add the Docker repository and install Docker Community Edition on Ubuntu systems.

```bash
$ sudo apt-get update
$ sudo apt-get install apt-transport-https ca-certificates curl software-properties-common
$ curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
$ sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"
```

```bash
$ sudo apt-get update
$ sudo apt-get install docker-ce
```

```bash
$ sudo usermod -aG docker <username>
```

```bash
$ docker run hello-world
```

```bash
$ docker image ls
$ docker rmi -f hello-world
```

--------------------------------

### Install PostGraphile and pg-simplify-inflector

Source: https://www.graphile.org/postgraphile/examples

Installs PostGraphile and the pg-simplify-inflector plugin using Yarn. This plugin simplifies field names in the generated GraphQL schema.

```bash
yarn add postgraphile @graphile-contrib/pg-simplify-inflector
```

--------------------------------

### GraphQL Subscription Example

Source: https://www.graphile.org/postgraphile/realtime

Demonstrates a GraphQL subscription query to receive proactive updates when a new chat message is added to a specific channel. This example shows how to subscribe to an event and retrieve relevant data.

```graphql
subscription {
  chatMessageAdded(channel: 27) {
    id
    message
    author { id name avatarUrl }
    timestamp
  }
}
```

--------------------------------

### SQL: Example Product Table Schema

Source: https://www.graphile.org/postgraphile/make-extend-schema-plugin

Provides an example SQL `CREATE TABLE` statement for a `product` table, including columns for `id`, `name`, and `price_in_us_cents`. This serves as the database schema definition that PostGraphile might interact with.

```sql
create table product (
  id uuid primary key,
  name text not null,
  price_in_us_cents integer not null
);
```

--------------------------------

### Run PostGraphile with pg-simplify-inflector

Source: https://www.graphile.org/postgraphile/examples

Starts PostGraphile with the pg-simplify-inflector plugin appended to the default plugins. This command is used to serve the GraphQL API.

```bash
yarn postgraphile --append-plugins @graphile-contrib/pg-simplify-inflector
```

--------------------------------

### Dockerfile: Multi-stage Build for PostGraphile

Source: https://www.graphile.org/postgraphile/deploying-docker

This Dockerfile demonstrates a multi-stage build process for a PostGraphile application. It utilizes Node.js Alpine images for efficient builds and includes stages for dependency installation, code copying, and final image creation. The process optimizes for production by selectively copying artifacts and performing a clean dependency installation.

```Dockerfile
ARG NODE_ENV="production"

FROM node:12-alpine as builder
ARG NODE_ENV
COPY package.json yarn.lock /app/
WORKDIR /app/
RUN yarn install --frozen-lockfile --production=false --no-progress
COPY server/ /app/server/
RUN yarn run build

FROM node:12-alpine as clean
ARG NODE_ENV
COPY --from=builder /app/package.json /app/yarn.lock /app/
COPY --from=builder /app/server/dist/ /app/server/dist/

FROM node:12-alpine
ARG NODE_ENV
EXPOSE 5000
WORKDIR /app/
COPY --from=clean /app/ /app/
RUN yarn install --frozen-lockfile --production=true --no-progress
LABEL description="My PostGraphile-powered server"
ENV GRAPHILE_TURBO=1
ENV NODE_ENV=$NODE_ENV
ENTRYPOINT yarn start
```

--------------------------------

### PostGraphile Example: Passport Login

Source: https://www.graphile.org/postgraphile/community-plugins

An example plugin demonstrating how to add username/password `login` and `register` mutations using Passport.js with PostGraphile. This provides a basic authentication mechanism.

```javascript
import { postgraphile } from 'postgraphile';

app.use(postgraphile({
  plugins: [
    require('./PassportLoginPlugin') // Assuming the plugin is in the same directory
  ]
}));
```

--------------------------------

### PostGraphile Plugin Example: makePluginByCombiningPlugins

Source: https://www.graphile.org/postgraphile/make-extend-schema-plugin

Shows how to use `makePluginByCombiningPlugins` to bundle multiple PostGraphile plugins into a single, reusable plugin.

```javascript
const { makePluginByCombiningPlugins, makeExtendSchemaPlugin, makeAddPgTableConditionPlugin } = require('postgraphile');

const combinedPlugin = makePluginByCombiningPlugins(
  makeExtendSchemaPlugin({ /* ... */ }),
  makeAddPgTableConditionPlugin('public.users', { isActive: true })
);

// Use the combinedPlugin with PostGraphile
```

--------------------------------

### PostGraphile JWT Guide - Example

Source: https://www.graphile.org/postgraphile/community-contributions

This example demonstrates a basic JWT token structure that can be used with PostGraphile for authentication. It shows a typical payload format for JWTs.

```json
{
  "sub": "1234567890",
  "name": "John Doe",
  "iat": 1516239022,
  "https://graphile.github.io/jwt/claims": {
    "role": "user"
  }
}
```

--------------------------------

### PostGraphile v4 Inflector Example

Source: https://www.graphile.org/postgraphile/v4-new-features

Shows how to simplify naming conventions in the GraphQL schema using the `@graphile-contrib/pg-simplify-inflector` plugin. This example demonstrates renaming a column to make it more idiomatic in GraphQL.

```bash
postgraphile --plugins @graphile-contrib/pg-simplify-inflector
```

--------------------------------

### GraphQL Query Batch Payload Example

Source: https://www.graphile.org/postgraphile/v4-new-features

Provides an example of the JSON payload structure for a batch of three GraphQL queries, including the query string, operation name, and variables for each individual query.

```json
[
  {
    "query": "...",
    "operationName": "...",
    "variables": {
      "someVariable": "..."
    }
  },
  {
    "query": "...",
    "operationName": "...",
    "variables": {
      "someVariable": "..."
    }
  },
  {
    "query": "...",
    "operationName": "...",
    "variables": {
      "someVariable": "..."
    }
  }
]
```

--------------------------------

### PostGraphile JWT Guide Example

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

Provides an example of how to configure PostGraphile to use JSON Web Tokens (JWT) for authentication and authorization. This typically involves specifying JWT-related options and potentially custom claims or verification methods.

```javascript
const postgraphile = require('postgraphile');

// Assuming you have a JWT verification function
// async function verifyJwt(token) { ... }

postgraphile(
  'postgres://user:password@host:port/database',
  {
    // Other options...
    jwtVerifyOptions: {
      // e.g., issuer: 'your-app-issuer'
    },
    // You might need to provide a custom JWT verification function if not using standard options
    // jwtSecret: process.env.JWT_SECRET,
    // jwtPgTypeIdentifier: 'my_jwt_type' // If using custom JWT types in Postgres
  }
);

// Note: The exact configuration for JWT depends heavily on your authentication strategy
// and how JWTs are issued and verified.
```

--------------------------------

### Install JWT/JWK Verification Dependencies (Yarn/NPM)

Source: https://www.graphile.org/postgraphile/jwk-verification

Installs the necessary Node.js packages for JWT and JWK verification using either Yarn or NPM. This includes express for the HTTP framework, express-jwt for JWT validation middleware, and jwks-rsa for retrieving JWKS.

```shell
yarn add express express-jwt jwks-rsa
# Or:
npm install --save express express-jwt jwks-rsa
```

--------------------------------

### Running PostGraphile in Docker

Source: https://www.graphile.org/postgraphile/community-contributions

This section provides instructions and examples for running PostGraphile using Docker. It covers setting up a Docker container for PostGraphile, including database connection and configuration.

```docker
docker run
  -d
  -p 5000:5000
  -e DATABASE_URL=postgres://user:password@host:port/database
  postgraphile/postgraphile
```

--------------------------------

### Run PostGraphile with Connection Strings

Source: https://www.graphile.org/postgraphile/quick-start-guide

Illustrates different ways to run PostGraphile by specifying the database connection string. Examples include connecting to a local database, a secure host requiring SSL/TLS, and a remote database with specific host and port.

```bash
# Connect to the `mydb` database within the local PostgreSQL server
$ postgraphile -c "postgres:///mydb"

```

```bash
# Connect to a database that requires SSL/TLS
$ postgraphile -c "postgres://securehost:5432/db?ssl=true"

```

```bash
# Connect to the `somedb` database within the PostgreSQL at somehost port 2345
$ postgraphile -c "postgres://somehost:2345/somedb"

```

--------------------------------

### PostGraphile v4 Library Usage

Source: https://www.graphile.org/postgraphile/v4-new-features

Provides an example of using PostGraphile as a library within a Node.js application. This allows for programmatic integration and customization of the GraphQL API.

```javascript
import PostGraphile from 'postgraphile';

const postgraphile = new PostGraphile(
  'postgres://user:pass@host:port/database',
  ['public'],
  {
    // options
  }
);

// Use postgraphile middleware with your server, e.g., Express:
// app.use(postgraphile);

```

--------------------------------

### PostGraphile Schema-only Usage Example

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

Shows how to use PostGraphile to generate a GraphQL schema from a PostgreSQL database without immediately starting a server. This is useful for introspection or integration with other GraphQL tools.

```javascript
const { postgraphile } = require('postgraphile');

async function getSchema() {
  const schema = await postgraphile(
    // Database connection details
    // e.g., 'postgres://user:password@host:port/database',
    // Or use a connection string from environment variables
    // process.env.DATABASE_URL,
    {
      // Options like schema, subscriptions, etc.
      // schema: 'public',
      // subscriptions: true,
      // exportSchemaJson: true // To get the schema as JSON
    }
  );
  // You can now use the 'schema' object for introspection or other purposes
  console.log('GraphQL schema generated successfully.');
  // For example, to get the schema as a string:
  // const { printSchema } = require('graphql');
  // console.log(printSchema(schema));
}

getSchema();
```

--------------------------------

### Create a PostgreSQL Database

Source: https://www.graphile.org/postgraphile/quick-start-guide

Shows how to create a new PostgreSQL database named 'mydb' using the `createdb` command. It also demonstrates connecting to the newly created database using `psql` and running a simple SQL query.

```bash
$ createdb mydb

```

```bash
$ psql "postgres:///mydb"

psql (9.6.*)
Type "help" for help.

=#

```

```sql
select 1 + 1 as two;

```

```bash
 two
-----
   2
(1 row)

=#

```

--------------------------------

### PostGraphile Library Usage Example

Source: https://www.graphile.org/postgraphile/make-extend-schema-plugin

Illustrates how to integrate PostGraphile as a library within a Node.js application. This allows for programmatic configuration and integration into existing server setups, often with frameworks like Express.

```javascript
const { postgraphile } = require('postgraphile');
const express = require('express');

const app = express();

app.use(postgraphile({
  connectionString: 'postgres://user:password@host:port/database',
  // Other options like schema, watchPg, etc.
}));

// ... rest of your Express app setup
```

--------------------------------

### Allow Explain

Source: https://www.graphile.org/postgraphile/usage-cli

Allows users to use the 'Explain' button in GraphiQL to view the execution plan for SQL queries. This is an experimental feature and should NOT be used in production due to potential performance and security implications.

```bash
--allow-explain
```

--------------------------------

### Handle Errors in PostGraphile

Source: https://www.graphile.org/postgraphile/v4-new-features

This example shows how to override the default error handling mechanism in PostGraphile using the `handleErrors` option within `graphileBuildOptions`. This allows for custom error reporting or logging.

```javascript
import PostGraphile from 'postgraphile';

const postgraphile = new PostGraphile(
  'postgres://user:password@host:port/database',
  'public',
  {
    graphileBuildOptions: {
      handleErrors: (errors, context) => {
        // Custom error handling logic here
        console.error('Graphile Errors:', errors);
        // You might return a modified error structure or log to a service
        return errors;
      },
    },
  }
);

// ... rest of your PostGraphile setup
```

--------------------------------

### PostGraphile with Docker Example

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

Shows how to run PostGraphile using Docker, which is a common deployment method. This typically involves a Dockerfile and a docker-compose.yml file to configure the container and its connection to a PostgreSQL database.

```dockerfile
# Example Dockerfile for PostGraphile
FROM node:18-alpine

WORKDIR /app

COPY package*.json ./ 
RUN npm install

COPY . .

EXPOSE 5000

CMD [ "node", "index.js" ]

# Example docker-compose.yml snippet
# version: '3.8'
# services:
#   postgraphile:
#     build: .
#     ports:
#       - "5000:5000"
#     environment:
#       DATABASE_URL: postgres://user:password@db:5432/database
#     depends_on:
#       - db
#   db:
#     image: postgres:14-alpine
#     # ... other postgres configuration
```

--------------------------------

### PostGraphile CLI: Subscriptions and Live Queries

Source: https://www.graphile.org/postgraphile/usage-cli

Options to enable GraphQL subscriptions and experimental live-query support. Live queries imply subscriptions.

```bash
postgraphile --subscriptions
postgraphile --live
```

--------------------------------

### PostGraphile JWT Guide - JWK Verification

Source: https://www.graphile.org/postgraphile/community-contributions

This example illustrates the process of verifying a JWT using a JSON Web Key (JWK) obtained from a JWKS endpoint. It outlines the steps for secure token validation.

```javascript
const jwt = require('jsonwebtoken');
const jwksClient = require('jwks-rsa');

const client = jwksClient({
    jwksUri: 'YOUR_JWKS_URI'
});

function getKey(header, callback) {
    client.getSigningKey(header.kid, function(err, key) {
        const signingKey = key.publicKey || key.privateKey;
        callback(null, signingKey);
    });
}

const token = 'YOUR_JWT_TOKEN';

jwt.verify(token, getKey, { audience: 'YOUR_AUDIENCE', issuer: 'YOUR_ISSUER' }, function(err, decoded) {
    if (err) {
        console.error('Token verification failed:', err);
    } else {
        console.log('Token verified successfully:', decoded);
    }
});
```

--------------------------------

### Project Structure for PostGraphile Library Usage

Source: https://www.graphile.org/postgraphile/deploying-gcp

Illustrates a basic project directory structure for using PostGraphile as a library. It shows the placement of `package.json` at the root and the main application logic in `src/index.js`.

```text
/project
|--package.json
|--/src
   |--index.js
```

--------------------------------

### GraphQL Test Helper Setup (TypeScript)

Source: https://www.graphile.org/postgraphile/testing-jest

This TypeScript code sets up a testing helper for running GraphQL queries with PostGraphile. It includes functions for initializing and tearing down the database connection, creating the PostGraphile schema, and a utility function to sanitize query results for stable snapshot testing.

```typescript
import { Request, Response } from "express";
import { ExecutionResult, graphql, GraphQLSchema } from "graphql";
import { Pool, PoolClient } from "pg";
import {
  createPostGraphileSchema,
  PostGraphileOptions,
  withPostGraphileContext,
} from "postgraphile";

import { getPostGraphileOptions } from "../src/middleware/installPostGraphile";

const MockReq = require("mock-req");

let known: Record<
  string,
  { counter: number; values: Map<unknown, string> }
> = {};
beforeEach(() => {
  known = {};
});
/*
 * This function replaces values that are expected to change with static
 * placeholders so that our snapshot testing doesn't throw an error
 * every time we run the tests because time has ticked on in it's inevitable
 * march toward the future.
 */
export function sanitize(json: any): any {
  /* This allows us to maintain stable references whilst dealing with variable values */
  function mask(value: unknown, type: string) {
    if (!known[type]) {
      known[type] = { counter: 0, values: new Map() };
    }
    const o = known[type];
    if (!o.values.has(value)) {
      o.values.set(value, `[${type}-${++o.counter}]`);
    }
    return o.values.get(value);
  }

  if (Array.isArray(json)) {
    return json.map(val => sanitize(val));
  } else if (json && typeof json === "object") {
    const result = { ...json };
    for (const k in result) {
      if (k === "nodeId" && typeof result[k] === "string") {
        result[k] = mask(result[k], "nodeId");
      } else if (
        k === "id" ||
        k === "uuid" ||
        (k.endsWith("Id") &&
          (typeof json[k] === "number" || typeof json[k] === "string")) ||
        (k.endsWith("Uuid") && typeof k === "string")
      ) {
        result[k] = mask(result[k], "id");
      } else if (
        (k.endsWith("At") || k === "datetime") &&
        typeof json[k] === "string"
      ) {
        result[k] = mask(result[k], "timestamp");
      } else if (
        k.match(/^deleted[A-Za-z0-9]+Id$/) &&
        typeof json[k] === "string"
      ) {
        result[k] = mask(result[k], "nodeId");
      } else if (k === "email" && typeof json[k] === "string") {
        result[k] = mask(result[k], "email");
      } else if (k === "username" && typeof json[k] === "string") {
        result[k] = mask(result[k], "username");
      } else {
        result[k] = sanitize(json[k]);
      }
    }
    return result;
  } else {
    return json;
  }
}

// Contains the PostGraphile schema and rootPgPool
interface ICtx {
  rootPgPool: Pool;
  options: PostGraphileOptions<Request, Response>;
  schema: GraphQLSchema;
}
let ctx: ICtx | null = null;

export const setup = async () => {
  const rootPgPool = new Pool({
    connectionString: process.env.TEST_DATABASE_URL,
  });

  const options = getPostGraphileOptions({ rootPgPool });
  const schema = await createPostGraphileSchema(
    rootPgPool,
    "app_public",
    options
  );

  // Store the context
  ctx = {
    rootPgPool,
    options,
    schema,
  };
};

export const teardown = async () => {
  try {
    if (!ctx) {
      return null;
    }
    const { rootPgPool } = ctx;
    ctx = null;
    rootPgPool.end();
    return null;
  } catch (e) {
    console.error(e);
    return null;
  }
};

export const runGraphQLQuery = async function runGraphQLQuery(
  query: string, // The GraphQL query string
  variables: { [key: string]: any } | null, // The GraphQL variables
  reqOptions: { [key: string]: any } | null, // Any additional items to set on `req` (e.g. `{user: {id: 17}}`)
  checker: (
    result: ExecutionResult,
    context: { pgClient: PoolClient }
  ) => void | ExecutionResult | Promise<void | ExecutionResult> = () => {}
) {
```

--------------------------------

### Help

Source: https://www.graphile.org/postgraphile/usage-cli

Outputs usage information for the PostGraphile CLI.

```bash
-h, --help
```

--------------------------------

### PostGraphile v4 Schema-Only Mode

Source: https://www.graphile.org/postgraphile/v4-new-features

Demonstrates the `--no-server` option, which allows PostGraphile to generate the GraphQL schema and exit without starting a server. This is useful for schema introspection or integration with build processes.

```bash
postgraphile --no-server
```

--------------------------------

### Demonstration of SET LOCAL Scope

Source: https://www.graphile.org/postgraphile/jwt-guide

This SQL example demonstrates the transactional scope of `SET LOCAL`. It shows that parameters set with `SET LOCAL` within a transaction block are accessible, but become inaccessible after the block is committed or rolled back.

```SQL
begin;
set local jwt.claims.user_id to 2;

-- Has access to `jwt.claims.user_id`
commit;

-- Does not have access to `jwt.claims.user_id`
```

--------------------------------

### PostGraphile CLI: Schema Caching

Source: https://www.graphile.org/postgraphile/usage-cli

Experimental options to improve startup time by reading from or writing to a local cache file.

```bash
postgraphile --read-cache <path>
postgraphile --write-cache <path>
```

--------------------------------

### JWK Verification (Auth0) with PostGraphile

Source: https://www.graphile.org/postgraphile/community-contributions

This guide explains how to verify JSON Web Keys (JWKs), specifically for integration with Auth0, within a PostGraphile setup. It covers the necessary steps to ensure secure authentication using external identity providers.

```javascript
import PostGraphile from 'postgraphile';
import jwksClient from 'jwks-rsa';

const client = jwksClient({
  jwksUri: 'https://your-auth0-domain.auth0.com/.well-known/jwks.json'
});

function getSigningKey(header, callback) {
  client.getSigningKey(header.kid, function(err, key) {
    const signingKey = key.publicKey || key.privateKey;
    callback(null, signingKey);
  });
}

const postgraphile = new PostGraphile(
  'postgres://user:password@host:port/database',
  'public',
  {
    jwtVerifyOptions: {
      audience: 'your-api-audience',
      issuer: 'https://your-auth0-domain.auth0.com/'
    },
    jwtValidationFn: async (decodedToken, callback) => {
      // Verify the token using JWKS
      // ... implementation using jwksClient ...
      callback(null, decodedToken);
    }
  }
);
```

--------------------------------

### Dockerfile for PostGraphile Service

Source: https://www.graphile.org/postgraphile/running-postgraphile-in-docker

This Dockerfile sets up the PostGraphile container. It starts from a Node.js Alpine base image, installs PostGraphile and the connection filter plugin globally using npm, exposes port 5000, and sets the entrypoint to run PostGraphile, listening on all interfaces.

```dockerfile
FROM node:alpine
LABEL description="Instant high-performance GraphQL API for your PostgreSQL database https://github.com/graphile/postgraphile"

# Install PostGraphile and PostGraphile connection filter plugin
RUN npm install -g postgraphile
RUN npm install -g postgraphile-plugin-connection-filter

EXPOSE 5000
ENTRYPOINT ["postgraphile", "-n", "0.0.0.0"]
```

--------------------------------

### PostGraphile PostGraphiQL Explain Configuration

Source: https://www.graphile.org/postgraphile/debugging

Enable the 'Explain' feature in PostGraphiQL to view generated SQL queries and their execution plans. This requires specific CLI flags or library options to be set.

```cli
--enhance-graphiql --allow-explain
```

```javascript
enhanceGraphiql: true, allowExplain: (req) => { return true; }
```

--------------------------------

### Install wal2json Extension for PostgreSQL

Source: https://www.graphile.org/postgraphile/live-queries

Installs the `wal2json` extension into PostgreSQL, which is required for logical decoding. This involves cloning the repository, compiling, and installing the extension.

```bash
git clone https://github.com/eulerto/wal2json.git
cd wal2json
USE_PGXS=1 make
USE_PGXS=1 make install
```

--------------------------------

### Enable Simple Subscriptions via CLI

Source: https://www.graphile.org/postgraphile/subscriptions

Enables simple subscriptions by loading the '@graphile/pg-pubsub' plugin and passing the '--subscriptions' and '--simple-subscriptions' flags to PostGraphile.

```bash
postgraphile \
  --plugins @graphile/pg-pubsub \
  --subscriptions \
  --simple-subscriptions \
  -c mydb
```

--------------------------------

### PostGraphile CLI Usage Example

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

Demonstrates how to use PostGraphile via the command-line interface to create a GraphQL API from a PostgreSQL database. This typically involves specifying connection details and any desired plugins.

```bash
# Example CLI usage (actual command may vary based on configuration and plugins)
postgraphile --connection "postgres://user:password@host:port/database" --schema "public" --subscriptions
```

--------------------------------

### PostgreSQL PL/pgSQL Function Example

Source: https://www.graphile.org/postgraphile/functions

An example of a PostgreSQL function written in PL/pgSQL, performing the same addition operation as the SQL example. PL/pgSQL is suitable for triggers and more complex logic.

```PL/pgSQL
CREATE FUNCTION add(a int, b int) RETURNS int AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
```

--------------------------------

### PostgreSQL Dummy Data

Source: https://www.graphile.org/postgraphile/running-postgraphile-in-docker

SQL script to populate the 'user' and 'post' tables with sample data. It includes INSERT statements for creating users and posts with associated author IDs.

```sql
\connect forum_example;

/*Create some dummy users*/
INSERT INTO public.user (username) VALUES
('Benjie'),
('Singingwolfboy'),
('Lexius');

/*Create some dummy posts*/
INSERT INTO public.post (title, body, author_id) VALUES
('First post example', 'Lorem ipsum dolor sit amet', 1),
('Second post example', 'Consectetur adipiscing elit', 2),
('Third post example', 'Aenean blandit felis sodales', 3);
```

--------------------------------

### Install Docker Compose

Source: https://www.graphile.org/postgraphile/running-postgraphile-in-docker

Command to install Docker Compose on Debian-based systems. Docker Compose is used to manage multi-container Docker applications.

```bash
sudo apt install docker-compose
```

--------------------------------

### Install PostGraphile with npm or yarn

Source: https://www.graphile.org/postgraphile/requirements

PostGraphile is a Node.js module that can be installed using either npm or yarn. Yarn is generally recommended for a smoother experience.

```shell
npm install postgraphile
# or
yarn add postgraphile
```

--------------------------------

### PostGraphile Server Setup (JavaScript)

Source: https://www.graphile.org/postgraphile/testing-jest

This JavaScript code snippet demonstrates how to set up an Express server with PostGraphile in library mode. It includes importing necessary modules, defining a function to retrieve PostGraphile options, and configuring the middleware with database connection details.

```javascript
const express = require("express");
const { postgraphile } = require("postgraphile");

const app = express();

function postgraphileOptions() {  return {    dynamicJson: true,  };
}
exports.postgraphileOptions = postgraphileOptions;
app.use(
  postgraphile(
    process.env.DATABASE_URL || "postgres:///",
    ["app_public"],
    postgraphileOptions()
  )
);

app.listen(process.env.PORT || 3000);
```

--------------------------------

### PostgreSQL: Create Roles and Grant Permissions

Source: https://www.graphile.org/postgraphile/required-knowledge

Demonstrates creating different PostgreSQL roles ('my_superuser', 'app_authenticator', 'app_visitor') with specific privileges like login, superuser, and noinherit. It also shows how to grant permissions on a table to a role.

```SQL
create role my_superuser with login password 'password here' superuser;
create role app_authenticator with login password 'password here' noinherit;
create role app_visitor;
grant app_visitor to app_authenticator;
```

```SQL
grant
  select,
  insert (column_1, column_2),
  update (column_2, column_3),
  delete
on my_table to app_visitor;
```

--------------------------------

### Enable PostGraphile Plugins via CLI

Source: https://www.graphile.org/postgraphile/plugins

Specifies PostGraphile plugins using the `--plugins` CLI flag. This flag must be the first one passed, and multiple plugins can be listed, separated by commas. This method is suitable for command-line usage.

```bash
postgraphile --plugins \
  @graphile/operation-hooks,@graphile/pg-pubsub,@graphile/pro \
  -c postgres:///my_db
```

--------------------------------

### Example JWT Claims Object

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

An example of a JSON object that can be used as claims within a JSON Web Token (JWT). JWTs are used by PostGraphile for authorization.

```JSON
{
  "a": 1,
  "b": 2,
  "c": 3
}
```

--------------------------------

### PostGraphile CLI: Version and Plugins

Source: https://www.graphile.org/postgraphile/usage-cli

Options to display the PostGraphile version and specify server plugins. The `--plugins` option must be the first option if used.

```bash
postgraphile --version
postgraphile --plugins <string>
```

--------------------------------

### PostGraphile Mutation Example with Node ID

Source: https://www.graphile.org/postgraphile/make-extend-schema-plugin

Shows a mutation that uses a global Node ID as input, for example, to update or delete a specific record in the database.

```graphql
mutation UpdateUserNode($input: UpdateUserInput!) {
  updateUser(input: $input) {
    user {
      id
      name
    }
  }
}
```
Tokens:
Raw