What is SQL Management
We have all seen SQL clients. GUI clients with editors where we can run our SQL queries. They help you list all the tables, triggers, procedures and run queries and show resulting data. But they don't help you manage your queries.
Query management goes beyond that and allows your to manage your SQL queries within an application. What is the need of such management. Well, as the application code grows we need to consistently maintain the SQL inside the application code. There is a lot of application code dependent on the SQL. SQL forms the lower most layer (DAO Layer) and is responsible for communicating with the services and mappers.
DAO Pattern
One of the advantages of DAO pattern is that it allows developers to reuse the queries into multiple services. The DAO layer isolates database access from service logic.
A good application design suggests to store the SQL in an external file or database, where they can be managed. This is a form of SQL management. A properties file or a set of files to store all SQL queries or a database to store the SQL. This allows efficient debugging, and easy maintenance of the SQL over time. The DAO layer here can be used to access these SQL managed externally.
Advantages of externalized SQL
- Reusable SQL: The SQL can be referenced at multiple instances and can be modified at once place.
- No application build required: SQL modification does not require application build. Application builds can take time and slow down the hotfix. Server needs to be put down until the build process is completed.
- No Testing Required: Application build needs to be tested again under separate environment if sql resides inside the application code. There are cases where you need to perform tests, no doubt about that. But here the idea is that you can test your query in your favorite SQL editor and go ahead if the fix is needed.
- Accessible to DBAs: DBAs don't need to scan your source code or the application developer doesn't need to dig into the code to copy paste the query to the DBA. Imagine those concatenated strings of SQL that need to be sanitized before handing it over the DBA.
Beyond external SQL
Externalizing the SQL is the start of this revolution. Beyond the externalization, SQL management is storing metadata information about these queries. Managing SQL needs to consider the needs of the DBA, on the standpoint of maintainability.
Let us try to understands the needs:
- Identifier: And ID or name unique to the SQL to be referenced inside the application code
- Description: The purpose the SQL. Just like application code has comments in code. This will help maintainability.
- Correctness: The query must be correct when stored for future reference.
- Reference: Information about the resources referring the SQL. This helps identify orphan SQL statements lying in bucket.
- Result Information: This may be not be an essential information but handy when we want to testing. Information like number of records returned.
- Benchmark: Time to execute the SQL. This is handy information when we change the SQL under the same identifier. The SQL performance can deteriorate over time with increasing data or changes in SQL query. This data is helpful in such cases.
- Grouping: SQL can be tagged to a logical group for future use cases.
- Test Data: Managing the test data along with the SQL. The queries are parameterized when stored and do not hold all the values needed for the SQL to run. Maintaining test data along side can help testing and debugging.
In the following blog we will cover how Metamug API Console helps developers manage SQL. Reusing SQL Queries in multiple resources
SQL Catalog
Metamug API Console allows you to do manage SQL in a catalog. The catalog numbers all the SQL declared in a backend. You can check the catalog and use the SQL reference number. The SQL management catalog maintains all the SQL with the qualifications mentioned above. The test data is stored along with the SQL.
SQL Catalog connects to the editor to run the sql if needed.
Example
You can find the example in SQL catalog docs
