One of the most important things to care about as a web developer is the integrity of the data. In this article, we learn what a transaction is and how it can help us ensure that our data is correct. The idea behind transactions A transaction is a set of instructions that either happens entirely […]
A significant advantage of SQL databases is handling relationships between various tables. Since, in web applications, entities often relate to each other, designing relationships is a big part of working with SQL databases. In this article, we continue learning MikroORM and use it to form relationships. You can find the code from this article in […]
So far on this blog, we’ve used the serial type to define autoincrementing ids for our tables. However, TypeORM started fully supporting identity columns very recently. Since PostgreSQL official recommends using them, it is a good moment to go through both approaches and compare them. Serial type The serial data type allows us to generate unique […]
In PostgreSQL, views act as virtual tables. Although they have rows and columns, we can’t insert any data into them manually. Instead, PostgreSQL runs an underlying query when we refer to the view. Creating views with PostgreSQL Let’s create a table of users to use it in a view.
|
1 2 3 4 5 |
CREATE TABLE users ( id serial PRIMARY KEY, email text UNIQUE, is_email_confirmed boolean ) |
Now, let’s define a view […]
On this blog, we’ve covered a variety of different column types. So far, we’ve performed various operations on said columns such as INSERT and UPDATE to modify the data directly. In this article, we cover generated columns that work differently. Generated columns in PostgreSQL We call the above columns generated because PostgreSQL automatically computes their data based […]
Database normalization is a term often used when discussing database architecture design. Unfortunately, it is often presented with the use of complex definitions that make it difficult to grasp. In this article, we explain what normalization is and provide examples using TypeORM and PostgreSQL. Database normalization aims to improve the integrity and reduce the redundancy […]
JavaScript NestJS SQL TypeScript
In the previous parts of this series, we’ve learned how to upload files to Amazon S3 and store their metadata in our PostgreSQL database. While this is a highly scalable approach, it might be overkill for our application. A great example is storing users’ avatars. They are usually small and simple files, and we might […]
JavaScript NestJS SQL TypeScript
In this series, we’ve implemented deleting functionalities for various entities. So far, it has always meant removing records permanently from our database. Instead, we can perform soft deletes. When we delete a record using a soft delete, we only mark it as deleted. You can find the code from this series in this repository. Soft […]
An important part of designing a database is controlling the data we store inside. The most fundamental way of doing that is defining the types for our columns. With that, we can make sure that a particular column holds only text, for example. In this article, we use constraints to have even more control of […]
Sometimes when working with databases, we need to represent a range of values. For example, we might want to define a set of available numbers or a range of dates. One way to do that would be to create two columns that hold the bound values.
|
1 2 3 4 5 6 |
CREATE TABLE events ( id serial PRIMARY KEY, name text, start_date timestamptz, end_date timestamptz ) |
Above, we are using the timestamp with timezone […]
