Skip to content

Instantly share code, notes, and snippets.

@shopglobal

shopglobal/index.html

Created November 11, 2017 07:13
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save shopglobal/49ca873fae88ffa2f3ee0a93c53fea41 to your computer and use it in GitHub Desktop.
Save shopglobal/49ca873fae88ffa2f3ee0a93c53fea41 to your computer and use it in GitHub Desktop.
Download ZIP
Sequelize CRUD 102
Raw
index.html
<main role="main">
<article class="post tag-node-js tag-express-js tag-sequelize tag-api tag-javascript article_purus">
<header class="postheader_purus no-cover">
<div class="vertical_purus">
<div class="inner_purus">
<h1 class="posttitle_purus">Sequelize CRUD 102</h1>
<ul>
<li><time class="date_purus"> Oct 16 2016</time></li>
</ul>
</div>
</div>
</header>
<div class="wrapper_purus">
<div class="postcontent_purus">
<p>My previous post, <a href="http://lorenstewart.me/2016/10/03/sequelize-crud-101/" target="_blank">Sequelize CRUD 101</a>, covered the very basics of CRUD using the Node ORM, <a href="http://docs.sequelizejs.com/en/latest/" target="_blank">Sequelize</a>. This post will cover two intermediate sets of concepts:</p>
<ol>
<li>Querying multiple columns using a query object.</li>
<li>Creating, updating and deleting multiple records.</li>
</ol>
<p>You'll benefit from having an SQL database running locally (preferably PostgreSQL), and cloning <a href="https://github.com/lorenseanstewart/sequelize-crud-102" target="_blank">the repo that goes with this post</a>.</p>
<p>The repo for this post extends that of my <a href="http://lorenstewart.me/2016/10/03/sequelize-crud-101/" target="_blank">Sequelize 101 post</a>, so please refer to my prior post for a walk through of the folder structure and code.</p>
<h5 id="readreturningasubsetofdata">READ: Returning a subset of data</h5>
<p>Before getting into complex querying, let's ease into Sequelize with something fairly basic - returning a subset of data.</p>
<p>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 <code>attributes</code> option that allows us to declare which columns we want returned.</p>
<pre class=" language-javascript line-numbers"><code class="language-javascript">db<span class="token punctuation">.</span>pets<span class="token punctuation">.</span><span class="token function">findAll</span><span class="token punctuation">(</span><span class="token punctuation">{</span>
attributes<span class="token punctuation">:</span> <span class="token punctuation">[</span><span class="token string">'name'</span><span class="token punctuation">,</span> <span class="token string">'type'</span><span class="token punctuation">]</span><span class="token punctuation">,</span>
where<span class="token punctuation">:</span> <span class="token punctuation">{</span> city<span class="token punctuation">:</span> <span class="token string">'Los Angeles'</span> <span class="token punctuation">}</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token function">then</span><span class="token punctuation">(</span>pets <span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token punctuation">{</span>
console<span class="token punctuation">.</span><span class="token function">log</span><span class="token punctuation">(</span>pets<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<p>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:</p>
<pre class=" language-json line-numbers"><code class="language-json"><span class="token punctuation">[</span>
<span class="token punctuation">{</span>
<span class="token property">"name"</span><span class="token operator">:</span> <span class="token string">"Max"</span><span class="token punctuation">,</span>
<span class="token property">"type"</span><span class="token operator">:</span> <span class="token string">"cat"</span>
<span class="token punctuation">}</span><span class="token punctuation">,</span>
<span class="token punctuation">{</span>
<span class="token property">"name"</span><span class="token operator">:</span> <span class="token string">"Penelope"</span><span class="token punctuation">,</span>
<span class="token property">"type"</span><span class="token operator">:</span> <span class="token string">"dog"</span>
<span class="token punctuation">}</span>
<span class="token punctuation">]</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<h5 id="readqueryingmultiplecolumns">READ: Querying multiple columns</h5>
<p>Basic search functionality is a common feature of APIs, so let's build a search endpoint for our API. The following is an example of query that lets us search for cats in Los Angeles. </p>
<p>The code is simple, here it is: </p>
<pre class=" language-javascript line-numbers"><code class="language-javascript">db<span class="token punctuation">.</span>pets<span class="token punctuation">.</span><span class="token function">findAll</span><span class="token punctuation">(</span><span class="token punctuation">{</span>
where<span class="token punctuation">:</span> <span class="token punctuation">{</span>
city<span class="token punctuation">:</span> <span class="token string">'Los Angeles'</span><span class="token punctuation">,</span>
type<span class="token punctuation">:</span> <span class="token string">'cat'</span>
<span class="token punctuation">}</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<p>If we were searching <em>only one column</em>, e.g. city, we could send a single variable as a payload from the client (this example uses the popular <a href="https://visionmedia.github.io/superagent/" target="_blank">SuperAgent</a> library): </p>
<pre class=" language-javascript line-numbers"><code class="language-javascript"><span class="token keyword">import</span> superagent <span class="token keyword">from</span> <span class="token string">'superagent'</span><span class="token punctuation">;</span>
<span class="token keyword">const</span> petCity <span class="token operator">=</span> <span class="token string">'Los Angeles'</span><span class="token punctuation">;</span>
superagent
<span class="token punctuation">.</span><span class="token function">post</span><span class="token punctuation">(</span><span class="token string">'/search'</span><span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token function">send</span><span class="token punctuation">(</span><span class="token punctuation">{</span> city<span class="token punctuation">:</span> petCity <span class="token punctuation">}</span><span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token keyword">set</span><span class="token punctuation">(</span><span class="token string">'Accept'</span><span class="token punctuation">,</span> <span class="token string">'application/json'</span><span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token function">end</span><span class="token punctuation">(</span><span class="token keyword">function</span><span class="token punctuation">(</span>err<span class="token punctuation">,</span> res<span class="token punctuation">)</span><span class="token punctuation">{</span>
<span class="token keyword">if</span> <span class="token punctuation">(</span>err <span class="token operator">||</span> <span class="token operator">!</span>res<span class="token punctuation">.</span>ok<span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token comment" spellcheck="true">// handle error</span>
<span class="token punctuation">}</span> <span class="token keyword">else</span> <span class="token punctuation">{</span>
<span class="token comment" spellcheck="true">// handle success</span>
<span class="token punctuation">}</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<p>As always, our API is built with Express.js. The endpoint to receive our query would look like this: </p>
<pre class=" language-javascript line-numbers"><code class="language-javascript">app<span class="token punctuation">.</span><span class="token function">post</span><span class="token punctuation">(</span><span class="token string">'/search'</span><span class="token punctuation">,</span> <span class="token punctuation">(</span>req<span class="token punctuation">,</span> res<span class="token punctuation">)</span> <span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token punctuation">{</span>
<span class="token keyword">const</span> citySearch <span class="token operator">=</span> req<span class="token punctuation">.</span>body<span class="token punctuation">.</span>city<span class="token punctuation">;</span>
db<span class="token punctuation">.</span>pets<span class="token punctuation">.</span><span class="token function">findAll</span><span class="token punctuation">(</span><span class="token punctuation">{</span>
where<span class="token punctuation">:</span> citySearch
<span class="token punctuation">}</span><span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token function">then</span><span class="token punctuation">(</span>pets <span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token punctuation">{</span>
res<span class="token punctuation">.</span><span class="token function">json</span><span class="token punctuation">(</span>pets<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<p><strong>NOTE:</strong> Although we are 'getting' data, and the Sequelize method we are using is <code>findAll</code>, our endpoint is <em>not</em> a GET endpoint. Since we are sending/posting an object from the client, we need to make this endpoint a POST route.</p>
<p>But how do we search <em>multiple columns</em>, e.g. city <em>and</em> type, via our Express API? We'll have to pass an object to Sequelize, and this object will contain our query parameters.</p>
<p>Our client code would look something like this: </p>
<pre class=" language-javascript line-numbers"><code class="language-javascript"><span class="token keyword">import</span> superagent <span class="token keyword">from</span> <span class="token string">'superagent'</span><span class="token punctuation">;</span>
<span class="token keyword">const</span> myQuery <span class="token operator">=</span> <span class="token punctuation">{</span>
city<span class="token punctuation">:</span> <span class="token string">'Los Angeles'</span><span class="token punctuation">,</span>
type<span class="token punctuation">:</span> <span class="token string">'cat'</span>
<span class="token punctuation">}</span><span class="token punctuation">;</span>
superagent
<span class="token punctuation">.</span><span class="token function">post</span><span class="token punctuation">(</span><span class="token string">'/search'</span><span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token function">send</span><span class="token punctuation">(</span><span class="token punctuation">{</span> query<span class="token punctuation">:</span> myQuery <span class="token punctuation">}</span><span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token keyword">set</span><span class="token punctuation">(</span><span class="token string">'Accept'</span><span class="token punctuation">,</span> <span class="token string">'application/json'</span><span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token function">end</span><span class="token punctuation">(</span><span class="token keyword">function</span><span class="token punctuation">(</span>err<span class="token punctuation">,</span> res<span class="token punctuation">)</span><span class="token punctuation">{</span>
<span class="token keyword">if</span> <span class="token punctuation">(</span>err <span class="token operator">||</span> <span class="token operator">!</span>res<span class="token punctuation">.</span>ok<span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token comment" spellcheck="true">// handle error</span>
<span class="token punctuation">}</span> <span class="token keyword">else</span> <span class="token punctuation">{</span>
<span class="token comment" spellcheck="true">// handle success</span>
<span class="token punctuation">}</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<p>Our API endpoint would receive it like so: </p>
<pre class=" language-javascript line-numbers"><code class="language-javascript">app<span class="token punctuation">.</span><span class="token function">post</span><span class="token punctuation">(</span><span class="token string">'/search'</span><span class="token punctuation">,</span> <span class="token punctuation">(</span>req<span class="token punctuation">,</span> res<span class="token punctuation">)</span> <span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token punctuation">{</span>
<span class="token keyword">const</span> multipleSearch <span class="token operator">=</span> req<span class="token punctuation">.</span>body<span class="token punctuation">.</span>query<span class="token punctuation">;</span>
db<span class="token punctuation">.</span>pets<span class="token punctuation">.</span><span class="token function">findAll</span><span class="token punctuation">(</span><span class="token punctuation">{</span>
where<span class="token punctuation">:</span> multipleSearch
<span class="token punctuation">}</span><span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token function">then</span><span class="token punctuation">(</span>pets <span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token punctuation">{</span>
res<span class="token punctuation">.</span><span class="token function">json</span><span class="token punctuation">(</span>pets<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<h5 id="createbulkcreation">CREATE: Bulk creation</h5>
<p>Creating multiple records is the most straightforward of the bulk operations, because Sequelize has a <code>bulkCreate</code> method that accepts an array of objects.</p>
<p>To create two users at once, we need to send our API an array containing two user objects. Here is the object along with the SuperAgent code the client should send the creation endpoint.</p>
<pre class=" language-javascript line-numbers"><code class="language-javascript"><span class="token keyword">const</span> owners <span class="token operator">=</span> <span class="token punctuation">[</span>
<span class="token punctuation">{</span>
name<span class="token punctuation">:</span> <span class="token string">"John"</span><span class="token punctuation">,</span>
role<span class="token punctuation">:</span> <span class="token string">"user"</span>
<span class="token punctuation">}</span><span class="token punctuation">,</span>
<span class="token punctuation">{</span>
name<span class="token punctuation">:</span> <span class="token string">"Sean"</span><span class="token punctuation">,</span>
role<span class="token punctuation">:</span> <span class="token string">"user"</span>
<span class="token punctuation">}</span>
<span class="token punctuation">]</span><span class="token punctuation">;</span>
superagent
<span class="token punctuation">.</span><span class="token function">post</span><span class="token punctuation">(</span><span class="token string">'/owners/bulk'</span><span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token function">send</span><span class="token punctuation">(</span>owners<span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token keyword">set</span><span class="token punctuation">(</span><span class="token string">'Accept'</span><span class="token punctuation">,</span> <span class="token string">'application/json'</span><span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token function">end</span><span class="token punctuation">(</span><span class="token keyword">function</span><span class="token punctuation">(</span>err<span class="token punctuation">,</span> res<span class="token punctuation">)</span><span class="token punctuation">{</span>
<span class="token keyword">if</span> <span class="token punctuation">(</span>err <span class="token operator">||</span> <span class="token operator">!</span>res<span class="token punctuation">.</span>ok<span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token comment" spellcheck="true">// handle error</span>
<span class="token punctuation">}</span> <span class="token keyword">else</span> <span class="token punctuation">{</span>
<span class="token comment" spellcheck="true">// handle success</span>
<span class="token punctuation">}</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<p>Here is the endpoint that receives this POST request.</p>
<pre class=" language-javascript line-numbers"><code class="language-javascript">app<span class="token punctuation">.</span><span class="token function">post</span><span class="token punctuation">(</span><span class="token string">'/owners/bulk'</span><span class="token punctuation">,</span> <span class="token punctuation">(</span>req<span class="token punctuation">,</span> res<span class="token punctuation">)</span> <span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token punctuation">{</span>
<span class="token keyword">const</span> ownerList <span class="token operator">=</span> req<span class="token punctuation">.</span>body<span class="token punctuation">.</span>owners<span class="token punctuation">;</span>
db<span class="token punctuation">.</span>owners<span class="token punctuation">.</span><span class="token function">bulkCreate</span><span class="token punctuation">(</span>ownerList<span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token function">then</span><span class="token punctuation">(</span>newOwners <span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token punctuation">{</span>
res<span class="token punctuation">.</span><span class="token function">json</span><span class="token punctuation">(</span>newOwners<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<p>Sequelize's <code>bulkCreate</code> method returns the the newly created users. Here is the JSON response our bulk create request produces:</p>
<pre class=" language-json line-numbers"><code class="language-json"><span class="token punctuation">[</span>
<span class="token punctuation">{</span>
<span class="token property">"id"</span><span class="token operator">:</span> <span class="token string">"1c9fa4db-3499-43ed-8378-47c8f53e900a"</span><span class="token punctuation">,</span>
<span class="token property">"name"</span><span class="token operator">:</span> <span class="token string">"John"</span><span class="token punctuation">,</span>
<span class="token property">"role"</span><span class="token operator">:</span> <span class="token string">"user"</span><span class="token punctuation">,</span>
<span class="token property">"created_at"</span><span class="token operator">:</span> <span class="token string">"2016-10-15T20:23:05.020Z"</span><span class="token punctuation">,</span>
<span class="token property">"updated_at"</span><span class="token operator">:</span> <span class="token string">"2016-10-15T20:23:05.020Z"</span>
<span class="token punctuation">}</span><span class="token punctuation">,</span>
<span class="token punctuation">{</span>
<span class="token property">"id"</span><span class="token operator">:</span> <span class="token string">"b292ff23-9f56-4f15-84ca-68dae355da11"</span><span class="token punctuation">,</span>
<span class="token property">"name"</span><span class="token operator">:</span> <span class="token string">"Sean"</span><span class="token punctuation">,</span>
<span class="token property">"role"</span><span class="token operator">:</span> <span class="token string">"user"</span><span class="token punctuation">,</span>
<span class="token property">"created_at"</span><span class="token operator">:</span> <span class="token string">"2016-10-15T20:23:05.020Z"</span><span class="token punctuation">,</span>
<span class="token property">"updated_at"</span><span class="token operator">:</span> <span class="token string">"2016-10-15T20:23:05.020Z"</span>
<span class="token punctuation">}</span>
<span class="token punctuation">]</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<h5 id="updateupdatingmultiplerecords">UPDATE: Updating multiple records</h5>
<p>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.</p>
<p>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.</p>
<p><strong>Step 1:</strong> 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. </p>
<p>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: </p>
<pre class=" language-javascript line-numbers"><code class="language-javascript"><span class="token keyword">const</span> updateObj <span class="token operator">=</span> <span class="token punctuation">{</span>
ids<span class="token punctuation">:</span> <span class="token punctuation">[</span>
<span class="token string">"1c9fa4db-3499-43ed-8378-47c8f53e900a"</span><span class="token punctuation">,</span>
<span class="token string">"b292ff23-9f56-4f15-84ca-68dae355da11"</span>
<span class="token punctuation">]</span><span class="token punctuation">,</span>
updates<span class="token punctuation">:</span> <span class="token punctuation">{</span>
role<span class="token punctuation">:</span> <span class="token string">'admin'</span>
<span class="token punctuation">}</span>
<span class="token punctuation">}</span><span class="token punctuation">;</span>
superagent
<span class="token punctuation">.</span><span class="token function">patch</span><span class="token punctuation">(</span><span class="token string">'/owners/bulk'</span><span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token function">send</span><span class="token punctuation">(</span>updateObj<span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token keyword">set</span><span class="token punctuation">(</span><span class="token string">'Accept'</span><span class="token punctuation">,</span> <span class="token string">'application/json'</span><span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token function">end</span><span class="token punctuation">(</span><span class="token keyword">function</span><span class="token punctuation">(</span>err<span class="token punctuation">,</span> res<span class="token punctuation">)</span><span class="token punctuation">{</span>
<span class="token keyword">if</span> <span class="token punctuation">(</span>err <span class="token operator">||</span> <span class="token operator">!</span>res<span class="token punctuation">.</span>ok<span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token comment" spellcheck="true">// handle error</span>
<span class="token punctuation">}</span> <span class="token keyword">else</span> <span class="token punctuation">{</span>
<span class="token comment" spellcheck="true">// handle success</span>
<span class="token punctuation">}</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<p><strong>Step 2:</strong> 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 <code>$in</code> operator (see line 6 below). This operator will read each item in an array.</p>
<pre class=" language-javascript line-numbers"><code class="language-javascript">app<span class="token punctuation">.</span><span class="token function">patch</span><span class="token punctuation">(</span><span class="token string">'/owners/bulk'</span><span class="token punctuation">,</span> <span class="token punctuation">(</span>req<span class="token punctuation">,</span> res<span class="token punctuation">)</span> <span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token punctuation">{</span>
<span class="token keyword">const</span> ids <span class="token operator">=</span> req<span class="token punctuation">.</span>body<span class="token punctuation">.</span>ids<span class="token punctuation">;</span>
<span class="token keyword">const</span> updates <span class="token operator">=</span> req<span class="token punctuation">.</span>body<span class="token punctuation">.</span>updates<span class="token punctuation">;</span>
db<span class="token punctuation">.</span>owners<span class="token punctuation">.</span><span class="token function">findAll</span><span class="token punctuation">(</span><span class="token punctuation">{</span>
where<span class="token punctuation">:</span> <span class="token punctuation">{</span> id<span class="token punctuation">:</span> <span class="token punctuation">{</span> $<span class="token keyword">in</span><span class="token punctuation">:</span> ids <span class="token punctuation">}</span> <span class="token punctuation">}</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">// update logic goes here</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<p>In the code above, first we grab the ids and the updates from <code>req.body</code>. Then we query the owners tables using the <code>$in</code> operator and the array of ids. This query will return all the owners in the <code>ids</code> array. Now we need to apply the updates.</p>
<p>To make sure all of our updates are made before we send a response to the client, we need to use <code>Promise.all()</code>. We've been using promises constantly, as shown by our use of <code>.then()</code>, but we've been dealing with one promise at a time. </p>
<p>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. </p>
<p>In the logic outlined above, we are dealing with one operations at a time; do <em>this</em>, then do <em>that</em>. 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 <em>many operations</em>, once they are <em>all</em> resolved, then do X". This is where <code>Promise.all()</code> comes in. </p>
<p>Let's look at the specifics of our implementation. </p>
<pre class=" language-javascript line-numbers"><code class="language-javascript">app<span class="token punctuation">.</span><span class="token function">patch</span><span class="token punctuation">(</span><span class="token string">'/owners/bulk'</span><span class="token punctuation">,</span> <span class="token punctuation">(</span>req<span class="token punctuation">,</span> res<span class="token punctuation">)</span> <span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token punctuation">{</span>
<span class="token keyword">const</span> ids <span class="token operator">=</span> req<span class="token punctuation">.</span>body<span class="token punctuation">.</span>ids<span class="token punctuation">;</span>
<span class="token keyword">const</span> updates <span class="token operator">=</span> req<span class="token punctuation">.</span>body<span class="token punctuation">.</span>updates<span class="token punctuation">;</span>
db<span class="token punctuation">.</span>owners<span class="token punctuation">.</span><span class="token function">findAll</span><span class="token punctuation">(</span><span class="token punctuation">{</span>
where<span class="token punctuation">:</span> <span class="token punctuation">{</span> id<span class="token punctuation">:</span> <span class="token punctuation">{</span> $<span class="token keyword">in</span><span class="token punctuation">:</span> ids <span class="token punctuation">}</span> <span class="token punctuation">}</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token function">then</span><span class="token punctuation">(</span>owners <span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token punctuation">{</span>
<span class="token keyword">const</span> updatePromises <span class="token operator">=</span> owners<span class="token punctuation">.</span><span class="token function">map</span><span class="token punctuation">(</span>owner <span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token punctuation">{</span>
<span class="token comment" spellcheck="true">// the line below creates a new item/promise for</span>
<span class="token comment" spellcheck="true">// the updatePromises array</span>
<span class="token keyword">return</span> owner<span class="token punctuation">.</span><span class="token function">updateAttributes</span><span class="token punctuation">(</span>updates<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">return</span> db<span class="token punctuation">.</span>Sequelize<span class="token punctuation">.</span>Promise<span class="token punctuation">.</span><span class="token function">all</span><span class="token punctuation">(</span>updatePromises<span class="token punctuation">)</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token function">then</span><span class="token punctuation">(</span>updatedOwners <span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token punctuation">{</span>
res<span class="token punctuation">.</span><span class="token function">json</span><span class="token punctuation">(</span>updatedOwners<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<p>After retrieving the array of owner records from the database, we take the array and use it to create a new array of promises. The Javascript <code>.map()</code> method takes an array (in this case <code>owners</code>) and creates an <em>new</em> array from it. </p>
<p>On lines 9 - 13, we take the <code>owners</code> array and use it as material for creating an array called <code>updatePromises</code>. The latter contains one <code>updateAttributes</code> promise for every item in the <code>owners</code> array. We then pass the newly created <code>updatePromises</code> array to <code>Promise.all()</code>. <code>Promise.all()</code> waits for every promise in the <code>updatePromises</code> array to resolve before moving on to the next operation; in this case, sending a response back to the client.</p>
<p><strong>NOTE:</strong> The return statement in <code>.map()</code> is very important. If you leave it out, you'll produce a new array of <code>null</code> values. For more on Javascript array methods (which are essential for functional programming), check out <a href="http://cryto.net/~joepie91/blog/2015/05/04/functional-programming-in-javascript-map-filter-reduce/" target="_blank">this informative post</a>.</p>
<h5 id="deletedeletingmultiplerecords">DELETE: Deleting multiple records</h5>
<p>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.</p>
<pre class=" language-javascript line-numbers"><code class="language-javascript">app<span class="token punctuation">.</span><span class="token keyword">delete</span><span class="token punctuation">(</span><span class="token string">'/owners/bulk'</span><span class="token punctuation">,</span> <span class="token punctuation">(</span>req<span class="token punctuation">,</span> res<span class="token punctuation">)</span> <span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token punctuation">{</span>
<span class="token keyword">const</span> ids <span class="token operator">=</span> req<span class="token punctuation">.</span>body<span class="token punctuation">.</span>ids<span class="token punctuation">;</span>
db<span class="token punctuation">.</span>owners<span class="token punctuation">.</span><span class="token function">findAll</span><span class="token punctuation">(</span><span class="token punctuation">{</span>
where<span class="token punctuation">:</span> <span class="token punctuation">{</span> id<span class="token punctuation">:</span> <span class="token punctuation">{</span> $<span class="token keyword">in</span><span class="token punctuation">:</span> ids <span class="token punctuation">}</span> <span class="token punctuation">}</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token function">then</span><span class="token punctuation">(</span>owners <span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token punctuation">{</span>
<span class="token keyword">const</span> deletePromises <span class="token operator">=</span> owners<span class="token punctuation">.</span><span class="token function">map</span><span class="token punctuation">(</span>owner <span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token punctuation">{</span>
<span class="token keyword">return</span> owner<span class="token punctuation">.</span><span class="token function">destroy</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">return</span> db<span class="token punctuation">.</span>Sequelize<span class="token punctuation">.</span>Promise<span class="token punctuation">.</span><span class="token function">all</span><span class="token punctuation">(</span>deletePromises<span class="token punctuation">)</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span>
<span class="token punctuation">.</span><span class="token function">then</span><span class="token punctuation">(</span>deletedOwners <span class="token operator">=</span><span class="token operator">&gt;</span> <span class="token punctuation">{</span>
res<span class="token punctuation">.</span><span class="token function">json</span><span class="token punctuation">(</span>deletedOwners<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<p>On line 8, we see the <code>.destroy()</code> method at work. If your Sequelize model is set to <code>paranoid: true</code>, the <code>.destroy()</code> 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 <code>paranoid: false</code>, then the record will continue to be returned in queries, but there will be a timestamp indicating when the record was 'deleted'.</p>
<p>The response this route sends is an array of the deleted records, but it will contain the <code>deleted_at</code> column. Since our model is set to <code>paranoid: true</code>, these records will not be included in future queries.</p>
<pre class=" language-json line-numbers"><code class="language-json"><span class="token punctuation">[</span>
<span class="token punctuation">{</span>
<span class="token property">"id"</span><span class="token operator">:</span> <span class="token string">"1c9fa4db-3499-43ed-8378-47c8f53e900a"</span><span class="token punctuation">,</span>
<span class="token property">"name"</span><span class="token operator">:</span> <span class="token string">"John"</span><span class="token punctuation">,</span>
<span class="token property">"role"</span><span class="token operator">:</span> <span class="token string">"admin"</span><span class="token punctuation">,</span>
<span class="token property">"created_at"</span><span class="token operator">:</span> <span class="token string">"2016-10-15T20:23:05.020Z"</span><span class="token punctuation">,</span>
<span class="token property">"updated_at"</span><span class="token operator">:</span> <span class="token string">"2016-10-15T20:23:05.020Z"</span><span class="token punctuation">,</span>
<span class="token property">"deleted_at"</span><span class="token operator">:</span> <span class="token string">"2016-10-16T16:16:22.365Z"</span>
<span class="token punctuation">}</span><span class="token punctuation">,</span>
<span class="token punctuation">{</span>
<span class="token property">"id"</span><span class="token operator">:</span> <span class="token string">"b292ff23-9f56-4f15-84ca-68dae355da11"</span><span class="token punctuation">,</span>
<span class="token property">"name"</span><span class="token operator">:</span> <span class="token string">"Sean"</span><span class="token punctuation">,</span>
<span class="token property">"role"</span><span class="token operator">:</span> <span class="token string">"admin"</span><span class="token punctuation">,</span>
<span class="token property">"created_at"</span><span class="token operator">:</span> <span class="token string">"2016-10-15T20:23:05.020Z"</span><span class="token punctuation">,</span>
<span class="token property">"updated_at"</span><span class="token operator">:</span> <span class="token string">"2016-10-15T20:23:05.020Z"</span><span class="token punctuation">,</span>
<span class="token property">"deleted_at"</span><span class="token operator">:</span> <span class="token string">"2016-10-16T16:16:22.365Z"</span>
<span class="token punctuation">}</span>
<span class="token punctuation">]</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<p><em>If you'd like to be notified when I publish new content, you can sign up for my mailing list in the navbar.</em></p>
<div class="promo-banner"><p>Check out my FREE course <a href="http://courses.codemore.io/" target="_blank">The Serverless Framework: Quick Start!</a></p></div>
</div>
<footer class="article-footer_purus">
<section class="written-by_purus">
<h6>Written by</h6>
<div class="footerflex_purus">
<div class="authorimage_purus" style="background-image: url(/content/images/2017/06/speakr_headshot_cropped.png)"></div>
<div class="about-author_purus">
<h4 class="authorname_purus"><a href="https://lorenstewart.me/author/lorenseanstewart">Loren Stewart</a></h4>
<p class="authorbio_purus">Writing code with the wonderful devs at Speaker, a data-driven influencer marketing company. Also an educator for Codemore.</p>
<h5>Los Angeles, CA</h5>
<h5><a href="http://codemore.io" itemprop="url">http://codemore.io</a></h5>
<br>
<a class="social-icon_purus" href="https://twitter.com/lorenstewart111"><i class="fa fa-twitter"><span class="hidden_purus">@lorenstewart111</span></i></a>
</div>
</div>
</section>
<section class="tags-post_purus">
<h6>Tags</h6>
<span><a href="/tag/node-js/">node.js</a><a href="/tag/express-js/">express.js</a><a href="/tag/sequelize/">sequelize</a><a href="/tag/api/">api</a><a href="/tag/javascript/">JavaScript</a></span>
</section>
</footer>
</div>
</article>
<!-- <div class="wrapper_purus">
<div class="disqusbox_purus">
<div id="disqus_thread"><form onsubmit="my.loadDisqus();return false;" class=formBlock><input class="cmd_purus" type=submit value="Load Comments"></form>
</div>
<script type="text/javascript">
var disqus_shortname = 'example'; // required: replace example with your Disqus shortname
/* * * DON'T EDIT BELOW THIS LINE * * */
var disqus_url = "https://lorenstewart.me/2016/10/16/sequelize-crud-102/";
var disqus_identifier = '';
var my = my || {};
my.loadDisqus = function() {
var dsq = document.createElement('script');
dsq.type = 'text/javascript';
dsq.async = true;
dsq.src = '//' + disqus_shortname + '.disqus.com/embed.js';
(document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(dsq);
};
</script>
<noscript>Please enable javascript to <a rel="nofollow" href="http://disqus.com/?ref_noscript">discus this page, powered by Disqus.</a></noscript>
<style scoped=scoped>
@media print{
.disqusbox{display:none}
}
</style>
</div>
</div> -->
</main>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment