cdaringe/postgraphile-upsert

add postgres upsert mutations to postgraphile :elephant:

postgraphile-upsert-plugin

Add postgres upsert mutations to postgraphile.

Getting Started

Install

pnpm install --save postgraphile-upsert-plugin

CLI

postgraphile --append-plugins postgraphile-upsert-plugin:PgMutationUpsertPlugin

See here for more information about loading plugins with PostGraphile.

Library

import express from "express";
import { postgraphile } from "postgraphile";
import { PgMutationUpsertPlugin } from "postgraphile-upsert-plugin";

const app = express();

app.use(
  postgraphile(pgConfig, schema, {
    appendPlugins: [PgMutationUpsertPlugin],
  })
);

app.listen(5000);

Usage

This plugin supports an optional where clause in your upsert<Table> mutation. Supports multi-column unique indexes.

Example

create table bikes (
  id serial primary key,
  make varchar,
  model varchar
  serial_number varchar unique not null,
  weight real
)

A basic upsert would look like this:

mutation {
  upsertBike(
    where: { serial_number: "abc123" }
    input: {
      bike: {
        make: "kona"
        model: "kula deluxe"
        serial_number: "abc123"
        weight: 25.6
      }
    }
  ) {
    clientMutationId
  }
}

Smart Tags Support

• Add @omit updateOnConflict to column comments to prevent them from being modified on existing rows in an upsert mutation.

Contributing

See CONTRIBUTING.md.

Credits

• This is a typescript-ified knock off of the original upsert plugin

postgraphile-upsert's Issues

Row level security

Hello, thank you for documentation update!

I try to use upsert with row level security enabled, I have this table :

create table if not exists musicbot_public.music
(
    id         serial primary key,
    user_id    integer not null references musicbot_public.user (id) on delete cascade default musicbot_public.current_musicbot(),
    title      text default '',
    album      text default '',
    artist     text default '',
    constraint unique_music unique (title, album, artist, user_id) deferrable
);
create index if not exists music_user_idx on musicbot_public.music (user_id);

(complete version here: https://github.com/AdrienPensart/musicbot/blob/master/musicbot/schema/d_music.sql)

With this spec in my mutation :

where: {
      title: "Welcome To Bucketheadland"
      album: "Giant Robot"
      artist: "Buckethead"
    }

I get this error :

there is no unique or exclusion constraint matching the ON CONFLICT specification

If I try to use userId: 1 in where and object spec, I have another error :

insert or update on table \"music\" violates foreign key constraint \"music_user_id_fkey\"

It works well when I disable row level security.

My old code (https://github.com/AdrienPensart/musicbot/blob/96d55d03096f998118b99c6b8fa4ba230030fe19/musicbot/schema/raw_music.sql) for upsert worked with a deferred constraint in a plpgsql function, but if I put deferrable again, it triggers this error :

ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters

Do you have an idea to fix this ?
Maybe I need a constraint trigger ?
Maybe this plugin should wrap the upsert statement inside a plpgsql function ?

An in-range update of lint-staged is breaking the build 🚨

The devDependency lint-staged was updated from 8.1.0 to 8.1.1.

🚨 View failing branch.

This version is covered by your current version range and after updating it in your project the build failed.

lint-staged is a devDependency of this project. It might not break your production code or affect downstream projects, but probably breaks your build or test tools, which may prevent deploying or publishing.

Status Details

• ❌ ci/circleci: build: Your tests failed on CircleCI (Details).

---

Release Notes for v8.1.1

8.1.1 (2019-01-28)

Bug Fixes

• Fix configuration validation and allow specifying custom renderers (#572) (d5e738d), closes #567

Commits

The new version differs by 7 commits.

• d5e738d fix: Fix configuration validation and allow specifying custom renderers (#572)
• 406a0c0 chore: Revert "chore: pin cosmiconfig to 5.0.6" (#571)
• adfc1d4 docs(readme): Add example for environment variables (#564)
• 73e04d7 chore: Use unmock to get rid of mock hoisting (#563)
• ac8cdf1 docs: Remove comment about hunks support (#553)
• 352ab8c docs: Update for JetBrains support (#552)
• 30576d6 chore: Upgrade semantic-release to latest (#548)

See the full diff

FAQ and help

There is a collection of frequently asked questions. If those don’t help, you can always ask the humans behind Greenkeeper.

---

Your Greenkeeper Bot 🌴

Error in the docs + question

Hello, I think there is an error in the docs, it should be:

import { PgMutationUpsertPlugin } from "postgraphile-upsert-plugin";

not

import PgMutationUpsertPlugin from "postgraphile-upsert-plugin";

I have also a question, would be possible to implement the upsert on multiple objects? so, accept an array instead of a single object?

columns with value 0.0 are removed from ON CONFLICT column list

If the bike table has a unique constraint on serial_number and weight together, and I try to upsert a bike with weight 0.0, for example

mutation {
  upsertBike(
    where: { serial_number: "abc123", weight: 0.0 }
    input: {
      bike: {
        make: "kona"
        model: "cool-ie deluxe"
        serial_number: "abc123"
        weight: 0.0
      }
    }
  ) {
    clientMutationId
  }
}

the SQL statement that gets produced is something like

INSERT INTO bike (make,model,serial_number,weight) VALUES (...) ON CONFLICT (serial_number) DO UPDATE ...

and it gets rejected because there's no constraint on just serial_number

perhaps attributes with value 0.0 is getting filtered out because it looks like false

Support attribute omitting for upsert mutation in standard omit smart tag

Support attribute omitting for upsert mutation in standard omit smart tag

Example:

{
  "$schema": "https://gist.githubusercontent.com/singingwolfboy/a7144db4e24b5d31ba81f28b878a4b51/raw/cd1e371baa41756e02f1d6f36a3dea86388d4084/pg-smart-tags-schema.json",
  "version": 1,
  "config": {
    "class": {
    },
    "attribute": {
      "created_at": { "tags": { "omit": "create,update,upsert" } },
    },
}

3.1.0 version in npm and yarn are missing files

Have a look at https://yarnpkg.com/package/postgraphile-upsert-plugin?files
If you install postgraphile-upsert-plugin v 3.1.0 using yarn or npm, you get the README and the package.json but nothing else.
It looks like a mishap in the build and release steps.

Is this plugin (already) v5 compatible?

v5 is being pre-released. Will post if I get it to work or if I get an error referring to this plugin.

Edit: v4 Plugins do not work for v5 as they previously were functions, now they are declarative objects (https://postgraphile.org/postgraphile/next/migrating-from-v4/#plugins).

Add `where` input argument - a new fork

👋 Hey! I've forked this repo and added a where input argument to the upsert.

As of writing, the original repo here only uses the primary key in the upsert on conflict, which isn't very helpful (why would you do an upsert if you already have the primary key?).

Instead, my fork allows you to use any of the unique constraints to specify the upsert condition:

Example

create table bikes (
  id serial PRIMARY KEY,
  "serialNumber" varchar UNIQUE NOT NULL,
  weight real,
  make varchar,
  model varchar
)

An upsert would look like this:

mutation {
  upsertBike(
    where: { serialNumber: "abc123" }
    input: {
      bike: {
        serialNumber: "abc123"
        weight: 25.6
        make: "kona"
        model: "cool-ie deluxe"
      }
    }
  ) {
    clientMutationId
  }
}

You can find it here

Upsert should respect @omit tags

Upsert GraphQL query objects are created, even when @omit tags are given to the database objects.

PR #174 should fix this.

Thanks

An in-range update of husky is breaking the build 🚨

The devDependency husky was updated from 3.0.0 to 3.0.1.

🚨 View failing branch.

This version is covered by your current version range and after updating it in your project the build failed.

husky is a devDependency of this project. It might not break your production code or affect downstream projects, but probably breaks your build or test tools, which may prevent deploying or publishing.

Status Details

• ❌ ci/circleci: build: Your tests failed on CircleCI (Details).

---

Release Notes for v3.0.1

• Improve error message if git command fails

Commits

The new version differs by 6 commits.

• ecf434b 3.0.1
• 3b2d7e7 improve error message if git command fails (#543)
• a694261 Bump lodash from 4.17.11 to 4.17.14 (#537)
• c1fc84e Update README.md
• b6835ee remove redundant code
• d8c9f9f Update CHANGELOG.md

See the full diff

FAQ and help

There is a collection of frequently asked questions. If those don’t help, you can always ask the humans behind Greenkeeper.

---

Your Greenkeeper Bot 🌴

An in-range update of prettier-standard is breaking the build 🚨

The devDependency prettier-standard was updated from 9.0.0 to 9.0.1.

🚨 View failing branch.

This version is covered by your current version range and after updating it in your project the build failed.

prettier-standard is a devDependency of this project. It might not break your production code or affect downstream projects, but probably breaks your build or test tools, which may prevent deploying or publishing.

Status Details

• ❌ ci/circleci: build: Your tests failed on CircleCI (Details).

---

Commits

The new version differs by 2 commits.

• 2f62135 Bump to 9.0.1
• eb712f2 Auto-detect parser for prettier

See the full diff

FAQ and help

There is a collection of frequently asked questions. If those don’t help, you can always ask the humans behind Greenkeeper.

---

Your Greenkeeper Bot 🌴

An in-range update of @typescript-eslint/parser is breaking the build 🚨

The devDependency @typescript-eslint/parser was updated from 1.9.0 to 1.10.0.

🚨 View failing branch.

This version is covered by your current version range and after updating it in your project the build failed.

@typescript-eslint/parser is a devDependency of this project. It might not break your production code or affect downstream projects, but probably breaks your build or test tools, which may prevent deploying or publishing.

Status Details

• ❌ ci/circleci: build: Your tests failed on CircleCI (Details).

---

FAQ and help

There is a collection of frequently asked questions. If those don’t help, you can always ask the humans behind Greenkeeper.

---

Your Greenkeeper Bot 🌴

error: postgraphile-upsert is not assignable to type 'Plugin'

I've added the plugin as noted in the README, but I'm getting this error in VSCode

error TS2322: Type 'typeof import("/Users/hkong/devel/feServer/feserver/graphql-server/node_modules/postgraphile-upsert-plugin/build/postgraphile-upsert")' is not assignable to type 'Plugin'.
  Type 'typeof import("/Users/hkong/devel/feServer/feserver/graphql-server/node_modules/postgraphile-upsert-plugin/build/postgraphile-upsert")' provides no match for the signature '(builder: SchemaBuilder, options: Options): void | Promise<void>'.

I'm using node 16.15.1, ts-node 10.9.1, typescript 4.7.4 and postgraphile 4.12.9. What is really strange is that I have other plugins ( ConnectionFilterPlugin and PostgisPlugin), and they do not have this error.

Is there something I am not doing correctly?

No plugin found matching spec

Hello,

I am still a beginner with postgraphile and plugins, I use only the CLI, not the express.js or javascript, I have this error :

yarn add postgraphile-upsert-plugin
DEBUG=postgraphile:postgres:notice postgraphile [other options] --append-plugins postgraphile-plugin-connection-filter,@graphile-contrib/pg-simplify-inflector,postgraphile-upsert-plugin
/home/crunch/.nvm/versions/node/v16.8.0/lib/node_modules/postgraphile/build/postgraphile/cli.js:304
            throw new Error(`No plugin found matching spec '${name}' - expected function, found '${typeof plugin}'`);
            ^

Error: No plugin found matching spec 'postgraphile-upsert-plugin' - expected function, found 'object'
    at /home/crunch/.nvm/versions/node/v16.8.0/lib/node_modules/postgraphile/build/postgraphile/cli.js:304:19
    at Array.map (<anonymous>)
    at loadPlugins (/home/crunch/.nvm/versions/node/v16.8.0/lib/node_modules/postgraphile/build/postgraphile/cli.js:274:18)
    at Object.<anonymous> (/home/crunch/.nvm/versions/node/v16.8.0/lib/node_modules/postgraphile/build/postgraphile/cli.js:342:23)
    at Module._compile (node:internal/modules/cjs/loader:1101:14)
    at Object.Module._extensions..js (node:internal/modules/cjs/loader:1153:10)
    at Module.load (node:internal/modules/cjs/loader:981:32)
    at Function.Module._load (node:internal/modules/cjs/loader:822:12)
    at Module.require (node:internal/modules/cjs/loader:1005:19)
    at require (node:internal/modules/cjs/helpers:94:18)

Remove dependency of `build` package if possible, add one to `graphile-build`

We have a dependency to the build package https://www.npmjs.com/package/build which is ancient and includes among other things a dependency to an old version of uglify-js which has security problems.
I haven't found an import of the build package.

We do import from graphile-build package though, so that should be listed as a dependency or a peerDependency.

An in-range update of commitlint is breaking the build 🚨

There have been updates to the commitlint monorepo:

• • The devDependency @commitlint/cli was updated from 8.0.0 to 8.1.0.

• The devDependency @commitlint/config-conventional was updated from 8.0.0 to 8.1.0.

🚨 View failing branch.

This version is covered by your current version range and after updating it in your project the build failed.

This monorepo update includes releases of one or more dependencies which all belong to the commitlint group definition.

commitlint is a devDependency of this project. It might not break your production code or affect downstream projects, but probably breaks your build or test tools, which may prevent deploying or publishing.

Status Details

• ❌ ci/circleci: build: Your tests failed on CircleCI (Details).

---

Commits

The new version differs by 39 commits.

• c17420d v8.1.0
• ca19d70 chore: update dependency lodash to v4.17.14 (#724)
• 5757ef2 build(deps): bump lodash.template from 4.4.0 to 4.5.0 (#721)
• 5b5f855 build(deps): bump lodash.merge from 4.6.0 to 4.6.2 (#722)
• 4cb979d build(deps): bump lodash from 4.17.11 to 4.17.13 (#723)
• a89c1ba chore: add devcontainer setup
• 9aa5709 chore: pin dependencies (#714)
• c9ef5e2 chore: centralize typescript and jest setups (#710)
• c9dcf1a chore: pin dependencies (#708)
• 6a6a8b0 refactor: rewrite top level to typescript (#679)
• 0fedbc0 chore: update dependency @types/jest to v24.0.15 (#694)
• 0b9c7ed chore: update dependency typescript to v3.5.2 (#695)
• 4efb34b chore: update dependency globby to v10 (#705)
• 804af8b chore: update dependency lint-staged to v8.2.1 (#696)
• 9075844 fix: add explicit dependency on chalk (#687)

There are 39 commits in total.

See the full diff

FAQ and help

There is a collection of frequently asked questions. If those don’t help, you can always ask the humans behind Greenkeeper.

---

Your Greenkeeper Bot 🌴

Dependency Dashboard

This issue lists Renovate updates and detected dependencies. Read the Dependency Dashboard docs to learn more.

Open

These updates have all been created already. Click a checkbox below to force a retry/rebase of any.

• chore(deps): update dependency ava to v6
• chore(deps): update dependency execa to v9
• chore(deps): update dependency p-retry to v6
• chore(deps): update dependency semantic-release to v23
• Click on this checkbox to rebase all open PRs at once

Ignored or Blocked

These are blocked by an existing closed PR and will not be recreated unless you click a checkbox below.

• chore(deps): update dependency standard to v17.1.0
• chore(deps): update dependency node-fetch to v3

Detected dependencies

github-actions

.github/workflows/main.yml

• actions/checkout v4
• actions/setup-node v4

.github/workflows/onchange.yml

• actions/checkout v4
• actions/setup-node v4

npm

package.json

• graphile-build 4
• @commitlint/cli 19.3.0
• @commitlint/config-conventional 19.2.2
• @types/bluebird 3.5.42
• @types/dockerode 3.3.29
• @types/nanographql 2.0.3
• @types/node 20.12.12
• @types/node-fetch 2.6.11
• @types/pg 8.11.6
• @types/wait-on 5.3.4
• @typescript-eslint/eslint-plugin 7.10.0
• @typescript-eslint/parser 7.10.0
• ava 5.3.1
• bluebird 3.7.2
• c8 9.1.0
• dockerode 4.0.2
• eslint 9.3.0
• execa 5.1.1
• graphql 16.8.1
• husky 9.0.11
• json-decycle 3.0.0
• node-fetch 2.7.0
• p-retry 4.6.2
• pg 8.11.5
• postgraphile 4.13.0
• postgraphile-core 4.13.0
• prettier 3.2.5
• prettier-plugin-organize-imports 3.2.4
• semantic-release 21.1.2
• standard 17.0.0
• ts-node 10.9.2
• typescript 5.4.5
• node >=8

nvm

.nvmrc

• node 20.13.1

---

• Check this box to trigger a request for Renovate to run again on this repository

GraphiQL using the cli won't load the mutations, but it works on the server

Hey!

I've been trying to use this plugin on my local environment using postgraphile cli, but I've been having a hard time setting it up.

This is my rc file:

module.exports = {
    options: {
        graphileBuildOptions: {
            connectionFilterRelations: true,
        },
        plugins: [
            "postgraphile-plugin-connection-filter",
            "postgraphile-upsert-plugin"
        ]
    },
};

For some reason, event leaving only postgraphile-upsert-plugin enabled, it still won't load/autocomplete upsert mutations.

When debugging, it says it was loaded:

Do you know the reason this might be happening?

The automated release is failing 🚨

🚨 The automated release from the master branch failed. 🚨

I recommend you give this issue a high priority, so other packages depending on you could benefit from your bug fixes and new features.

You can find below the list of errors reported by semantic-release. Each one of them has to be resolved in order to automatically publish your package. I’m sure you can resolve this 💪.

Errors are usually caused by a misconfiguration or an authentication problem. With each error reported below you will find explanation and guidance to help you to resolve it.

Once all the errors are resolved, semantic-release will release your package the next time you push a commit to the master branch. You can also manually restart the failed CI job that runs semantic-release.

If you are not sure how to resolve this, here is some links that can help you:

• Usage documentation
• Frequently Asked Questions
• Support channels

If those don’t help, or if this issue is reporting something you think isn’t right, you can always ask the humans behind semantic-release.

---

Invalid npm token.

The npm token configured in the NPM_TOKEN environment variable must be a valid token allowing to publish to the registry https://registry.npmjs.org/.

If you are using Two-Factor Authentication, make configure the auth-only level is supported. semantic-release cannot publish with the default auth-and-writes level.

Please make sure to set the NPM_TOKEN environment variable in your CI with the exact value of the npm token.

---

Good luck with your project ✨

Your semantic-release bot 📦🚀

Upsert should support create, update, where

Example

upsert({
  update: {
    role: 'ADMIN'
  },
  where: {
    email: '[email protected]'
  },
  create: {
    name: 'Alice',
    email: '[email protected]',
    role: 'ADMIN'
  }
})

Documentation is incorrect

Read-me should be
yarn add postgraphile-upsert-plugin

Error: Input Object type TableWhere must define one or more fields.

When the table does not have any unique columns, the schema generation fails with the error:

Input Object type TableWhere must define one or more fields.

Version: 2.0.1
Postgraphile version: 4.12.3

An in-range update of postgraphile is breaking the build 🚨

The devDependency postgraphile was updated from 4.4.0 to 4.4.1.

🚨 View failing branch.

This version is covered by your current version range and after updating it in your project the build failed.

postgraphile is a devDependency of this project. It might not break your production code or affect downstream projects, but probably breaks your build or test tools, which may prevent deploying or publishing.

Status Details

• ❌ ci/circleci: build: Your tests failed on CircleCI (Details).

---

Release Notes for v4.4.1 - Performance improvements, versioned docker tags

The main feature of this release is significant performance improvements — enjoy!

We also overhauled how Docker builds work, so they're now tagged in a more sensible manner. Almost all previous tags have been deleted. From now onwards we have versioned Docker images:

• graphile/postgraphile:4 will give you the latest stable in the "v4.x.x" line (no alphas, betas, rcs); this is the recommended version to use
• Every new vX.Y.Z git tag (i.e. no alpha/beta/rc) will automatically release graphile/postgraphile:X-Y and graphile/postgraphile:X-Y-Z
• graphile/postgraphile:latest will give you the latest stable (but beware of major version bumps!)
• graphile/postgraphile:next will give you the equivalent of what's on master right now (i.e. pre-release/bleeding edge/nightly)

We're currently in a teething period for this, so there may be some bumpiness - if you face any issues, please let me know via GitHub issues or discord.

NOTE: this only applies to future releases; we are not back-filling previous releases, so there's not many tags to choose from right now.

Other changes:

• update various dependencies
• significant performance improvements
• fix a bug with standalone LDS server announcements for insert/update (thanks @pepijnverburg)
• export more TypeScript interfaces from graphile-utils
• add missing dependencies (thanks @michaelbeaumont)
• respect externalUrlBase for websockets (thanks @DvdGiessen)
• typo fixes (thanks @angelosarto, @ludwigbacklund)
• add support for PGHOSTADDR envvar if PGHOST is not present (thanks @encima)
• 🚨 Remove invalid fields from payloads for SETOF function mutations (these fields were never valid, so any client using them would already be broken, thus I am not classing this as a breaking change).

Commits

The new version differs by 10 commits.

• f4c1be8 4.4.1
• 616bc5b feat(deps): upgrade postgraphile (#1103)
• b72f1ef feat(cli): accept PGHOSTADDR if PGHOST is not present (#1096)
• 5951739 docs(README): fix typo (#1097)
• 83024fb 4.4.1-rc.0
• fe6e91a chore(docker): tidy the Dockerfile, use fewer layers (#1079)
• 9aa3799 feat(docker): update Dockerfile to build fresh rather than install from npm (#1078)
• d237417 feat(perf): performance enhancements (#1077)
• c2638ec sponsorship: update sponsors for May 🙏 (#1071)
• e3d7d19 fix(postgraphile): use externalUrlBase for websockets (#1070)

See the full diff

FAQ and help

There is a collection of frequently asked questions. If those don’t help, you can always ask the humans behind Greenkeeper.

---

Your Greenkeeper Bot 🌴

Suggestion - omit upsert if update or insert are manually omitted

Let me know if you're interested in pulling this change into your upstream, I will make a PR

ioancole@e404ebb

Basically, if create or update are manually omitted, then maybe it is safe to assume that they want to also omit upsert..

Upsert should respect @omit tags again

Originally #175
This is an issue again. It should be fixed with #260

Composite unique indexes not supported?

I made index like below, but this library seems like doesn’t support use column1 and column2 in where clause.

CREATE UNIQUE INDEX "shifts_column1_column2_unique_index" ON "my_table" ("column1", "column2");

Can you support this index?