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

Raw API fallbacks #10

Open
schickling opened this issue May 20, 2019 · 1 comment

Comments

@schickling
Copy link
Member

commented May 20, 2019

await prisma.users({
  where: { email: { contains: '@gmail.com' } },
  orderBy: {
    $raw: 'age + postsViewCount DESC',
  },
})

const someEmail = 'bob@prisma.io'
await prisma.users({
  orderBy: {
    $raw: 'age + postsViewCount DESC',
  },
  where: {
    $raw: ['email = $1', someEmail],
  },
})

// Raw: Knex & Prisma
const userWithPostsAndFriends1 = await prisma.users.findOne({
  where: knex.whereBuilderInSelecet(
    knex.fields.User.name,
    knex.queryMany.Post({ title: 'Alice' }, kx.fields.Post.title),
  ),
  select: knex.select('*').from('User'),
})

// Raw: SQL & Prisma
const userWithPostsAndFriends2 = await prisma.users.findOne({
  where: {
    $raw: 'User.name != "n/a"',
  },
  select: {
    $raw: {
      name: {
        query: 'User.firstName + User.lastName; DROP TABLE',
        type: 'string',
      },
      hobbies: {
        topLevelQuery: 'SELECT * from Hobbies where User.id = $id',
        type: {
          name: 'Hobby',
          fields: {
            id: {
              type: 'string',
            },
            name: {
              type: 'string',
            },
          },
        },
      },
    },
  },
})
@matthewmueller

This comment has been minimized.

Copy link
Contributor

commented Jun 25, 2019

I've been think about this on and off for quite a while and I'd like to propose an alternative. 3 APIs to cover raw SQL:

1. Photon generated methods. What we've always been doing. These will get us 90% of the way.

photon.users.find({ where: { id: 10 }})

2. Query builder, something like:

photon.query(selectFrom("users", "name", "last_name", selectFrom("posts", "title"))

This will make it easy to write more complex queries, but will be SQL / NoSQL specific.

  • Place in Call for Projects

3. Template strings

We'll generate type-safe column name accessors.

export const user = {
  first_name: "first_name",
  last_name: "last_name",
  table: "users"
}

await photon.raw(`select ${user.first_name} from ${user.table}`)
  • input variables?
  • more clear what raw is pointing too (mongo vs. mysql)
  • this only queries a single datasource in a polyglot setting.
  • how does mongo work?

This should cover all the cases for complex SQL queries. Overtime we can build more into Photon, but this gives us a clear escape hatch. I think another reasonable alternative is ditching 2 and only having 1 & 3.

We also may want to expose the raw query protocol to allow low-level cross-datasource joins. When we add support for these types of operations, most likely we'll add a Photon API for them, but perhaps not always.


Consider Partial Raw as well. Main issue, how does the query get put back together?

@matthewmueller matthewmueller modified the milestone: 1.0 Jun 25, 2019

@matthewmueller matthewmueller added this to the Prisma 2 GA milestone Jun 26, 2019

@janpio janpio removed this from the Prisma 2 GA milestone Aug 9, 2019

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