prisma: Migration failed on `ERROR: type "geometry" does not exist`

Bug description

Given the following schema.prisma

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
  binaryTargets = ["native", "rhel-openssl-1.0.x"]
}

model TestModel {
  id Int @id
  position Unsupported("geometry")
}

If I try to run yarn prisma migrate dev.

I get the following error:

Database error:
db error: ERROR: type "geometry" does not exist

But it generates the following migration file:

-- CreateTable
CREATE TABLE "TestModel" (
    "id" INTEGER NOT NULL,
    "position" geometry NOT NULL,

    PRIMARY KEY ("id")
);

Which if I run manually on DB it passes.

How to reproduce

use my prisma schema
create following docker-compose.yml file

version: '3'

services:
  db:
    image: supabase/postgres
    ports:
      - "5432:5432"
    environment:
      POSTGRES_PASSWORD: postgres

run yarn prisma migrate dev

Expected behavior

database should be created.

Prisma information

DATABASE_URL=$MIGRATE_DATABASE_URL prisma migrate dev
  prisma:loadEnv project root found at /Users/michalkvasnicak/Work/brosoft/labka/package.json +0ms
  prisma:tryLoadEnv Environment variables loaded from /Users/michalkvasnicak/Work/brosoft/labka/.env +0ms
[dotenv][DEBUG] did not match key and value when parsing line 1: # Environment variables declared in this file are automatically made available to Prisma.
[dotenv][DEBUG] did not match key and value when parsing line 2: # See the documentation for more detail: https://pris.ly/d/prisma-schema#using-environment-variables
[dotenv][DEBUG] did not match key and value when parsing line 3:
[dotenv][DEBUG] did not match key and value when parsing line 4: # Prisma supports the native connection string format for PostgreSQL, MySQL and SQLite.
[dotenv][DEBUG] did not match key and value when parsing line 5: # See the documentation for all the connection string options: https://pris.ly/d/connection-strings
[dotenv][DEBUG] did not match key and value when parsing line 6:
[dotenv][DEBUG] "DATABASE_URL" is already defined in `process.env` and will not be overwritten
Environment variables loaded from .env
  prisma:engines using NAPI: false +0ms
  prisma:engines binaries to download query-engine, migration-engine, introspection-engine, prisma-fmt +0ms
Prisma schema loaded from prisma/schema.prisma
  prisma:getConfig Using Query Engine Binary at: /Users/michalkvasnicak/Work/brosoft/labka/node_modules/@prisma/engines/query-engine-darwin +0ms
Datasource "db": PostgreSQL database "postgres", schema "public" at "db.iqhvqssjbcjoyluwamks.supabase.co:5432"

  prisma:getDMMF Using Query Engine Binary at: /Users/michalkvasnicak/Work/brosoft/labka/node_modules/@prisma/engines/query-engine-darwin +0ms
  prisma:getConfig Using Query Engine Binary at: /Users/michalkvasnicak/Work/brosoft/labka/node_modules/@prisma/engines/query-engine-darwin +45ms
  prisma:getConfig Using Query Engine Binary at: /Users/michalkvasnicak/Work/brosoft/labka/node_modules/@prisma/engines/query-engine-darwin +15ms
  prisma:migrateEngine:rpc starting migration engine with binary: /Users/michalkvasnicak/Work/brosoft/labka/node_modules/@prisma/engines/migration-engine-darwin +0ms
  prisma:migrateEngine:rpc SENDING RPC CALL {"id":1,"jsonrpc":"2.0","method":"devDiagnostic","params":{"migrationsDirectoryPath":"/Users/michalkvasnicak/Work/brosoft/labka/prisma/migrations"}} +5ms
  prisma:migrateEngine:stderr Jun 04 16:39:44.862  INFO migration_engine: Starting migration engine RPC server git_hash="18095475d5ee64536e2f93995e48ad800737a9e4" +0ms
  prisma:migrateEngine:stderr Jun 04 16:39:45.114  INFO quaint::single: Starting a postgresql connection. +252ms
  prisma:migrateEngine:stderr Jun 04 16:39:45.224  INFO DevDiagnostic:calculate_drift:sql_schema_from_migration_history: quaint::single: Starting a postgresql connection. +110ms
  prisma:migrateEngine:stderr Jun 04 16:39:45.224  INFO DevDiagnostic:calculate_drift:sql_schema_from_migration_history: sql_migration_connector::flavour::postgres: Connecting to user-provided shadow database at localhost.Some("postgres") +0ms
  prisma:migrateEngine:stderr Jun 04 16:39:45.785  INFO DevDiagnostic:validate_migrations:sql_schema_from_migration_history: quaint::single: Starting a postgresql connection. +560ms
  prisma:migrateEngine:stderr Jun 04 16:39:45.785  INFO DevDiagnostic:validate_migrations:sql_schema_from_migration_history: sql_migration_connector::flavour::postgres: Connecting to user-provided shadow database at localhost.Some("postgres") +0ms
  prisma:migrateEngine:rpc {
  prisma:migrateEngine:rpc   jsonrpc: '2.0',
  prisma:migrateEngine:rpc   error: {
  prisma:migrateEngine:rpc     code: 4466,
  prisma:migrateEngine:rpc     message: 'An error happened. Check the data field for details.',
  prisma:migrateEngine:rpc     data: {
  prisma:migrateEngine:rpc       is_panic: false,
  prisma:migrateEngine:rpc       message: 'Migration `20210604143732_test` failed to apply cleanly to the shadow database. \n' +
  prisma:migrateEngine:rpc         'Error:\n' +
  prisma:migrateEngine:rpc         'Database error\n' +
  prisma:migrateEngine:rpc         'Error querying the database: db error: ERROR: type "geometry" does not exist\n' +
  prisma:migrateEngine:rpc         '   0: sql_migration_connector::flavour::postgres::sql_schema_from_migration_history\n' +
  prisma:migrateEngine:rpc         '             at migration-engine/connectors/sql-migration-connector/src/flavour/postgres.rs:280\n' +
  prisma:migrateEngine:rpc         '   1: sql_migration_connector::sql_database_migration_inferrer::validate_migrations\n' +
  prisma:migrateEngine:rpc         '             at migration-engine/connectors/sql-migration-connector/src/sql_database_migration_inferrer.rs:88\n' +
  prisma:migrateEngine:rpc         '   2: migration_core::api::DevDiagnostic\n' +
  prisma:migrateEngine:rpc         '             at migration-engine/core/src/api.rs:95',
  prisma:migrateEngine:rpc       meta: [Object],
  prisma:migrateEngine:rpc       error_code: 'P3006'
  prisma:migrateEngine:rpc     }
  prisma:migrateEngine:rpc   },
  prisma:migrateEngine:rpc   id: 1
  prisma:migrateEngine:rpc } +934ms
Error: Error: P3006

Migration `20210604143732_test` failed to apply cleanly to the shadow database.
Error:
Database error
Error querying the database: db error: ERROR: type "geometry" does not exist
   0: sql_migration_connector::flavour::postgres::sql_schema_from_migration_history
             at migration-engine/connectors/sql-migration-connector/src/flavour/postgres.rs:280
   1: sql_migration_connector::sql_database_migration_inferrer::validate_migrations
             at migration-engine/connectors/sql-migration-connector/src/sql_database_migration_inferrer.rs:88
   2: migration_core::api::DevDiagnostic
             at migration-engine/core/src/api.rs:95

    at Object.<anonymous> (/Users/michalkvasnicak/Work/brosoft/labka/node_modules/prisma/build/index.js:57455:26)
    at MigrateEngine.handleResponse (/Users/michalkvasnicak/Work/brosoft/labka/node_modules/prisma/build/index.js:57330:38)
    at LineStream.<anonymous> (/Users/michalkvasnicak/Work/brosoft/labka/node_modules/prisma/build/index.js:57415:18)
    at LineStream.emit (events.js:314:20)
    at LineStream.EventEmitter.emit (domain.js:483:12)
    at addChunk (_stream_readable.js:297:12)
    at readableAddChunk (_stream_readable.js:272:9)
    at LineStream.Readable.push (_stream_readable.js:213:10)
    at LineStream.Transform.push (_stream_transform.js:152:32)
    at LineStream._pushBuffer (/Users/michalkvasnicak/Work/brosoft/labka/node_modules/prisma/build/index.js:57205:19)

Environment & setup

OS: MacOS 11.4 (20F71)
Database: supabase/postgres
Node.js version: 12.21.0

Prisma Version

prisma               : 2.24.1
@prisma/client       : 2.24.1
Current platform     : darwin
Query Engine         : query-engine 18095475d5ee64536e2f93995e48ad800737a9e4 (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine     : migration-engine-cli 18095475d5ee64536e2f93995e48ad800737a9e4 (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core 18095475d5ee64536e2f93995e48ad800737a9e4 (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary        : prisma-fmt 18095475d5ee64536e2f93995e48ad800737a9e4 (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash : 18095475d5ee64536e2f93995e48ad800737a9e4
Studio               : 0.397.0

About this issue

Original URL
State: closed
Created 3 years ago
Comments: 20 (16 by maintainers)

Most upvoted comments

Yes — at a minimum, we should document this. There might be more we can do there, but it doesn’t look easy. Glad you figured it out @michalkvasnicak 😃

tomhoule on Jun 14, 2021

Appending

CREATE SCHEMA IF NOT EXISTS "extensions";

CREATE EXTENSION IF NOT EXISTS "postgis" WITH SCHEMA "extensions";

to the top of initial migration fixes the problem locally and on supabase.

Thank you @tomhoule for pointing me to the right direction.

michalkvasnicak on Jun 14, 2021

Ok manually changing type sql file to extensions.geometry fixes the problem.

CREATE TABLE "TestModel" (
    "id" INTEGER NOT NULL,
-   "position" geometry NOT NULL,
+  "position" extensions.geometry NOT NULL,

    PRIMARY KEY ("id")
);

michalkvasnicak on Jun 14, 2021

Hi @michalkvasnicak — is the CREATE EXTENSION 'postgis'; in a migration at the beginning of your migrations history (before you use things enabled by postgis)? If not, this could be the cause.

tomhoule on Jun 8, 2021