SQL and ORM

Francisco Barcena - July 16, 2018

SQL stands for Structured Query Language and there are many database technologies that make use of SQL, for example MySQL, PostgreSQL, Oracle, and SQLite3.

Back when I was gaining experience, I did work for a software company that had a proprietary database language, it was slightly different than SQL, but had all the same functionality, including inner and outer joins. I always knew I wouldn't find it difficult to transfer some of those skills over to writing SQL statements for other database technologies, and I didn't.

Unfortunately, while working with Python and some Node.js I discovered an ORM (Object Relational Mapper) here and there. An ORM basically allows you to write complex database queries from a simpler point of view. In most cases a database record, along with any other linked records across many tables, represents 1 real life thing. 1 real life thing that a regular user may understand. There's database records for a sales order, a patient's chart, books checked out from a library, etc. Well, and ORM allows us to query data records and assign those records to object variables so that we no longer struggle to stay focused as we manipulate data across many tables.

Instead of point to records and fields on a database, an ORM assigns relevant data to conventionally named variables. If we know the I.D. of a purchase order, we may pull it from the database like so: my_order = Order.get(32442) where 32442 may be the I.D of the order we want.

Then we can access properties of that order in the following way: my_order.total() or my_order.products()

This allows a programmer to think in an easier and more concrete manner, as opposed to accessing order data through writing a query.

The bad part about discovering ORMs is that you begin to work less and less with actual SQL statements so it takes a bit to get up to speed when directly interacting with a database through a terminal and joins can be specially painful.

One good piece of advice is to save all your previously written SQL queries in a text file, or on your database workbench software, that way you can always just copy and modify them for future use. Doing this has saved me tons of time.


   SQL    ORM