cheatsheets devlog projects

database-importance.md

The Importance of Databases

2022-01-12

Hello! I caught the rona, so this is going to be a bit more stream of consciousness than anything else. Hopefully once I start feeling better, I’ll do a mini dive into node’s sequelize ORM.

My day job involves working with multivalue databases which you may not have heard of but the essence is that it is a database that stores things as strings with columns delimited by specific characters. Thanks to this job, I have deep appreciation of databases and how integral they are to businesses. Often the applications themselves can be bolted on but the database is forever. What we do in it, the columns we add, the indexes we create, and the custom functions we write are usually forever.

Even for my site, leftwrite, I would say that the database is far more important than the application. If I lose the nodejs files, that is recoverable. If I lose the database, I am dead in the water with no way out. This means that I should understand my database and its structures intimately. This is something that stands in opposition to using ORMs.

ORMs are object relational mappers and they serve to take SQL rows and make them into an object that programmers can use easily. The whole point is that programmers are already trained on using objects and so instead of dealing with raw sql, we can add abstraction that is easier to use.

We are programmers after all, if there’s one thing we’re reallly good at, its adding abstractions, necessary and unnecessary!

I am a fan of ORMs, they really do simplify working with SQL. I am far more comfortable doing something like Article.findAll({ { where: { id: req.id } } ) rather than SELECT * from articles where id = 'some_id';. I also like that I get a list of objects back that have each column as an attribute. This is in contrast to SQL which returns an array.

However SQL queries begin to start looking polluted and unwieldy and something about the queries that ORMs usually generate feel strange. This is very much a gut feeling as I don’t have much experience actually writing raw SQL. I also believe that most of the popular ORMs are probably better at what they do than me so I trust them but I think that for me, I should try going on an adventure of using raw SQL and writing my own helper utilities.

By using an ORM, I am very much removed from how my tables look and how the foreign keys and indexes are set up. I can’t open up a connection to my own database and understand everything when I do something like \d+ articles. I think if I could understand that, then I’d feel much more comfortable using an ORM like sequelize which is what I’m currently using.

This post is very much a personal post trying to come to grips with how important the database behind leftwrite is for me, but also with the fact that I don’t understand everything I’ve built. The next post might go into some sequelize topics as I did run into some optimizations like using the separate keyword in one-to-many and many-to-many relationships. I also learned what the sync option does which is quite basic is clearly explained in the documentation but somehow I never actually processed what it did.

I wonder how much of the code I write is something I can actually understand explain. I would also love to be able to understand and explain everything from the application to the electrons flowing around in transistors but that’s a big ask. Maybe one day!