Ent is a powerful Entity framework that helps developers write neat code that is translated into (possibly complex) database queries. As the usage of your application grows, it doesn’t take long until you stumble upon performance issues with your database. Troubleshooting database performance issues is notoriously hard, especially when you’re not equipped with the right tools.
The following example shows how Ent query code is translated into an SQL query.
Example 1 - ent code is translated to SQL query
Traditionally, it has been very difficult to correlate between poorly performing database queries and the application code that is generating them. Database performance analysis tools could help point out slow queries by analyzing database server logs, but how could they be traced back to the application?
Earlier this year, Google introduced Sqlcommenter. Sqlcommenter is
an open source library that addresses the gap between the ORM libraries and understanding database performance. Sqlcommenter gives application developers visibility into which application code is generating slow queries and maps application traces to database query plansIn other words, Sqlcommenter adds application context metadata to SQL queries. This information can then be used to provide meaningful insights. It does so by adding [SQL comments](https://en.wikipedia.org/wiki/SQL_syntax#Comments) to the query that carry metadata but are ignored by the database during query execution. For example, the following query contains a comment that carries metadata about the application that issued it (`users-mgr`), which controller and route triggered it (`users` and `user_rename`, respectively), and the database driver that was used (`ent:v0.9.1`):
To get a taste of how the analysis of metadata collected from Sqlcommenter metadata can help us better understand performance issues of our application, consider the following example: Google Cloud recently launched Cloud SQL Insights, a cloud-based SQL performance analysis product. In the image below, we see a screenshot from the Cloud SQL Insights Dashboard that shows that the HTTP route 'api/users' is causing many locks on the database. We can also see that this query got called 16,067 times in the last 6 hours.
Screenshot from Cloud SQL Insights Dashboard
This is the power of SQL tags - they provide you correlation between your application-level information and your Database monitors.
sqlcomment is an Ent driver that adds metadata to SQL queries using comments following the sqlcommenter specification. By wrapping an existing Ent driver with
sqlcomment, users can leverage any tool that supports the standard to triage query performance issues. Without further ado, let’s see
sqlcomment in action.
First, to install sqlcomment run:
sqlcomment is wrapping an underlying SQL driver, therefore, we need to open our SQL connection using ent’s
sql module, instead of Ent's popular helper
Make sure to import
entgo.io/ent/dialect/sql in the following snippet :::
Now, whenever we execute a query,
sqlcomment will suffix our SQL query with the tags we set up. If we were to run the following query:
Ent would output the following commented SQL query:
As you can see, Ent outputted an SQL query with a comment at the end, containing all the relevant information associated with that query.
In this post I showed how adding metadata to queries using SQL comments can help correlate between source code and database queries. Next, I introduced
sqlcomment - an Ent driver that adds SQL tags to all of your queries. Finally, I got to see
sqlcomment in action, by installing and configuring it with Ent. If you like the code and/or want to contribute - feel free to checkout the project on GitHub.
Have questions? Need help with getting started? Feel free to join our Slack channel.
- Subscribe to our Newsletter
- Follow us on Twitter
- Join us on #ent on the Gophers Slack
- Join us on the Ent Discord Server