drizzle-team / drizzle-team/drizzle-orm

[BUG]: MySQL VARBINARY/BINARY columns return strings instead of Buffer objects starting from v0.41.0

Open
#4,751 2 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.44.3 (bug also reproducible in 0.41.0 and later versions)

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

0.31.4

### Other packages

mysql2@3.14.2, MySQL 8.0

### Describe the Bug

> What is the undesired behavior?

Starting from drizzle-orm v0.41.0, data retrieved from MySQL `VARBINARY` and `BINARY` columns is incorrectly returned as strings instead of Buffer objects. This breaks existing code that expects binary data to be handled as Buffers.

> What are the steps to reproduce it?

A complete reproduction case is available at: https://github.com/hiro1989/repro-drizzle-varbinary-bug

The repository includes:

- Test cases demonstrating the issue
- Instructions for testing different drizzle-orm versions
- Complete reproduction environment using TestContainers

The following is an excerpt of the most important parts.

1. Create a MySQL table with `VARBINARY` or `BINARY` columns:

```sql
CREATE TABLE test_table (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
varbinary_data VARBINARY(10),
binary_data BINARY(3)
);
```

2. Define the schema in Drizzle:

```typescript
import {
mysqlTable,
varbinary,
binary,
int,
varchar,
} from "drizzle-orm/mysql-core";

export const testTable = mysqlTable("test_table", {
id: int("id").primaryKey().autoincrement(),
name: varchar("name", { length: 100 }),
varbinaryData: varbinary("varbinary_data", { length: 10 }),
binaryData: binary("binary_data", { length: 3 }),
});
```

3. Insert binary data:

```typescript
const testBinary = Buffer.from([0xff, 0xfe, 0xfd]);
const testVarbinary = Buffer.from([0xff, 0xfe, 0xfd, 0xfc]);

await db.insert(testTable).values({
name: "test-record",
binaryData: testBinary,
varbinaryData: testVarbinary,
});
```

4. Query the data:

```typescript
const [result] = await db.select().from(testTable);

console.log(typeof result.binaryData); // Expected: "object" (Buffer), Actual: "string"
console.log(result.binaryData instanceof Buffer); // Expected: true, Actual: false
```

> What is the desired result?

The `binaryData` and `varbinaryData` fields should be returned as Buffer objects, not strings. This was the behavior in drizzle-orm v0.40.1 and earlier.

#### Additional Information

- **Database engine**: MySQL 8.0
- **Node.js version**: v22.17.1
- **TypeScript version**: 5.8.3

#### Version Comparison

- ✅ **v0.40.1**: Returns Buffer objects correctly
- ❌ **v0.41.0**: Returns strings (regression introduced)
- ❌ **v0.44.3**: Still returns strings

#### Impact

- This is a breaking change that affects any application working with binary data in MySQL.
- Currently, there seem to be only two possible workarounds:
- Continue using version 0.40.1
- Don't use MySQL VARBINARY/BINARY types, use VARCHAR/CHAR types instead

Buffer type data is irreversibly corrupted by the following processing:

- [Various fixes, features bundled for v0.41.0 (#4293) · drizzle-team/drizzle-orm@f39f885](https://github.com/drizzle-team/drizzle-orm/commit/f39f885779800982e90dd3c89aba6df3217a6fd2#diff-7f419fc03c01575cb3631269ad5bfb388160fc3d9e76dee239c9427013884fb7R46)

The data converted to string there cannot restore the original Buffer data with either `Buffer.from(selectedData, "utf-8");` or `Buffer.from(selectedData, "binary")`.

#### Other Information

> What database engine are you using?

MySQL 8.0

> Are you using a specific cloud provider? Which one?

- AWS Aurora MySQL
- TiDB Serverless (8.0.11-TiDB-v7.5.2-serverless)
- MySQL 8.0.34 on Docker

> Do you think this bug pertains to a specific database driver? Which one?

No. I think the problem is using `toString()` in the following code, so it's a JavaScript issue.

- [Various fixes, features bundled for v0.41.0 (#4293) · drizzle-team/drizzle-orm@f39f885](https://github.com/drizzle-team/drizzle-orm/commit/f39f885779800982e90dd3c89aba6df3217a6fd2#diff-7f419fc03c01575cb3631269ad5bfb388160fc3d9e76dee239c9427013884fb7R46)

> Are you working in a monorepo?

No.

> If this is a bug related to types: What Typescript version are you using? What's the content of your tsconfig.json file?

This is a runtime issue, not a type issue.

> If you're using a runtime that isn't Node.js: Which one? What version? Have you verified that this isn't an issue with the runtime itself?

No other runtimes are used, only Node.js.

---

Thanks to the awesome Drizzle team for all their hard work on this fantastic ORM! Hope this bug report helps make the library even better for everyone dealing with binary data in MySQL.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.