Prisma1: PostgreSQL Connector

[marktani]

This feature request serves as a central place to discuss development and progress for the PostgreSQL connector.

[pie6k]

Good thing to note is that Postgres has special json and jsonp column types (where jsonp content can be included as a condition of any SQL query).

Such fields might be good for cases of nested-fields like:

type User {
  id: ID! @unique
  email: String! @unique
  location: Location!
}

type Location {
  lat: Int!
  lng: Int!
}

However, it could make migrations hard to manage.

[felipesabino]

@pie6k Oh and don't forget about arrays

[IAlexandr]

And geometry type

[HugoDellinger]

So excited to be able to see Prisma work with Postgres. Is there any time horizon concerning such a PostgreSQL support ?

[brandonmp]

Good thing to note is that Postgres has special json and jsonp column types (where jsonp content can be included as a condition of any SQL query).

I am just learning Postgres recently, so maybe I'm wrong, but I think (a) it's jsonb, and (b) json and jsonb can both be queried

The only differences, per this SO answer, are:

• jsonb usually takes more disk space to store than json (sometimes not)
• jsonb takes more time to build from its input representation than json
• json operations take significantly more time than jsonb (& parsing also needs to be done each time you do some operation at a json typed value)

[TomMarius]

The difference lies in storage and ability to search through the data. JSON data type stores whatever text you feed it and all the operations Postgres allows you to do are ran on top of the string, thus slow. JSONB takes a little more time to parse (save), because it's converted to a binary representation that can be efficiently searched. The choice depends on what you're building, for a log storage I'd choose JSON and for a generic app I'd choose JSONB.

[sedubois]

I have a legacy Ruby on Rails 4 application with a PostgreSQL DB. I’m waiting for this connector in order to develop a new app on top of that DB while the legacy keeps running. My only concern is that I think there’s no guarantee that the Rails ActiveRecord ORM immediately persists every change, so there might be some race condition if a same change is done on both sides at once. But this is probably a low provabilith and at least I can set up a read-only Prisma view on the DB to start with, then grow from there.

The database contains normal tables with relations concerning users, subscriptions and purchases, the various content the app offers structured in a few related tables, and some tables storing how users have interacted with this content.

If/once a migration to Prisma is complete, I would be looking forward to learn how to have different instances of the database with a load balancer in front of them for faster access across different regions, but this is a longer term effort and I don’t know anything about this.

Our DB currently runs in Ireland on Heroku, our primary market is France, but we have worldwide market as well and I will need to be able to operate and administrate from south east Asia.

[sedubois]

NB: as explained above, our PostgresQL DB already exists and is deployed on Heroku (EU region). Could the connector be developed in a way that it could remotely connect to that DB instead of needing a DB Docker image running on the same machine?

[marktani]

Could the connector be developed in a way that it could remotely connect to that DB instead of needing a DB Docker image running on the same machine?

Sure. In fact, it is already possible to connect to a remote MySQL database using the active MySQL connector in Prisma currently.

[brandonmp]

Sure. In fact, it is already possible to connect to a remote MySQL database using the active MySQL connector in Prisma currently.

Are there docs on/examples of this? would love to point my prisma @ an RDS db

[cameronk]

@marktani Bumping @brandonmp's comment, would really love to see an example of this.

If I understand right, it has to do with a modification to some variables for the prisma cluster, documented here.

Gonna try to figure this out now, but would appreciate any team insights!

[aranwe]

@cameronk Look @ the env vars for prisma-database:
just remove from the docker file completely prisma-db (btw: not a very lucky choice of name - as it is the mysql db container - check image: mysql-5.7)
And change the env vars:

SQL_CLIENT_HOST=prisma-db
SQL_CLIENT_PORT=3306

you should be able to set SQL_CLIENT_HOST either to IP or any accessible host from the server where the docker container runs (either LAN or the internet).

Anyone, please correct me if I am wrong :)

[ghost]

@marktani how exactly can we do this?

Sure. In fact, it is already possible to connect to a remote MySQL database using the active MySQL connector in Prisma currently.

[divyenduz]

Max has put this up! https://github.com/maxdarque/prisma-docker

Does that help?

[ghost]

Thanks! I'll take a look at that... I want to host my Prisma backend on Heroku and Heroku gives me an external database

[mikeclymer]

I asked this in Slack, but my question might be better suited for this forum...

Will the ability to manage connection pools be available in this connector? I can setup PgBouncer, but I would prefer to have programmatic control from a limited set of content processing services that need more connections than the rest of my app.

[kylemh]

@coco98 just announced in Reactiflux QnA that he'll be open sourcing Hasura's solution to exporting Postgres into a GraphQL server in the next few months! The code should prove valuable in moving this issue forward.

[do4gr]

We just released the alpha version of the Postgres connector with Prisma 1.7, check it out!

[stratosgear]

Is there a place to ask postgres specific questions? For example, how to edit the default$default generated schema?

[marktani]

You can ask questions in the Forum, on Slack, and in a new issue on Github.

[marktani]

We've just released Prisma 1.8 which includes stable support for Postgres databases: https://blog.graph.cool/prisma-now-supports-postgres-aad74ba479cb

Thanks a lot for your feedback 🙂

[celvin]

I just have started to play with graphQL, so with this stabe support for Postgres databases, is not needed Graphile (https://github.com/graphile/postgraphile) anymore right?

[james-ff]

I believe it is down to your use case. For myself I'm using a postgres db with existing data, which is still experimental within prisma, and not all the features that I need are supported yet. Until such time I am sticking with postgraphile (but will work with Prisma to test these features when they come in). If speed is an issue currently postgraphile is faster, and better at more complex queries too https://medium.com/@Benjie/how-i-made-postgraphile-faster-than-prisma-graphql-server-in-8-hours-e66b4c511160 It would be nice for the two platforms to merge, to get the best of both solutions, but It's unclear how likely that is.

[celvin]

Thanks a lot, very enlightening. Now I just need to find the best angular client for this.

[dhruvbhatia]

@marktani Does the @unique decorator not consider case insensitive entries with the same characters as violations under PostgreSQL?

E.g. with the following schema I can generate seperate records which have usernames that only differ in case such as marktani, Marktani, MarkTani etc:

type User {
  id: ID! @unique
  username: String! @unique
}

This seems incorrect based on the Graphcool docs here, but I suppose that documentation may be MySQL specific?

Keen on hearing thoughts on how to best model case insensitive unique fields.

[marktani]

Thanks for bringing this up @dhruvbhatia, for MySQL this behaviour is actually configured with the collation - the default collation used is case insensitive but maybe you have a case sensitive collation?

We should add this to the documentation!

More resources:

• https://dev.mysql.com/doc/refman/8.0/en/case-sensitivity.html
• https://stackoverflow.com/a/4558736/1176596

[dhruvbhatia]

@marktani thanks for the information. I'm using the prismagraphql/prisma:1.9 docker image which is spinning up a standard postgres docker image. I suppose this has a case sensitive collation by default? Will see if I can change this somewhere in the settings, but agree that the documentation should be updated to reflect!

Edit: I found a workaround in the interim. This tutorial showed me how I can write raw SQL in my resolvers. Combined with Postgres' ILIKE operator I can check user input against DB records (in a case insensitive manner) and then handle accordingly. Not super elegant but does the job.

[tomitrescak]

Hello, sorry for off topic, but I am trying to access the Postgres database osing pgAdmin, with no luck. Any idea how can it be achieved? This is my docker-compose

version: '3'
services:
  prisma:
    image: prismagraphql/prisma:1.11
    restart: always
    ports:
    - "4466:4466"
    environment:
      PRISMA_CONFIG: |
        port: 4466
        # uncomment the next line and provide the env var PRISMA_MANAGEMENT_API_SECRET=my-secret to activate cluster security
        # managementApiSecret: my-secret
        databases:
          default:
            connector: postgres
            host: postgres
            port: 5432
            user: prisma
            password: prisma
            migrations: true
  postgres:
    image: postgres
    restart: always
    environment:
      POSTGRES_USER: prisma
      POSTGRES_PASSWORD: prisma
    volumes:
      - postgres:/var/lib/postgresql/data

  pgadmin:
    image: dpage/pgadmin4
    depends_on:
      - postgres
    ports:
      - "5555:80"
    environment:
      PGADMIN_DEFAULT_EMAIL: ****
      PGADMIN_DEFAULT_PASSWORD: ****
    restart: unless-stopped
volumes:
  postgres:

[marktani]

@tomitrescak you need to forward the port from your postgres container. Here's a related discussion: https://stackoverflow.com/a/51241338/1176596

I encourage you to post further questions either on Stack Overflow or the Prisma Forum, comments in closed GitHub issues can be easily missed 🙂

[tomitrescak]

@marktani thanks for your answer. Yeah I found the forum after I posted the question here. I felt silly afterwards. Thanks for the amazing effort guys. You make server dev work heaven.