https://www.prisma.io/ logo
Join Slack
Powered by
After installing an extension through prisma-migra...
# prisma-migrate
s
After installing an extension through prisma-migrate, I can find it via 
SELECT * FROM pg_extension
but it doesn’t work for my schema/tables.
Copy code
-- migration.sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
It does work if I manually install it through a terminal with admin user What is the right way to install an extension? Should I run migrate with admin privileges? PS: I am using a schema… does that matter?
So I figured out that the extension was created under the global namespace but the search path of prisma is always the schema I define in my connection string. That’s why I cant find the extension.
How do I enable the extension to work with prisma? Without knowing the schema?
(with schema I mean the postgres schema)
👍 1
d
Hey Sven, I’ve had success adding the following the the migration
Copy code
CREATE EXTENSION postgis;
But I haven’t tired using 
pg_trgm
Can you share the output of 
pg_available_extensions
?
s
it’s in there!
👍 1
the issue is really the search_path
💡 1
Ryan had a solution
calling the extensions’ functions with the 
public.
schema
👍 1
this took me 3 hours
can I add it to the docs somewhere?
d
calling the extensions’ functions with the 
public.
  schema
Can you give a more concrete example of what you mean here? Do you call the extensions’ functions in the migration or in raw SQL queries?
s
for example:
Copy code
SELECT *, public.similarity(title, ${query}::text) As score
FROM "Book"
WHERE public.similarity(title, ${query}::text) > 0.2
ORDER BY score DESC
💯 1
it uses the similarity function from 
pg_trgm
👍 1
which is installed on the public schema
d
Got it! So it’s basically calling the function from the extension that is enabled on the 
public
PostgreSQL schema. I wonder, is there no way to install the extension in a specific schema?
s
yes there is
CREATE EXTENSION pg_trgm SCHEMA mySchema;
in our case we create schema’s dynamically
each PR gets their own database setup, so a schema can be 
pr-48
d
Gotcha!
Thanks for sharing! Glad you were able to figure this out
s
not sure if it fits on that page (customizing migrations), it’s too postgres specific
d
Yeah now I realised this after your explanation
2 Views
Open in Slack
PreviousNext
Powered by