Databases are the backbone to any and all full stack projects and ensuring that a project’s database is optimized for efficiency will ensure that the project can access necessary data quickly and scale accordingly. There are a multitude of factors when first organizing a database and a mindful developer would test runtime efficiencies of potential database schemas and query designs when doing so.

Here, SeeQR comes into play. SeeQR is an open source application for testing SQL database designs for efficiency and scalability by providing developers with data driven metrics on their queries. SeeQR allows developers to determine query efficiency against a targeted or multitude of SQL databases, modify existing schemas and queries, and generate dummy data to investigate a database’s potential for scalability. SeeQR 6.0 continues this trend of improving SQL database design by including updates that allow users to persist vital query information over multiple sessions and grouping executed queries for better visual organization.

Getting Started

Upon launching SeeQR, the application automatically connects to the developer’s local instance of PostgreSQL under the role ‘postgres.’ Thus all databases that ‘postgres’ has access to will also be available to SeeQR. In addition to existing databases, the developer may also create additional databases by importing .sql and/or .tar files through clicking the plus icon. These databases will be available to the developer for testing in SeeQR.

Importing and Copying a Database

Databases

The developer also has the option to manually create SQL tables and schemas by following the below three steps:

1. Click “CREATE NEW DATABASE”;
2. Enter a “Database Name” and click “INITIALIZE DATABASE” to create the database; and
3. Enter commands to create tables and schemas within the block and click “UPDATE DATABASE.”

Manually Creating a Database

The new database and its new table(s) will then populate on the sidebar under the “DATABASES” view. Developers can also make edits to existing databases by following the three steps below:

1. Click “CREATE NEW DATABASE”;
2. Clicking the database to be edited on the left; and
3. Enter any commands to update and/or tables and schemas within the block and click “UPDATE DATABASE.”

Databases can also be exported by clicking on the “EXPORT” button, which will save the database as a .sql file on the developer’s desktop. Existing databases can be copied by clicking the copy icon. Clicking on a database will open the database, and allow the developer to view the database’s schema. Developers can then generate dummy data for any table by clicking the “GENERATE DUMMY DATA” button and entering the amount of rows to be generated.

Populating Dummy Data

Queries

Clicking on the “QUERIES” view will bring the developer to the query section. Here metrics on queries can be made by following the below four steps:

1. Clicking the plus icon;
2. Enter a “label” for the individual query and a “group” for the comparison group the query belongs to;
3. Select the database from the Database dropdown to run the query from; and
4. Enter the query within the block and click “RUN QUERY.”

Executed queries will then populate on the sidebar, under the banner of their respective groups. Clicking on the individual query will open the query, and allow the developer to view the results of that query. Clicking on the “EXECUTION PLAN” button allows developers to see a tree of the query’s execution plan.

Query ExecutionsQuery Execution Plan

The above four steps can be repeated as many times as necessary. Comparing the efficiency of the queries can be made by clicking the charts icon and then clicking the empty box icon of all queries to be compared. Here a visual comparison of each query’s speed will be displayed, along with a table containing metrics on each individual query.

Comparing Queries

In this update, SeeQR introduces the ability to locally persist executed queries. Users can now save executed queries for comparisons over multiple sessions. To enjoy this new feature, please follow the below three steps:

1. Click on the save file path icon, this will open a dialog window prompting the developer to designate a local save location;
2. Click on the save icon of each individual query the developer wishes to save; and
3. When the developer wishes to compare the saved queries, click on the upload icon to load the previously saved queries.

Saving and Importing Queries

Summary

SeeQR is a fantastic application for developers who want to build the best possible SQL database for their project by allowing developers to compare different schemas and queries allowing them to determine the best efficiency optimizations that will provide long term results.

For more information, to download the application and/or to contribute, please visit us at github or seeqr.com.