drizzle-team / drizzle-team/drizzle-orm
[BUG]: `sql.raw` does not accept number or boolean in type definition
- 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
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