Prisma1: Log the generated/executed SQL queries

[divyenduz]

Is your feature request related to a problem? Please describe.
Currently, it is not possible to see the logs for SQL queries generated/executed against executed GraphQL queries.

Describe the solution you'd like
There should be a way to log the generated SQL queries. Maybe append them to the Prisma docker logs, probably behind a debug flag.

Describe alternatives you've considered
It is possible to get the executing/executed queries from DB.

Additional context
It will help debugging issues much easier.

[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.

[AndrewRayCode]

"This issue has been automatically marked as stale because it has not had recent activity."

Yes please

Im getting errors from psotgres and have no idea why since I can't see the query

[pbassut]

This is not stale. People aren't just commenting on it.
This is something I would really like to see.
Since prisma doesn't have a very good documentation I basically need to understand what query is it building in order to write the code correctly that calls the prisma API.

I'm trying to understand how _every, _some, _none works

[nambuntu]

Up vote for this!

[meebix]

This would be very helpful in development.

[jhalborg]

@divyenduz - Any reason this feature request is so old? It seems like to me to be a very simple task that provides tremendous value to Prisma users in development and debugging, and it doesn't require much speccing (as the tag suggests) besides what you wrote yourself in the original description.

What should hinder the team in implementing this with the next release?

[divyenduz]

This feature has been available since Prisma 1.30.x and can be used with the following environment variable:

Key
LOG_LEVEL

Values:
INFO -> default logging
DEBUG -> more detailed logging, emits SQL
TRACE -> logs everything

Please let me know if this works for you.

[jhalborg]

@divyenduz Not for me.

I tried LOG_LEVEL at both DEBUG and TRACE - and even though I get DEBUG and TRACE tagged output in the console, no SQL queries are shown.

prisma.yml (note that I added the var both under the root environment as well as the PRISMA_CONFIG, not sure where it was supposed to go and there seems to be no docs on it:

version: '3'
services:
  prisma:
    image: prismagraphql/prisma:1.30.1
    restart: always
    ports:
    - "4466:4466"
    environment:
      LOG_LEVEL: TRACE # Log SQL queries
      PRISMA_CONFIG: |
        port: 4466
        # uncomment the next line and provide the env var PRISMA_MANAGEMENT_API_SECRET=my-secret to activate cluster security
        managementApiSecret: my-secret
        LOG_LEVEL: TRACE # Log SQL queries
        databases:
          default:
            connector: mysql
            host: mysql
            port: 3306
            user: root
            password: prisma
            migrations: true
            rawAccess: true
  mysql:
    image: mysql:5.7
    restart: always
    ports:
    - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: prisma
    volumes:
      - mysql:/var/lib/mysql
volumes:
  mysql:

Output from a query

prisma_1  | [DEBUG] resuming
prisma_1  | [TRACE][Jwt-rs] Verify - handing out: 0x7f79c0422750
prisma_1  | [TRACE][Jwt-rs] Dropping buffer at: 0x7f79c0422750
prisma_1  | [DEBUG] {} - configuration:
prisma_1  | [DEBUG] allowPoolSuspension.............false
prisma_1  | [DEBUG] autoCommit......................true
prisma_1  | [DEBUG] catalog.........................null
prisma_1  | [DEBUG] connectionInitSql..............."set names utf8mb4;"
prisma_1  | [DEBUG] connectionTestQuery.............null
prisma_1  | [DEBUG] connectionTimeout...............5000
prisma_1  | [DEBUG] dataSource......................null
prisma_1  | [DEBUG] dataSourceClassName............."slick.jdbc.DriverDataSource"
prisma_1  | [DEBUG] dataSourceJNDI..................null
prisma_1  | [DEBUG] dataSourceProperties............{user=root, url=jdbc:mysql://mysql:3306/?autoReconnect=true&useSSL=false&requireSSL=false&serverTimeZone=UTC&useUnicode=true&characterEncoding=UTF-8&socketTimeout=60000&usePipelineAuth=false&cachePrepStmts=true, password=<masked>}
prisma_1  | [DEBUG] driverClassName.................null
prisma_1  | [DEBUG] healthCheckProperties...........{}
prisma_1  | [DEBUG] healthCheckRegistry.............null
prisma_1  | [DEBUG] idleTimeout.....................600000
prisma_1  | [DEBUG] initializationFailFast..........false
prisma_1  | [DEBUG] isolateInternalQueries..........false
prisma_1  | [DEBUG] jdbc4ConnectionTest.............false
prisma_1  | [DEBUG] jdbcUrl.........................null
prisma_1  | [DEBUG] leakDetectionThreshold..........0
prisma_1  | [DEBUG] maxLifetime.....................1800000
prisma_1  | [DEBUG] maximumPoolSize.................9
prisma_1  | [DEBUG] metricRegistry..................null
prisma_1  | [DEBUG] metricsTrackerFactory...........null
prisma_1  | [DEBUG] minimumIdle.....................9
prisma_1  | [DEBUG] password........................<masked>
prisma_1  | [DEBUG] poolName........................"database"
prisma_1  | [DEBUG] readOnly........................false
prisma_1  | [DEBUG] registerMbeans..................false
prisma_1  | [DEBUG] scheduledExecutorService........null
prisma_1  | [DEBUG] threadFactory...................null
prisma_1  | [DEBUG] transactionIsolation............null
prisma_1  | [DEBUG] username........................null
prisma_1  | [DEBUG] validationTimeout...............1000
prisma_1  | [INFO] {} - Started.
prisma_1  | [DEBUG] {} - {}stats (total={}, active={}, idle={}, waiting={})
prisma_1  | [DEBUG] {} - Added connection {}
prisma_1  | [DEBUG] {} - Added connection {}
prisma_1  | [DEBUG] {} - Added connection {}
prisma_1  | [DEBUG] {} - Added connection {}
prisma_1  | [DEBUG] {} - Added connection {}
prisma_1  | [DEBUG] {} - Added connection {}
prisma_1  | [DEBUG] {} - Added connection {}
prisma_1  | [DEBUG] {} - Added connection {}
prisma_1  | [DEBUG] {} - Added connection {}
prisma_1  | [DEBUG] {} - {}stats (total={}, active={}, idle={}, waiting={})
prisma_1  | [DEBUG] #1: SimpleJdbcAction
prisma_1  | [TRACE][Jwt-rs] Verify - handing out: 0x7f7954138f20
prisma_1  | [TRACE][Jwt-rs] Dropping buffer at: 0x7f7954138f20
prisma_1  | [DEBUG] #1: SimpleJdbcAction
prisma_1  | [DEBUG] #1: SimpleJdbcAction
prisma_1  | [DEBUG] #1: SimpleJdbcAction
prisma_1  | [DEBUG] #1: SimpleJdbcAction
prisma_1  | [DEBUG] #1: SimpleJdbcAction
prisma_1  | [DEBUG] #1: SimpleJdbcAction
prisma_1  | [DEBUG] #1: SimpleJdbcAction
prisma_1  | [DEBUG] {} - {}stats (total={}, active={}, idle={}, waiting={})
prisma_1  | [DEBUG] #1: SimpleJdbcAction
prisma_1  | [DEBUG] pausing
prisma_1  | [DEBUG] resuming
prisma_1  | [DEBUG] {} - {}stats (total={}, active={}, idle={}, waiting={})
prisma_1  | [DEBUG] {} - {}stats (total={}, active={}, idle={}, waiting={})
prisma_1  | [DEBUG] #1: SimpleJdbcAction
prisma_1  | [DEBUG] pausing
prisma_1  | [DEBUG] resuming

[bkstorm]

me too. LOG_LEVEL: TRACE doesn't show any sql queries. Here is my console log:

prisma_1    | [DEBUG] resuming
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] pausing
prisma_1    | [DEBUG] resuming
prisma_1    | [DEBUG] {} - {}stats (total={}, active={}, idle={}, waiting={})
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] pausing
prisma_1    | [DEBUG] resuming
prisma_1    | [DEBUG] {} - {}stats (total={}, active={}, idle={}, waiting={})
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction
prisma_1    | [DEBUG] #1: SimpleJdbcAction

[paulrostorp]

I confirm that this does not work on v1.32

[toriaezunama]

Doesn't work for Prisma CLI version: prisma/1.32.0 (darwin-x64) node-v10.4.
Only prints SQL statements for the initial creation of the tables in the prisma database

[jhalborg]

Friendly ping @divyenduz 😄

[peterrogov]

Any progress here? I'd love to see SQL queries too

[joshhopkins]

I think this would be my number one request at this point. Suggested something similar here #3936.

[peterrogov]

As a workaround I switched prisma to a local MySQL installation (instead of docker container) and switched on general log on MySQL Server where it logs all incoming SQL queries. Lets me see prisma's queries.

[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.

[jhalborg]

Go away stale bot 😅

[tedb19]

Any word on this? I love the LOG_LEVEL idea, except it doesn't work...

[dvelitchkov]

Really curious - how do Prisma devs themselves know whether the generated queries are good or not if there's no way to see them?

[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.

[joshhopkins]

This seems like low-hanging fruit. No fix here @divyenduz?

[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.

[jugaltheshah]

Not stale. +1.

[DanielNetzeriAm]

Definitly not stale. seems like a basic feature required.

[juliendangers]

Really curious - how do Prisma devs themselves know whether the generated queries are good or not if there's no way to see them?

I really wonder. I worked on SQL query optimization a few days ago, and I was not able to find any test relating to generated queries (may be I did not look in the right place), nor a simple log. I went through with the debugger, but I just created a PR to add the log (trace level). Query's parameters are not in the output, but it gives a pretty good idea of what the query looks like.

Let's see if it'll be merged, since Prisma v1 project is quite dead since the upcoming of v2.

[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.

[tousdan]

While this is a bit more inconvenient than adding a small flag in the env/config here's a workaround i've been using (these are concrete steps to enable general log, as previously suggested by another commenter):

(find the docker container name) docker ps
(get into the container) docker exec -it container name bash
(connect to the mysql instance, u and p might be different) mysql -uroot -pprisma

(figure the path where the general log is) show variables like 'general_log%';
(exit the mysql prompt) exit;

then tail the log file that was shown in the general_log_file, for me it was:
tail -f /var/lib/mysql/83b148d33e0e.log

when you're done, open up mysql and turn the general log off.

[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.