Quick SQL - Enhancements and Generative AI Modelling

Oracle APEX has long been celebrated for its rapid application development capabilities, empowering developers to build robust web applications with minimal code. At the heart of this agility lies Quick SQL, a powerful utility that transforms shorthand syntax into fully-fledged SQL DDL. With the release of Oracle APEX 23.2, Quick SQL received significant enhancements that streamline data modelling even further. And now, in 24.2, the integration of AI-driven data modelling is set to revolutionise how we design our database schemas.

In this blog, we will discover how AI is taking data modelling to the next level, outputting directly into Quick SQL for unparalleled speed and efficiency, as well as exploring some of the enhancements to Quick SQL introduced in 23.2.

AI-driven data modelling

A new addition to Oracle APEX (APEX 24.2) introduced a groundbreaking feature: AI-driven data modelling. This allows you to describe your application's data requirements in natural language, and AI generates the corresponding data model, with the option to output it directly as Quick SQL. Let's take a look.

• Prerequisite: Ensure you have a Generative AI Service configured in your APEX Workspace (Workspace Utilities > Generative AI). This is typically an OCI Generative AI Service or another compatible AI provider.

Now, if we check in the SQL Workshop Utilities, "Create Data Model Using AI" should become available.

Selecting "Create Data Model Using AI" will launch the APEX Assistant in a modal window. The assistant asks us to describe the elements we want our data model to include. There are also some samples available to get you started.

The first thing I am going to do, however, is to switch over to Quick SQL mode. This will generate my data model in Quick SQL. AI-generated code, while powerful, should always be reviewed and validated. Quick SQL acts as an excellent intermediate, simplified representation of the data model, making that review and subsequent adjustments far more efficient. It allows you to focus on the logical structure of the data model rather than the intricate syntax of DDL. This format will also come in handy for showing off the Quick SQL enhancements later, but there is also the option to generate your data model in standard Oracle SQL.

I will keep my data model description vague to see how intuitive the AI assistant is.

After a moment to think, the assistant soon had my Quick SQL script generated. It has also offered some advice on further enhancements. It is very simple to continue using natural language to interact with the APEX Assistant, building on the output until you are happy with the result. Here is an example where I have asked for another column to be included.

The data model is accurately updated with my request, along with an explanation of the changes made and further advice for improvements. Clicking "Review Quick SQL" launches the Quick SQL utility with our AI-generated data model in place.

Quick SQL Enhancements

Before version 23.2 of APEX, building with Quick SQL was a multi-step process. You would need to write some Quick SQL, then click generate SQL to see your script output. Then make any changes required, generate the entire output again, repeating this process until you were happy with the result. Since 23.2, APEX has introduced instantaneous SQL generation. You can see the exact SQL being generated in real-time, allowing for immediate adjustments and fine-tuning.

In the example below, I am adding an extra table to my Quick SQL, and the results are reflected in the output almost instantly.

Another Quick SQL utility enhancement is the Entity Relationship Diagram (ERD) creator. Previously, after generating your SQL, you would have to visualise the relationships in your head or use external tools. Now, by switching to the "Diagram" tab, a live ERD dynamically updates as you type your Quick SQL shorthand.

This visual representation is invaluable for:

• Understanding Relationships: Clearly see how your tables are linked, making it easier to identify missing foreign keys or incorrect relationships.
  
  
• Catching Errors Early: Spot logical flaws in your data model before you even run the SQL.
  
  
• Collaboration: A picture is worth a thousand lines of code, and an ERD provides a clear, shared understanding of the database structure for team members.

The ERD also updates instantaneously, reflecting the changes to tables, columns, relationships, and any other variations you are making to your Quick SQL. Here is the same change I made earlier, but this time visualised in the diagram tab:

The new table and relationship are seamlessly incorporated into the diagram.

I couldn't find a way to save or download a copy of the diagram other than screenshotting it. Only a minor criticism, but maybe a nice simple addition we could see in future releases. Lastly, I can review and run my generated script, and my database objects have been created!

Conclusion

Oracle APEX continues to push the boundaries of rapid application development. The enhancements to Quick SQL, particularly the live Entity Relationship Diagram and instantaneous SQL generation, have made data modelling more intuitive and efficient. The recent addition of AI-driven data modelling with its ability to output directly into Quick SQL takes this a step further, allowing developers to go from an idea expressed in natural language to a working database schema in record time.

For more information, check out our Oracle APEX Services, or contact us today, and one of our expert developers will be in touch.