Explain and analyze

[juliomuhlbauer]

Add explain and analyze options in the logger. It would be optional.
Related: drizzle-team/drizzle-orm#2498

[max-programming]

This is a very good addition but there are 3 roadblocks

1. The query logger runs before query execution
2. The query logger does not have access to the db
3. EXPLAIN is ok but running ANALYZE on every query will be a lot of overhead

Which makes me think this is not a good idea to implement it, expecially considering the 3rd point

[juliomuhlbauer]

Get it.

1. Yeah, it would need to run another query
2. We could declare the db client to use
3. It would be opt in (mostly when "analyzing" query performance)

I would find this very useful. Maybe we can build a user land helper.

[vladjerca]

The connection is created before the drizzle instance.

const connection = createConnection(...);

const db = drizzle(connection, { logger: false });

For explain/analyze one could format the logger options something like:

type DebugMode = 'explain' | 'analyze' | 'time';

type EnhancedQueryLoggerOptions = {
    log: (...data: unknown[]) => void;
    debug: {
        connection: null;
    } | {
        connection: Connection;
        modes: DebugMode[];
    }
}

export class EnhancedQueryLogger implements Logger {
  constructor(private readonly options = { log: console.log, debug: { connection: null } ) {}
}

I think the real nasty bit will be dealing with the myriad of driver variations 😜

[max-programming]

Yeah although this is possible that we can utilise the connection by passing it into the constructor. But there are many ways the user can use the drizzle initialiser

They can either make a separate connection like your code, or pass the connection string directly, now we can't have access to the connection or driver

// Make sure to install the 'pg' package 
import { drizzle } from 'drizzle-orm/node-postgres';

const db = drizzle(process.env.DATABASE_URL);
 
const result = await db.execute('select 1');

Or in cases like better-sqlite3, the new Database() instance is made, but the db is not connected unless the asynchronous connect() method is called

[max-programming]

Also one more thing came to my thought.
What we have is a query logger as opposed to a query result logger

And talking about EXPLAIN and ANALYSE, one needs to look into their result or output, whereas we only log the query

I think the explain analyse stuff needs to be a "drizzle plugin" or better yet, a way we can define custom hooks to Drizzle to run those explain and analyse queries (I don't think drizzle has hooks atm)

Also if we run "all" queries with EXPLAIN ANALYZE, they'll run twice. So a select query, insert query etc. there will be 2 selects and 2 inserts (one by the user, one by the logger). Although this won't happen if we remove ANALYZE. So it'll be better if this was more ingrained inside Drizzle when any operation like .select(), .insert() is called, so that explain analyze can be prepended to existing queries rather than running separate queries