Low code data modeling with QuickSQL

QuickSQL to generate sample data

As easy as it is to create a feature-rich application in mere hours using Oracle APEX, if you’ve got a demo to run, you’re gonna want some data in those tables. Even more so if you’ll be wanting to show off cool dashboards with Oracle JET visualizations.

So friends, get ready to start typing!

OR, you could leverage one of my favorite features of QuickSQL, the ability to generate sample data.

Simply add the /insert XX directive to your tables to add as many sample records to your table as you’d like (up to 1000 records).

If you’ve added a check constraint to any of your columns (example: /check Yes, No), your sample data will use those values.

Want to force a value in your sample data? For example, only use NY as a city code? Use the /constant NY column directive.

QuickSQL to iterate through model changes with the ‘Include Drops’ option

In the early stages of your project, you’re likely still working on your final model.

1:MANY relationships might change to MANY:MANY, columns names might changes, data types, etc.

Toggle ‘Include DROPS’ (Quick SQL settings) to “on” to include drop statements for all objects you create. Run and tweak as many times as you want until you’re happy with your model.

QuickSQL to automatically generate table APIs and REST-enable your tables

If this bit isn’t the bees’ knees, I don’t know what is.

By simply adding /api after your table names, QuickSQL will create an entire package with GET_ROW, INSERT_ROW, UPDATE_ROW and DELETE_ROW procedures.

Add a little /rest command in there too, to rest-enable that table, and boom! You’ve got the world’s lowest code REST API ready to go. (No fact-checking on that claim, please).

Note: if you want to add an API for all tables in the model, you can simply use the general setting PL/SQL API. Toggle that to ON to generate an API for each table. If set to OFF, QuickSQL will only create an API for tables where you specifically use the /api notation.

QuickSQL to easily add comments to your columns and tables

Not all your column names will be as obvious as FIRST_NAME or LAST_NAME.

Does PRICE include tax? Is it always in a specific currency? How about that aptly named column: IND_NO_FLS_MRK? You might know exactly what it’s used for, but I can pretty much guarantee that other developers coming in later won’t have a clue (speaking from experience, here).

That’s where table and column comments come in, and QuickSQL makes this super easy.

Simply add your comments inline with the column name using the — notation, and see this translate into a series of  ‘comment on column’ statements.

Anton says he’s even created a QuickSQL model for existing tables just to generate the comment commands and thrown away the rest.

QuickSQL to enforce integrity

A robust data model is at the heart of any successful application. Get your model wrong, and you, your clients, and your users will likely be paying the price for years to come.

And while it’s unlikely you’ll forget to add your foreign key constraints altogether, if you use QuickSQL, it will add them for you automatically.

When you indent tables under each other, QuickSQL will infer the relationship between them and add in foreign keys automatically. As a bonus, it will also create indexes on the FK columns.

Note the alternate notation to generate foreign keys below.

By using product id, customer id, promotion id in the sales table below, the generated SQL includes FK constraints (with cascade delete) to the appropriate tables.

Have another preference for how to handle deletes? The settings panel offers Cascade, Restrict and Set Null options.

QuickSQL to learn SQL and DDL syntax

What’s the syntax I need to capture the username in my auditing columns? The one that considers whether I am working in an APEX session?

coalesce(sys_context('APEX$SESSION','APP_USER'),user)

How about enforcing check constraints on a column? Foreign keys? DDL syntax to create tables, triggers or even packages? Simply use QuickSQL’s shorthand notation with built-in help in the left-hand panel, click on the Generate SQL button, to see your DDL come to life.

One thing I particularly love is QuickSQL’s built-in sample models. Click the LOAD button to access a number of shorthand notations for a variety of different models that you can use yourself, or learn from as you familiarize yourself with the shorthand notation.

By the way, to get that username syntax in your own model, simply choose APEX Enabled and Auditing columns in your Settings tab.

QuickSQL to enforce standards

When you have a team of developers, you want to make sure your standards are well communicated and understood by all.

Do you like to use identity columns, SYS_GUID or sequences for your primary keys?

Are date fields TIMESTAMP with TIMEZONE, TIMESTAMP with LOCAL TIMEZONE (Lookin’ at you, Anton..)?

Should your primary keys be prefixed with the table name? How do you like to name your audit columns?

QuickSQL lets you configure your preferences under the Settings tab, which you can then save as a snippet to include in future data modeling projects, or even better, publish for your team.

Check out the Insum snippet below, with some of our favorite (standard) QuickSQL settings

# settings = { prefix: "insum", onDelete: "RESTRICT", date: "TIMESTAMP WITH LOCAL TIME ZONE", semantics: "CHAR", auditCols: true, tenantID: true, longVC: true, language: "EN", APEX: true }

Team members can simply copy/paste this snippet into the left hand window of QuickSQL to apply our standard settings.

You’ll find your own settings ‘snippet’ at the bottom of the generated SQL code.

See QuickSQL in action

Want to see how easy QuickSQL is for yourself?

Grab the 38 lines of code I mentioned at the start here, paste it into your own QuickSQL, and watch it generate over 2500 lines of SQL. Magic!

And for a couple of other demos, check out episode #6 of APEX Instant Tips, where Oracle APEX team member Neil Fernandez gave us a tour. Or you might also want to check out our Insum Insider episode, where Anton built an app in 45 minutes, using QuickSQL for data modelling.

As a side note, we track and review Data Modelling as part of our Insum InTrack review process. We discussed this process and some of the key elements of a successful Oracle APEX project in this post here.