Clash of the Backends leaves Developer stunned
This I think is a question everyone needs to answer for themselves. Both
options have pro’s and con’s.
The most important thing to realize though is that SQL really falls
short as a standard. The standard really only defines the syntax for
writing queries and even that is followed to questionable levels by some
databases (though you’re pretty safe here when sticking to the major ones).
Obviously if you decide to stick to the standard you can easily switch
between the different platforms. You can use SQLite to deploy to single
user systems, you can use PostgreSQL for your larger clients, you can
deploy to Oracle or MSSQL server for clients who for whatever misguided
reason what a big name behind the technology you use, etc.
However, if those types of issues do not mean anything to you, choosing
a single database platform allows you to specialize in that RDMS and
that can be very much worth your while.
You’ve already had your first taste of one area where the SQL standard
is hopelessly non-existent. Sequence numbers. There is nothing in the
standard that dictates how to generate unique primary keys for tables.
There is nothing in the standard for how to retrieve those values once
generated. Every database has its own unique implementation. PostgreSQL’
returning syntax as far as I know is unique to PostgreSQL but its also
one of its strong points.
Another area that does start to bite you is that there is no standard
for data types. While each database has the standard data types for
columns there are various differences especially around large text
fields and binary fields that can be a pain. Especially once you start
to have a need for optimizing how things work as there are crazy
performance differences between say a varchar field and a text field on
For instance I saw someone suggest to you using “numeric_field::text
ilike ‘123%'”, that works exactly as you’d want, it also completely
kills performs on any large table as this relegates execution of that
query to a table scan (the database reading each and every single row in
the table to figure out if its the row you’re looking for = SLOW).
Note that you can make this fast by creating an index on a cast column,
and casting to a size limited varchar but again that is not part of the
SQL standard and only supported by a handful of back ends (Postgres
being one of those).
A few other doors that will close for you if you want to stay backend
– no access to users or other login meta data, this can be a biggy, you
have to roll your own access logic which will make a lot of security
features in the RDMS unavailable to you unless you demand of your
clients to create and maintain users logins outside of your application.
– no access to triggers. many databases don’t support them, and for
those that do the syntax is not portable
– no access to stored functions. really just the same reason as triggers.
– no access to specialized data types, you’d be amazed how much fun you
can have with PostgreSQLs JSON data type especially now that we have
half decent functions in Omnis for JSON.
But again, and I can’t underline this enough. It totally depends on what
sort of application you are making. I think you need very good and
practical reasons not to specialize to one RDMS because you are giving
up ALOT and making your life far more difficult. But depending on your
situation there are compelling reasons to stick to standard SQL not to
mention that there are certain areas of our business where you do not
have a choice. Bottom line, if you have a reason to support multiple
back ends, you have a really solid reason to stick with standard SQL.
P.S. one final thing, keep in mind that Omnis in building what they
build into the core product, stuck to standard SQL. On one hand I
applaud them, it means that Omnis is not tied into a single back end. On
the other hand its the foremost reason why it is so hard for new people
to get started in Omnis Studio because they have to write all the
One really cool feature of Omnis Classic was that you could start it up,
follow a tutorial that spanned less then an A4 that told you to create a
datafile, create a file class, dump some fields and buttons on a window
and you had a working application.
It didn’t first have to explain to you how to setup users, how to log
in, how to create tables, how to roll your own code for creating unique
identifiers, how to well you get my drift…. You are a chapter into
reading about writing boiler plate code in Omnis Studio before you reach
that point with an SQL back end, and really that manual should be
different for each back end.
Us as a community can do better helping out there with more example code
being made available which has become infinitely easier now that Omnis
Studio supports GitHub. I hope to play my part in this soon once I am
able to free up my schedule a little bit and redo my “getting started”
On 3/2/18 9:11 am, Das Goravani wrote:
> So I’ve heard from a number of Omnis/SQL Gurus on the subject of “Generic SQL” and Backends.
> One told me to stick to the Standard SQL as much as possible for portability. Other amongst the knowing tell me that there are multiple standards and besides the backends give differing results for the same commands, and that trying to handle multiple backends is a pain.
> So my very general question is this: Should one tell oneself that they are trying to stick to the standard? Should one try at all?
> I just had the case where I need to “cast” my numeric field to text so that I can do a LIKE comparison in a Where Clause. I was given two ways of casting… one is to write cast() in front of the column name in question, and the other is Postgres’s way where you say name::text to cast it to text. I’m using a Postgres server as I develop, and it does not recognize the first method, only it’s own method.
> So as I sit here I’m faced with “Have bugs now”. I either can run in dev mode with this Postgres server and do things it’s way or face bugs now. Weird. I’d have to fork now for differing backends and I can see as Alex said that “That is a pain”. I can see that. I don’t want that.
> The clientele I’ll be aiming at in my “practice” when I’ve learnt enough to “get out there” will be smaller companies, because I work alone. I believe that smaller means “not as stuck on what backend they want to use” as larger corporations who may already have a standard they stick to. So I am MORE OK with just Postgres than if I were targeting the very large companies.
> What to tell myself ? Which way to develop in the here and now ? I don’t want to fork all over the place for different DAMS and backends. I can barely do what I’m doing, I mean it’s a hard learning curve as is. What to do brothers and sisters in arms ? What to do?
> Manage your list subscriptions at lists.omnis-dev.com
> Start a new message -> mailto:email@example.com