SQL Archives - Marcin Wanago Blog - JavaScript, both frontend and backend

API with NestJS #185. Operations with PostGIS Polygons in PostgreSQL and Drizzle

This entry is part 185 of 186 in the API with NestJS

When using PostgreSQL with PostGIS, we can do various operations using polygons. In this article, we learn how to do that both through raw SQL queries and the Drizzle ORM. Storing polygons using the Drizzle ORM To store polygons using the Drizzle ORM, we use a custom data type and the areas table. database-schema.ts

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

import { serial, text, pgTable } from 'drizzle-orm/pg-core';

import { customType } from 'drizzle-orm/pg-core';

import { Geometry } from 'wkx';

type Coordinate = [number, number];

interface GeoJson {

type: string;

coordinates: Coordinate[][];

}

const polygon = customType<{ data: Coordinate[][]; driverData: string }>({

dataType() {

return 'geometry(Polygon, 4326)';

},

toDriver(coordinates: Coordinate[][]): string {

return JSON.stringify({

type: 'Polygon',

coordinates,

});

},

fromDriver(data: string) {

const geoJson = Geometry.parse(

Buffer.from(data, 'hex'),

).toGeoJSON() as GeoJson;

return geoJson.coordinates;

},

});

export const areas = pgTable('areas', {

id: serial().primaryKey(),

name: text().notNull(),

polygon: polygon('polygon').notNull(),

});

export const databaseSchema = {

areas,

};

[…]

NestJS SQL

API with NestJS #184. Storing PostGIS Polygons in PostgreSQL with Drizzle ORM

This entry is part 184 of 186 in the API with NestJS

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 […]

NestJS SQL

API with NestJS #183. Distance and radius in PostgreSQL with Drizzle ORM

This entry is part 183 of 186 in the API with NestJS

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 […]

NestJS SQL

API with NestJS #182. Storing coordinates in PostgreSQL with Drizzle ORM

This entry is part 182 of 186 in the API with NestJS

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 […]

NestJS SQL

API with NestJS #181. Prepared statements in PostgreSQL with Drizzle ORM

This entry is part 181 of 186 in the API with NestJS

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 […]

NestJS SQL

API with NestJS #176. Database migrations with the Drizzle ORM

This entry is part 176 of 186 in the API with NestJS

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 […]

NestJS SQL

API with NestJS #174. Multiple PostgreSQL schemas with Drizzle ORM

This entry is part 174 of 186 in the API with NestJS

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,

};

[…]

NestJS SQL

API with NestJS #170. Polymorphic associations with PostgreSQL and Drizzle ORM

This entry is part 170 of 186 in the API with NestJS

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 […]

NestJS SQL

API with NestJS #165. Time intervals with the Drizzle ORM and PostgreSQL

This entry is part 165 of 186 in the API with NestJS

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 […]

NestJS SQL

API with NestJS #163. Full-text search with the Drizzle ORM and PostgreSQL

This entry is part 163 of 186 in the API with NestJS

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 […]