未加星标

Sequelize CRUD 102

字体大小 | |
[前端(javascript) 所属分类 前端(javascript) | 发布者 店小二03 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

My previous post,Sequelize CRUD 101, covered the very basics of CRUD using the Node ORM, Sequelize . This post will cover two intermediate sets of concepts:

Querying multiple columns using a query object. Creating, updating and deleting multiple records.

You'll benefit from having an SQL database running locally (preferably PostgreSQL), and cloning the repo that goes with this post .

The repo for this post extends that of mySequelize 101 post, so please refer to my prior post for a walk through of the folder structure and code.

READ: Returning a subset of data

Before getting into complex querying, let's ease into Sequelize with something fairly basic - returning a subset of data.

For example, imagine we have a table of pet information - owner names, ids, addresses, medical history, etc. - but we only want to return names and types from our query. Sequelize has an attributes option that allows us to declare which columns we want returned.

db.pets.findAll({ attributes: ['name', 'type'], where: { city: 'Los Angeles' } }) .then(pets => { console.log(pets); });

The above query will return all pets with the city 'Los Angeles', but will only return the name and breed or each pet. Supposing there only two pets with 'Los Angeles' as their city, the JSON response would look something like this:

[ { "name": "Max", "type": "cat" }, { "name": "Penelope", "type": "dog" } ] READ: Querying multiple columns

Basic search functionality is a common feature of APIs, so let's build one. The following is an example of query that lets us search for cats in Los Angeles.

The code is simple, here it is:

db.pets.findAll({ where: { city: 'Los Angeles', type: 'cat' } });

If we were searching one column, e.g. city, we could send a single variable as a payload from the client (this example uses the popular SuperAgent library):

import superagent from 'superagent'; const petCity = 'Los Angeles'; superagent .post('/search') .send({ city: petCity }) .set('Accept', 'application/json') .end(function(err, res){ if (err || !res.ok) { // handle error } else { // handle success } });

As always, our API is built with Express.js. The endpoint to receive our query would look like this:

app.post('/search', (req, res) => { const citySearch = req.body.city; db.pets.findAll({ where: citySearch }) .then(pets => { res.json(pets); }); });

NOTE:Although we are 'getting' data, and the Sequelize method we are using is findAll , our endpoint is not a GET endpoint. Since we are sending/posting an object from the client, we need to make this endpoint a POST route.

But how do we search multiple columns, e.g. city and type, via our Express API? We'll have to pass an object to Sequelize, an object that contains our query parameters.

Our client code would look something like this:

import superagent from 'superagent'; const myQuery = { city: 'Los Angeles', type: 'cat' }; superagent .post('/search') .send({ query: myQuery }) .set('Accept', 'application/json') .end(function(err, res){ if (err || !res.ok) { // handle error } else { // handle success } });

Our API endpoint would receive it like so:

app.post('/search', (req, res) => { const multipleSearch = req.body.query; db.pets.findAll({ where: multipleSearch }) .then(pets => { res.json(pets); }); }); CREATE: Bulk creation

Creating multiple records is the most straightforward of the bulk operations, because Sequelize has a bulkCreate method that accepts an array of objects.

To create two users at once, we need to send an array containing two user objects from the client. Here is the object along with the SuperAgent code for the client.

const owners = [ { name: "John", role: "user" }, { name: "Sean", role: "user" } ]; superagent .post('/owners/bulk') .send(owners) .set('Accept', 'application/json') .end(function(err, res){ if (err || !res.ok) { // handle error } else { // handle success } });

Here is the endpoint that receives this POST request.

app.post('/owners/bulk', (req, res) => { const ownerList = req.body.owners; db.owners.bulkCreate(ownerList) .then(newOwners => { res.json(newOwners); }) });

Sequelize will send you the the newly created users as it's response. Here is the server's JSON response of our bulk create request:

[ { "id": "1c9fa4db-3499-43ed-8378-47c8f53e900a", "name": "John", "role": "user", "created_at": "2016-10-15T20:23:05.020Z", "updated_at": "2016-10-15T20:23:05.020Z" }, { "id": "b292ff23-9f56-4f15-84ca-68dae355da11", "name": "Sean", "role": "user", "created_at": "2016-10-15T20:23:05.020Z", "updated_at": "2016-10-15T20:23:05.020Z" } ] UPDATE: Updating multiple records

Updating and deleting multiple records requires more effort on the part of the developer, because Sequelize doesn't have a method specifically for these operations. However, this gives us an opportunity to take advantage of the javascript promise functionality built into Sequelize.

There are two steps for updating (or deleting) multiple records. First, you query the records. Second, you update the records. The second step is the trickier of the two.

Step 1:We are going to keep this part as simple as possible. For our API, the client will have to send an array of ids corresponding to the records to be updated. We'll use this array to retrieve the records from the database. We will also need an object containing the columns and values for the update.

If we were to change the role of owners John and Sean from 'user' to 'admin', we would send the following code from the client:

const updateObj = { ids: [ "1c9fa4db-3499-43ed-8378-47c8f53e900a", "b292ff23-9f56-4f15-84ca-68dae355da11" ], updates: { role: 'admin' } }; superagent .patch('/owners/bulk') .send(updateObj) .set('Accept', 'application/json') .end(function(err, res){ if (err || !res.ok) { // handle error } else { // handle success } });

Step 2:Since we are updating existing records, we need to create a PATCH route (note line 11 in our SuperAgent code, too). The first piece of logic we need to code is a query that will search for multiple ids. We can do this by using Sequelize's $in operator. This operator will read each item in an array.

app.patch('/owners/bulk', (req, res) => { const ids = req.body.ids; const updates = req.body.updates; db.owners.findAll({ where: { id: { $in: ids } } }); // update logic goes here })

In the code above, first we grab the ids and the updates from req.body . Then we query the owners tables using the $in operator and the array of ids. This query will return all the owners in the ids array, and now we need to update them.

To make sure all of our updates are made before we send a response to the client, we need to use the Promise.all() method that's built into Sequelize. We've been using promises constantly, as shown by our use of .then() , but we've been dealing with one promise at a time.

The general form of the logic we have been using is "first do X, then do Y, then do Z". Specifically, the logic has been "query the database, then send back a response" or "query the database, then update the record, then send back the response". In these cases, promises allow us to wait until an operation is complete before moving on to the next step.

In the logic above, we are dealing with one operations at a time; do this , then do that . Now that we are updating multiple records, the logic is different. Rather than "do this, then do that", we need logic of the form "Do many operations , once they are all resolved, then do X". This is where Promise.all() comes in.

Let's look at the specifics of our implementation.

app.patch('/owners/bulk', (req, res) => { const ids = req.body.ids; const updates = req.body.updates; db.owners.findAll({ where: { id: { $in: ids } } }) .then(owners => { const updatePromises = owners.map(owner => { // the line below creates a new item/promise for // the updatePromises array return owner.updateAttributes(updates); }); return db.Sequelize.Promise.all(updatePromises) }) .then(updatedOwners => { res.json(updatedOwners); }); })

After retrieving the array of owner records from the database, we take this array of records and use it to create an array of promises. The Javascript .map() method takes an array (in this case owners ) and creates an new array from it.

On lines 9 - 13, we take the owners array and use it as material for creating an array called updatePromises . The latter contains one updateAttributes promise for every item in the owners array. We then pass the newly created updatePromises array to Promise.all() . Promise.all() waits for every promise in the updatePromises array to resolve before moving on to the next operation; in this case, sending a response back to the client.

NOTE:The return statement in .map() is very important. If you leave it out, you'll produce a new array of null values. For more on Javascript array methods (which are essential for functional programming), check out this informative post .

DELETE: Deleting multiple records

Deleting multiple records is similar to updating. In fact, it's slightly simpler because we don't need an update object - an array of ids is all that's required.

app.delete('/owners/bulk', (req, res) => { const ids = req.body.ids; db.owners.findAll({ where: { id: { $in: ids } } }) .then(owners => { const deletePromises = owners.map(owner => { return owner.destroy(); }); return db.Sequelize.Promise.all(deletePromises) }) .then(deletedOwners => { res.json(deletedOwners); }); });

On line 8, we see the .destroy() method at work. If your Sequelize model is set to paranoid: true , the .destroy() method will insert a timestamp indicating when the 'soft deletion' happened and the record will no longer be returned in queries. If the model is paranoid: false , then the record will continue to be returned in queries, but there will be a timestamp indicating when the record was 'deleted'.

The response this route sends is an array of the deleted records, but it will contain the deleted_at column. Since our model is set to paranoid: true , these records will not be included in future queries.

[ { "id": "1c9fa4db-3499-43ed-8378-47c8f53e900a", "name": "John", "role": "admin", "created_at": "2016-10-15T20:23:05.020Z", "updated_at": "2016-10-15T20:23:05.020Z", "deleted_at": "2016-10-16T16:16:22.365Z" }, { "id": "b292ff23-9f56-4f15-84ca-68dae355da11", "name": "Sean", "role": "admin", "created_at": "2016-10-15T20:23:05.020Z", "updated_at": "2016-10-15T20:23:05.020Z", "deleted_at": "2016-10-16T16:16:22.365Z" } ]

本文前端(javascript)相关术语:javascript是什么意思 javascript下载 javascript权威指南 javascript基础教程 javascript 正则表达式 javascript设计模式 javascript高级程序设计 精通javascript javascript教程

主题: JavaSQLPostgreSQL
分页:12
转载请注明
本文标题:Sequelize CRUD 102
本站链接:http://www.codesec.net/view/483617.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 前端(javascript) | 评论(0) | 阅读(22)