Integrating Spatialite with Sequelize

Sequelize ORM is a robust tool for Node.js developers, offering a streamlined interface for interacting with databases using JavaScript. It supports all major SQL relational databases, including MySQL, PostgreSQL, SQLite, Oracle, and MSSQL. Sequelize simplifies the process by providing migrations, associations, and validations, ensuring efficient and organized data management in your applications.

In a previous blog post, we explored the use of Spatialite with SQLite3 and Node.js. Now, we're going to delve into integrating Spatialite with Sequelize, enhancing the ORM experience for developers. You can refer to the previous blog post here. I recommend reading previous blog first.

https://surajgupta.hashnode.dev/using-spatialite-in-nodejs-with-sequelize

While Sequelize supports SQLite, it doesn't directly support Spatialite or any SQLite extensions. It does offer support for Geometry data for Postgres, but not for other databases. In this blog post, we'll modify the Sequelize source code to add Spatialite support, enabling the handling of Spatial data in SQLite. We'll be using Sequelize v6 for this demonstration, as Sequelize v7 is still in alpha at the time of writing.

I published a npm package for this, that you can use directly to use Spatilaite in Sequelize.

npm i sequelize-with-spatialite

Initialize Spatialite on SQLite db.

import { DataTypes, Sequelize } from "sequelize-with-spatialite";

const sequelize = new Sequelize({
    dialect: "sqlite",
    storage: "./db.sqlite",
    logging: false,
});
// this is only needed once, when Database is created
await sequelize.query("SELECT InitSpatialMetaData();");

That's it, Now you can use Sequelize for Post GIS data in SQLite with all the same syntax and notations as postgres.

const User = sequelize.define('User', {
    id: {
        type: Sequelize.UUID,
        primaryKey: true,
        defaultValue: Sequelize.UUIDV4
    },
    name: {
        type: DataTypes.STRING,
        allowNull: false
    },
    location: {
        type: DataTypes.GEOMETRY('POINT'),
        allowNull: true
    },
    area: {
        type: DataTypes.GEOMETRY('POLYGON'),
        allowNull: true
    }
}, {
    timestamps: false
});


import circleToPolygon from 'circle-to-polygon';
const coordinates = [73.78291978700082, 20.06382394128455]; // longitude, latitude
const point = {type: "Point", coordinates: coordinates};
const radius = 10000; // in meters
const polygon = circleToPolygon(coordinates, radius, 32); // 32 is the number of edges

await User.create({
    name: "John Doe",
    location: point,
    area: polygon
});

And run all POST GIS queries same as running for postgres (no messing with raw queries)

const coordinates = [72.58995868405185, 18.96251426730694]; // longitude, latitude 
const withinAreaUsers = await User.findAll({
    where: {
        [Op.and] : sequelize.fn('ST_Within', 'location', sequelize.col('area'))
    },
});

// distance is in CRS unit
const nearestUsers = await User.findAll({
    attributes: ["id", "name", "location",  [sequelize.literal(`ST_Distance(location, ST_GeomFromText('POINT(${coordinates[0]} ${coordinates[1]})'))`), 'distance']],
    order: sequelize.literal('distance ASC')
});

console.log(nearestUsers, withinAreaUsers);

You can find all code in my Github repo.

Suraj's Blog

Suraj's Blog

Integrating Spatialite with Sequelize