Unleash AI in the Database!
In 2021, I created an extension for Optimizely CMS that allows you to run SQL queries directly against the database from the user interface. This can be handy if you're using a cloud solution (or DXP) that does not allow direct database access, or if you simply want access to the database without using software like SQL Management Studio.
One use case could be the ability to retrieve specific data, without having to spend time customizing a report in the report center or writing code that needs to be tested and deployed.
If one doesn't have a complete grasp of the database and SQL syntax, it can be time-consuming to handwrite the perfect SQL query. But fear not – AI can assist us!
Here's an example where I write two lines (in green text) explaining in plain Norwegian what type of report I want. A click on "Generate query" will produce an SQL query that I then execute to generate the report. If you run the same report regularly, it's also possible to save it for later execution with just three clicks.
English translation of Norwegian prompt. "Give me a list of content, of type blogpost, written in Norwegian, in the afternoon. Present only the page name, url and the date it's published. Order alphabetical by title."
With full access to the database, one obviously has the potential to cause harm, but with this extension, there are numerous ways to limit the risk. Details are in the documentation.
- Make the extension available to selected users or groups.
- Specify a separate connection string that provides read-only access.
- Set a regex for validating allowed SQL.
- Set a regex for validating denied SQL.
- And if you think AI is daunting, the AI feature can, of course, be deactivated.
Good luck – with or without AI!