Maybe you guys should look at this code which is nothing more than an exercise in using SQLite database
https://rhaalovely.net/pkg_mgr/
Yes, I'm sure such thing could be implemented as bits of SQL queries that interface directly with the pkg SQLite database. However, that goes against the design principles of pkg where one of them is that nobody should rely on the internal implementation of the database, it can change at any time without any prior warning. How it should be done is by extending the pkg-query(8) mechanism to answer questions like "what kind of changes would
pkg install foobar
cause if executed".