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 […]
In the previous article, we’ve looked into various ways to store the date and time with PostgreSQL and TypeORM. Postgres can also manage intervals. With them, we can store a period of time. Ways to store and display intervals in PostgreSQL There are various ways we can input and view interval values. By default, PostgresSQL represents […]
While dealing with data, we often have to handle the date and the time. When doing so, there are quite a few things to consider. In this article, we approach various issues both from the standpoint of PostgreSQL and TypeORM. Ways to store and display date and time in PostgreSQL By default, Postgres represents dates […]
