ExecuteRaw No database selected

[mcmar]

Describe the bug When using executeRaw with the example given in the release notes, the following error is thrown:

Error: (conn=15) No database selected

In order to get around this, you must append the database name to every table call. This is typically service@stage (ie vibely@prod) or default@default if not specified in the endpoint url. I believe that the simplest way to get around this would be to issue a USE database_name; statement on the connection before prisma attempts to execute the query against the connection.

To Reproduce Steps to reproduce the behavior:

1. Spin up a prisma server with rawAccess: true flag enabled
2. Point PRISMA_ENDPOINT env var to your server with appropriate service and stage
3. Run yarn prisma deploy to get the latest schema with executeRaw added
4. Go to playground
5. Issue command as specified in release notes or similar

mutation {
  executeRaw(
    query: "SELECT * FROM User LIMIT 1;"
  )
}

Expected behavior The query will execute against the same database used for regular prisma CRUD operations. Ie, the one specified in PRISMA_ENDPOINT.

Actual behavior The query throws an error saying

Error: (conn=15) No database selected

Screenshots N/A

Versions (please complete the following information):

• OS: OS X High Sierra
• prisma CLI: prisma/1.17.1 (darwin-x64) node-v8.12.0
• Prisma Server: 1.17.2

Additional context Adding the database name to every table name is especially annoying because the database name includes the @ character, which requires me to escape it with backticks. That in turn interferes with JS template strings. Also, database names are expected to vary according to environment, so this needs to be configured dynamically, which adds more needless pain and suffering.

[mcmar]

My suggestion was to issue USE database_name; on the database connection before issuing the query in executeRaw, but I guess that requires that you either: A) Don't share database connections between services/stages -or- B) Always specify the database for all your other commands.

I'm not aware of any way to to something similar to USE database_name; that's scoped to just a single query, but if possible, that would be the ideal.

[mcmar]

And in case anyone's wondering, I already tried doing this:

mutation {
  executeRaw(
    query: "USE `default@default`; SELECT * FROM User LIMIT 1;"
  )
}

and I get the following error:

Error: (conn=35) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT

It seems that executeRaw will only take a single statement.

[mcmar]

It looks like postgres always connects you to 1 and only 1 DB, so this may be a MySQL connector issue. Not sure about Prisma+Postgres yet. Please someone try with postgres and report your results back here!

[divideby0]

@mcmar I troubleshot this via Slack with @divyenduz today and found a few things:

You must enable raw SQL access

Raw SQL access is not enabled by default in a Prisma server. If you haven't already, you need to add rawAccess: true to your PRISMA_CONFIG. This is what my Postgres config looks like:

version: '3'
services:
  prisma:
    image: prismagraphql/prisma:1.18.1
    restart: always
    ports:
    - "4466:4466"
    environment:
      PRISMA_CONFIG: |
        port: 4466
        databases:
          default:
            connector: postgres
            host: postgres
            port: 5432
            # port: 3306
            user: prisma
            password: prisma
            migrations: true
            rawAccess: true
  postgres:
    image: postgres:10.5
    restart: always
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: prisma
      POSTGRES_PASSWORD: prisma
    volumes:
      - postgres:/var/lib/postgresql/data
volumes:
  postgres:

Postgres and MySQL have different default schemas

This is more for anyone else that encounters the issue with Postgres. The MySQL connector uses default@default as the schema, but Postgres uses default$default.

For certain tables, you have to wrap the table names in quotes

At least that was the case for Postgres. For my schema, I had to query my User table as default$default."User". This query executed successfully for my Postgres setup:

mutation {
  executeRaw(
    query: "SELECT * FROM default$default.\"User\""
  )
}

[mponizil]

Perhaps some of this is unnecessary, but for anyone trying to execute raw SQL from Prisma client, this is working for me:

const query = `
  SELECT * FROM "app-name$app-stage"."Table"
`.replace(/"/g, '\\"').replace(/\n/g, ' ').replace(/\s+/g, ' ')
const response = await ctx.prisma.$graphql(`
  mutation {
    executeRaw(query: "${query}")
  }
`)
console.log('rows', response.executeRaw)

[stale[bot]]

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.

[BePasquet]

I was having an issue with a prisma service hosted on heroku and i fixed with @mponizil comment thanks for that and also here is just in case someone need it mutation { executeRaw( query: "SELECT * FROM prisma$dev.\"User\"" ) } where endpoint is https://app-473d3e7bfb.herokuapp.com/prisma/dev

[cihadturhan]

There is also a database option which accepts a enum value: default but it never works.

mutation {
  executeRaw(query: "SELECT * FROM User", database: default)
}

It returns:

{
  "data": null,
  "errors": [
    {
      "locations": [
        {
          "line": 2,
          "column": 3
        }
      ],
      "path": [
        "executeRaw"
      ],
      "code": 1046,
      "message": "(conn=50) No database selected",
      "requestId": "local:cjuv2qnm3001o0a115ffi9rfg"
    }
  ]
}

[XYShaoKang]

I use mysql, this works for me. I hope to help those in need.

mutation {
  executeRaw(query: "SELECT * FROM `default@default`.`User`")
}

[wbmedia]

I use mysql, this works for me. I hope to help those in need.

mutation {
  executeRaw(query: "SELECT * FROM `default@default`.`User`")
}

@XYShaoKang thanks works perfectly with MySQl

[shalkam]

I think executeRaw executes one operation at a time. I've tried the following with graphql aliases :

mutation {
  selectDB: executeRaw(query: "use `prisma`")
  showTables: executeRaw(query: "show tables;")
  showProjects: executeRaw(query: "select * from Project")
}

and got this

{
  "data": {
    "selectDB": 0,
    "showTables": [
      {
        "TABLE_NAME": "CloudSecret"
      },
      {
        "TABLE_NAME": "InternalMigration"
      },
      {
        "TABLE_NAME": "Migration"
      },
      {
        "TABLE_NAME": "Project"
      },
      {
        "TABLE_NAME": "TelemetryInfo"
      }
    ],
    "showProjects": [
      {
        "id": "youtube-svc@dev",
        "secrets": "[]",
        "allowQueries": true,
        "allowMutations": true,
        "functions": "[]"
      }
    ]
  }
}

[gotexis]

@mponizil dear sir , may I ask what is the use of .replace(/"/g, '\\"').replace(/\n/g, ' ').replace(/\s+/g, ' ')

Thanks

[baluragala]

Does this work for mongodb as well?

[anton6]

I managed to get it to work when using flag "rawAccess: true" and then running USE as part of every executeRaw before performing SELECT, but it only works a couple of few times before I get an error "exception":"com.prisma.api.schema.APIErrors$ExecuteRawError: (conn=6) No database selected"

And also I noticed that the admin panel locks up with spinning loading indicator.

[anton6]

After switching to Postgres I no longer face the issue. Comments form @divideby0 and @mponizil helped to get it working. Currently, I am using Prisma 1.34.1.

[dhnm]

Does this work for mongodb as well?

"The MongoDB connector currently doesn't support raw access, so you need to set this to false or omit it."

• At the very end of this page: https://www.prisma.io/docs/releases-and-maintenance/features-in-preview/mongodb-b6o5/#prisma_config

Note that this has been the case for some time now and it most likely won't change in Prisma 1. Maybe Prisma 2?

(It's been a while since the question was asked, but I wasted too much time looking for an answer, so hopefully no one else has to :) )