29Mar
By: Michelle Skamene On: March 29, 2022 In: APEX Basics, Oracle APEX Comments: 3

How many lines of code would you need to create a data model with 5 tables, complete with constraints (PK, FK, UK), indexes, auditing columns and triggers, sample data, table apis, and views to query it all? And what if you wanted to REST-enable your tables, too?

If you answered about 2500 lines of code, you’d be right.

What if I told you that you could generate all 2500 of those lines of your data model with a mere.. 38 lines of code?

That, my friends, is the magic of your low-code data modeling companion, QuickSQL.

A lot has been said about the low-code, app in minutes features of Oracle APEX. But we don’t often hear about the power-horse that is QuickSQL, hidden within the SQL Workshop Utilities.

If you want a robust data model but don’t have the time or know-how to leverage Oracle SQL Developer Modeler, look no further.

Read on to learn a bit about how we use QuickSQL in some of our projects to make our lightning-fast development even faster.

By the way, not sure where to find this little piece of magic? Log in to your APEX workspace, click on SQL Workshop –>Utilities, and 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.

Shorthand commands for sample data

  • /constant ABC (example: /constant NYC to populate NYC in all rows)
  • /check Male, Female (not only adds check constraint but uses these in sample data)
  • /value value1, value2, value3 (comma delimited list of values to use in sample records)

Download our QuickSQL cheat sheet here!

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.

By choosing 'Include Drops', the generated script includes drop statements for all created objects

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.

Table and column comments translate into a series of 'comment on table' and 'comment on column' statements

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.

QuickSQL shorthand: employees definition nested within the departments table infers a FK relationship
Generated SQL: department_id added as FK thanks to the shorthand indented notation

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.

Referencing ID columns in the shorthand notation generates the appropriate FK constraints

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.

Shorthand commands to know

  • /default to add a default on the column if null
  • /nn adds a not null constraint
  • /unique adds a unique constraing
  • /idx, /index, /indexed adds a non-unique index to the column
  • /hidden /invisible makes the column invisible
  • –, [ comments ]  add comments to your column or table

Download our QuickSQL cheat sheet here!

QuickSQL for a multi tenant application

Building an application that needs really strong row-level security? Where you need to segregate groups’ data from one another? You’ll likely want to leverage Oracle’s multi-tenancy features.

QuickSQL sets you up right from the start, with a setting for a multi-tenant application. Set to yes, and QuickSQL will automatically add a TENANT_ID column to every table.

Easy as 1-2-3!

Want to learn more about how to make an Oracle APEX application multi-tenant? Check out an Instant Tips episode where we showed how to do this in a VERY full 5 minutes 🙂

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.

QuickSQL Cheat Sheet

Although the built-in QuickSQL help is tough to beat, if you’re looking for an overview of all the shorthand commands, we’ve got a QuickSQL cheat sheet just for you.

Title photo courtesy of Rich Soule

Share this:
Share

3 Comments:

    • Marcelo osorio k.
    • April 03, 2022
    • Reply

    Amazing Job !! We need to keep an eye on this!!!

    • Niels Hecker
    • April 04, 2022
    • Reply

    Hello Michelle, shoudn’t the headers “Table Directives” and “Column Directives” in your “QuickSQL-Cheat-Sheet” be exchanged?

      • Michelle Skamene
      • April 04, 2022
      • Reply

      Niels, yes! Fixed, and thank you!

Leave reply:

Your email address will not be published.