PostGIS in a separate schema
I have seen it advised to install postgis in a separate schema, not the public schema.
However, I am running into errors such as "error: function st_dwithin(extensions.geometry, unknown, numeric) does not exist" when trying to query using PostGIS functions.
Does Drizzle support extensions installed in schemas other than public?
If so, how do you set the search_path with Drizzle (I understand it's on a session basis)?
3 Replies
Drizzle doesn't support having extensions in another schema. However, in the generated migration file you can add the schema prefix manually (like
col1 extensions_schema.geometry). For functions, you'd have to use the sql operator to also prefix them with the schema.
If you want to change the search_path, you could run db.execute(sql/* query to set the search path */) right after you'd initialized the connectionThanks @Mario564. I've ended up doing the latter (search_path with the extensions installed in a dedicated schema). I set the search_path in a migration via:
The first is to set the search_path for the session (aka running the migrations without having to declare
extensions.extension_field and the second is to set the search path permanently. You can't just use the latter as it requires a connection reset to take effect.
Seems to be working!Sounds good 👍