Using The SQL Query Tool

Using The SQL Query Tool

As of version, mojoPortal now includes a simple SQL Query Tool that developers can use for ad hoc queries against the database. It is disabled by default because it is a very dangerous tool in the wrong hands. It is possible to delete site data and completely break a site and it lets the user see any data in the database that they know how to query for. If you are not a SQL expert, it is highly recommend that you leave this tool disabled for your own protection from data lost. It would be wise to backup your database each time before you use this tool. That said, it can be very useful for a variety of purposes including rapid development of small features.

To enable it, you need to add this to your user.config file:

<add key="EnableDeveloperMenuInAdminMenu" value="true" />
<add key="EnableQueryTool" value="true" />

Even with these settings, it can only be used by a user in the Admins role of a root site, i.e. it is not available in child sites of a multi site installation at all. The data for all sites is stored in the database so a user with access to this tool can see the data for any of the sites.

Once enabled, a link for it can be found at Administration Menu > Advanced Tools > Developer Tools.

screen shot of SQL Query Tool

It has a list of tables in the database and can generate a simple statement for you with the click of a button. Queries can be saved and re-loaded later. Queries can also be retrieved by name and used for simple data access in small custom features as we will see in another article where I will show how to populate a chart from a saved query. However this approach is really not what I recommend for complex features, it is just very convenient for rapid development of small features.