Add extension postgis

I want to add the PostGIS extension to my db. I added this into my main.wasp :

  db: {
    system: PostgreSQL,
    prisma: {
      clientPreviewFeatures: ["postgresqlExtensions"],
      dbExtensions: [
        { name: "postgis", version: "3" },
      ]
    },
  },

  db: {
    system: PostgreSQL,
    prisma: {
      clientPreviewFeatures: ["postgresqlExtensions"],
      dbExtensions: [
        { name: "postgis", version: "3" },
      ]
    },
  },

Problem:

db error: ERROR: extension "postgis" has no installation script nor update path for version "3"

db error: ERROR: extension "postgis" has no installation script nor update path for version "3"

So I added the installation script:

docker ps
docker exec -it [container_id] bash
apt update
apt install postgis postgresql-16-postgis-3

docker ps
docker exec -it [container_id] bash
apt update
apt install postgis postgresql-16-postgis-3

I succeeded to add the extension manually in the local database with:

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE EXTENSION IF NOT EXISTS postgis;

Problem:

Error: P3006

Migration `20240611131009_add_postgis_extension` failed to apply cleanly to the shadow database. 
Error:
db error: ERROR: extension "postgis" has no installation script nor update path for version "3"
   0: schema_core::state::DevDiagnostic
             at schema-engine/core/src/state.rs:266



:x: --- [Error] Migrate dev failed: ------------------------------------------------

Migrate (dev) failed with exit code: 1

The shadow database doesn't have the script I added to the local database.

Error: P3006

Migration `20240611131009_add_postgis_extension` failed to apply cleanly to the shadow database. 
Error:
db error: ERROR: extension "postgis" has no installation script nor update path for version "3"
   0: schema_core::state::DevDiagnostic
             at schema-engine/core/src/state.rs:266



:x: --- [Error] Migrate dev failed: ------------------------------------------------

Migrate (dev) failed with exit code: 1

The shadow database doesn't have the script I added to the local database.

So I tried something else : I buit my docker image with postgres16 + the script that I want to use locally so the database and the shadow database will have the script. I add to my env : DATABASE_URL=postgresql://postgres:mysecretpassword@localhost:5432/postgres But impossible to use my built database as "local" :

The database needs to be running in order to execute this command. You can easily start a managed dev database with `wasp start db`.

The database needs to be running in order to execute this command. You can easily start a managed dev database with `wasp start db`.

How can I add my extension please?

12 Replies

kapa.ai•3mo ago

Hi @Estelle. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave: ---------------------- It seems like you're encountering a few different issues here. Let's try to address them one by one. 1. PostGIS Extension Error: The error message you're seeing suggests that the PostGIS extension is not available for the version "3". This could be due to a mismatch between the PostGIS version and the PostgreSQL version you're using. You might want to check the compatibility between the two and ensure you're using a supported version of PostGIS for your PostgreSQL version. 2. Shadow Database Issue: The shadow database is a temporary database that Prisma Migrate uses to detect schema drift, i.e., to check if the database schema has diverged from the Prisma schema. It seems like the shadow database is not able to find the PostGIS extension. This could be because the extension is not installed in the shadow database. You might need to ensure that the extension is installed in the shadow database as well. 3. Using a Custom Docker Image: If you're trying to use a custom Docker image with PostgreSQL and PostGIS installed, you might need to ensure that the Docker container is running and accessible from Wasp. The error message suggests that Wasp is not able to connect to the database. You might want to check if the Docker container is running and if the database connection URL is correct. Unfortunately, the knowledge sources provided do not contain specific instructions on how to resolve these issues. I would recommend checking the Wasp documentation (https://wasp-lang.dev/docs/data-model/backends#prisma-configuration) and the Prisma documentation (https://www.prisma.io/docs/concepts/components/preview-features/client-preview-features) for more information on how to configure the database and use preview features. You might also want to check the PostgreSQL documentation (https://www.postgresql.org/docs/current/contrib.html) for more information on how to use extensions. If you're still encountering issues, you might want to consider opening an issue on the Wasp GitHub repository (https://github.com/wasp-lang/wasp) or asking for help in the Wasp Discord community (https://discord.com/channels/686873244791210014/). ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ---------------------- Sorry @Estelle, I seem to be having a bit of trouble. Don't worry though, I've pinged my support and they will try to fix me up! :technologist: Perhaps try again in 15mins?

Estelle•3mo ago

@miho @Vinny (@Wasp) I saw in the documentation that you used the PostGIS extension : https://wasp-lang.dev/docs/data-model/backends#postgresql-extensions What did I miss?

Databases | Wasp

Entities, Operations and Automatic CRUD together make a high-level interface for working with your app's data. Still, all that data has to live somewhere, so let's see how Wasp deals with databases.

miho•3mo ago

I've done something with PG Vector extension, it should be pretty similar https://github.com/wasp-lang/ask-the-documents

GitHub

GitHub - wasp-lang/ask-the-documents: Ask The Documents (Embeddings...

Ask The Documents (Embeddings / ChatGPT) with Wasp & PG Vector - wasp-lang/ask-the-documents

Estelle•3mo ago

So I see that you use your custom db in your env:

Fill the env variables in .env.server:

OPENAI_API_KEY=<your_key>
DATABASE_URL=postgresql://postgres:devpass@localhost:5432/postgres

Fill the env variables in .env.server:

OPENAI_API_KEY=<your_key>
DATABASE_URL=postgresql://postgres:devpass@localhost:5432/postgres

But how did you do it? I have an error message from wasp, I can't do any migration on top of it From my previous msg: " So I tried something else : I buit my docker image with postgres16 + the script that I want to use locally so the database and the shadow database will have the script. I add to my env : DATABASE_URL=postgresql://postgres:mysecretpassword@localhost:5432/postgres But impossible to use my built database as "local" :

The database needs to be running in order to execute this command. You can easily start a managed dev database with `wasp start db`.

The database needs to be running in order to execute this command. You can easily start a managed dev database with `wasp start db`.

" How do you use your custom database locally with Wasp? I tested your repo: it works. I tested my db on your repo: it works. I tested my db on my repo: it doesn't want to run the db-migrate without having me run "wasp db start" eventhough I have my env filled. What did you do in your repo so wasp uses you custom db when running 'wasp db migrate-dev' ? What am I missing?

miho•3mo ago

Let me see I can get PostGIS up and running 😄

Estelle•3mo ago

I succeeded to use the database locally when I delete the .wasp folder before (idk what it keeps but it would be nice that when we fill the env it knows it) I'm still fighting with PostGIS to make it work, but if you can do it before please And it would be nice to add those steps in the doc, I thought I just had to add it in the extension in main.wasp... If you can get PostGIS and running I would be so grateful 🙏

miho•3mo ago

As you might imagine, we try to predict as many configurations for DB usage as possible but when we can't cover them all 😄 we try to give you as much tools as possible When I try running a PostGIS db in my repo I get this:

2024-06-11 17:42:45.853 UTC [1] DETAIL:  The data directory was initialized by PostgreSQL version 15, which is not compatible with this version 16.3 (Debian 16.3-1.pgdg110+1).

2024-06-11 17:42:45.853 UTC [1] DETAIL:  The data directory was initialized by PostgreSQL version 15, which is not compatible with this version 16.3 (Debian 16.3-1.pgdg110+1).

Which is expected, I need to remove the old DB volume since they are incompabilble Postgre versions. Then I deleted the ask-the-documents DB volume for the DB container. At the end I used this to get it up and running: Wasp file:

dbExtensions: [{ name: "postgis" }]

dbExtensions: [{ name: "postgis" }]

(removing the version helped) Dockerfile:

FROM --platform=linux/amd64 postgis/postgis:15-3.4 AS build

COPY extensions.sql /docker-entrypoint-initdb.d

FROM --platform=linux/amd64 postgis/postgis:15-3.4 AS build

COPY extensions.sql /docker-entrypoint-initdb.d

extensions.sql

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE EXTENSION IF NOT EXISTS postgis;

MEE6•3mo ago

Wohooo @miho, you just became a Waspeteer level 34!

Estelle•3mo ago

thank you very much I wil do that 🙏 thank you it worked!!!

martinsos•3mo ago

@Estelle and @miho awesome work! @Estelle , what was the final conclusion: is there something you think we should be adding to the docs or even to Wasp that you think might help with this issue you had? Any ideas are welcome.

Estelle•3mo ago

Sorry @martinsos just saw that Maybe you can add the repo @miho send me in the doc where you show that we can add extension to the base, and explain that locally we have to use a custom db with an example 🙂

Estelle•3mo ago

Just here: https://wasp-lang.dev/docs/data-model/backends#postgresql-extensions Add some sentences to explain + this example: https://github.com/wasp-lang/ask-the-documents

GitHub

GitHub - wasp-lang/ask-the-documents: Ask The Documents (Embeddings...

Ask The Documents (Embeddings / ChatGPT) with Wasp & PG Vector - wasp-lang/ask-the-documents

Databases | Wasp

Entities, Operations and Automatic CRUD together make a high-level interface for working with your app's data. Still, all that data has to live somewhere, so let's see how Wasp deals with databases.

Gaming

Programming

Add extension postgis