Is there a way that I can access the statement information of a query through in extension as in knex with const { sql, bindings } = queryBuilder.toSQL(); ? I want to check them to communicate with the performance schemas of my db engine.
#Access to statement information
rain moat
trail pelicanBOT
Thanks for posting! This is a community powered server, so you may or may not get an answer based on available help and expertise. To increase your chances of somebody being able to help you, please help us help you making sure you:
- Adding an explanation of exactly what you're trying to achieve.
- Adding any and all related code or previous attempts.
- Describing the exact issue or error you are facing.
- Posting any screenshots if applicable.
- Reading through https://stackoverflow.com/help/how-to-ask.
When you're done with this thread, please close it. Thanks! ✨
(If you have a support agreement and need help, please contact the core team via email.)
lime sage
Is there a way that I can access the statement information of a query through in...
not quite sure how you mean that? you can do that if you're building a knex query sure 🤷♂️ if you want the sql queries done by directus itself you can set LOG_LEVEL=trace that should log all sql queries done.
lime sage
docker does keep a cache of the logs but no they're not written to a file unless you do so
rain moat
how can I catch them? is there a method? I want to find a way to get the statement and query information about each request in my system, something like readByQuery().info(), because I'm building a load balancer with many different processes running at once, so I can have a better understanding of each of those processes
lime sage
You dont catch them, they get logged
Matching those up automatically would be quite difficult i understand, dont think there is an easy way of doing that. logging trace i generally used to debug specific queries manually 🤔
rain moat
Hmmm I need something more automatic because I'm using it to manage a bunch of computers doing a lot of different tasks of data extraction and processing and they change dynamically so a more automatic approach would be nicer. I think I will stick with storing the log with some library and try to infer what's the related statement.
is there a way to not log anything from the app and only from the extensions?
rain moat
If anyone has this question here's how to
On any extension endpoint, hook or other
// Your imports
export default (router, {
services,
getSchema,
// logger is Pino instance
logger,
}) => {
// Some simple custom route
router.get('/get-active-processes', async(req, res, next) => {
const { accountability } = req
const { user:user_id, admin } = accountability
if(!admin) return res.json(false)
try {
const messages = [];
// Selects which log level to capture
const originalLogFn = logger.trace;
// From here starts getting messages from the logger
logger.trace = (message, ...args) => {
const timestamp = new Date().toISOString();
messages.push({
timestamp,
message,
args,
meta: extractLogMetadata(message),
});
originalLogFn.call(logger, message, ...args);
};
const result = res.json(await get_active_processes({
getSchema,
services,
accountability,
}));
// Here stops getting messages from the logger
console.log('Captured log messages:', messages);
return result
} catch (error) {
console.log('error', error)
}
})
};
A helper function to extract the logs metadata
const extractLogMetadata = (message) => {
const regex = /^\[(.*?)\]/;
const match = message.match(regex);
if (match && match.length > 1) {
const executionTime = match[1];
const timeRegex = /(\d+(?:\.\d+)?)(\w+)/;
const timeMatch = executionTime.match(timeRegex);
let query = null;
if (timeMatch && timeMatch.length > 2) {
const timeNumber = parseFloat(timeMatch[1]);
const timeUnit = timeMatch[2];
// Extract the query from the log message
const queryRegex = /]\s+(.*)$/;
const queryMatch = message.match(queryRegex);
if (queryMatch && queryMatch.length > 1) {
query = queryMatch[1];
}
return {
query,
executionTime,
timeUnit,
timeNumber,
};
}
return {
query,
executionTime,
timeUnit: null,
timeNumber: null,
};
}
return null;
};
So i was playing around with a couple ideas i had and it looks like you can do something like this too 🤓
export default function registerHook({action}, { database: knex, getSchema, services }) {
const { ItemsService } = services;
action('server.start', async () => {
knex.on('query', function(data) {
console.log('query', data.sql);
})
const items = new ItemsService('test', { knex, schema: await getSchema() })
console.log('output', await items.readByQuery({limit:1}));
});
}
in my tests this only catches the queries for your specific extension (or the services using that knex instance to be specific)
rain moat
So i was playing around with a couple ideas i had and it looks like you can do s...
Woah that's also handy and even simpler to hook, I can see that pattern's usage for other kind of things. Thank you! 
rain moat
So i was playing around with a couple ideas i had and it looks like you can do s...
Ok, so I tested both methods extensively and the hook is waay better, it's only required to define the queryContext to the queries involved in any ItemsService method (with like a shadow query that point the beggining of the connection, end and any marking you want to make in between) and the knex connection has a unique id (__knexUid) which works great and is binded to the instance of that connection, so you can capture either only what's involved in that connection or everything happening until that connection is resolved. It's cool because from an ACID standpoint it makes sense to check any locks or other things that could generate some overhead or locks, and it can get huge if your are tracking everything so also memory management is important because it can generate leaks if something is not closed or cleaned. But you still can mark the beggining and end and capture all the queries with more precision than the log (where I don't have much control about whats logged). It is required to build some memory for all the processes that are being tracked, but it is achievable.
And the pattern looks the same as the previous one.
Where you want to track something
const get_active_processes_querySignal = querySignal(database, {
id: 'get-active-processes',
uid: 'asd-asd-test-test',
level: 'local'
})
await get_active_processes_querySignal.start()
const result = {
data: null,
meta: {},
}
try {
result.data = await get_active_processes({
// ...params
});
} catch (error) {
// error handling
}
// some marking, here doesn't make sense but just to try it out
await get_active_processes_querySignal.mark({
id: 'get-active-processes_subprocess',
uid: 'asd-asd-test-test-sub-sub',
})
await get_active_processes_querySignal.complete()
The start is something like this
// Some lightweight query
const query = knex.select(1)
const queryContext = {
id,
uid,
ttl,
pointer: 'start',
start_timestamp,
current_timestamp,
end_timestamp,
start_ns,
ms,
meta,
}
query.queryContext(queryContext)
const response = await query
const fullQueryContext = query.queryContext()
return fullQueryContext