Improper escaping of string containing quotes for `introspect` on SQLite (libsql)

[bradobro]

Summary: pulling schema from a local SQLite database creates schema statements that don't parse as TypeScript.

Example:

// generates field definitions including this:
{
    type: text("type").default("sql`("base")`").notNull(),
        //  unescaped double quotes:     ^        ^  break parsing.  
}

Environment:

Package Versions: "drizzle-kit": "^0.21.2" "drizzle-orm": "^0.30.10" "@libsql/client": "^0.6.0" "typescript": "^5.0.0"

Runtimes:

• Node.js v21.7.1
• Bun 1.1.8

OS/Hardware:

• MacOS Sonoma 14.4.1
• MacBook Air M1

Recreating Here's a SQL to create a table that will cause some offenders:

CREATE TABLE `_collections` (
                `id`         TEXT PRIMARY KEY NOT NULL,
                `system`     BOOLEAN DEFAULT FALSE NOT NULL,
                `type`       TEXT DEFAULT "base" NOT NULL,
                `created` TEXT DEFAULT "" NOT NULL,
                `updated` TEXT DEFAULT "" NOT NULL,
                `name`       TEXT UNIQUE NOT NULL,
                `schema`     JSON DEFAULT "[]" NOT NULL,
                `indexes`    JSON DEFAULT "[]" NOT NULL,
                `listRule`   TEXT DEFAULT NULL,
                `viewRule`   TEXT DEFAULT NULL,
                `createRule` TEXT DEFAULT NULL,
                `updateRule` TEXT DEFAULT NULL,
                `deleteRule` TEXT DEFAULT NULL,
                `options`    JSON DEFAULT "{}" NOT NULL
            );

[bradobro]

I suspect the real culprit is the software creating the table, that if it had used single quotes for the default strings drizzle-kit would have generated valid code.

But SQLite seems to allow--and preserve--this, which isn't likely to change quickly.

I wonder if there's a simple but robust workaround for this? Maybe generate code for strings with JSON.stringify(thestring);?

For example:

const desiredString = 'sql`("base")`';
const jsonString = JSON.stringify(desiredString);
console.log(desiredString, jsonVersion);
// output: sql`("base")` "sql`(\"base\")`"