Adventures in POSTGRES: Lowercase Everything

So I’ve been trying to transition from using MSSQL to POSTGRES, and I’ve encountered a few bumps here and there.

One of the tutorials I was following recommended using the Chinook database (a handy test database which has set ups for multiple db providers) as a sandbox. The database uses the naming convention I’m familiar with which is UpperCamelCase for some db objects like tables and its columns. For other things like stored procedures or functions, it’s in LowerCamelCase. Imagine my shock when I ran a simple query to the table “Albums” in the default schema “public”.

[sql]select title from album[/sql]

I got served a

relation “album” does not exist

It turns out that if objects like table names are in UpperCamelCase, then you have to wrap them in quotes before POSTGRES understands what you’re actually trying to do, like so

[sql]select “Title” from “Album”[/sql]

So if you want to avoid all the pain and frustration of having to type in those extra double quotes every single time, keep your objects in lowercase.

[sql]create table artist(
id serial primary key,
title varchar(160) not null
);[/sql]

The POSTGRES docs give good guidelines on this. I guess the convention is good in that it’s easier to type things everywhere, but I can just imagine how painful it would be to attempt to migrate an MSSQL database to POSTGRES later on.

As a bonus, not only did I learn about the casing problem, but I also learned that relations in “relational databases” had nothing to do with the lines drawn between tables we call “relationships”.

Attempts at NoSQL

In my frantic search for the holy grail of data storage I’ve now and again been tempted to dabble in the mystic arts of NoSQL. But this is a classic case of falsely believing that there’s a silver bullet to database development. And this hasn’t happened only once. I’ve actually tried building a prototype (read: did what some tutorials told me to) from MongoDb about 3 times now. I keep forgetting why I stopped last time and I kept hitting that same wall so I figured that I’d put it in writing this time.

Documents

Talking specifically about document-databases like MongoDb, the idea that I didn’t need tables seemed novel. Why do I even need to save to multiple tables? If I saved the whole object as one document in the database, I wouldn’t need to worry about speed of writes. If I needed the object I saved back to my application, I wouldn’t need to rebuild it from multiple joins. Nor would I have to worry about things like the object relational impedance mismatch.

But now that I think about it, if all I’m worried about is that I’m not saving my data as documents, then why not just save the whole document in a column on a table? That way I can deserialize that document back to an object and solve my object-relational impedance mismatch. Fortunately, modern relational databases seem to be taking this approach. POSTGRES has extensive support for JSON types with its JSON and JSONB data types. The other relational databases seem to be catching up as well as companies like Microsoft add better support for JSON strings if not JSON types to the latest version of their software. Implementing it like this allows developers to have the best of both worlds: a document-oriented approach when you need it, while keeping all the features existing relational databases provide.

And speaking of those features, there’s a critical one that NoSQL solutions can’t seem to provide.

Transactions

At every attempt I’ve made to try to prototype Mongodb (not bashing on it, just so happened that I haven’t had time to try the others yet), it fails when I want to store multiple related documents at the same time. I have to rely on eventual consistency, and that’s where the problem comes up. There are cases where eventual consistency just won’t do. Personally, I’ve even had trouble implementing eventual consistency where it really was the only option because of the complexity it adds to an application.

Simply put these document databases aren’t ACID compliant, because maybe they’re not meant to be in the first place. But there are a lot of cases in building an application where you want to be ACID compliant. When implementing DDD for instance, I may need to update multiple aggregates in the same transaction, or save domain events in one fell swoop. Failing to save one, and allowing to save another would destroy the integrity of my data. Having the option to switch between a relational approach and a document-oriented approach as is the case with dbs like POSTGRES gives the developer freedom to apply the best tool suited for the task.

Hybrid Approaches

If you feel the need to dive into NoSQL because it’s the next big thing and you think it’ll magically solve all the difficulties you’ve had with your relational database, you may want to hold onto doing that just yet. The time you’ll spend in learning new tech may be just as well spent in improving your understanding and skills in the current ones you know. That’s where I found myself. If you’re interested in giving this a shot here a few resources which might be of help:

About Me

Hi, I’m Jonn. I’m a professing Christian (Reformed Baptist), a Software Engineer by trade, and an avid gamer from the Philippines. This blog is more of an exercise in writing down my thoughts in the hopes that it will help me form a more concrete and consistent understanding of how the world works. It’s currently a mix of my stream of thought, and the occasional programming post.

Creative Commons License
Images, Videos, and other content in my blog are licensed under Creative Commons Attribution 4.0 International License unless otherwise stated.

Additional disclaimer

This is a personal blog. Any views or opinions represented in this blog are personal and belong solely to the blog owner and do not represent those of people, institutions or organizations that the owner may or may not be associated with in professional or personal capacity, unless explicitly stated.

Any views or opinions are not intended to malign any religion, ethnic group, club, organization, company, or individual.

All content provided on this blog is for informational purposes only. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site.

The owner will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information.

Downloadable Files and Images

Any downloadable file, including but not limited to pdfs, docs, jpegs, pngs, is provided at the user’s own risk. The owner will not be liable for any losses, injuries, or damages resulting from a corrupted or damaged file.

Comments

Comments are welcome. However, the blog owner reserves the right to edit or delete any comments submitted to this blog without notice due to :

– Comments deemed to be spam or questionable spam.
– Comments including profanity.
– Comments containing language or concepts that could be deemed offensive.
– Comments containing hate speech, credible threats, or direct attacks on an individual or group.

The blog owner is not responsible for the content in comments.

This blog disclaimer is subject to change at anytime.

Disclaimer helpfully provided by http://sampleblogdisclaimer.blogspot.com

Book Review: Dreaming in Code

Dreaming in Code: Two Dozen Programmers, Three Years, 4,732 Bugs, and One Quest for Transcendent SoftwareDreaming in Code: Two Dozen Programmers, Three Years, 4,732 Bugs, and One Quest for Transcendent Software by Scott Rosenberg
My rating: 5 of 5 stars

“Nobody should start to undertake a large project. You start with a small trivial project, and you should never expect it to get large… If it doesn’t solve some fairly immediate need, it’s almost certainly overdesigned.” – Linus Torvalds, Linux Times, 2004

Life is complex. Trying to model even one real-life process into an ethereal tool will no doubt embody part of life’s complexity. But this isn’t immediately obvious to the optimistic programmer. A grand vision of building the-next-big-thing is enough to fuel one’s optimism and blind them to the fact that the-next-big-thing is like really, really big. This isn’t uncommon in the Software Industry where building things (small things) seem to not take as much time as other industries. Open an editor, write a few lines of code and you’ve got something on your screen unlike say a bridge where the first may take a few weeks or months after someone draws up the first draft (I have limited knowledge on bridge building so don’t quote me on this comparison). And when a programmer finds out that building software isn’t as easy as they thought it would be, they can’t help but wonder if there’s something they’re missing. And then they get a book like this and try to read about how other people are doing.

Your average pretentious software engineer might read the title of the book and think that “These must be some enterprise software engineers unlucky to be in one of those process heavy companies where they have to do some archaic process like waterfall or RUP. If only they’d done agile they wouldn’t be in this mess.” Surprisingly though, your average pretentious software engineer would be proven wrong as they find themselves in the shoes of the Chandler team. The team involved a lot of the brightest minds working on an open-source project with a huge budget that’s just enough to keep them going for more than 5 years. Depressingly enough they did go for more than 5 years. I’m very grateful to Mitch Kapor for giving the world a window into one of his most challenging projects. His openness and willingness to have this documented and shared just shows what kind of person he is.

The book itself is a fun read. It isn’t your computer science or management textbook nor is it your boring documentation of the Chandler team where the narrator jumps from one event to the next. Scott Rosenberg gives his readers a view of the conversations, meetings and other happenings inside the Chandler team’s office. From a programmer’s perspective, some events may even sound eerily familiar to their own encounters and conversations. I do appreciate his attempt at providing the occasional explanation of a principle or idea to the reader, sometimes even giving a short backstory at how that came to be. Some find that distracting, but I do find it helpful. This can even serve as a gateway to newbie software engineers trying to understand the world they work in and introducing them to some very helpful engineering principes while they’re at it. Though there are some concepts that he does seem hard pressed to explain in layman’s terms, but hey who doesn’t have to go through that challenge every once in a while? Concerning the book’s theme, one might get the impression that this book is one big what-not-to-do-when-building-software. There is much of that yes. We can benefit from other people’s hindsight, examples of which are when in early chapters/years they rebuilt much of what they could have reused from elsewhere. But some of those simply reflect the difficulty of building software. Where collaboration and figuring out how the team that builds the software works together and the direction they should take is a great deal important as actually building the software itself. There are other points in the project where taking a calculated risk does not mean that it stops being a risk such as the team’s investment against the web technologies of their time. There’s a lot to learn from every team. The team’s members themselves would no doubt have learned much from the experience. And readers of this book will find much to learn from them as well.

Great read. Learned a lot. Would recommend. 5 stars.

View all my reviews

This review was first posted on Goodreads