Unable to parse Connection String in multi hostname MongoDB

[mrtom]

Bug description

Cannot connect successfully to mongodb when using a URL with a replica set specified.

• Removing the repeated host:port portion(s) for the replica set and instead just setting a single node works fine.
• The full URL with the replica set works fine when used with mongo on the command line.

Further details: A new project using Prisma 3.9.1 (https://registry.yarnpkg.com/prisma/-/prisma-3.9.1.tgz#7510a8bf06018a5313b9427b1127ce4750b1ce5c)

My mongodb connection URL, as printed from process.env.DATABASE_URL within my running program is as per the following (with some bits redacted, obviously).

This works perfectly if I connect using mongo on the command line.

When running in node and using const prisma = new PrismaClient(); I get the following error:

(node:50866) UnhandledPromiseRejectionWarning: Error: The provided database string is invalid. Unable to parse URL. in database URL. Please refer to the documentation in [https://www.prisma.io/docs/reference/database-reference/connection-urls]() for constructing a correct connection string. In some cases, certain characters must be escaped. Please check the string for any illegal characters.
    at [/path/to/my/app/project/node_modules/]()@prisma[/client/runtime/index.js:36300:21]()

When running npx prisma db pull --force using the CLI I get a different error:

project (main*) » npx prisma db pull --force                                                                                                                                                                                            Prisma schema loaded from prisma/schema.prisma
Environment variables loaded from .env
Datasource "db"

Introspecting based on datasource defined in prisma/schema.prisma …

Error: invalid port number in database URL

My database URL, as printed to the console via process.env.DATABASE_URL immediately prior to initing the PrismaClient is as follows:

mongodb://user:password@my-shard-00.atlas.mongodb.net:27017,my-shard-01.atlas.mongodb.net:27017,my-shard-02.atlas.mongodb.net:27017/database?ssl=true&replicaSet=atlas-xxxxx-shard-0&authSource=admin&retryWrites=true

How to reproduce

1. Follow the Prisma getting started guide to create a new project using Mongo, from here.
2. Use a Mongo Atlas connection URL with a replica set
3. Cannot connect either via the prisma CLI tool or using the Prisma Client from within node.

Expected behavior

To be able to connect to a replica set.

Prisma information

From schema.prisma:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["mongoDb"]
}

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

Environment & setup

• OS: MacOS 12.1
• Database: Mongodb Atlas
• Node.js version: v14.19.0

Prisma Version

prisma                  : 3.9.1
@prisma/client          : 3.9.1
Current platform        : darwin
Query Engine (Node-API) : libquery-engine bcc2ff906db47790ee902e7bbc76d7ffb1893009 (at node_modules/@prisma/engines/libquery_engine-darwin.dylib.node)
Migration Engine        : migration-engine-cli bcc2ff906db47790ee902e7bbc76d7ffb1893009 (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine    : introspection-core bcc2ff906db47790ee902e7bbc76d7ffb1893009 (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary           : prisma-fmt bcc2ff906db47790ee902e7bbc76d7ffb1893009 (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash    : bcc2ff906db47790ee902e7bbc76d7ffb1893009
Studio                  : 0.457.0
Preview Features        : mongoDb

[Jolg42]

I can reproduce using the following schema

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["mongoDb"]
}

datasource db {
  provider = "mongodb"
  url      = "mongodb://user:password@my-shard-00.atlas.mongodb.net:27017,my-shard-01.atlas.mongodb.net:27017,my-shard-02.atlas.mongodb.net:27017/database?ssl=true&replicaSet=atlas-xxxxx-shard-0&authSource=admin&retryWrites=true"
}

model User {
  id    String  @id @default(auto()) @map("_id") @db.ObjectId
  email String  @unique
}

This from DEBUG="*" npx prisma db pull CLI

❯ DEBUG="*" npx prisma db pull
[...]
Introspecting based on datasource defined in prisma/schema.prisma …
  prisma:introspectionEngine:rpc starting introspection engine with binary: /Users/j42/Dev/repro/11684/node_modules/@prisma/engines/introspection-engine-darwin +0ms
  prisma:introspectionEngine:rpc SENDING RPC CALL {"id":1,"jsonrpc":"2.0","method":"introspect","params":[{"schema":"generator client {\n  provider        = \"prisma-client-js\"\n  previewFeatures = [\"mongoDb\"]\n}\n\ndatasource db {\n  provider = \"mongodb\"\n  url      = \"mongodb://user:password@my-shard-00.atlas.mongodb.net:27017,my-shard-01.atlas.mongodb.net:27017,my-shard-02.atlas.mongodb.net:27017/database?ssl=true&replicaSet=atlas-xxxxx-shard-0&authSource=admin&retryWrites=true\"\n}\n"}]} +7ms
  prisma:introspectionEngine:rpc {
  prisma:introspectionEngine:rpc   jsonrpc: '2.0',
  prisma:introspectionEngine:rpc   error: {
  prisma:introspectionEngine:rpc     code: 4466,
  prisma:introspectionEngine:rpc     message: 'An error happened. Check the data field for details.',
  prisma:introspectionEngine:rpc     data: {
  prisma:introspectionEngine:rpc       is_panic: false,
  prisma:introspectionEngine:rpc       message: 'invalid port number in database URL',
  prisma:introspectionEngine:rpc       backtrace: null
  prisma:introspectionEngine:rpc     }
  prisma:introspectionEngine:rpc   },
  prisma:introspectionEngine:rpc   id: 1
  prisma:introspectionEngine:rpc } +6ms

Error: Error: invalid port number in database URL

    at Object[...]

and for the client

[...]
  prisma:client:libraryEngine sending request, this.libraryStarted: false +29ms
  prisma:client:libraryEngine Searching for Query Engine Library in /Users/j42/Dev/repro/11684/node_modules/.prisma/client +1ms
  prisma:client:libraryEngine loadEngine using /Users/j42/Dev/repro/11684/node_modules/.prisma/client/libquery_engine-darwin.dylib.node +0ms
  prisma:client:libraryEngine library starting +12ms
  prisma:client:fetcher Error: The provided database string is invalid. Unable to parse URL. in database URL. Please refer to the documentation in https://www.prisma.io/docs/reference/database-reference/connection-urls for constructing a correct connection string. In some cases, certain characters must be escaped. Please check the string for any illegal characters.
  prisma:client:fetcher     at /Users/j42/Dev/repro/11684/node_modu
[...]

[Jolg42]

Note that it could be that it's an old format that we don't need to support (someone with more knowledge here could help) though a "better" error could help here.

Examples from Atlas UI The connection string looks similar as this issue, but only for Node.js and "Version" = "2.2.12 or later"

The connection string looks similar as this issue, but only for MongoDB Shell and "Version" = "3.4 or earlier"

[Jolg42]

Note: From mongoDB docs

The standard URI connection scheme has the form:
mongodb://[username:password@]host1[:port1][,...hostN[:portN]][/[defaultauthdb][?options]]

[Jolg42]

So it seems here that URLs in this format are old / obsolete and were used with MongoDB versions <4 (@matthewmueller will check this)

mongodb://user:password@my-shard-00.atlas.mongodb.net:27017,my-shard-01.atlas.mongodb.net:27017,my-shard-02.atlas.mongodb.net:27017/database?retryWrites=true

and can be replaced with the new mongodb+srv:// scheme which make the URL much shorter like:

mongodb+srv://user:password@cluster0.atlas.mongodb.net/database?retryWrites=true

[mrtom]

Thanks for the info @Jolg42 that's news to me!

But whilst the 'old' format looks like it's been superseded it's still in the docs as the 'standard' format and, as far as I know, hasn't been deprecated. So I would expect Prisma to support it still.

(Hopefully that reasoning is factually accurate - given I hadn't realised there was a newer format I might be wrong that the format we're using isn't deprecated!)

[rtsp]

So it seems here that URLs in this format are old / obsolete and were used with MongoDB versions <4 (@matthewmueller will check this)

mongodb://user:password@my-shard-00.atlas.mongodb.net:27017,my-shard-01.atlas.mongodb.net:27017,my-shard-02.atlas.mongodb.net:27017/database?retryWrites=true

and can be replaced with the new mongodb+srv:// scheme which make the URL much shorter like:

mongodb+srv://user:password@cluster0.atlas.mongodb.net/database?retryWrites=true

Currently, MongoDB drivers accept 2 connection string URI format

1. Standard Connection String Format (comma separated) is not the old format. It is a standard format and still widely used on non-Atlas deployment.
2. DNS Seed List Connection Format (mongodb+srv://) is the alternate format that need specific DNS record and it's not always available on non-Atlas environment.

I'm pretty sure that the Standard Connection String Format isn't going to be deprecated in near future because in order to use the DNS Seed List Connection Format, you need to setup the DNS server providing SRV records just to serve the a MongoDB replica set.

IMO, the DNS Seed List Connection Format is more suitable for public cloud deployment as the public cloud provider usually provide a managed DNS service but for on-premise/non-cloud deployment, it's not that practical to use this format.

[matthewmueller]

Hey @rtsp and @mrtom, what version of MongoDB server area you running? Are you above 4.2?

[rtsp]

Hey @rtsp and @mrtom, what version of MongoDB server area you running? Are you above 4.2?

Yep, I'm running MongoDB 5.0 PSA Replica Cluster on private coud.

[Jolg42]

@matthewmueller It sounds to me that the DNS Seed list (with +srv):

• is something Atlas does by default but
• is optional
• some hosts / custom hosting might not support it / not implement it

So the standard connection string format here always works, and we should support it.

[mrtom]

We're on 4.4 and using Atlas, but agreed that we would prefer to use the standard format for now as it's a fairly large job for us to move all our various services over to the new format.

[janpio]

Current implementation of this error message is here: https://github.com/prisma/prisma-engines/blob/b70a888e76ca5f3bd704f64afd8d03745a5c9205/query-engine/connectors/mongodb-query-connector/src/interface/mod.rs#L32-L37

[matthewmueller]

Thanks for the comments. I now agree we should support the multi hostname URL format.

My main concern was even if we supported it, your MongoDB version would be too old. We support MongoDB 4.2+ though so once we support this URL format, Prisma should work for you!

[janpio]

(I created a new issues without the information finding and discussion under https://github.com/prisma/prisma/issues/11873 - when that work is done, we can probably also close this issue)