title: REST API - Single vs Bulk POST / PATCH (SQL INSERT / UPDATE)
body: <p>I know that this question has been asked a bunch of times, but I they don't address the database SQL part, and don't have a suitable example.</p>
  
  <p><strong>The goal is to insert or update an invoice in a database, more specifically insert or update the items in the invoice, using a REST API.</strong></p>
  
  <hr>
  
  <h1>Data</h1>
  
  <p>The data is comprised of two parts.</p>
  
  <pre><code>let invoiceHeader = {
    id: 123,
    key: "2019/00001",
    client: 42
  }
  
  let invoiceItems = [
    { id: 2001, sku: "A12345", quantity: 10, price: 300 },
    { id: 2002, sku: "B54321", quantity: 6, price: 500 }, 
    { id: 2003, sku: "C11223", quantity: 20, price: 200 },
  ]
  </code></pre>
  
  <hr>
  
  <h1>Database</h1>
  
  <p>This data needs to be inserted into 2 database tables.</p>
  
  <pre><code>invoice
  +-----+------------+--------+
  | id  |    key     | client |
  +-----+------------+--------+
  | 123 | 2019/00001 |   42   |
  +-----+------------+--------+
  
  invoiceItems
  +------+--------------+--------+----------+-------+
  |  id  | fk_invoiceId |  sku   | quantity | price |
  +------+--------------+--------+----------+-------+
  | 2001 |          123 | A12345 |       10 |   300 |
  | 2002 |          123 | B54321 |        6 |   500 |
  | 2003 |          123 | C11223 |       20 |   200 |
  +------+--------------+--------+----------+-------+
  </code></pre>
  
  <hr>
  
  <h1>Invoice Header</h1>
  
  <p>The invoice header is straightforward</p>
  
  <p><strong>Insert Invoice Header (Create new invoice)</strong></p>
  
  <pre><code>POST http://example.com/api/invoices
  {
    "key": "2019/00001",
    "client": 42
  }
  
  INSERT INTO invoice (key, client) VALUES ('2019/00001', 42);
  </code></pre>
  
  <p><strong>Update Invoice Header (with new client)</strong></p>
  
  <pre><code>PATCH http://example.com/api/invoices/123
  {
    "id": 123,
    "client": 66
  }
  
  UPDATE invoice SET client = 66 WHERE id = 123;
  </code></pre>
  
  <hr>
  
  <h1>Invoice Items</h1>
  
  <p>However, the invoice items can be done in 2 ways.</p>
  
  <h3>Approach 1 - Single</h3>
  
  <p><strong>Insert Invoice Items</strong></p>
  
  <pre><code>POST http://example.com/api/invoices/123/items
  { "sku": "A12345", "quantity": 10, "price": 300 }
  INSERT INTO invoiceItems (sku, quantity, price) VALUES ('A12345', 10, 300);
  
  POST http://example.com/api/invoices/123/items
  { "sku": "B54321", "quantity": 6, "price": 500 }
  INSERT INTO invoiceItems (sku, quantity, price) VALUES ('B54321', 6, 500);
  
  POST http://example.com/api/invoices/123/items
  { "sku": "C11223", "quantity": 20, "price": 200 }
  INSERT INTO invoiceItems (sku, quantity, price) VALUES ('C11223', 20, 200);
  </code></pre>
  
  <p><strong>Update Invoice item (new quantity and price)</strong></p>
  
  <pre><code>PATCH http://example.com/api/invoices/123/items/2001
  { "id": 2001, "quantity": 23, "price": 130 }
  UPDATE invoiceItems SET quantity = 23, price = 130 WHERE id = 2001;
  </code></pre>
  
  <hr>
  
  <h3>Approach 2 - Bulk</h3>
  
  <p><strong>Insert Invoice Items</strong></p>
  
  <pre><code>POST http://example.com/api/invoices/123/items
  [
    { "sku": "A12345", "quantity": 10, "price": 300 },
    { "sku": "B54321", "quantity": 6, "price": 500 }, 
    { "sku": "C11223", "quantity": 20, "price": 200 },
  ]
  
  INSERT INTO invoiceItems (sku, quantity, price) 
  VALUES 
    ('A12345', 10, 300),
    ('B54321', 6, 500),
    ('C11223', 20, 200)
  ;
  </code></pre>
  
  <p><strong>Update Invoice item (new quantity and price for a single item)</strong></p>
  
  <pre><code>PATCH http://example.com/api/invoices/123/items
  [
    { "id": 2001, "sku": "A12345", "quantity": 23, "price": 130 },
    { "id": 2002, "sku": "B54321", "quantity": 6, "price": 500 }, 
    { "id": 2003, "sku": "C11223", "quantity": 20, "price": 200 },
  ]
  
  INSERT INTO table (id, sku, quantity, price) 
  VALUES 
      (2001, 'A12345', 10, 300),
      (2002, 'B54321', 6, 500),
      (2003, 'C11223', 20, 200)
  ON DUPLICATE KEY UPDATE 
      sku = values(sku),
      quantity = values(quantity),
      price = values(price)
  ;
  </code></pre>
  
  <hr>
  
  <h1>Summary</h1>
  
  <p>The single approach makes a ton of requests, while the bulk approach sends unnecessary data.</p>
  
  <p>Keep in mind that an invoice can easily have 300+ items.</p>
  
  <p><strong>So, which approach is better in your opinion?</strong></p>