https://www.prisma.io/ logo
Join Slack
Powered by
Hi all - I'm gearing up to add geolocation search ...
# orm-help
s
Hi all - I'm gearing up to add geolocation search to my graphQL database. Does anyone have suggestions on best 3rd party services or am I missing some built in features for graphQL and geolocation?
r
Hey @Steve Jenkins 👋 Could you explain your use case if possible?
s
Hey Ryan - I'd like to allow users to input a zip code and return items within a 10 mile radius. I'm following Wes Bos' Advanced React tutorial, which includes search by title or description, but does not include a way to search by radius using latitude and longitude. Some additional searching led me to Algolia, but it's unclear to me whether that can fit inside the tutorial's tech stack (prisma, graphql yoga). Any pointers or recommendations would be much appreciated!
r
Personally I have used Postgres, you can use this extension to store geolocation types. You would need to use introspect as Prisma doesn't natively support that, and then you would need to add data based on the locations lat and lng. Also you would need to use a raw query to search for the items within a radius as that is a special function required. I will try to add an example in the Prisma examples repo here so that you can get a simple idea of how it works.
s
An example would be amazing 🙂
When you say introspect, are you referring to this: https://www.prisma.io/docs/reference/tools-and-interfaces/introspection ?
r
Yes I am 🙂
s
Ok, thanks, and if you do create an example, you should tweet it at Wes Bos he is very good at sharing helpful information with the community
One more question: when you say raw query does that mean I have to use something other than apollo client? Please excuse my ignorance here (python dev and early days with js/graphql)
r
I was saying that while the creating the backend. You would need to use the 
prisma.queryRaw
API to fetch data from the database. Apollo Client is for the frontend 🙂
s
Got it, very helpful, thank you Ryan! 🙏
💯 1
r
Do keep an eye out in the examples for geolocations in the coming days 🙂
s
Will do!
If I have a follow up question, can I reach out again?
r
Sure 🙂
s
I really appreciate it
Hey @Ryan - follow up question: I created a schema.sql like so:
Copy code
-- Create PostGIS extensions if they don't exist

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;

-- User location data
CREATE TABLE user_location (
  user_id INTEGER PRIMARY KEY,
  location GEOGRAPHY(Point)
);

-- Landmark location data
CREATE TABLE landmark (
  id SERIAL PRIMARY KEY,
  name TEXT,
  type TEXT,
  location GEOGRAPHY(Point)
);
And I run
Copy code
npx prisma introspect
However, I get the following warning:
Copy code
*** WARNING ***

These fields were commented out because we currently do not support their types.
- Model "landmark", field: "location", original data type: "geography"
- Model "user_location", field: "location", original data type: "geography"
The schema.prisma looks like this:
Copy code
model landmark {
  id          Int        @default(autoincrement()) @id
  // This type is currently not supported.
  // location geography?
  name        String?
  type        String?
}

model spatial_ref_sys {
  auth_name String?
  auth_srid Int?
  proj4text String?
  srid      Int     @id
  srtext    String?
}

model user_location {
  // This type is currently not supported.
  // location geography?
  user_id     Int        @id
}
I have my db hosted on heroku (with Postgres extension installed). My .env looks like:
Copy code
DATABASE_URL="<postgresql://fdkbxsadlorxmc:<mypasswordishere>@ec2-34-225-162-157.compute-1.amazonaws.com:5432/deoq5qsn6u8k07>"
And at this point I'm kind of stuck. A couple of questions come up: 0. How can I query if the type geography is not supported? 1. Am I on the right track? 2. How do I write queries and mutations if I cannot connect to the server like I did when I set up prisma alongside graphql yoga? 3. I planned to use apollo. Is that an alternative to @prisma/client or are they related? THANK YOU for your help 🙏
r
How can I query if the type geography is not supported?
You cannot currently. This is a limitation in Prisma currently and so you can only perform any operations on the type but cannot fetch it for the time being.
Am I on the right track?
Yes. You can check this out if it helps (still not final though) 🙂
How do I write queries and mutations if I cannot connect to the server like I did when I set up prisma alongside graphql yoga?
You can use the same example above. Although it uses Express, you can use any server like Apollo Server or GrapQL Yoga.
I planned to use apollo. Is that an alternative to @prisma/client or are they related?
@prisma/client
is just a tool that helps you query your database. Apollo is a server that you use to serve your GraphQL API. Both work well together so you wouldn't have any issues there.
s
Thanks @Ryan - super helpful!
💯 1
Hey @Ryan - one more follow up question. As I add new columns to my database, I add something like this to seed.sql:
Copy code
create table "public"."Store" (
  id serial primary key,
  "name" text not null
);
then I run:
Copy code
npm run seed
and my database has the columns. Unless, I'm missing something, It seems to defeat the purpose of building relations in the schema.prisma file, because for example, I'd like to create a store model with relations to storeOwners like so:
Copy code
model Store {
  id   Int    @default(autoincrement()) @id
  name String
  owners [StoreOwner]
}
And if the table column does not exist, then it obviously will not work. My questions are: 1. Is there a better way to doing this so that my schema.prisma can inform my development, rather than my seed.sql 2. If not, can you point me to an example of a seed.sql file that handles relationships like the one I'm hoping to constuct in my model Store {...} I liked how in prisma1, I could run prisma deploy and didn't have to bother with writing relationships between data and the tables would just appear in my database. I imagine that's available in prisma2, but can't see it (or perhaps it's not possible with my use case where I need to include PostGIS functionality) Thanks!! 🙏
r
Hey @Steve Jenkins 👋
Is there a better way to doing this so that my schema.prisma can inform my development, rather than my seed.sql
Unfortunately as Prisma doesn't support the Geography type of Postgres natively, you would have to do that in SQL as of now as the Prisma schema wouldn't understand that datatype.
If not, can you point me to an example of a seed.sql file that handles relationships like the one I'm hoping to constuct in my model Store {...}
I could help you model that if you could share what are you looking for 🙂
s
Hi @Ryan that'd be nice, so I can get started and build on the knowledge you share with me. My use case is not too complicated. I have stores that have items. Each store has multiple items. Each item can only belong to one store. I'd like my schema.prisma file to look like this:
Copy code
model Item {
  createdAt   DateTime?
  description String?
  id          Int       @default(autoincrement()) @id
  image       String?
  largeImage  String?
  price       Int?
  title       String
  updatedAt   DateTime?
}

model Store {
  id   Int    @default(autoincrement()) @id
  name String
  items [Item]
}
I'm using the seed.sql file to seed the database and imagine I need to write something like this:
Copy code
create table "public"."Item" (
  id serial primary key,
  "title" text not null,
  "description" text,
  "price" int,
  "image" text,
  "largeImage" text,
  "createdAt" timestamp,
  "updatedAt" timestamp
  < ADD RELATION TO STORE HERE >
);

create table "public"."Store" (
  id serial primary key,
  "name" text not null
  < ADD RELATION TO ITEMS HERE >
);
Any nudge in the right direction would be super helpful. Also, I'm dropping the table each time I need to update the database because I read the migrations were experimental and not to be used in production. Is this the correct way to go about it?
r
Also, I'm dropping the table each time I need to update the database because I read the migrations were experimental and not to be used in production
You don't need to drop the tables every time to perform a migration. Also as you will be using Postgis, I would suggest doing it with SQL files itself for the time being instead of Migrate. To create the above relation, you would need to do the following:
Copy code
create table "public"."Store" (
  id serial primary key,
  "name" text not null
);

create table "public"."Item" (
  id serial primary key,
  "title" text not null,
  "description" text,
  "price" int,
  "image" text,
  "largeImage" text,
  "createdAt" timestamp,
  "updatedAt" timestamp,
  "storeId" int references "public"."Store" ("id")
);
On seeding the above SQL and then running 
prisma introspect
you should be able to see the models created in your 
schema.prisma
. Also regarding dropping tables that you have mentioned above, I would suggest creating different seed files for migrations and keeping them in a folder like 
migrations
. So when you create the first tables, you could name the file seed0001.sql and then run the SQL file. After that, if you need to add another table you can create a seed0002.sql and create your table and then run this one. If you need to alter a table, don't edit the previous file in which the table was created but create a new seed file with an increased number and run that. Think of migrations as incremental changes that you add to your database. So you do not need to keep a single SQL file. One file for every change 🙂
s
@Ryan Thanks this is SUPER helpful 🙂
💯 1
Open in Slack
PreviousNext
Powered by