Skip to main content

Postgres extensions

Overview​

Prisma Postgres supports the following PostgreSQL extensions:

Support for more extensions will follow soon. If there are specific extensions you'd like to see in Prisma Postgres, fill out this form.

warning

Postgres extensions support in Prisma Postgres is currently in Early Access and not yet recommended for production scenarios.

Using extensions with Prisma ORM​

Some extensions may already be supported by Prisma Postgres but not yet by Prisma ORM. Native support for some Postgres extensions in Prisma ORM is coming soon. In the meantime, you can still use these extensions with Prisma ORM by using customized migrations and TypedSQL (or another mechanism to send raw SQL via in Prisma ORM).

Let's walk through an example with pgvector.

1. Create an empty migration file​

To customize a migration, first create an empty migration file:

npx prisma migrate dev --name add-pgvector --create-only

Notice the --create-only flag which will create an empty migration file in your migrations directory.

2. Create and use the extension in your migration file​

In the empty migration file, you can write any custom SQL to be executed in the database:

-- prisma/migrations/<timestamp>-add-pgvector/migration.sql
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE "Document" (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  embedding VECTOR(4) -- use 4 for demo purposes; real-world values are much bigger
);

In this case, you:

  • install the pgvector extension in your database using the CREATE EXTENSION statement
  • create a Document table that uses the VECTOR type from that extension

3. Execute the migration against the database​

Run the following command to execute the migration and apply its changes in your database:

npx prisma migrate deploy

This command will apply the pending prisma/migrations/<timestamp>-add-pgvector/migration.sql migration and create the Document table in your database.

4. Pull the document table into your Prisma schema​

Introspect the database schema with the new Document table and update your Prisma schema with it:

npx prisma db pull
Show CLI results
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "postgres", schema "public" at "accelerate.prisma-data.net"

✔ Introspected 3 models and wrote them into prisma/schema.prisma in 3.23s
      
*** WARNING ***

These fields are not supported by Prisma Client, because Prisma currently does not support their types:
  - Model: "Document", field: "embedding", original data type: "vector"

Run prisma generate to generate Prisma Client.

The warning in the CLI output of the command is expected because Prisma ORM doesn't natively support the VECTOR type yet.

You Prisma schema will now contain the Document model:

model Document {
  id        Int                    @id @default(autoincrement())
  title     String
  embedding Unsupported("vector")?
}

Because the VECTOR type is not yet natively supported by Prisma ORM, it's represented as an Unsupported type.

4. Query with raw SQL​

Here's an example query for inserting a new row into the Document table:

await prisma.$executeRaw`
  INSERT INTO "Document" (title, embedding)
  VALUES ('My Title', '[1,22,1,42]'::vector)
`;

You can also use TypedSQL for type-safe SQL queries against your database.

Temporary limitations​

pgvector is only available on newly created Prisma Postgres instances​

For now, pgvector is only available on newly created Prisma Postgres instances. It will be rolled out for existing instances soon.

No Prisma Studio support for pgvector​

Prisma Studio currently doesn't support tables where types of the pgvector extensions are used. It will show the following error:

Show Prisma Studio error message
{
  "error": "KnownError { message: \"Raw query failed. Code: `N/A`. Message: `Failed to deserialize column of type 'vector'. If you're using $queryRaw and this column is explicitly marked as `Unsupported` in your Prisma schema, try casting this column to any supported Prisma type such as `String`.`\", meta: Object {\"code\": String(\"N/A\"), \"message\": String(\"Failed to deserialize column of type 'vector'. If you're using $queryRaw and this column is explicitly marked as `Unsupported` in your Prisma schema, try casting this column to any supported Prisma type such as `String`.\")}, error_code: \"P2010\" }",
  "user_facing_error": {
    "is_panic": false,
    "message": "Raw query failed. Code: `N/A`. Message: `Failed to deserialize column of type 'vector'. If you're using $queryRaw and this column is explicitly marked as `Unsupported` in your Prisma schema, try casting this column to any supported Prisma type such as `String`.`",
    "meta": {
      "code": "N/A",
      "message": "Failed to deserialize column of type 'vector'. If you're using $queryRaw and this column is explicitly marked as `Unsupported` in your Prisma schema, try casting this column to any supported Prisma type such as `String`."
    },
    "error_code": "P2010"
  }
}

Other extensions are coming soon​

Support for the following extensions is going to come soon:

If there are specific extensions you'd like to see, fill out this form.