Add LineString and Other Geometry to your DB using Drizzle

Article summary

I was recently tasked with uploading geographic data into a PostgreSQL database as a piece of functionality to a larger whole. For our choice of ORM, we picked Drizzle! Overall, this has been a sleek and useful tool. At the time of writing this, the latest version is ^0.38 – in case anything changes later.

The Problem

Before it was too late, I realized that some helpful tools were missing when it came to implementing geometry columns. Currently, Drizzle only really supports adding geometry points – that is – geometry(Point) columns. With some work, I also added linestrings and potentially more geometry types, in the same way, using the steps below.

My Solution

I wanted to use as much of the ORM as possible before doing anything custom to work around limitations. Hopefully, the solution below isn’t too hacky for anyone to pick up. One thing that I don’t mention in detail here is that I also took the liberty of installing a PostGIS extension! Keep this in mind if you have any issues.

1. Schema

Thankfully, Drizzle allows you to import a geometry type directly from drizzle-orm/pg-core. Since typing support isn’t fully there for linestrings right now, I was able to manually create a column in a given table schema like this:

pathway: geometry("pathway", { type: "linestring", srid: 4326 })

Using drizzle-kit generate I was able to generate migration files successfully this way.

2. Migrations

Here is where we have to do some manual work. Although I mentioned type: “linestring” in the schema, what you will actually see in the migration file generated is that this actually gets replaced with point since that’s the only geometry type supported right now.

Inside the migration file you will find statements like this:

ALTER TABLE "table-name" ADD COLUMN "pathway" geometry(point, 4326)

I learned that drizzle-kit – although it generates the migration using point, you can just change this to linestring and it will run the migration just fine against your database. Once this done, you should be able to inspect your DB column structure and see that geometry(LineString) is correctly initialized.

3. Seeding

At this point of the process, I had almost given up. But we persist! So, to now seed this data into the column we need to write and execute raw SQL. We can still use Drizzle for this – although, it isn’t as intuitive for now until the ORM gets updated.

Here’s what I did:

const tableData = () => {
  let rawSql = `INSERT INTO ${getTableName(tableName)} (${getTableColumns(tableName)}) VALUES `;

  // Either just once or in a loop, construct the SQL string
  rawSql = rawSql.concat(`
  (‘${columnAValue}’, ‘${columnBValue}’, … , ‘LINESTRING(${geometryValue})’),
  `);

  // Replace last leading comma ',' with semicolon ';' to query
  return rawSql.replace(/,\s*$/, ";");
}

await db.execute(tableData());

By massaging a string to the correct format, I was able to insert all of my data easily. I hope that what I’ve found and done here will also be useful to you on your current or next project!

Conversation

Join the conversation

Your email address will not be published. Required fields are marked *