CLI Starter create-directus-project: Allow for specifying options param in db connection

[jpizzle34]

When quickstarting a new project with:

pnpm create directus-project <my-project-name>

I would like to connect to a CockroachDB Serverless cluster which requires a specific cluster to be specified in the options parameter of the connection string:

e.g. postgresql://<db-user>:<db-password>@free-tier8.aws-ap-southeast-1.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&options=--cluster%3D<cluster-identifier>

The problem is that the CLI only accepts db connection parameters and not a connection string, and the parameters that are asked for don't include an options string.

Proposed Solution

1. Extend the Credentials interface to include an options?: string property:
   

   directus/api/src/cli/utils/create-db-connection.ts

   Lines 5 to 14 in cbe1e7f

   	export type Credentials = {
   	filename?: string;
   	host?: string;
   	port?: number;
   	database?: string;
   	user?: string;
   	password?: string;
   	ssl?: boolean;
   	options__encrypt?: boolean;
   	};

2. Set the options property here exposing Knex.PgConnectionConfig:

   directus/api/src/cli/utils/create-db-connection.ts

   Lines 38 to 41 in cbe1e7f

   	if (client === 'pg' || client === 'cockroachdb') {
   	const { ssl } = credentials as Credentials;
   	connection['ssl'] = ssl;
   	}

3. Add an optional question in the cli asking for an options string if the dbclient is pg or cockroachdb here: https://github.com/directus/directus/blob/cbe1e7fbc69c8e9bd02254e9ed6efc35ba6468ef/api/src/cli/commands/init/questions.ts

Why?

This will make it alot easier for developers new to Directus to bootstrap a new project, especially those wanting to connect to a multi-tennant, serverless database offering like CockroachDB Serverless.

Alternative Solutions

1. Do a manual installation as outlined below, setting the DB_CONNECTION_STRING env var before running pnpm directus bootstrap: https://docs.directus.io/self-hosted/installation/manual.html
2. In the CLI starter, give the developer an option to specify a db connection string instead of parameters.

Originally posted by @jpizzle34 in #15848

[rijkvanzanten]

The problem is that the CLI only accepts db connection parameters and not a connection string, and the parameters that are asked for don't include an options string.

Your current solution (thanks for that btw! :) ) is to add the options field as another input, but wouldn't it make more sense to drop all those flags in favor of asking for the connection string? 🤔 The options format is a little unintuitive, and CockroachDB's hosted version will default to giving you the connection string anyways, so it feels like that might make the integration a tad smoother :)

[jpizzle34]

Hi @rijkvanzanten. No worries, thanks for the feedback.

Yes, I agree with everything you said. Asking for a connection string would definitely be the most ergonomic solution. Most DB services, including CRDB as you noted, provide them.

That was my initial thinking too, but I chose to go the low impact route with this PR to minimize changes to the codebase 😅. I'll take another shot at it with the aim of replacing all flags in favor of asking for the connection string as you suggested. I'll keep you posted :)

[rijkvanzanten]

Sounds good, thanks! 💟

[rijkvanzanten]

Linear: ENG-158