Passive Postgres Connector

[sorenbs]

Passive Sql Connector

Prisma supports two kinds of database connectors:

• Active connectors - manage and migrate the database schema
• Passive connectors - read and write data only

This is a living document outlining all considerations for a passive SQL connector. This is not a final spec - please contribute by commenting below.

Configuration points

Fundamental mapping

• table name
• column name
• hide fields in mutations (ie. auto increment or other auto values in db)
• relation mapping

Extra

• generate value (id, date, random, computed)
• constraints

Example SDL

type User @table(name: "tblUsers") {
  id: Int @unique @primaryKey(name: "primaryId", auto: true)
  name: String @column(name: "nameField")
}

Relation Mapping

Below are given examples for all possible relation configurations. We can likely support a more concise syntax

one-one

• one relation column
• two relation columns
• relation table

one relation column

type A {
  b: B @relation(ownColumn: "bId")
}

type B {
  a: A @relation(foreignColumn: "bId")
}

two relation columns

type A {
  b: B @relation(ownColumn: "bId", foreignColumn: "aId")
}

type B {
  a: A @relation(ownColumn: "aId", foreignColumn: "bId")
}

relation table

type A {
  b: B
    @relation(
      ownColumn: "bId"
      foreignColumn: "aId"
      relationTable: "ABRelation"
    )
}

type B {
  a: A
    @relation(
      ownColumn: "aId"
      foreignColumn: "bId"
      relationTable: "ABRelation"
    )
}

one-many

• one relation column
• relation table

one relation column

type A {
  b: B @relation(ownColumn: "bId")
}

type B {
  a: A @relation(foreignColumn: "bId")
}

relation table

type A {
  b: B
    @relation(
      ownColumn: "bId"
      foreignColumn: "aId"
      relationTable: "ABRelation"
    )
}

type B {
  a: A
    @relation(
      ownColumn: "aId"
      foreignColumn: "bId"
      relationTable: "ABRelation"
    )
}

many-many

• relation table

relation table

type A {
  b: B
    @relation(
      ownColumn: "bId"
      foreignColumn: "aId"
      relationTable: "ABRelation"
    )
}

type B {
  a: A
    @relation(
      ownColumn: "aId"
      foreignColumn: "bId"
      relationTable: "ABRelation"
    )
}

Special consideration: join-table with extra columns

A join table can have extra columns that should be accessible in the API. This is especially critical if the columns are required and mutations should be supported.

This can be supported by modelling the join table as a normal table with two one-relations with a single relation column. In the future we can add direct support for extra fields on relations in query and mutation API.

Data Types

See #1753

Questions

• Should we catch format violations in API or handle SQL error?
  • Probably both
• should we expose all sub types? (ie. varchar, mediumText for String)?
  • Probably not
• Do we need to know about collation?
  • Probably not

Int

• integer
• int
• tinyInt
• smallInt
• meddiumInt
• bigInt

Float

• float
• double

Decimal

• decimal
• numeric

Boolean

• bool
• tinyInt

String

• varchar
• char
• smallText
• mediumText
• bigText
• binary?

DateTime

• Date
• dateTime
• timestamp
• time
• year

SDL Generation

The passive connector requires an SDL file that describes the layout of the database schema. If the database schema follows common conventions we can automatically generate this SDL file. If the database schema deviates from common conventions we can generate a partial SDL file that the developer can use as a starting point.

[msand]

How about stored procedures?

[sorenbs]

@msand Great point. Could you write up a few examples for stored procedures and how you want to expose them through GraphQL?

[msand]

@sorenbs PostGraphile has great support for them: https://www.graphile.org/postgraphile/procedures/

There are a few ways procedures in PostGraphile can be used. All of these will be covered in their own section.

As mutations.
As queries.
As connections (list of nodes, like postNodes).
As computed columns.

At least in PostgreSQL they are really quite powerful, supporting these languages in the base distribution: SQL, PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python.

And these externally maintained ones:
https://www.postgresql.org/docs/current/static/external-pl.html
PL/Java | Java | https://tada.github.io/pljava/
PL/Lua | Lua | https://github.com/pllua/pllua
PL/R | R | http://www.joeconway.com/plr.html
PL/sh | Unix shell | https://github.com/petere/plsh
PL/v8 | JavaScript | https://github.com/plv8/plv8

So, essentially they can be used for almost any and all back-end work.
E.g. one use case would be an CQRS based system, where all actions are queued and stored to an event store (very fast and reliable writes of commands). Then, using triggers, one can maintain several optimized query models, essentially as a real-time updating database with dependency tracking similar to e.g. mobx.

This can simplify:
Only fetch the very minimal amount, of maximally pre-computed data for rendering (above-the-fold) content
Subscribe in a granular fashion to real-time updates of only the relevant parts
Only do the computation once on the server, reuse in all thin-clients / under-powered devices
Store optimistic updates as a separate collection, easily update/recreate the query models when actual server responses arrive
Allow users to fetch all necessary events / computed-data to work offline
Trigger e-mail to be sent, external events to other systems etc.

Not to mention how the query planner tends to do very well when it can pre-compile and combine procedures, it also decreases network traffic, and places more of the computation right next to the data (less context switching and communication, more intelligent optimization when combining procedures, etc). Of course, it's no silver bullet, and won't always be faster or the right tool for the job.

Edit: This probably belongs here: https://github.com/graphcool/prisma/issues/1641

[msand]

Just realized this was the mysql issue, but mostly the same should apply with regards to use cases.

[pacohernandezg]

Just a few words to comment the differences with our approach.

Passive SQL connectors are similar to what we do at NileDB (https://github.com/NileDB/com.niledb.core), but we follow a simpler approach because we support PostgreSQL only. So, our model is based 100% on PostgreSQL capabilities (tables, views, fields, references, data types, schemas, ...).

Are you going to have a different PassiveSQL connector models for each supported database engine or are you trying to use the same model for all supported database engines?

The problem I see with having only one model for every supported database engine is that existing databases may be using specifics of each database engine (i.e. tables in different schemas, specific data-types like tsvector, ...., materialized views, gist indexes, ...) and it is very difficult to support different databases in the same model [meta-model] (common denominator problem). IMHO.

Our approach is different from the beginning. Although we don't support other databases directly (PostgreSQL only), we can get data from different sources using PostgreSQL's FDW (Foreign Data Wrappers) [https://wiki.postgresql.org/wiki/Foreign_data_wrappers]. You can integrate data from different sources at the same time (i.e. integrate data from MySQL, SQL Server, a flat file, ... in the same GraphQL query). With this approach, we can provide a GraphQL API on top of almost anything (including flat files, any database, ...).

The same occurs with aggregations, stored procedures, ..., we don't support them, but it is very easy for us to create a PostgreSQL VIEW on top of a query that uses aggregation functions and expose the VIEW through GraphQL.

We are developing NileDB to use it in our own business use cases. We needed to provide GraphQL API on top of existing complex databases (with tables in different schemas, ...). We evaluated Postgraphile, but its query capabilities, .., where not enough, so we decided to implement our own Graphql layer in Java.

[mledu]

What about support for database name? So you have @table("tableName") but often people have data spread across different logical dbs in a mysql instance. Something like @database('dbName") would work.

[sorenbs]

Prisma currently supports existing Postgres databases. As part of the upcoming changes to the datamodel, we will introduce support for existing MySQL databases: prisma/prisma#3408

The new MongoDB connector already works with existing databases: prisma/prisma#1643

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed in 10 days if no further activity occurs. Thank you for your contributions.