prisma db push를 진행한 직후 자동으로 2dsphere 인덱스를 추가하는 방법

To properly define a 2dsphere index in your schema.prisma file for MongoDB, Prisma currently does not support geospatial indexes directly through the @index or @@index attributes. However, you can manually create the index using raw database commands after migrating your Prisma schema.

Steps to Add a 2dsphere Index:

1. Define Your Schema in schema.prisma

In your Prisma schema, define the location field using a JSON type (since Prisma does not support geospatial types natively):

model AcademyModel {
  id       String @id @default(auto()) @map("_id")
  name     String
  location Json // Stores GeoJSON object (e.g., { type: "Point", coordinates: [lng, lat] })
  
  @@unique([name]) // Optional unique constraint
}

2. Apply Migration

Run:

npx prisma db push

3. Manually Create a 2dsphere Index in MongoDB

After applying the migration, you need to manually create the 2dsphere index via MongoDB CLI or a script:

Using MongoDB Shell
db.AcademyModel.createIndex({ location: "2dsphere" });
Using Prisma’s $executeRaw in a Script

You can use Prisma's prisma.$executeRawUnsafe() to create the index programmatically:

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function createIndex() {
  await prisma.$executeRawUnsafe(`
    db.AcademyModel.createIndex({ location: "2dsphere" })
  `);
}

createIndex()
  .then(() => console.log("2dsphere index created"))
  .catch((e) => console.error(e))
  .finally(() => prisma.$disconnect());

How to Store and Query Geospatial Data

When inserting location data, ensure you use the correct GeoJSON format:

await prisma.academyModel.create({
  data: {
    name: "Seoul Academy",
    location: {
      type: "Point",
      coordinates: [126.9780, 37.5665], // [longitude, latitude]
    },
  },
});

To find locations near a given point:

const nearbyAcademies = await prisma.$queryRaw`
  db.AcademyModel.find({
    location: {
      $near: {
        $geometry: { type: "Point", coordinates: [126.9780, 37.5665] },
        $maxDistance: 5000 // 5km radius
      }
    }
  })
`;

Important Notes

Would you like a script to automate index creation upon deployment? 🚀