I was working on a small project where I set up a new database and found that the build option is actually really handy and even the sequelize.sync function is pretty useful in development. I think it would have been better to plan out how the data should look and write my database before but this also doesn't mesh with the iterative part of development. I'm at a crossroads there where I believe its better to iterate quickly vs planning ahead and figuring exactly what the project should do. The sequelize.sync function is very helpful when you want to just iterate as quickly as possible, adding and deleting entire columns as you write code.
I'm going to aim to cover the next 3 core concepts here and maybe I'll learn something else that comes in as handy as the build function!
This section goes over the basic crud operations. The create function maps to the insert function which I'm curious why they aren't the same and why insert was chosen instead of create anyway. I do think create is a better name for the insert as we are creating a row but I can see arguments for both. It's also interesting that you can specify the fields to set when creating a new table entry. This way you can actually do create(req.body, { fields: ["F1","F2"] }). This assumes that the req.body is fully validated and legit or you can rely on the database to do the validation for you. I'm curious however, if I don't give any fields, then if the form has attributes not on the object they probably just get ignored. I already use this technique so it probably works as I think it does.
I wonder where validation should happen. I think it'd be better to do it on the database side as that's what I usually do for work but I can see the bonus of doing it in the application. However this means that the application now holds information that the database doesn't know about it which I think is wrong. The database ideally would be the source of truth, not the database + the application logic.
The findAll function is a standard select statement. I need to get in the habit of using the attributes object to specify which attributes that we want returned. So far I've been getting them all and this has led to bloated SQL queries getting logged as instead of doing select *, sequelize will do a select with all of the attributes named. It might not matter ultimately but the logging would definitely get cleaned up if sequelize defaulted the star when no attributes are supplied.
The manual also goes over how to do aggregate functions which I haven't found a use for nor can I think of one besides that it might be faster. The example they show is a count but running a findAll and doing a length function on it should be the same. Maybe it's less memory used and faster because we don't pass back all the rows.
The where clause is probably the most important part of the ORM and SQL in general. This is how we filter data from a table. The most common things I use are equality and and statements. There is also some in operations that I do as I use arrays in postgres.
Interesting, there is also the ability to run SQL functions through the ORM, I imagine they need to exist first in the database but you can call them pretty easily. Otherwise you would need to get all the relevant rows and then filter them in the application. It would be faster to do it in the database I'm guessing. I'll need to think about a use case for this because this would be fun to do.
Ah! Update queries can take where clauses. So I could do updates on data without actually reading in the row. This is interesting. I could rely on the database throwing an error if the object isn't found instead of manually checking for nulls. I wonder if this is good practice.
Delete queries can also take wheres. Definitely good to know. I also apparently have used this before without being aware of it.
There is also a way to destroy all the data but not lose the table which is through the truncate option. I can't imagine a situation where if I'm going to destroy everything, I don't mind recreating the entire table.
There is a bulkCreate function which looks extremely useful for the cases where I am creating multiple things for a user on registration. Currently I have each creation in a separate function and pass in transactions but this could probably be refactored into using bulkCreate. I took a quick glance and it would pretty straightforward to change.
The ordering and group by functions are pretty straightforward, though I haven't used the group by yet. I should definitely read more about running group bys and aggregate functions as that could be helpful in generating reports of invoices.
The limit and offset parameters let you do pagination which is pretty neat. I need to implement this for the articles page of leftwrite as right now it just shows a giant list of 1000 rows.
There are also some count, min, max and sum functions available for use which could be helpful. Well they definitely are in the invoicing application I'm working on.
Even in the basics, I learn something new! It was a good idea to read the manual.
FindAll is pretty straightforward and something that's covered earlier. FindByPk is interesting in that it exists. I guess it's a shortcut for findall findOne where you pass in the id. FindOne is useful. I wonder if there is difference in using findOne vs findByPk.
Interesting to note that findAll automatically generates sequelize objects ready to be used however if there is too much data, you can run a raw query to get the raw data. This is something I did for my own ORM I was working on however it was still terribly slow. I wonder if sequelize does something to generate objects quickly.
https://stackoverflow.com/questions/67477656/is-there-a-difference-between-findone-and-findbypk-in-sequelize
Looks like findOne and findByPk are both shortcuts to the findAll. Good to know.
FindOrCreate is pretty useful. Not sure if I trust it completely but I do have a pattern of doing a find, failing to find one, then doing a create otherwise an update. This would let me simplify some things as I can get rid of the check and do a straight update or create. Already started using this, I had to treat the returned result as an array though even though there should only be one result. This is probably because the find can return an array so the create returns an array as well to keep consistent.
FindandCountAll is a function that combines the find all and the count. I wonder if its better to just do count if you don't actually want all the rows and just need the total for display purposes.
Getters are functions that run when values are read from the database so you can transform data before the application actually sees anything. I'm not a fan of this as this is just magic and I'd rather the application handle this explicitly. This is pretty analogous to the magic methods in python.
Setters are the same way but run before a value is persisted to disk. An example of using this would be to write a setter for passwords so that they are always hashed. Not sure what you get besides that you will never forget to hash your passwords. This also makes sure the password hashing happens in just one place instead of the multiple that it can be due to resetting passwords and registering.
Virtuals are interesting but also a bit too magical for my taste. You can create fields on the sequelize model that don't actually exist on the database. A good example is full name where you set up a full name field but in the database it is a firstname and lastname field. The virtual field has getters and setters set to properly handle it. It's a cool idea and I'm sure there are plenty of uses for it but maybe there shouldn't be. I wonder if this could be helpful to share session data and user table entries as my sessions usually require most of the user data anyway.
That's all for part 2! Still have the rona, getting better, getting worse.