与pg承诺的相互依赖的交易 [英] Interdependent Transactions with pg-promise
问题描述
我正在尝试构建一个涉及帖子的帖子和标签的应用。对于这些,我有一个 post
,标签
和 post_tag
表。 标签
具有我在手之前定义的标签,并且在应用程序的某个地方向前端的用户建议。 post_tag
表将每个行上的帖子和标签 ids p>
我使用express.js和postgreql和pg-promise。
据我所知,我需要一个事务查询(ies)用于创建帖子操作。
此外,我还需要一种机制来检测标签是否不在标签
表,当用户创建帖子,以便我可以即时插入,我有一个 tag_id
每个标签 c $ c> post_id 和 tag_id 中的
插入
code> into post_tag
table。否则,由于我需要 post_tag
表的列 post_id
和 tag_id
以引用帖子
和标签
表 id
列。
这是我使用的url函数,不成功:
privateAPIRoutes.post('/ ask',function(req,res){
console.log '/ ask req.body:',req.body);
//写入帖子
var post_id =''
var post_url =''
db.query(
`
INSERT INTO
posts(title,text,post_url,author_id,post_type)
VALUES
($(title),$(text),$(post_url) ,$(author_id),$(post_type))
RETURNING id
`,
{
title:req.body.title,
text:req.body。文字,
post_url:slug(req.body.title ),
author_id:req.user.id,
post_type:'question'
} //记住req.user包含由mw保存的解码的jwt。
)
.then(post => {
console.log('/ ask post:',post);
post_id = post.id
post_url = post.post_url
//如果tag deos不存在在这里创建
var tags = req.body.tags;
console.log('2nd block tags1 ',tags);
for(var i = 0; i< tags.length; i ++){
if(tags [i] .id == undefined){
console.log ('req.body.tags [i] .id == undefined',tags [i] .id);
var q1 = db.query(insert into tags(tag)values($(tag))返回id,{tag:tags [i] .label})
.then(data => {
console.log('2nd block tags2',tags);
tags [ i] .id = data [0] .id
//写入post_tag
db.tx(t => {
var queries = [];
for(var j = 0; j< tags.length; j ++){
var query = t.query(
`
INSERT INTO
post_tag(post_id,tag_id)
VALUES
($(post_id),$(tag_id))
`,
{
post_id:post_id,
tag_id:tags [j] .id
}
)
queries.push(query);
}
return t.batch(queries)
})
.then(data => {
res.json({post_id:post_id,post_url:post_url })
})
.catch(error => {
console.error(error);
})
})
.catch error => {
console.error(error);
});
}
}
})
.catch(error => {
console.error(error);
})
} );
您有的主要问题 - 您不能使用任务或事务中的根级别 db
对象。尝试在事务内部创建新连接时会中断事务。在这种情况下,您需要使用 t.tx
。但是,在你的情况下,我根本看不到你需要它。
更正的代码
privateAPIRoutes.post('/ ask',(req,res)=> {
console.log('/ ask req.body: ,req.body);
db.tx(t => {
return t.one(
`
INSERT INTO
posts(title,text,post_url ,author_id,post_type)
VALUES
($(title),$(text),$(post_url),$(author_id),$(post_type))
RETURNING *
`,
{
title:req.body.title,
text:req.body.text,
post_url:slug(req.body.title),
author_id:req.user.id,
post_type:'question'
} //记住req.user包含由mw保存的解码的jwt
)
.then(post = > {
console.log('/ ask second query:post [0]:',post);
console.log('/ ask second query:tags:',req.body.tags);
console.log('/ ask second query:tags [0]:',req.body.tags [0]);
//答案的关键部分:
var tagIds = req.body.tags.map(tag => {
return tag.id || t.one (insert into tags(tag)values($ 1)returned id,tag.label,a => a.id);
});
返回t.batch(tagIds)
.then(ids => {
var queries = ids.map(id => {
return t。一个(
`
INSERT INTO post_tag(post_id,tag_id)
VALUES($(post_id),$(tag_id))
RETURNING post_id,tag_id
`,
{
post_id:post.id,
tag_id:id
}
)
});
返回t.batch(queries);
});
});
})
.then(data => {
// data =最后一个查询的结果;
console.log('/ api / ask',data);
res.json(data);
})
.catch(error => {
// error
});
});
这里的关键是简单地迭代标签id,而对于那些不是设置 - 使用插入。然后你通过将数组传递到 t.batch
中来解决他们。
其他建议:
- 执行插入时,您应该使用方法
一个
$>
- 您应该使用
尝试
/catch
只有一次,交易。这与如何使用承诺相关,而不仅仅是对于这个库 - 您可以将查询放入外部SQL文件,请参阅查询文件
要更好地了解条件插入,请参阅 SELECT-> INSERT
I am trying to build an app involves posts and tags for posts. For these I have a post
, tags
and post_tag
table. tags
has the tags I have defined before hand and in somewhere in the app is suggested to the user on the front-end. post_tag
table holds the post and tag ids as pairs on each row.
I use express.js and postgreql and pg-promise.
As far as I know I need a transactional query(ies) for a create post operation.
Also I need a mechanism to detect if a tag was not in tags
table when the user created the post, so that I can insert it on the fly, and I have a tag_id
for each tag that is neccessary to use in insertion
of the post_id
and tag_id
into post_tag
table. Otherwise, I will have a foreign key error
since I need to post_tag
table's columns post_id
and tag_id
to reference posts
and tags
table id
columns, respectively.
Here is the url function I use for this I have used so far unsuccessful:
privateAPIRoutes.post('/ask', function (req, res) {
console.log('/ask req.body: ', req.body);
// write to posts
var post_id = ''
var post_url = ''
db.query(
`
INSERT INTO
posts (title, text, post_url, author_id, post_type)
VALUES
($(title), $(text), $(post_url), $(author_id), $(post_type))
RETURNING id
`,
{
title: req.body.title,
text: req.body.text,
post_url: slug(req.body.title),
author_id: req.user.id,
post_type: 'question'
} // remember req.user contains decoded jwt saved by mw above.
)
.then(post => {
console.log('/ask post: ', post);
post_id = post.id
post_url = post.post_url
// if tag deos not exist create it here
var tags = req.body.tags;
console.log('2nd block tags1', tags);
for (var i = 0; i < tags.length; i++) {
if (tags[i].id == undefined) {
console.log('req.body.tags[i].id == undefined', tags[i].id);
var q1 = db.query("insert into tags (tag) values ($(tag)) returning id", {tag: tags[i].label})
.then(data => {
console.log('2nd block tags2', tags);
tags[i].id = data[0].id
// write to the post_tag
db.tx(t => {
var queries = [];
for (var j = 0; j < tags.length; j++) {
var query = t.query(
`
INSERT INTO
post_tag (post_id, tag_id)
VALUES
($(post_id), $(tag_id))
`,
{
post_id: post_id,
tag_id: tags[j].id
}
)
queries.push(query);
}
return t.batch(queries)
})
.then(data => {
res.json({post_id: post_id, post_url: post_url})
})
.catch(error => {
console.error(error);
})
})
.catch(error => {
console.error(error);
});
}
}
})
.catch(error => {
console.error(error);
})
});
The main problem you have - you can't use the root-level db
object inside a task or transaction. Trying to create a new connection while inside a transaction breaks the transaction. You would need to use t.tx
in such cases. However, in your case I don't see that you need it at all.
corrected code:
privateAPIRoutes.post('/ask', (req, res) => {
console.log('/ask req.body: ', req.body);
db.tx(t => {
return t.one(
`
INSERT INTO
posts (title, text, post_url, author_id, post_type)
VALUES
($(title), $(text), $(post_url), $(author_id), $(post_type))
RETURNING *
`,
{
title: req.body.title,
text: req.body.text,
post_url: slug(req.body.title),
author_id: req.user.id,
post_type: 'question'
} // remember req.user contains decoded jwt saved by mw above.
)
.then(post => {
console.log('/ask second query: post[0]: ', post);
console.log('/ask second query: tags: ', req.body.tags);
console.log('/ask second query: tags[0]: ', req.body.tags[0]);
// the key piece to the answer:
var tagIds = req.body.tags.map(tag => {
return tag.id || t.one("insert into tags(tag) values($1) returning id", tag.label, a=>a.id);
});
return t.batch(tagIds)
.then(ids => {
var queries = ids.map(id => {
return t.one(
`
INSERT INTO post_tag (post_id, tag_id)
VALUES ($(post_id), $(tag_id))
RETURNING post_id, tag_id
`,
{
post_id: post.id,
tag_id: id
}
)
});
return t.batch(queries);
});
});
})
.then(data => {
// data = result from the last query;
console.log('/api/ask', data);
res.json(data);
})
.catch(error => {
// error
});
});
The key here is simply to iterate through the tag id-s, and for the ones that are not set - use an insert. Then you settle them all by passing the array into t.batch
.
Other recommendations:
- You should use method
one
when executing an insert that returns the new record columns. - You should use
try
/catch
only once there, on the transaction. This is relevant to how to use promises, and not just for this library - You can place your queries into external SQL files, see Query Files
To understand conditional inserts better, see SELECT->INSERT
这篇关于与pg承诺的相互依赖的交易的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!