PostgreSQL, together with PostGIS, allows us to store various types of geographical data. Besides working with simple coordinates, we can also store entire areas in the form of polygons. In this article, we learn how to handle polygons with PostgreSQL and the Drizzle ORM. While Drizzle ORM does not support it out of the box, we […]
PostgreSQL provides many options for working with geographical data, especially with the PostGIS extension. In this article, we’ll explore how to calculate the distance between two coordinates and how to identify locations within a specific radius. If you want to check out the basics of storing coordinates in PostgreSQL with the Drizzle ORM, check out […]
Many applications rely on geographical data to calculate distances and track locations. PostgreSQL offers several ways to store geospatial data, each designed with different goals. In this article, we learn how to store coordinates when working with PostgreSQL and the Drizzle ORM. Latitude and longitude A coordinate consists of two numbers that pinpoint a location […]
When we execute an SQL query, PostgreSQL follows a process that consists of multiple steps. First, it parses the SQL statement, checking for syntax errors. Next, it analyzes whether the tables and columns used in the query exist. Then, it plans the instructions necessary to achieve the desired result. Finally, it executes the instructions based on […]
Relational databases are known for their strict data structures. Every table requires a defined schema, including columns, indexes, and relationships. Despite careful planning during database design, application requirements often evolve. As a result, the database must adapt to keep up with these new needs. However, it’s crucial to ensure that no existing data is lost […]
PostgreSQL uses schemas as namespaces within the database to hold tables and other structures, such as indexes. In this article, we explain how to use them with the Drizzle ORM and how they can be beneficial. The public schema Out of the box, PostgreSQL creates a schema called public for each new database. database-schema.ts
|
1 2 3 4 5 6 7 8 9 10 11 |
import { serial, text, pgTable } from 'drizzle-orm/pg-core'; export const articles = pgTable('articles', { id: serial('id').primaryKey(), title: text('title').notNull(), content: text('content').notNull(), }); export const databaseSchema = { articles, }; |
[…]
It’s not uncommon for a single table to be related to multiple similar tables. A good example is a comment the user can write under a photo, an article, or an audio file. A possible approach would be to create a separate table for each type of comment, such as PhotoComment or ArticleComment. Unfortunately, this […]
Sometimes, in our application, we would like to define a specific duration of time. We could represent it as a number of seconds, for example. However, we might want to be more flexible and be able to use various units, such as minutes, hours, days, or weeks. In this article, we learn how to achieve […]
With PostgreSQL’s full-text search feature, we can quickly find documents that contain a particular word or phrase. It can also sort the results to show the most relevant matches first. In this article, we learn how to implement it with the Drizzle ORM, PostgreSQL, and NestJS. Column types used with the text-search feature To implement […]
Our SQL queries can become more complex as the application we develop grows. To deal with that, PostgreSQL implements views that act as virtual tables with rows and columns from which we can select. Additionally, we can create a cached version of our data using materialized views that store our virtual tables in the database. […]
