Module 1: Database Basics

How to Build It

The syntax for SQL is English-like and requires fewer symbols than programming languages like C, Java, and JavaScript. It is declarative and concise, which means there is much less to learn to use effectively.

When learning SQL, it is helpful to understand that each command is designed for a different purpose. If we classify the commands by purpose, we'll end up with the following sub-categories of SQL:

• Data Definition Language (DDL): used to modify database objects. Some examples are: CREATE TABLE, ALTER TABLE, and DROP TABLE.
• Data Manipulation Language (DML): used to manipulate the data stored in the database. Some examples are: INSERT, UPDATE, and DELETE.
• Data Query Language (DQL): used to ask questions about the data stored in the database. The most commonly used SQL command is SELECT, and it falls in this category.
• Data Control Language (DCL): used to manage database security and user's access to data. These commands fall into the realm of Database Administrators. Some examples are GRANT and REVOKE.
• Transaction Control Commands: used for managing groups of statements that must execute as a unit or not execute at all. Examples are COMMIT and ROLLBACK.

As a developer, you'll need to get familiar with DDL and become proficient in using DML and DQL. Therefore, this lesson will cover only DML and DQL commands.

How to Build It

Filtering results using WHERE clause

When querying a database, the default result will be every entry in the given table. However, often, we are looking for a specific record or a set of records that meets certain criteria.

A WHERE clause can help in both cases.

Here's an example where we might only want to find customers living in Berlin.

select City, CustomerName, ContactName
from Customers
where City = 'Berlin'

We can also chain together WHERE clauses using OR and AND to limit our results further.

The following query includes only records that match both criteria.

select City, CustomerName, ContactName
from Customers
where Country = 'France' and City = 'Paris'

And this query includes records that match either criteria.

select City, CustomerName, ContactName
from Customers
where Country = 'France' or City = 'Paris'

These operators can be combined and grouped with parentheses to add complex selection logic. They behave similarly to what you're used to in programming languages.

You can read more about SQLite operators from w3resource.

To select a single record, we can use a WHERE statement with a uniquely identifying field, like an id:

select * from Customers
where CustomerId=3;

Other comparison operators also work in WHERE conditions, such as >, <, <=, and >=.

select * from employees where salary >= 50000

Ordering results using the ORDER BY clause

Query results are shown in the same order the data was inserted. To control how the data is sorted, we can use the ORDER BY clause. Let's see an example.

-- sorts the results first by salary in descending order, then by the last name in ascending order
select * from employees order by salary desc, last_name;

We can pass a list of field names to order by and optionally choose asc or desc for the sort direction. The default is asc, so it doesn't need to be specified.

Some SQL engines also support using field abbreviations when sorting.

select name, salary, department from employees order by 3, 2 desc;

In this case, the results are sorted by the department in ascending order first and then by salary in descending order. The numbers refer to the fields' position in the selection portion of the query, so 1 would be name, 2 would be salary, and so on.

Note that the WHERE clause should come after the FROM clause. The ORDER BY clause always goes last.

select * from employees where salary > 50000 order by last_name;

Limiting results using the LIMIT clause

When we wish to see only a limited number of records, we can use a LIMIT clause.

The following returns the first ten records in the products table:

select * from products
limit 10

LIMIT clauses are often used in conjunction with ORDER BY. The following shows us the five most expensive products:

select * from products
order by price desc
limit 5

Inserting data using INSERT

An insert statement adds a new record to the database. All non-null fields must be listed out in the same order as their values. Some fields, like ids and timestamps, may be auto-generated and do not need to be included in an INSERT statement.

-- we can add fields in any order; the values need to be in the same ordinal position
-- the id will be assigned automatically
insert into Customers (Country, CustomerName, ContactName, Address, City, PostalCode)
values ('USA', 'BloomTech', 'Austen Allred', '1 Bloom Court', 'Provo', '84601');

The values in an insert statement must not violate any restrictions and constraints the database has in place, such as expected data types. We will learn more about constraints and schema design in a later section.

Modifying recording using UPDATE

When modifying a record, we identify a single record or a set of records to update using a WHERE clause. Then we can set the new value(s) in place.

update Customers
set City = 'Silicon Valley', Country = 'USA'
where CustomerName = 'BloomTech'

Technically the WHERE clause is not required, but leaving it off would result in every record within the table receiving the update.

Removing records using DELETE

When removing a record or set of records, we need only identify which record(s) to remove using a WHERE clause:

delete from Customers
where CustomerName = 'BloomTech';

Once again, the WHERE clause is not required, but leaving it off would remove every record in the table, so it's essential.

How to Build It

Knex Setup

To use Knex in a repository, we'll need to add two libraries:

npm install knex sqlite3

knex is our query builder library, and sqlite3 allows us to interface with a sqlite database. We'll learn more about sqlite and other database management systems in the following module. For now, know that you need both libraries.

Next, we use Knex to set up a config file:

const knex = require('knex');

const config = {
  client: 'sqlite3',
  connection: {
    filename: './data/posts.db3',
  },
  useNullAsDefault: true,
};

module.exports = knex(config);

We need to call Knex and pass in a config object to use the query builder elsewhere in our code. We'll be discussing knex configuration more in a future module. Still, we only need the client, connection, and useNullAsDefault keys, as shown above. The filename should point towards the pre-existing database file, which the .db3 extension can recognize.

Once Knex is configured, we can import the above config file anywhere in our codebase to access the database.

const db = require('../data/db-config.js);

The db object provides methods that allow us to begin building queries.

SELECT using Knex

In Knex, the equivalent of SELECT * FROM users is:

db.select('*').from('users');

There's a simpler way to write the same command:

db('users');

Using this, we could write a GET endpoint.

router.get('/api/users', (req, res) => {
  db('users')
  .then(users => {
    res.json(users);
  })
  .catch (err => {
    res.status(500).json({ message: 'Failed to get users' });
  });
});

NOTE: All Knex queries return promises.

Knex also allows for a where clause. In Knex, we could write SELECT * FROM users WHERE id=1 as

db('users').where({ id: 1 });

This method will resolve to an array containing a single entry like so: [{ id: 1, name: 'bill' }].

Using this, we might add a GET endpoint where a specific user:

server.get('api/users/:id', (req, res) => {
  const { id } = req.params;

  db('users').where({ id })
  .then(users => {
    // we must check the length to find our if our user exists
    if (users.length) {
      res.json(users);
    } else {
      res.status(404).json({ message: 'Could not find user with given id.' })
   })
  .catch (err => {
    res.status(500).json({ message: 'Failed to get user' });
  });
});

INSERT using Knex

In Knex, the equivalent of INSERT INTO users (name, age) VALUES ('Eva', 32) is:

db('users').insert({ name: 'Eva', age: 32 });

The insert method in Knex will resolve to an array containing the newly created id for that user like so: [3].

UPDATE using Knex

In knex, the equivalent of UPDATE users SET name='Ava', age=33 WHERE id=3; is:

db('users').where({ id: 3 })
.update({name: 'Ava', age: 33 });

Note that the where method comes before update, unlike in SQL.

Update will resolve to a count of rows updated.

DELETE using Knex

In Knex, the equivalent of DELETE FROM users WHERE age=33; is:

db('users').where({ age: 33}).del();

Once again, the where must come before the del. This method will resolve to a count of records removed.