Raw database access fallback

[schickling]

Introduction

Prisma exposes a flexible API that conforms to the OpenCRUD specification.

This API has excellent developer ergonomics and is powerful enough to cover most use cases. Sometimes, however you need to drop down to the native API of the underlying database to performa a more complex operation. This ticket specifies how raw database access can be exposed in the existing GraphQL API.

API

Considerations:

• As an arbitrary query can modify data, it should be a mutation and the name should suggest that it is an imperative action.
• It must be possible to select which database to execute the query against
• Different kinds of databases (MySQL, MongoDB) should be supported

The two following API extensions are proposed:

Option A: Nested input

mutation {
  executeRaw({
    default: "SELECT TOP(10) FROM Users ORDER BY uuid()"
  })
}

Option B: Enum based

mutation {
  executeRaw(
    database: default,
    query: "SELECT TOP(10) FROM Users ORDER BY uuid()"
  )
}

Option B should have database be an optional argument.

Unless there are good arguments against it, we will go with option B

Databases

SQL

SQL databases are queried using a SQL string:

"SELECT * FROM Users ORDER BY uuid() Limit 2"

Return value is an array of objects:

[{
  name: "Carl"
  id: 1
},
{
  name: "Caroline",
  id: 2
}]

To return multiple result sets, simply use aliases to perform multiple executeRaw mutations

MongoDB

MongoDB is queried using a string containing a normal MongoDB query operation:

"db.users.find().limit(2)"

Return value is an array of objects:

[{
  name: "Carl"
  id: 1
},
{
  name: "Caroline",
  id: 2
}]

Some mongo queries return a single object. In that case the single object is wrapped in an array.

Elasticsearch

Elastic is queried using the normal query DSL:

"{
  \"query\": { \"match_all\": {} }
}"

Note that the query itself does not specify the collection to search. Normally this is determined by the URL that is being queried, ie /user/_search. We will need an extra field in the GraphQL API to specify the collection:

mutation {
  executeRaw(
    database: default,
    query: "{\"query\": { \"match_all\": {} }}",
    collection: "user"
  )
}

And returns an array of objects:

[{
  name: "Carl"
  id: 1
},
{
  name: "Caroline",
  id: 2
}]

Redis

Redis supports a large set of commands, for example scan:

"scan 0"

Return set is an array containing either nested arrays, objects or scalar values depending on the command:

[
  17,
  ["key:12", "kay:8"]
]

Notes

Also see https://www.youtube.com/watch?v=YUjlBuI8xsU

Configuration

This feature significantly widens the database access given to users of a Prisma service. Therefore, it should be possible to configure the feature to limit access or completely disable it.

Disable completely

If the feature is disabled for all databases, the executeRaw mutation is completely removed from the GraphQL API.

In the databases section of the PRISMA_CONFIG you can set the field rawAccess to false to disable raw database access. If the field is not specified it defaults to false.

PRISMA_CONFIG: |
        managementApiSecret: my-server-secret-123
        port: 4466
        databases:
          default:
            rawAccess: false
            connector: mysql
            migrations: true
            host: mysql-db
            port: 3306
            user: root
            password: prisma

Fine-grained control

Any query run through the executeRaw mutation is executed with the privileges of the database user configured for the connector. This includes the ability to access a schema belonging to a different service in a multi-tenant prisma installation. We should explore how we can allow raw database access while limiting the scope to the same service.

Prisma Client

The prisma client is implemented in several different languages. This describes the Typescript implementation and other languages will have a similar API.

API

To execute a raw query you need to specify what database to query, as well as the actual query. The following is the method definition for querying the default database:

prisma.$raw.default(query: String): Promise<Array<any>>

For convenience, the default database can also be queried directly:

prisma.$raw(query: String): Promise<Array<any>>

The method interface can differ for different types of databases. For example a Elastic Search database requires you to specify the collection as well as the query:

prisma.$raw.mySearchDatabase(collection: String, query: String): Promise<Array<any>>

Note: we could decide to provide a JsonArray interface as described in microsoft/TypeScript#1897 (comment)

Example

prisma.$raw("SELECT * FROM user LIMIT 1")

returns

[{
  id: 1,
  name: "Søren",
  age: 42
}]

[rostislav-simonik]

Hello, I'd like ask you what the man can imagine under given issue?
Something similar as prisma-bindings or interface for native prisma extensions.

Very thanks for explaining the initial intension.

[mavilein]

The executeRaw mutation is now available in 1.17.0-beta, which was just published now.

[mcmar]

Hi, I noticed the examples given here as well as in the release notes don't actually work due to this bug: prisma/prisma#3300
Please see the bug and determine if fixing that is something that you'd like to support.

[terion-name]

Why does this api is not exposed in "prisma-binding"?

[sorenbs]

@terion-name - thanks for opening the issue in prisma-binding! It would be great for somebody in the community to open a pull-request adding support.

Keep in mind that we have an upcoming addition to add support for parameter interpolation. It might be worth it to wait for this change.

cc @maticzav

[maticzav]

@sorenbs I believe prisma-binding should work without any further changes. Why do you think we would have to change anything?

[terion-name]

@maticzav well I've checked - bindings do not expose mutation.executeRaw with new Prisma.

UPD
Saw your comment in other issue. It's strange, maybe I've done something wrong, need to check once more