js / Cannot configure Drizzle Kit with SQLite Cloud DB connection URL

[unatarajan]

Validation/ Reproduction Steps

1. Initialize a new TypeScript/ Node.js/ Drizzle project using the following instructions:

mkdir hello-drizzle
cd hello-drizzle

npm init -y
npm install typescript ts-node @types/node --save-dev
npx tsc --init

npm i drizzle-orm dotenv @libsql/client
npm i -D drizzle-kit

2. In.env, set DATABASE_URL to your SQLite Cloud DB connection string.

3. Create src/db/index.ts. Input the following code to connect Drizzle ORM to the database:

import { config } from 'dotenv';
import { drizzle } from 'drizzle-orm/libsql';
import { createClient } from '@libsql/client';

config({ path: '.env' });

const client = createClient({
  url: process.env.DATABASE_URL!,
});

export const db = drizzle(client);

4. Create drizzle.config.ts in the project root. Input the following code to configure Drizzle Kit with the database connection:

import { config } from 'dotenv';
import { defineConfig } from 'drizzle-kit';

config({ path: '.env ' });

export default defineConfig({
  schema: './src/db/schema.ts',
  dialect: 'sqlite',
  dbCredentials: {
    url: process.env.DATABASE_URL!, 
  },
});

5. In package.json, include a new script: introspect": "drizzle-kit introspect. This command pulls DDL from an existing database and generates the schema.ts.

6. npm run introspect throws LibsqlError: URL_PARAM_NOT_SUPPORTED: Unknown URL query parameter "apikey".

7. Update drizzle.config.ts with the following code:

import { config } from 'dotenv';
import { defineConfig } from 'drizzle-kit';

config({ path: '.env ' });

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'sqlite',
  dbCredentials: {
    user: 'upasana-admin',
    password: process.env.DATABASE_PWD, // used admin user's password from the SQLite Cloud dashboard
    host: 'chtwalrwiz',
    port: 8860,
    database: 'chinook.sqlite',
  },
});

8. npm run introspect throws Please provide required params: [x] url: undefined.

Notes

• Per this config doc, dbCredentials > SQLiteCredentials, the url can be a path to a local sqlite file. So Drizzle expects a SQLite DB connection string to point to a project DB file.
• Drizzle dialects: postgresql, mysql, sqlite.
  Drizzle allows 2 ways to set dbCredentials (see steps 4 and 7 above). For non-sqlite dialects, you can use either configuration. However, sqlite dialect only works with url (see step 8 error message).

Related Issue(s)

sample content / JavaScript & Drizzle

Screenshots & Videos

N/A.
I'd attach my own project to this comment, but GitHub doesn't allow it. Happy to share over Slack.