drizzle-team / drizzle-team/drizzle-orm

[BUG]: Unable to insert a vector value in table with vector column

Open
#3,899 3 comments 2 reactions 0 assignees View on GitHub
bug db/sqlite priority
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.38.3

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

0.30.1

### Other packages

@libsql/client@0.14.0, expo@52.0.23, expo-sqlite@15.0.5

### Describe the Bug

### Bug report

I'm unable to persist a vector column with embeddings. I followed the turso guide [here](https://docs.turso.tech/sdk/ts/orm/drizzle#vector-embeddings) here for defining the types And the Drizzle [documentation](https://orm.drizzle.team/docs/connect-op-sqlite). I've also taken inspiration from this [blog post](https://www.expostarter.com/blog/expo-libsql-improve-app-performance) for the setup with expo

#### Expected behaviour

I can insert a vector into my sqlite database

### Libs and versions

`"@libsql/client": "^0.14.0"`
`"expo": "^52.0.23"`
`"expo-sqlite": "~15.0.5"` (I need this for the Drizzle Studio's React Native SQLite client)
`"drizzle-orm": "^0.38.3"`
`"drizzle-kit": "^0.30.1"`
`@op-engineering/op-sqlite": "^11.2.12`

### Steps

0. Database configuration + Drizzle config

```typescript
// db/index.ts

import { drizzle, OPSQLiteDatabase } from "drizzle-orm/op-sqlite";
import {
open,
DB
} from "@op-engineering/op-sqlite";
import { logError } from "~/lib/utils";

let db: OPSQLiteDatabase
let sqlite: DB

try {
sqlite = open({
name: "meadowmind.local.db",
});

db = drizzle(sqlite);

} catch (error) {
const updatedError = logError('Error initializing database client', error);
throw updatedError;
}

export { db }

```

```typescript
// drizzle.config.ts

import type { Config } from "drizzle-kit";

export default {
schema: "./db/schema.ts",
out: "./db/generated",
dialect: "sqlite",
driver: 'expo',
casing: "snake_case"
} satisfies Config;
```

1. Define custom vector type

```typescript
const embedding = customType<{
data: number[];
config: { dimensions: number };
configRequired: true;
driverData: Buffer;
}>({
dataType(config) {
return `F32_BLOB(${config.dimensions})`;
},
fromDriver(value: Buffer) {
return Array.from(new Float32Array(value.buffer));
},
toDriver(value: number[]) {
return sql`vector32(${JSON.stringify(value)})`;
},
});
```

2. Define table with vector column

```typescript
export const content = sqliteTable('content', {
id: integer('id').primaryKey({ autoIncrement: true }),
title: text('title').notNull(),
data: text('data', { mode: 'json'}).notNull().$type<{value: string, type: string, extension: string}>(),
embedding: embedding('embedding', { dimensions: 512 })
});
```

3. Add an index in my generated/migrations .sql file

```sql
-- Custom SQL migration file, put your code below! --
CREATE INDEX IF NOT EXISTS embedding_index
ON content(embedding)
USING vector_cosine(512)
```

4. Run migration to add index
```bash
npm run db:migrate
```

5. Loading migrations in my _layout.tsx
```typescript
import { useMigrations } from 'drizzle-orm/op-sqlite/migrator';

const { success, error } = useMigrations(db, migrations);
```

6. Insert vector data

```typescript
import {content } from "../schema";

await tx.insert(content).values([{
title,
data,
embedding: sql`vector32(${JSON.stringify(embedding)})`
}])

```

### Observation

In the insert operation in 6) `title` and `data` are stored just alright; however, the `embedding` field is an empty array.

input
```json
{
"title":"Fela Anikulapo-Kuti",
"data":{
"extension":"txt",
"type":"document",
"value":"When Fela Married 27 Wives in One Day…
Decades after his death, the Afrobeat pioneer, Fela Anikulapo-Kuti, remains popular for his profound music as much as for his colourful lifestyle.

In 1978, he sparked controversy by marrying 27 women in one day, a ceremony his lawyer considered \"immoral and against social norms.\"

Ayomide Tayo (@AOT2) explains how the controversial marriage remains an unforgettable chapter in Fela’s extraordinary life, and what it meant for the women."
},
"embedding":[
0.012433307,
0.000879206,
-0.049000476,
-0.042124201,
0.003929944,
-0.045924541,
-0.005988847,
-0.022684528,
0.066774383,
-0.005061252,
-0.064606495,
0.097819507,
0.06952507,
-0.007537428,
-0.013797897,
-0.007841787,
-0.049507208,
0.001878012,
-0.037323162,
-0.008916524,
0.073531531,
-0.001295857,
-0.007732654,
-0.082401663,
-0.030007754,
0.001723102,
-0.006311808,
0.03720988,
-0.015803736,
0.003562244,
-0.047981154,
0.038184144,
0.02043343,
0.05129198,
0.015271586,
-0.004848813,
0.050641805,
-0.027155409,
0.008130196,
0.044699542,
-0.008438642,
0.025702046,
0.062172756,
0.032098554,
0.044641841,
0.061407741,
-0.006749806,
0.065505162,
-0.035065114,
0.097289138,
0.027404733,
-0.019313157,
-0.014370874,
0.033781007,
0.012366299,
-0.04439602,
-0.024634935,
0.067365013,
0.067435548,
0.018790256,
-0.009746296,
0.017817318,
-0.034663305,
-0.045189541,
0.019437021,
-0.047978144,
0.01017065,
0.021477306,
-0.016593374,
-0.037275806,
0.023175772,
0.001509435,
0.02980376,
0.058008965,
0.07670521,
0.008201106,
-0.028090769,
0.019242138,
0.064037815,
0.068999678,
-0.059227947,
-0.042806637,
-0.08463648,
-0.021406306,
0.003837257,
-0.017657062,
-0.008671685,
0.055509958,
-0.054081611,
0.00915083,
-0.056511462,
-0.00370933,
-0.081296325,
-0.025864372,
-0.014364374,
-0.059612337,
0.020668497,
-0.036052492,
-0.052742839,
0.009076023,
0.011638342,
0.080624759,
-0.016183693,
0.027544277,
-0.015234491,
-0.051251534,
0.001031438,
-0.053530604,
0.0397778,
0.041525852,
-0.017669743,
-0.031379286,
0.112906978,
0.039843023,
0.132616743,
-0.045706823,
0.045298196,
0.018565519,
0.007024899,
-0.081013575,
-0.062281616,
-0.020887302,
-0.035830125,
-0.084489062,
0.014869731,
-0.007338552,
0.068377487,
0.021324351,
-0.000330319,
0.039918479,
0.090587407,
0.0290101,
-0.010488758,
0.003067225,
0.011708861,
-0.032771893,
-0.020596415,
0.077550955,
0.012737009,
0.017258668,
-0.132526547,
0.047859903,
0.013184664,
-0.033835217,
0.039558552,
0.03994203,
-0.044182539,
-0.016343148,
-0.002466366,
-0.035200812,
-0.043141674,
-0.068883002,
-0.054325387,
-0.043598711,
-0.031245992,
-0.030228797,
0.017478349,
0.067277774,
0.069866568,
-0.122589424,
-0.053330041,
0.004688295,
0.072194785,
-0.034667488,
0.107099958,
0.028203761,
-0.059888795,
0.033458773,
-0.038652074,
0.002349865,
0.00555755,
-0.036237698,
-0.04892464,
-0.034407634,
0.046760552,
-0.045608401,
-0.034257267,
0.072295494,
-0.121243671,
-0.002906932,
-0.0578654,
-0.021735067,
-0.005022852,
0.066042431,
0.082805619,
-0.016593928,
-0.033515025,
-0.033094123,
-0.063083656,
0.026867779,
0.028476335,
0.055273741,
0.003314005,
-0.066442788,
0.022275941,
0.026841054,
0.02232076,
-0.040002298,
0.04144397,
0.054466221,
0.053549699,
-0.03444238,
-0.038925894,
0.009934359,
0.054084878,
0.01491442,
0.069073349,
0.051987417,
-0.031147938,
-0.065038517,
0.061156284,
-0.027118655,
0.093422681,
0.02820039,
0.022582874,
0.052358747,
-0.038249746,
-0.049864355,
-0.038465194,
0.026731607,
-0.019681659,
0.048622385,
0.005826494,
0.070484526,
0.0390095,
-0.058547273,
0.004203031,
-0.023390517,
0.01734044,
-0.004288104,
-0.072071843,
0.003411584,
-0.015620628,
-0.046302408,
0.019389553,
-0.01663537,
-0.026795378,
0.014665077,
-0.09124545,
-0.00472858,
-0.056118142,
-0.01156836,
0.001533464,
0.10896948,
-0.046592649,
-0.012014167,
-0.078368321,
0.031523533,
-0.025830764,
-0.020031705,
-0.074646987,
-0.029049942,
0.020590838,
0.042630207,
0.094746858,
0.029408053,
-0.006712872,
-0.056783326,
0.006217035,
-0.041925821,
0.020095823,
0.015083564,
0.010830041,
-0.027855009,
-0.034205526,
0.021922229,
-0.021801069,
-0.024583096,
-0.087249056,
-0.030046985,
0.129137158,
-0.064169861,
0.039349675,
-0.001412158,
-0.020009136,
-0.07507775,
-0.04186029,
0.018063523,
-0.036022842,
-0.071952224,
0.023338251,
-0.014283082,
-0.027926797,
0.076651886,
-0.011819998,
-0.032001223,
0.031926863,
0.001537266,
0.005265334,
0.011536567,
-0.032316953,
-0.045544241,
0.06027811,
-0.040051896,
0.01605955,
-0.007863138,
-0.011198358,
-0.028805146,
0.019558236,
-0.019273834,
0.023243502,
0.039992064,
0.07515645,
-0.008039846,
0.045679457,
-0.009872046,
-0.006180441,
-0.062361058,
0.065851785,
0.011274956,
-0.019908642,
-0.074858569,
-0.02137485,
0.029465592,
-0.038108189,
0.040712371,
0.004078364,
0.038652293,
-0.027883247,
-0.016899794,
0.016009072,
0.012454452,
-0.04000001,
-0.030497933,
0.009576852,
-0.023590578,
0.030308787,
-0.006138589,
0.086879224,
0.014185901,
-0.006922823,
0.089827254,
0.003545873,
-0.000696058,
-0.008435111,
0.048772369,
-0.049996767,
0.014792421,
-0.030721486,
-0.008119312,
-0.052268609,
0.072834127,
0.044885039,
0.030391023,
0.112056524,
0.021264849,
-0.021489603,
0.016964274,
0.058459479,
0.006605118,
-0.017287236,
-0.00417388,
0.050459318,
0.003576809,
0.064760938,
0.071099833,
0.052012857,
0.081922472,
-0.043281324,
0.028166974,
0.064256251,
-0.025789274,
0.058987278,
0.030445902,
-0.064635009,
-0.089521386,
-0.018125024,
0.019914301,
-0.036493819,
-0.027784541,
0.06527774,
-0.00583497,
0.057076275,
0.000614706,
0.029292054,
0.022419255,
0.014372119,
-0.067319006,
0.003882196,
0.021379722,
0.072347045,
-0.001646063,
0.002811631,
0.029658912,
-0.015160983,
-0.000295376,
0.040437046,
0.006849716,
-0.040982559,
0.067151189,
0.080868602,
-0.012111188,
-0.035450056,
-0.013850837,
0.0744574,
0.012811719,
0.035659034,
-0.052149236,
0.02625777,
0.038023081,
-0.039345101,
-0.057550583,
-0.039968256,
0.037983976,
0.051579993,
-0.065384835,
-0.003250157,
-0.029112739,
-0.019348746,
-0.03553858,
0.034464248,
-0.121963888,
-0.000389006,
0.032675464,
0.04143117,
0.018794188,
0.042745441,
0.013741082,
-0.025545679,
-0.037419513,
-0.020526839,
0.017812785,
0.044659417,
-0.05224077,
0.046479143,
-0.002984011,
0.055966314,
0.042987797,
0.007494315,
0.001740386,
-0.017207248,
-0.030721005,
0.076521881,
-0.007650499,
-0.03892199,
-0.016416555,
0.025214333,
0.060688335,
0.043233678,
0.078810133,
-0.044670172,
-0.002841229,
0.039818007,
0.034684815,
0.097876735,
-0.12230169,
-0.016368508,
0.078384899,
0.009572638,
-0.051611211,
0.07216718,
0.028087018,
-0.097871035,
-0.00589612,
0.040038601,
0.076276325,
0.042646859,
0.02933258,
0.013096774,
0.003611998,
0.046878941,
-0.003724126,
0.004463197,
-0.016964717,
-0.045126282,
-0.008026638,
0.015850101,
0.015862556,
0.016791379,
0.010232083,
-0.042852513,
-0.003040787,
0.013036176,
0.007895037,
-0.05152161,
-0.041725606,
0.034439895,
-0.016113477,
0.05866877,
-0.012154002,
0.010513727,
0.032069825,
0.039486077,
0.028605685,
0.048998307,
-0.006187533,
-0.0096764,
0.00552999,
-0.049024772,
-0.037088826,
0.021026868,
-0.023336353,
0.007530467,
-0.041222762,
-0.00701218,
0.012167967,
0.018031463,
0.091693968,
-0.00537691,
-0.043257091,
0.023684869,
0.001350527,
-0.01420179,
0.000128599,
0.017438304,
-0.044723898,
0.042174757,
-0.024409678,
-0.020899676,
-0.069140956,
0.021539098,
0.024257805
]
}
```

output (db entry)

```json
{
"createdAt":"Date"{
"NaN"
},
"data":{
"extension":"txt",
"type":"document",
"value":"When Fela Married 27 Wives in One Day…
Decades after his death, the Afrobeat pioneer, Fela Anikulapo-Kuti, remains popular for his profound music as much as for his colourful lifestyle.

In 1978, he sparked controversy by marrying 27 women in one day, a ceremony his lawyer considered \"immoral and against social norms.\"

Ayomide Tayo (@AOT2) explains how the controversial marriage remains an unforgettable chapter in Fela’s extraordinary life, and what it meant for the women."
},
"embedding":[

],
"id":1,
"publicId":"con_qj8ee79efa30dbyv59w40c5g",
"title":"Fela Anikulapo-Kuti",
"updatedAt":"Date"{
"NaN"
}
}
```

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.