Module 2: Schema Design

How to Build It

What is a DBMS?

To manage digital databases we use specialized software called DataBase Management Systems (DBMS). These systems typically run on servers and are managed by DataBase Administrators (DBAs).

In less technical terms, we need a type of software that will allow us to create, access, and generally manage our databases. In the world of relational databases, we specifically use Relational Database Management Systems (RDBMs). Some examples are Postgres, SQLite, MySQL, and Oracle.

Choosing a DBMS determines everything from how you set up your database, where and how the data is stored, and what SQL commands you can use. Most systems share the core of the SQL language that you've already learned.

In other words, you can expect SELECT, UPDATE, INSERT, WHERE , and the like to be the same across all DBMSs, but the subtleties of the language may vary.

What is SQLite?

SQLite is the DBMS we primarily use at BloomTech. As the name suggests, it is a more lightweight system and thus easier to set up than others.

SQLite allows us to store databases as single files. Many of the challenges and guided projects in Lambda have a .db3 extension. That is the database.

SQLite is not a database (like relational, graph, or document are databases) but rather a database management system.

Opening an existing database in SQLite Studio

One useful visual interface we might use with an SQLite database is called SQLite Studio. Install SQLite Studio here.

Once installed, we can use SQLite Studio to open any .db3 file from a previous lesson. We may view the tables, view the data, and even make changes to the database.

For a more detailed look at SQLite Studio, follow along in the video above.

How to Build It

Schema Design in SQLite Studio

For a look at schema design in SQLite Studio, follow along in the video above.

Table Design Considerations

When designing a single table, we need to ask three things:

• What fields (or columns) are present?
• What type of data do we expect for each field?
• Are there other restrictions needed for each column?

Looking at the following schema diagram for an accounts table, we can answer each other those questions:

field	data type	metadata
id	unsigned integer	primary key, auto-increments, generated by database
name	string	required, unique
budget	numeric	required

Table Fields

Choosing which fields to include in a table is relatively straightforward. What information needs to be tracked regarding this resource? In the real world, this is determined by the intended use of the product or app.

However, this is one requirement every table should satisfy: a primary key. A primary key is a way to identify each entry in the database uniquely. It is most often represented as an auto-incrementing integer called id or [tablename]Id

Datatypes

Each field must also have a specified datatype. The data type available depends on our DBMS. Some supported datatype in SQLite include:

• Null: Missing or unknown information.
• Integer: Whole numbers.
• Real: Any number, including decimals.
• Text: Character data.
• *Blob: a large binary object that can be used to store miscellaneous data.

Any data inserted into the table must match the data types determined in schema design.

Constraints

Beyond data types, we may add additional constraints on each field. Some examples include:

• Not Null: The field cannot be left empty
• Unique: No two records can have the same value in this field
• Primary key: - Indicates this field is the primary key. Both the not null and unique constraints will be enforced.
• Default: - Sets a default value if none is provided.

As with data types, any data that does not satisfy the schema constraints will be rejected from the database.

Multi-Table Design

Another critical component of schema design is to understand how different tables relate to each other. This will be covered in a later lesson.

How to Build It

Knex Cli

To use migrations (and to make Knex setup easier), we need to use knex cli. Install knex globally with npm install -g knex.

This allows you to use Knex commands within any repo that has Knex as a local dependency. If you have any issues with this global install, you can use the npx knex command instead.

Initializing Knex

To start, add the knex and sqlite3 libraries to your repository.

npm install knex sqlite3

We've seen how to use manually create a config object to get started with Knex, but the best practice is to use the following command:

knex init

Or, if Knex isn't globally installed:

npx knex init

This command will generate a file in your root folder called knexfile.js. It will be auto populated with three config objects, based on different environments. We can delete all except for the development object.

module.exports = {

  development: {
    client: 'sqlite3',
    connection: {
      filename: './dev.sqlite3'
    }
  }

};

We'll need to update the location (or desired location) of the database as well as add the useNullAsDefault option. The latter option prevents crashes when working with sqlite3.

module.exports = {

  development: {
    // our DBMS driver
    client: 'sqlite3',
    // the location of our db
    connection: {
      filename: './data/database_file.db3',
    },
    // necessary when using sqlite3
    useNullAsDefault: true
  }

};

Now, wherever we configure our database, we may use the following syntax instead of hardcoding in a config object.

const knex = require('knex');

const config = require('../knexfile.js');

// we must select the development object from our knexfile
const db = knex(config.development);

// export for use in codebase
module.exports = db;

Knex Migrations

Once our knexfile is set up, we can begin creating migrations. Though it's not required, we are going to add an addition option to the config object to specify a directory for the migration files.

development: {
    client: 'sqlite3',
    connection: {
      filename: './data/produce.db3',
    },
    useNullAsDefault: true,
    // generates migration files in a data/migrations/ folder
    migrations: {
      directory: './data/migrations'
    }
  }

We can generate a new migration with the following command:

knex migrate:make [migration-name]

If we needed to create an accounts table, we might run:

knex migrate:make create-accounts

Note that inside data/migrations/ a new file has appeared. Migrations have a timestamp in their filenames automatically. Wither you like this or not, do not edit migration names.

The migration file should have both an up and a down function. Within the up function, we write the ended database changes. Within the down function, we write the code to undo the up functions. This allows us to undo any changes made to the schema if necessary.

exports.up = function(knex, Promise) {
  // don't forget the return statement
  return knex.schema.createTable('accounts', tbl => {
    // creates a primary key called id
    tbl.increments();
    // creates a text field called name which is both required and unique
    tbl.text('name', 128).unique().notNullable();
    // creates a numeric field called budget which is required
    tbl.decimal('budget').notNullable();
  });
};

exports.down = function(knex, Promise) {
  // drops the entire table
  return knex.schema.dropTableIfExists('accounts');
};

References for these methods are found in the schema builder section of the Knex docs.

At this point, the table is not yet created. To run this (and any other) migrations, use the command:

knex migrate:latest

Note if the database does not exist, this command will auto-generate one. We can use SQLite Studio to confirm that the accounts table has been created.

Changes and Rollbacks

If we realize you need to update your schema later down the road, you shouldn't edit the migration file. Instead, you will want to create a new migration with the command:

knex migrate:make accounts-schema-update

Once we've written our updates into this file, we save and close with:

knex migrate:latest

If we migrate our database and then quickly realize something isn't right, we can edit the migration file. However, first, we need to rollback (or undo) our last migration with:

knex migrate:rollback

Finally, we are free to rerun that file with knex migrate latest.

Note: A rollback should not be used to edit an old migration file once that file has been accepted into a main branch. However, an entire team may use a rollback to return to a previous version of a database.

How to Build It

Creating and Running Seeds

The Knex command-line tool offers a way to seed our database; in other words, pre-populate our tables.

Similarly to migrations, we want to customize where our seed files are generated using our knexfile:

development: {
    client: 'sqlite3',
    connection: {
      filename: './data/produce.db3',
    },
    useNullAsDefault: true,
    // generates migration files in a data/migrations/ folder
    migrations: {
      directory: './data/migrations'
    },
    seeds: {
      directory: './data/seeds'
    }
  }

To create a seed run:

knex seed:make 001-seedName

Numbering is a good idea because Knex doesn't attach a timestamp to the name like migrate does. By adding numbers to the file name, we can control the order in which they run.

We want to create seeds for our accounts table:

knex seed:make 001-accounts

A file will appear in the designated seed folder.

exports.seed = function(knex, Promise) {
  // we want to remove all data before seeding
  // truncate will reset the primary key each time
  return knex('accounts').truncate()
    .then(function () {
      // add data into insert
      return knex('accounts').insert([
        { name: 'Stephenson', budget: 10000 },
        { name: 'Gordon & Gale', budget: 40400 },
      ]);
    });
};

Run the seed files by typing:

knex seed:run

You can now use SQLite Studio to confirm that the accounts table has two entries.