What is SQL?Link to heading

It stands for “Server Query Language”:

• Server: abstract word for, in this case, a program that manages a database
• Query: a request/question you can make to that database
• Language: a convenient way to “query” a “server”

In simplified terms, SQL is “a way to ask for data”.

Just like “English” or “JavaScript”, it’s a language with a bunch of dialects (variations), in this case depending on the specific database being used.

What is a database?Link to heading

A database is a collection of structured data.

It’s that generic because it could be anything.

If you, a human (presumably), decided to keep track of what food items you have at home (your inventory), it might look something like this:

1
item, amount
2

3
eggs, 6
4
tomatoes, 2
5
bread slices, 10
6
lettuce heads, 1

That’s a database: you have some data, and you store it somewhere, in this case a file.

Just like the database we’ll look at later, it supports certain operations:

• If you buy eggs for the first time, you can Create a new row for them
• If you want to know how many eggs you have, you can Read the amount in the relevant row
• If you eat or buy an egg, you can Update the row to decrease the amount
• If you no longer care about eggs, you can Delete the relevant row

We call these CRUD operations, and typical databases support them.

While databases can be much more complex in order to deal with more data, more efficiently, the basic concept is pretty close to our inventory.txt example.

One of the most loved databases by startups and JavaScript developers for decades is Postgres, so that’s the one we’re going to work with today.

Why use a dedicated database?Link to heading

Say that instead of manually writing to that inventory.txt file, you want to make a web application with buttons so it’s easier to use.

You could write the values directly in your script.js, running in the browser:

1
// in your browser
2

3
const inventory = [
4
  { item: 'eggs', amount: 6 },
5
  { item: 'tomatoes', amount: 2 },
6
  // ...
7
]
8

9
useEggButton.onclick = () => {
10
  const eggs = inventory.find(row => row.item === 'eggs');
11
  eggs.amount = eggs.amount - 1;
12

13
  eggsDisplay.textContent = eggs.amount;
14
}

This works, as long as you never refresh or leave the page, and only ever use it from one computer.

Because as soon as you re-open the page, inventory will be reset.

So you want this data to come from your server!

1
// in your express.js server
2

3
const inventory = [
4
  { item: 'eggs', amount: 6 },
5
  { item: 'tomatoes', amount: 2 },
6
  // ...
7
]
8

9
app.get('/eggs/use', (req, res) => {
10
  const eggs = inventory.find(row => row.item === 'eggs');
11
  eggs.amount = eggs.amount - 1;
12

13
  res.send(eggs.amount);
14
})

Great! Now you can refresh the page, or use it from your phone, and the inventory will be shared!

But oh no! You found a bug somewhere in your server, and need to restart it after you fix the code. Your inventory is lost again!

So the server should get the data from a file, so even if you reset it or restart the computer, you don’t lose any data:

1
// in your express.js server
2

3
const inventory = JSON.parse(fs.readFileSync('inventory.json', 'utf-8'))
4

5
app.get('/eggs/use', (req, res) => {
6
  const eggs = inventory.find(row => row.item === 'eggs');
7
  eggs.amount = eggs.amount - 1;
8

9
  fs.writeFileSync('inventory.json', JSON.stringify(inventory));
10

11
  res.send(eggs.amount);
12
})

Hooray! For this usecase, that would be perfectly fine, and some simple applications do exactly this.

Say that instead of tracking your home inventory, you’re writing an application for your local supermarket’s inventory.

Our current database helps, but it’s very limited! We want to store not only the amount of each item, but each individual item’s:

• date_of_expiry for health safety reasons
• seller for contract reasons
• return_address in case of defects
• status, e.g. sold, returned, storage, shelves, ordered, …
• bar_code_id for scanning
• image_url for the website

Suddenly that’s a lot of information, for a lot of items! After a few months of business, it might reach gigabytes¹!

Node, along with most programs that are not about databases, is not good at keeping gigabytes of data in memory (in a variable). It will slow down and eventually crash.

We can split this into multiple files and load them one at a time, or distribute them among multiple servers and processes, but are you going to write the code to manage all that? While keeping all of them in sync? What about the code to quickly get the item from its bar_code_id so the scanning process is near-instant?

It turns out you’re not the first human (presumably) to have this problem, and you can use existing solutions!

PostgresLink to heading

In postgres, this is valid SQL code for our simple case:

1
-- equivalent of setting up the inventory.txt file:
2
CREATE TABLE inventory (item TEXT, amount NUMERIC);
3

4
-- "creating" an eggs row:
5
INSERT INTO inventory VALUES ('eggs', 6);
6

7
-- "reading" how many eggs you have:
8
SELECT amount FROM inventory WHERE item = 'eggs';
9

10
-- "updating" the amount of eggs (decreasing by 1):
11
UPDATE inventory SET amount = amount - 1 WHERE item = 'eggs';
12

13
-- "deleting" the eggs row:
14
DELETE FROM inventory WHERE item = 'eggs';

The best way to learn about this is hands-on, let’s get your own postgres database!

SupabaseLink to heading

There are many ways to play with postgres, but I reccomend you set up a free supabase account, and use the SQL editor in the dashboard.

That’s worth the entire minute it takes to set it up: supabase is a service used by many startups and personal projects to handle data and authentication, so learning how to use it will give you a leg up in the job market.

Create a project. Defaults are fine.

SQL EditorLink to heading

In the dashbord for that project, open the SQL Editor on the left side.

This is where SQL code goes.

This is an example SQL statement, it should never fail:

1
SELECT 1 + 1 AS result;

You can execute it by pressing Ctrl+Enter, you should see something like this:

JS RefresherLink to heading

You’ll see the JS equivalent of SQL code at many points in this article.

You may not have encountered some of these JS features before, so here’s a quick overview:

1
const double = n => n * 2  // arrow functions: concise syntax for functions
2
const double3 = double(3); // double3 = 3 * 2 = 6
3

4
const array = [1, 4, 6, 9]              // arrays and array methods
5
array.push(10);                         //   array = [1, 4, 6, 9, 10]
6
const tripled = array.map(n => n * 3);  // doubled = [3, 12, 18, 27, 30]: for every value, do `n => n * 3`
7
const small = array.filter(n => n < 5); //   small = [1, 4]: values where `n => n < 5` is `true`
8
const middle = array.slice(1, 3);       //  middle = [4, 6]: values from index 1 to 3 (excluded)
9

10
const object = { x: 1, y: 2 };         // objects: object.x = 1; object.y = 2;
11
const { x } = object;                  // object destructuring: same as `const x = object.x`
12
const extension = { ...object, z: 3 }; // object spreading: = { x: object.x, y: object.y, z: 3 }
13
const otherObj = { a: 0, x };          // object property shorthand: = { a: 0, x: x }
14

15
// all combined
16
const objArray = [{ x: 1, y: 2 }, { x: 3, y: 4 }, { x: 5, y: 6 }];
17
const xs = objArray.map(({ x }) => x);                   // [1, 3, 5]
18
const onlyXs = objArray.map(({ x }) => ({ x }));         // [{ x: 1 }, { x: 3 }, { x: 5 }]
19
const withZ = objArray.map((obj) => ({ ...obj, z: 3 })); // [{ x: 1, y: 2, z: 3 }, ...]

If you don’t understand them, don’t worry too much, but when you encounter them you can always get back to this section

CreateLink to heading

In postgres, most queries act on [“tables”](LINK NEEDED).

In JS terms, that’s an [array](LINK NEEDED) of [objects](LINK NEEDED)²:

1
CREATE TABLE inventory (item TEXT, amount NUMERIC)
2

3
INSERT INTO inventory VALUES ('eggs', 6);

1
let inventory = [];
2

3
inventory.push({ item: 'eggs', amount: 6 }):

We have:

• an inventory table (array),
• with an eggs row (object)
• and item, amount columns (properties)

In a table, all rows have the same columns, meaning all objects in the array have the same property names and types.

In postgres, TEXT is similar to JavaScript’s string, and NUMERIC is similar to JavaScript’s number. The data types in postgres are [much more varied](LINK NEEDED), but you can do plenty with just a few.

Feel free to execute the above SQL, and let’s add a few more rows:

1
INSERT INTO inventory
2
VALUES ('tomatoes', 2),
3
       ('bread slices', 10),
4
       ('lettuce heads', 1);

1
inventory.push(
2
  { item: 'tomatoes', amount: 2 },
3
  { item: 'bread slices', amount: 10 },
4
  { item: 'lettuce heads', amount: 1 });

To see what our table looks like now, you can go to supabase’s “Table Editor”, just above the SQL Editor, and you should see something like this:

You can always get an overview of what your data looks like by going there, but don’t rely on it too much or you won’t learn any SQL!

Anything you can do in that screen, you can also do it via SQL queries: the table above is the result of SELECT * FROM inventory.

ReadLink to heading

This is the most common operation, and what you should practice most.

SELECTLink to heading

To read data from a table, we use SELECT statements.

How many eggs do we have?

1
SELECT amount
2
  FROM inventory
3
 WHERE item = 'eggs';

1
inventory
2
  .filter(({ item }) => item === 'eggs')
3
  .map(({ amount }) => ({ amount }))

Each of those lines are called clauses:

• the SELECT clause defines which column(s) to read out (amount)
• the FROM clause defines which table to look at (inventory)
• the WHERE clause defines which row(s) to look at (ones that have item = 'eggs')

A SELECT statement can have many clauses, and each clause has its own syntax.

For a full overview of what’s available, see the documentation. Don’t worry, we’ll go through the important ones, with friendlier descriptions.

You can use the documentation or other tutorials for an in-depth understanding of each individual clause, here we focus on the big picture and learning by example.

I will throw some code at you, play around with it in the SQL editor to try and understand it.

SELECTLink to heading

Decides which column(s) to include in the result:

1
SELECT item
2
  FROM inventory

1
inventory
2
  .map(({ item }) => ({ item }))

1
SELECT item, amount
2
  FROM inventory;
3

4
 -- shorthand for all columns
5
 SELECT *
6
  FROM inventory

1
inventory
2
  .map(({ item, amount }) => ({ item, amount });
3

4

5
inventory
6
  .map(row => ({ ...row }));

You can use AS for simple renaming. We call this “aliasing”:

1
SELECT item, amount AS count
2
  FROM inventory

1
inventory
2
  .map(({ item, amount }) => ({ item, count: amount }));

Or to name a column that is the result of a calculation

1
SELECT item, amount, amount/2 AS half
2
  FROM inventory

1
inventory
2
  .map(({ item, amount }) => ({ item, amount, half: amount/2 }));

WHERELink to heading

Only considers the rows that match the criteria in the result:

1
SELECT item, amount
2
  FROM inventory
3
 WHERE item = 'tomatoes'

1
inventory
2
  .filter({ item }) => item === 'tomatoes')
3
  .map(({ item, amount }) => ({ item, amount }));

The result can be more than one row:

1
SELECT item, amount
2
  FROM inventory
3
 WHERE amount < 3

1
inventory
2
  .filter({ item }) => amount < 3)
3
  .map(({ item, amount }) => ({ item, amount }));

For multiple conditions, use AND/OR:

1
SELECT item, amount
2
  FROM inventory
3
 WHERE amount < 3
4
       -- even amounts
5
       AND mod(amount, 2) = 0

1
inventory
2
  .filter({ item }) =>
3
    amount < 3
4
    && (amount % 3 === 0))
5
  .map(({ item, amount }) => ({ item, amount }));

ORDER BYLink to heading

To sort the order of the results:

1
SELECT item, amount
2
  FROM inventory
3
 ORDER BY amount

1
inventory
2
  .sort((a, b) => a.amount - b.amount)
3
  .map(({ item, amount }) => ({ item, amount }));

By default, it’s in “ascending” order (increasing, a to b). You can specify a descending order instead:

1
SELECT item, amount
2
  FROM inventory
3
 ORDER BY amount DESC

1
inventory
2
  .sort((a, b) => a.amount - b.amount).reverse()
3
  .map(({ item, amount }) => ({ item, amount }));

You’re not limited to numbers, you can also sort alphabetically:

1
SELECT item, amount
2
  FROM inventory
3
 ORDER BY item

1
inventory
2
  .sort((a, b) => a.item.localeCompare(b.item))
3
  .map(({ item, amount }) => ({ item, amount }));

Pagination: LIMIT + OFFSETLink to heading

For a bit more depth, let’s break down a common feature: pagination.

1
SELECT item, amount
2
  FROM inventory
3
 LIMIT 2

1
inventory
2
  .map(({ item, amount }) => ({ item, amount }));
3
  .slice(0, 2)

When LIMIT is set, the query returns only the first N results. This is useful so that when you don’t have to all of the relevant rows from the database at once. But how do you get the the next N results?

That’s where OFFSET comes in. We can tell it to skip the first 2 results to get the 2nd page:

1
SELECT item, amount
2
  FROM inventory
3
 LIMIT 2 OFFSET 2

1
inventory
2
  .map(({ item, amount }) => ({ item, amount }));
3
  .slice(2, 2 + 2) // from offset, to offset + limit

In combination with ORDER BY, this is how most results are paginated on websites.

For example, imagine we had thousands of items in a supermarket inventory, and on the website a user clicks a filter for “in stock” (amount >= 1), and sort by “name” in alphabetical order.

They start at page 1, where we show results 1-20, so our application gets the data for those 20 items with this query:

1
SELECT item
2
  FROM inventory
3
 WHERE amount >= 1
4
 ORDER BY item
5
 LIMIT 20 OFFSET 0

1
inventory
2
  .filter(({ amount }) => amount >= 1)
3
  .sort((a, b) => a.item.localeCompare(b.item))
4
  .slice(0, 0 + 20)
5
  .map(({ item }) => ({ item }))

Now they click a button to get to page 2, where we have items 21-40, so our query just changes the offset:

1
SELECT item
2
  FROM inventory
3
 WHERE amount >= 1
4
 ORDER BY item
5
 LIMIT 20 OFFSET 20

1
inventory
2
  .filter(({ amount }) => amount >= 1)
3
  .sort((a, b) => a.item.localeCompare(b.item))
4
  .slice(20, 20 + 20)
5
  .map(({ item }) => ({ item }))

For page 3, we’d just need to change the OFFSET to 40.

In general, if you’re on page page, and each page displays pageSize (20) items, the query is:

1
...
2
LIMIT <pageSize> OFFSET (<page>-1) * <pageSize>

We use page-1 so that page 1 doesn’t skip the first 20 items. Take a moment to convince yourself this works.

CountLink to heading

We’re missing one bit of information to display on the page: how many total items we have.

1
SELECT COUNT(*) AS total
2
  FROM inventory
3
 WHERE amount >= 1

1
inventory
2
  .filter(({ amount }) => amount >= 1)
3
  .length

If our page size is 20, that means we have 10 pages in total.

Many tablesLink to heading

What if you have multiple tables that are relevant for the data you’re trying to find?

1:many Link to heading

Here’s the scenario: once a month, you want to email your customers letting reminding them to come shop and fill their fridge. But only for items we still have in stock!

You have a purchases table that has a row for every purchase that has happened:

1
CREATE TABLE purchases (customer_email TEXT, item TEXT);
2

3
INSERT INTO purchases
4
VALUES ('alice@gmail.com', 'avocados'),
5
       ('bob@proton.me', 'tomatoes'),
6
       ('bob@proton.me', 'eggs'),
7
       ('charlie@yahoo.com', 'eggs'),
8
       ('charlie@yahoo.com', 'avocados');
9

10
-- an out of stock item
11
INSERT INTO inventory
12
VALUES ('avocados', 0);

So we want that list of emails!

We don’t have any avocados in stock:

• Alice only ever bought avocados, so no point in sending her an email.
• Charlie bought avocados, but also eggs, which we do have, so better drop an email!
• Bob never liked avocados, so an email for him would make sense.

Easy enough to look at our tables and figure it out, but what if we wanted to this with SQL, once we have too much data to process manually?

We can get a full list of emails with this query:

1
SELECT customer_email
2
  FROM purchases

1
purchases
2
  .map(({ customer_email }) => ({ customer_email }));

Alice is in that list, but she shouldn’t be. How do we only select emails from purchases where the purchased item’s inventory amount is greater than 0?

1
SELECT customer_email
2
  FROM purchases
3
  -- not valid! Which inventory row should we look at?
4
 WHERE inventory.amount > 0

That’s where JOIN comes in. It looks complicated, but bear with it:

1
SELECT purchases.customer_email
2
  FROM purchases
3
  JOIN inventory
4
    ON inventory.item = purchases.item
5
 WHERE intentory.amount > 0

1
purchases // FROM
2
  .flatMap((purchases_row) =>
3
    inventory
4
      .filter(({ item }) => item === purchases_row.purchased_item) // ON
5
      .map((inventory_row) => ({ purchases_row, inventory_row }))) // JOIN
6
  .filter(({ inventory_row }) => inventory_row.amount > 0) // WHERE
7
  .map(({ purchases_row }) => ({ customer_email: purchases_row.customer_email }));

This is where SQL starts looking much simpler than JavaScript.

If you think you can understand the JS, give it a go. Run it through with an example, it will make the intuition for JOIN easier.

Step-by-step JOIN/ON/WHERELink to heading

For the rest of us, here’s a more detailed step-by-step breakdown:

1. JOIN considers all combinations of rows from purchases and inventory

1
SELECT purchases.customer_email,
2
       purchases.item AS purchased_item,
3
       inventory.item AS inventory_item,
4
       inventory.amount,
5
  FROM purchases
6
  JOIN inventory

1
purchases // FROM
2
  .flatMap((purchases_row) =>
3
    inventory
4
      .map((inventory_row) => ({ purchases_row, inventory_row }))) // JOIN
5
  .map(({ purchases_row, inventory_row }) => ({
6
    customer_email: purchases_row.customer_email,
7
    purchased_item: purchases_row.item,
8
    inventory_item: inventory_row.item,
9
    amount: inventory_row.amount
10
  });

Big table! If purchases has 5 rows, and inventory also has 5 rows, this temporarily table that combines them is 5x5 = 25 rows.

We don’t care about most of these, the only reason we want to combine them is so we can get inventory.amount for the item that was purchased.

So we only want to keep the rows where inventory.item = purchase.item. We do that with ON:

1
SELECT purchases.customer_email,
2
       purchases.item AS purchased_item,
3
       inventory.item AS inventory_item,
4
       inventory.amount,
5
  FROM purchases
6
  JOIN inventory
7
    ON inventory.item = purchases.item

1
purchases // FROM
2
  .flatMap((purchases_row) =>
3
    inventory
4
      .filter(({ item }) => item === purchases_row.item) // ON
5
      .map((inventory_row) => ({ purchases_row, inventory_row }))) // JOIN
6
  .map(({ purchases_row, inventory_row }) => ({
7
    customer_email: purchases_row.customer_email,
8
    purchased_item: purchases_row.item,
9
    inventory_item: inventory_row.item,
10
    amount: inventory_row.amount
11
  });

Much nicer! We now only have 1 row per purchase, because each purchases row corresponds to exactly 1 inventory row.

Now we just need to get rid of the ones where the amount is 0!

Adding WHERE inventory.amount > 0 is enough to get rid of the avocado purchases:

See the first JOIN example for the full query.

Note that, technically, omitting the ON clause and using WHERE inventory.item = purchases.item AND inventory.amount > 0 would have lead to the same output.

By convention, if the condition is about picking which rows to join, we put that in the ON clause.

It might affect performance not to do it this way. For example, note that in the JS equivalent, we filter using ON before we filter on the joined rows using WHERE, leading to less comparisons needed overall.

In reality, databases like Postgres are very advanced and perform tons of optimizations behind the scenes, so it might not matter. What’s important for now is to understand the concept of what a JOIN/ON is.

DISTINCTLink to heading

Almost there! The only issue is that our result now includes a duplicate email for Bob.

The way we tell Postgres to only provide unique results is with DISTINCT:

1
SELECT DISTINCT customer_email
2
  FROM purchases

1
purchases
2
  .map(({ customer_email }) => ({ customer_email }))
3
  .filter(isUnique) // DISTINCT

We haven’t defined isUnique, but a concise, inefficient, implementation could look something like this:

1
const isEqual = (a, b) => JSON.stringify(a) === JSON.stringify(b);
2
const isUnique = (a, _index, array) =>
3
  array.filter(b => isEqual(a, b)).length === 1;

This uniqueness constraint happens after everything else, and depends on the specific values in the result.

For example, if the result also had to include the purchased item, there would no longer be duplicate rows, so DISTINCT would do nothing.

many:many Link to heading

Alright! You’ve sent those emails: “Dear <name>, you must be starving, come shop again! We have your items in stock”.

You wait for your helpful feature to reach the customers, and for profit to happen, but instead you get a bunch of angry responses: “Unsolicited emails? No thank you, I’m shopping somewhere else from now on!”.

Of course! We focused so much on whether we could, we never stopped to consider whether we should!

It’s still a helpful feature, and some customers might genuinely appreciate knowing that what they typically buy is still in stock after they ran out.

The sensible thing to do is to allow them to “opt in” for this feature. A setting on the website for “I would like to receive monthly information about stock”.

This information goes into a new table:

1
CREATE TABLE customers (email TEXT, wants_updates BOOLEAN);
2

3
INSERT INTO customers
4
     VALUES ('alice@gmail.com', true),
5
            ('bob@proton.me', false),
6
            ('charlie@yahoo.com', true);

Ok! We can now write a new query that also checks if the customer wants updates, before sending the automatic email:

1
SELECT customers.email
2
  FROM customers
3
  JOIN purchases
4
    ON purchases.customer_email = customers.email
5
  JOIN inventory
6
    ON inventory.item = purchases.item
7
 WHERE customers.wants_updates
8
       AND inventory.amount > 0

1
customers // FROM
2
  .flatMap((customers_row) =>
3
    purchases
4
      .filter(({ customer_email }) => customer_email === customers_row.email) // ON
5
      .map((purchases_row) => ({ customers_row, purchases_row }))) // JOIN
6
  .flatMap((row) =>
7
    inventory
8
      .filter(({ item }) => item === row.purchases_row.purchased_item) // ON
9
      .map((inventory_row) => ({ ...row, inventory_row }))) // JOIN
10
  .filter(({ inventory_row, customers_row }) =>
11
    customers_row.wants_updates && inventory_row.amount > 0) // WHERE
12
  .map(({ customers_row }) => ({ email: customers_row.email }));

We need information from all 3 tables, so we need to combine all of them, so we need 2 JOINs!

Conceptually, this temporarily creates a table that has customers.length * purchases.length * inventory.length rows: 3x5x5 = 75.

We can then say which rows we care about with ON, and finally which of the rows with the full information we want with WHERE:

• Alice wanted updates, but we didn’t have any for her, so no email
• Bob didn’t want updates, so even though we had some, no email
• Charlie is the only one that wanted updates, and we had some.

If you could write the above query by yourself in similar contexts, your SQL would be better than most JS developers I’ve met!

So give it a go, invent a scenario, make the database for it, and practice querying it!

UpdateLink to heading

UPDATE + SETLink to heading

The syntax is similar to SELECT’s.

We re-supply our ‘eggs’ , and need to increase the amount by 6.

1
UPDATE inventory
2
   SET amount = amount + 6
3
 WHERE item = 'eggs'

1
inventory
2
  .filter(({ item }) => item === 'eggs')
3
  .forEach((row) => { row.amount = row.amount + 6 });

Our store went up in flames and we all of our inventory burnt up? That’s a problem for our business, but easy to represent in our database:

1
UPDATE inventory
2
   SET amount = 0

1
inventory
2
  .forEach((row) => { row.amount = 0 });

If you don’t specify WHERE, it will match all of the rows, just as with SELECT.

So watch out, this one is easy to execute accidentally, and there’s no undo button!

ALTERLink to heading

Our database has had a noticeable flaw: we don’t have prices listed for each item!

The online shop experience must have been very confusing.

We don’t need to make a new table for it though, we can add a column to the existing table:

1
ALTER TABLE inventory
2
 ADD COLUMN price NUMERIC DEFAULT 0

1
inventory
2
  .forEach((row) => { row.price = 0 });

We would then UPDATE the price of our items as needed.

DeleteLink to heading

DELETE FROMLink to heading

Bob sent us a GDPR request to delete his information from our servers.

1
DELETE FROM customers
2
      WHERE email = 'bob@proton.me';
3

4
UPDATE purchases
5
   SET customer_email = '<redacted>'
6
 WHERE customer_email = 'bob@proton.me';

1
customers = customers
2
  .filter(({ email }) => email !== 'bob@proton.me');
3

4
purchases
5
  .filter(({ customer_email }) => customer_email === 'bob@proton.me')
6
  .forEach((row) => { row.customer_email = '<redacted>' });

DROP TABLELink to heading

You can also delete an entire table.

Say we decided that we don’t want to track purchases anymore because we care about privacy.

1
DROP TABLE purchases;

1
delete globalThis.purchases

Again, no “undo” button in SQL! Act with care!

Nevermind that it would be an accounting nightmare.

ConclusionLink to heading

If you’ve followed this article and practiced, you could now handle the data side of a typical startup.

TODO links

There’s a few more concepts you should learn about before you go all out.

• SQL injection (SQLi): blindly replacing strings in JS to execute SQL may bankrupt you!
• primary keys: efficiently identifying a row by the value of a column, e.g. customers.email.
• foreign keys: efficiently identifying rows in other tables, by the value of a column in this table, e.g. purchases.customer_email -fk> customers.email.
• schemas: a way to organize tables. The public schema is like the window object in a browser: you automatically access it.

And some advanced ones that may not be needed right away:

• indices: efficiently finding rows of a table by the value of other columns. Like primary keys, but more flexible.
• data normalization: avoid messy data structures, with duplicate data or unnecessary tables.

More articles are coming about understanding SQL from the perspective of a JS dev, let me know if you’d like to learn about a particular topic!