英文:
Drizzle Columns Schema JSON stores my JSON as text in PostgreSQL
问题
我正在使用Drizzle作为一个Typescript后端，提供一些API端点。我的数据库是Postgresql，有一个JSON列。
export const transactions = pgTable("transactions", {
id: serial("id").primaryKey(),
my_json: json('my_json')
});
如果我尝试从Supabase表编辑器存储{"hello":"world"}，我得到的是：
如果我尝试使用TS/Drizzle插入{"hello":"world"}，我得到的是：
不知何故，我无法找到设置或方法来使Drizzle将其存储为真正的JSON对象而不是其字符串版本。
英文:
I'm using Drizzle for a Typescript backend that serves a few API endpoints. My database is Postgresql and there's a JSON column.
export const transactions = pgTable("transactions", {
id: serial("id").primaryKey(),
my_json: json('my_json')
});
I if try to store {"hello":"world"} from a supabase table editor, this is what I'm getting:
If I try to insert my {"hello":"world"} using TS/Drizzle, this is what I'm getting:
Somehow I can't find a setting or a way to make drizzle store it as a real JSON object and not a string version of it.
答案1
得分: 4
目前（2023年6月）由于一个错误，这是不可能的：https://github.com/drizzle-team/drizzle-orm/issues/724
您需要执行一个原始的DML查询：
db.execute(sql`INSERT INTO table(foo_id, foo_json)...VALUES(123,${yourObject})`)
例如，对我来说这样有效。
```const statement = sqlINSERT INTO wikidata_article (wikidata_id, category, grade, en_raw_length, en_url_title, labels, sitelinks) VALUES (${articleDetails.wikiDataId}, ${articleDetails.category}, ${articleDetails.grade}, ${articleDetails.enBytes}, ${articleDetails.enUrlTitle}, ${articleDetails.labels}, ${articleDetails.sitelinks}) ON CONFLICT (wikidata_id) DO UPDATE SET category = ${articleDetails.category}, grade = ${articleDetails.grade}, en_raw_length = ${articleDetails.enBytes}, en_url_title = ${articleDetails.enUrlTitle}, labels = ${articleDetails.labels}, sitelinks = ${articleDetails.sitelinks};
await db.execute(statement);
<details>
<summary>英文:</summary>
Currently (June 2023) it's not possible due to a bug: https://github.com/drizzle-team/drizzle-orm/issues/724
You'll need to execute a raw DML query instead:
```db.execute(sql`INSERT INTO table(foo_id, foo_json)...VALUES(123,${yourObject})`)```
For example this worked for me.
const statement = sql
INSERT INTO wikidata_article (wikidata_id, category, grade, en_raw_length, en_url_title, labels, sitelinks)
VALUES (${articleDetails.wikiDataId}, ${articleDetails.category}, ${articleDetails.grade}, ${articleDetails.enBytes}, ${articleDetails.enUrlTitle}, ${articleDetails.labels}, ${articleDetails.sitelinks})
ON CONFLICT (wikidata_id) DO UPDATE
SET
category = ${articleDetails.category},
grade = ${articleDetails.grade},
en_raw_length = ${articleDetails.enBytes},
en_url_title = ${articleDetails.enUrlTitle},
labels = ${articleDetails.labels},
sitelinks = ${articleDetails.sitelinks}
await db.execute(statement);
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论