drizzle-team / drizzle-team/drizzle-orm

[BUG]: `sql.raw` does not accept number or boolean in type definition

Open
#5,260 0 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
TypeScript
Stars
35.8k
Forks
1.6k
Avg merge
2d 7h
Merged PRs (30d)
4

Description

### Report hasn't been filed before.

- [x] I have verified that the bug I'm about to report hasn't been filed before.

### What version of `drizzle-orm` are you using?

0.31.8

### What version of `drizzle-kit` are you using?

Unused

### Other packages

@electric-sql/pglite@^0.3.14,@typescript/native-preview@7.0.0-dev.20260114.1

### Describe the Bug

```ts
import { drizzle } from "drizzle-orm/pglite";
import { pgTable, integer, doublePrecision } from "drizzle-orm/pg-core";
import { sql, sum, eq } from "drizzle-orm";

const configTbl = pgTable("config", {
id: integer("id").primaryKey().default(0),
limitPerPage: integer("limit_per_page").notNull().default(10),
});

// pi / 2 = 1 + sum ( n=1 to infinity ) [ n! / (2n+1)!! ]
const eulerTbl = pgTable("euler", {
n: integer("n").primaryKey(),
x: doublePrecision("x").notNull(),
});

const db = drizzle({
schema: { configTbl, eulerTbl },
});

await db.execute(sql`CREATE TABLE IF NOT EXISTS euler (
n INTEGER PRIMARY KEY NOT NULL,
x DOUBLE PRECISION NOT NULL
);`);
await db.execute(sql`CREATE TABLE IF NOT EXISTS config (
id INTEGER PRIMARY KEY NOT NULL DEFAULT 0,
limit_per_page INTEGER NOT NULL DEFAULT 10
);`);
await db.execute(sql`WITH RECURSIVE euler(n, x) AS (
SELECT 1, 2.0 / 3
UNION ALL
SELECT n + 1, x * (n + 1) / (n * 2 + 3)
FROM euler
WHERE x > 1e-15
) INSERT INTO euler (n, x)
SELECT n, x FROM euler ON CONFLICT (n) DO NOTHING;`);
await db.execute(sql`
INSERT INTO config (id, limit_per_page) VALUES (0, 10)
ON CONFLICT (id) DO NOTHING;
`);

const piQuery = db.select({ pi: sql`2 + ${sum(eulerTbl.x)}` }).from(eulerTbl);

console.log(piQuery.toSQL().sql);
console.log(`PI (full) = ${(await piQuery)[0]!.pi}`);
const limitQuery = db
.select({ limit: configTbl.limitPerPage })
.from(configTbl)
.where(eq(configTbl.id, sql.raw(0)));
const sub = db
.$with("sub")
.as(db.select({ x: eulerTbl.x }).from(eulerTbl).limit(limitQuery));
const query = db
.with(sub)
.select({ pi: sql`2 + ${sum(sub.x)}` })
.from(sub);
console.log(query.toSQL().sql);
console.log(
`PI (first ${(await limitQuery)[0]!.limit! + 1} terms): ${(await query)[0]!.pi}`,
);
const fixedSub = db
.$with("sub")
.as(db.select({ x: eulerTbl.x }).from(eulerTbl).limit(sql.raw(5)));
const fixedQuery = db
.with(fixedSub)
.select({ pi: sql`2 + ${sum(fixedSub.x)}` })
.from(fixedSub);
console.log(fixedQuery.toSQL().sql);
console.log(`PI (first 6 terms): ${(await fixedQuery)[0]!.pi}`);
```

The following output (by a recent Node.js) improves that the above code generates the correct SQL:

```
PI (full) = 3.141592653589793
with "sub" as (select "x" from "euler" limit (select "limit_per_page" from "config" where "config"."id" = 0)) select 2 + sum("sub"."x") from "sub"
PI (first 11 terms): 3.141106021601378
with "sub" as (select "x" from "euler" limit 5) select 2 + sum("sub"."x") from "sub"
PI (first 6 terms): 3.1215007215007216
```

However, TypeScript complains about type errors:

```
limit.ts:48:35 - error TS2345: Argument of type 'number' is not assignable to parameter of type 'string'.

48 .where(eq(configTbl.id, sql.raw(0)));
~

limit.ts:51:57 - error TS2345: Argument of type 'Omit; }, ... 6 more ..., { ...; }>, "where">' is not assignable to parameter of type 'number | Placeholder'.
Type 'Omit; }, ... 6 more ..., { ...; }>, "where">' is missing the following properties from type 'Placeholder': name, protected

51 .as(db.select({ x: eulerTbl.x }).from(eulerTbl).limit(limitQuery));
~~~~~~~~~~

limit.ts:62:57 - error TS2345: Argument of type 'SQL' is not assignable to parameter of type 'number | Placeholder'.
Type 'SQL' is missing the following properties from type 'Placeholder': name, protected

62 .as(db.select({ x: eulerTbl.x }).from(eulerTbl).limit(sql.raw(5)));
~~~~~~~~~~

limit.ts:62:65 - error TS2345: Argument of type 'number' is not assignable to parameter of type 'string'.

62 .as(db.select({ x: eulerTbl.x }).from(eulerTbl).limit(sql.raw(5)));
~

Found 4 errors in the same file, starting at: limit.ts:48
```

Look at the first and last errors. The type definition of `sql.raw` insists that it accepts only a string as its argument even though its process actually accepts a number. You sometimes want to embed fixed numbers to SQLs:

```sql
SELECT /* ... */ WHERE id = 0 -- line 48
SELECT /* ... */ LIMIT 5 -- line 62
SELECT /* ... */ FETCH FIRST 5 ROWS ONLY -- same
```

Also, you might occasionally want to embed a boolean literal `TRUE` or `FALSE` (verbose though). `sql.raw` should also accept them as an argument.

Contributor guide

Open the contributing guide

Research direction

Reproduce the reported TypeScript errors in limit.ts, then locate the sql.raw type definition and its existing type-check coverage. Done when sql.raw accepts string, number, and boolean arguments and the reported calls compile without errors.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql, typescript
Domain
databases
Issue type
Bug
Difficulty
1/5
Estimated time
Under an hour
Activity status
Stale
Clarity
Clearly specified
Newbie friendliness
55/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.