Raw API (Open questions & improvements)

schickling commented 5 years ago

Just gave @matthewmueller‘s first draft of the Raw Photon API a first read and really like the idea. I think there are a few more points to discuss and incorporate into the spec:

Consider using DB-native query interpolation for values
Polyglot persistence: Boundary of raw queries & entry points (Proposal: photon.raw() -> photon.raw.pg()
PhotonJS: raw overlap confusion (types vs exec)
Partial raw fallbacks (ask to Tim about Knex)
MongoDB: More examples (e.g. MongoDB query)
Categorize data source raw APIs (e.g. string based, object/array based, ...)

janpio commented 4 years ago

Is this fully incorporated @matthewmueller? Can you confirm this @schickling?

matthewmueller commented 4 years ago

Goal

We want an escape hatch in photon for complex queries that we don't yet support. We'll start with PhotonJS and Postgres/MySQL/SQLite. Below I present Postgres, but this will work fine for MySQL and SQLite.

Proposal

Given the following schema:

datasource pg {
  provider = "postgresql"
  url = "postgresql://user@localhost:5432/db?schema=public"
}

model User {
  email     String @map("email")
  firstName String @map("first_name")
  @@map("users")
}

We'll generate a raw API. This will be dependent on both the data source and the language we're generating for. For JS, it will be used like this:

import { Photon } from '@prisma/photon'
const photon = new Photon()

// destructure models
const { user } = Photon.raw.pg

// variables you pass in
const email = 'alice@prisma.io'
const firstName = 'Alice'

// template function
const out = photon.raw.pg`
  select ${user.firstName}, ${user.email} from ${user} where ${user.email} = ${email} and ${user.firstName} = ${firstName}
`

Which returns:

{
  query: 'select "public"."user"."first_name", "public"."user"."email" from "public"."user" where "public"."user"."email" = $1 and "public"."user"."first_name" = $2',
  variables: [ 'alice@prisma.io', 'Alice' ]
}

Notes

We use prepared parameters to avoid SQL injection attacks
pg is the name of the datasource as defined by prisma. For Postgres, the data source connect to a specific schema, in our case the public schema.
We can have multiple datasources by using different names (e.g. pg, mgo, etc.)
Other languages will generate raw differently depending on their syntax
See @matthewmueller for code for writing the template function.

Real-world SQL Query

Given the following SQL query:

SELECT *
FROM
  (SELECT userid,
          username,
          ts,
          message_id,
          TYPE,
          Timestampdiff(YEAR, cp.birthdate, Curdate()) AS age,
          IF(postcode = '' || postcode IS NULL, '?', LEFT(cp.postcode, 1)) AS postcode,

     (SELECT Count(*)
      FROM community_image
      WHERE community_profile_id = userid
        AND community_image.verified IS NOT NULL) AS images_count,

     (SELECT PATH
      FROM community_image
      WHERE community_profile_id = userid
        AND main = 1
        AND community_image.verified IS NOT NULL
        AND PRIVATE = 0) AS image,
          CASE TYPE
              WHEN 'gift' THEN '/default/images/mobile-present-sent.png'
              WHEN 'poke' THEN '/default/images/mobile-poked.png'
              WHEN 'visit' THEN '/default/images/mobile-visited.png'
              ELSE '/default/images/mobile-added-to-friends.png'
          END AS icon,
          CASE TYPE
              WHEN 'gift' THEN (REPLACE('%s hat dir ein Geschenk geschickt', '%s', username))
              WHEN 'poke' THEN (REPLACE('%s hat dich angestupst', '%s', username))
              WHEN 'visit' THEN (REPLACE('%s hat dich besucht', '%s', username))
              ELSE (REPLACE('%s hat dir eine Freundschaftsanfrage gesendet', '%s', username))
          END AS description,
          gift
   FROM
     (SELECT sender AS userid,
             sender_name AS username,
             `timestamp` AS ts ,
             community_message_id AS message_id,
             TYPE,
             IF(TYPE = 'gift',
                  (SELECT Concat('{ \"gift_id\": "', cgu.community_gift_user_id, '", \"image\": "' ,
                                   (SELECT image
                                    FROM community_gifts cg
                                    WHERE cgu.community_gift_id = cg.community_gift_id), '", \"name"\: "',
                                   (SELECT name
                                    FROM community_gifts cg
                                    WHERE cgu.community_gift_id = cg.community_gift_id), '", \"text"\: "', cgu.message, '"}')
                   FROM community_gift_user cgu
                   WHERE cgu.community_gift_user_id = text), NULL) AS gift
      FROM community_message AS cm
      WHERE TYPE IN('gift',
                    'visit',
                    'poke',
                    'friendship-request')
        AND cm.receiver = '1658024'
        AND cm.sender IN
          (SELECT auth_user_id
           FROM auth_user
           WHERE auth_user_id = cm.sender
             AND `status` = 'normal')
      UNION SELECT visiter AS userid,
                   username,
                   Max(Concat(cv.date, ' ', cv.time)) AS ts,
                   NULL AS message_id,
                   'visit' AS TYPE,
                   NULL AS gift
      FROM community_visit AS cv
      RIGHT JOIN auth_user ON visiter = auth_user_id
      AND `status` = 'normal'
      WHERE community_profile_id = '1658024' GROUP  BY visiter) AS un
   LEFT JOIN community_profile cp ON cp.community_profile_id = userid
   LEFT JOIN auth_user_role aur ON aur.auth_user_id = userid) AS ch GROUP  BY userid,
                                                                              message_id,
                                                                              TYPE
ORDER  BY ts DESC
LIMIT 0,
      20

Real-world JS mapping

import Photon from '@prisma/photon'

const photon = new Photon()
const {
  community_profile,
  community_image,
  community_message,
  community_gift_user,
  community_gifts,
  auth_user,
  community_visit,
  auth_user_role,
} = Photon.raw.pg

await photon.raw.pg`
SELECT *
FROM
  (SELECT userid,
          username,
          ts,
          message_id,
          TYPE,
          Timestampdiff(YEAR, ${community_profile.birthdate}, Curdate()) AS age,
          IF(postcode = '' || postcode IS NULL, '?', LEFT(${community_profile.postcode}, 1)) AS postcode,

     (SELECT Count(*)
      ${community_image}
      WHERE ${community_image.community_profile_id} = ${community_message.sender}
        AND ${community_image.verified} IS NOT NULL) AS images_count,

     (SELECT PATH
      FROM ${community_image}
      WHERE ${community_image.community_profile_id} = ${community_message.sender}
        AND ${community_image.main} = 1
        AND ${community_image.verified} IS NOT NULL
        AND ${community_image.private} = 0) AS image,
          CASE ${community_image.type}
              WHEN 'gift' THEN '/default/images/mobile-present-sent.png'
              WHEN 'poke' THEN '/default/images/mobile-poked.png'
              WHEN 'visit' THEN '/default/images/mobile-visited.png'
              ELSE '/default/images/mobile-added-to-friends.png'
          END AS icon,
          CASE ${community_image.type}
              WHEN 'gift' THEN (REPLACE('%s hat dir ein Geschenk geschickt', '%s', ${community_message.sender_name}))
              WHEN 'poke' THEN (REPLACE('%s hat dich angestupst', '%s', ${community_message.sender_name}))
              WHEN 'visit' THEN (REPLACE('%s hat dich besucht', '%s', ${community_message.sender_name}))
              ELSE (REPLACE('%s hat dir eine Freundschaftsanfrage gesendet', '%s', ${community_message.sender_name}))
          END AS description,
          gift
   FROM
     (SELECT ${community_message.sender} AS userid,
             ${community_message.sender_name} AS username,
             ${community_message.timestamp} AS ts ,
             ${community_message.community_message_id} AS message_id,
             ${community_message.type},
             IF(${community_message.type} = 'gift',
                  (SELECT Concat('{ \"gift_id\": "', ${community_gift_user.community_gift_user_id}, '", \"image\": "' ,
                                   (SELECT ${community_gifts.image}
                                    FROM ${community_gifts} cg
                                    WHERE ${community_gift_user.community_gift_id} = ${community_gifts.community_gift_id}), '", \"name"\: "',
                                   (SELECT ${community_gifts.name}
                                    FROM ${community_gifts} cg
                                    WHERE ${community_gift_user.community_gift_id} = ${community_gifts.community_gift_id}), '", \"text"\: "', ${
  community_gift_user.message
}, '"}')
                   FROM ${community_gift_user} cgu
                   WHERE ${community_gift_user.user_id} = text), NULL) AS gift
      FROM ${community_message} AS cm
      WHERE ${community_message.type} IN('gift',
                    'visit',
                    'poke',
                    'friendship-request')
        AND ${community_message.receiver} = '1658024'
        AND ${community_message.sender} IN
          (SELECT ${auth_user.auth_user_id}
           FROM ${auth_user}
           WHERE ${auth_user.auth_user_id} = ${community_message.sender}
             AND ${auth_user.status} = 'normal')
      UNION SELECT ${community_visit.visiter} AS userid,
                   ${community_visit.username},
                   Max(Concat(${community_visit.date}, ' ', ${community_visit.time})) AS ts,
                   NULL AS message_id,
                   ${community_visit.visit} AS TYPE,
                   NULL AS gift
      FROM ${community_visit} AS cv
      RIGHT JOIN ${auth_user} ON ${auth_user.visitor} = ${auth_user.auth_visitor_id}
      AND ${community_visit.status} = 'normal'
      WHERE ${community_visit.community_profile_id} = '1658024' GROUP BY ${auth_user.visitor}) AS un
   LEFT JOIN ${community_profile} cp ON ${community_profile.community_profile_id} = ${community_visit.visiter}
   LEFT JOIN ${auth_user_role} aur ON ${auth_user_role.auth_user_id} = ${community_visit.visiter}) AS ch GROUP  BY ${community_visit.visiter}, message_id, ${
  community_visit.visit
}
ORDER BY ts DESC
LIMIT 0, 20
`

prisma / specs