Does prisma supports GeoSpatial queries

Question

I found out that you use MySQL and PostgreSQL as of now, and they support geo-spatial types, how can i implement geo-spatial queries to my prisma.

Let say i want to get all events nearby NYC?

Answer 1

I "implemented" a custom geoSearch on my project using Prisma and a MySQL database :

You need to be able to connect programmatically to your database.

First of all, let's get our env var :

const host = process.env.MYSQL_ENDPOINT;
const user = process.env.MYSQL_ROOT_USERNAME;
const password = process.env.MYSQL_ROOT_PASSWORD;
const database = process.env.PRISMA_SERVICE + "@" + process.env.PRISMA_STAGE;

Now try to connect to our database using the package promise-mysql :

let connection;
try {
      //Create a connection to the database;
      connection = await mysql.createConnection({
        host,
        user,
        password,
        database
      });
    } catch (e) {
      console.error(e);
      throw new Error("Could not connect to the Database");
    }

It is needed to have a spatial column in your table, which should also have a spatial index on it. It is possible to do it programmatically using these (table must be empty) :

/**
* Add a spatial column to the table, used for geo-searching
 * @param  {string}  tableName name of the table to alter
 * @param  {string}  columnName name of the spatial column
 * @param  {string}  lonColumnName name of the longitude column
 * @param  {string}  latColumnName name of the latitude column
 * @param  {object}  connection connection to the database
 * @return {Promise} result of the table alteration
 */
 const addSpatialColumn = async (
  tableName,
  columnName,
  lonColumnName,
  latColumnName,
  connection
) => {
  return connection.query(`
  ALTER TABLE
      ${tableName} ADD ${columnName} POINT AS(
          ST_POINTFROMTEXT(
              CONCAT(
                  'POINT(',
                  ${lonColumnName},
                  ' ',
                  ${latColumnName},
                  ')'
              )
          )
      ) STORED NOT NULL;`);
};

/**
 * Add a spatial index to the table
 * @param  {string}  tableName  name of the table
 * @param  {string}  columnName name of the column to create an index on
 * @param  {object}  connection connection to the database
 * @return {Promise} result of the index creation
 */
const addSpatialIndex = async (tableName, columnName, connection) => {
  return connection.query(
    `ALTER TABLE ${tableName} ADD SPATIAL INDEX(${columnName});`
  );
};

Now comes the tricky part. As Prisma is not spoon-feeding us on this yet, you need to determine the parameters of your sql query yourself.

Then you can do your query, e.g. :

const query = `SELECT ${sqlSelect} FROM ${sqlFrom} WHERE 
MBRContains(ST_GeomFromText("${polygon}"), GeoPoint) ${sqlWhere} LIMIT 
${toSkip},${batchSize}`;

const selectedRows = await connection.query(query);

Post-scriptum : these snippets are not abstractions and therefore, may need modifications/improvements. I am just providing an example of a way to solve this temporary problem.

Answer 2

Geographic queries are not currently supported by Prisma. You can use Prisma for the majority of your queries and query the underlying database directly for geospatial queries.

Follow this feature request to be notified when this feature is available: https://github.com/prismagraphql/prisma/issues/28