Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

PostgreSQL Connector #1641

Closed
marktani opened this issue Jan 16, 2018 · 30 comments

Comments

@marktani
Copy link
Contributor

commented Jan 16, 2018

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

@pie6k

This comment has been minimized.

Copy link

commented Jan 16, 2018

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

This comment has been minimized.

Copy link

commented Jan 16, 2018

@pie6k Oh and don't forget about arrays

@IAlexandr

This comment has been minimized.

Copy link

commented Jan 17, 2018

And geometry type

@HugoDellinger

This comment has been minimized.

Copy link

commented Jan 21, 2018

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

@brandonmp

This comment has been minimized.

Copy link

commented Jan 27, 2018

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

This comment has been minimized.

Copy link

commented Jan 29, 2018

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

This comment has been minimized.

Copy link

commented Feb 1, 2018

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

This comment has been minimized.

Copy link

commented Feb 7, 2018

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

This comment has been minimized.

Copy link
Contributor Author

commented Feb 7, 2018

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

This comment has been minimized.

Copy link

commented Feb 7, 2018

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

This comment has been minimized.

Copy link

commented Mar 2, 2018

@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

This comment has been minimized.

Copy link

commented Mar 2, 2018

@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

This comment has been minimized.

Copy link

commented Mar 9, 2018

@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

This comment has been minimized.

Copy link
Contributor

commented Mar 9, 2018

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

Does that help?

@ghost

This comment has been minimized.

Copy link

commented Mar 9, 2018

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

This comment has been minimized.

Copy link

commented Apr 12, 2018

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

This comment has been minimized.

Copy link

commented Apr 19, 2018

@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

This comment has been minimized.

Copy link
Member

commented Apr 24, 2018

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

@stratosgear

This comment has been minimized.

Copy link

commented Apr 26, 2018

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

@marktani

This comment has been minimized.

Copy link
Contributor Author

commented Apr 26, 2018

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

@marktani

This comment has been minimized.

Copy link
Contributor Author

commented May 8, 2018

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 🙂

@marktani marktani closed this May 8, 2018

@celvin

This comment has been minimized.

Copy link

commented Jun 7, 2018

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

This comment has been minimized.

Copy link

commented Jun 7, 2018

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

This comment has been minimized.

Copy link

commented Jun 7, 2018

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

@dhruvbhatia

This comment has been minimized.

Copy link

commented Jun 24, 2018

@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

This comment has been minimized.

Copy link
Contributor Author

commented Jun 25, 2018

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:

@dhruvbhatia

This comment has been minimized.

Copy link

commented Jun 25, 2018

@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

This comment has been minimized.

Copy link

commented Jul 10, 2018

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

This comment has been minimized.

Copy link
Contributor Author

commented Jul 10, 2018

@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

This comment has been minimized.

Copy link

commented Jul 10, 2018

@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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
You can’t perform that action at this time.