Module 2: Schema Design

1. Working with SQLite Studio

Learn how to effectively use SQLite Studio for database management:

• Connecting to databases
• Exploring database structure
• Executing queries
• Managing database objects

2. Database Schema Fundamentals

Understand the core concepts of database schema design:

• Schema definition and purpose
• Table structure and relationships
• Data types and constraints
• Schema documentation

3. Knex.js Migrations

Master the art of database migrations:

• Creating migration files
• Defining table structures
• Managing schema changes
• Rolling back migrations

4. Database Seeding

Learn how to populate your database with test data:

• Creating seed files
• Inserting test data
• Managing seed dependencies
• Best practices for seeding

Using SQLite Studio

SQLite Studio is a powerful GUI tool for working with SQLite databases, allowing you to explore, query, and modify database structure and content.

Key Features of SQLite Studio

• Database Connection: Connect to existing SQLite database files with a few clicks.
• SQL Editor: Write and execute SQL queries with syntax highlighting and query history.
• Schema Browser: Easily navigate tables, views, indexes, and triggers in your database.
• Data Export/Import: Transfer data between various formats, including CSV, JSON, and SQL.

Working with SQLite Studio is straightforward:

1. Install SQLite Studio from sqlitestudio.pl
2. Open the application and click "Add a database" in the Database menu
3. Browse to your SQLite database file (e.g., dev.sqlite3)
4. Connect to the database by double-clicking on it in the database list

Database Schema Design

A database schema is the blueprint that defines the structure, organization, and relationships of data in a database system.

Key Elements of Schema Design

• Tables: Define the entities in your system (e.g., users, products, orders).
• Columns: Specify the attributes of each entity with appropriate data types.
• Constraints: Rules that maintain data integrity (e.g., NOT NULL, UNIQUE, CHECK).
• Relationships: Connections between tables using primary and foreign keys.
• Indexes: Structures that improve query performance on specific columns.

Example of a well-designed schema:

-- Users table
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Posts table with relationship to users
CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Create an index for faster lookups
CREATE INDEX idx_posts_user_id ON posts(user_id);
                

Knex.js Migrations

Migrations are like version control for your database schema, allowing you to evolve your database structure over time.

Migration Workflow

1. Create a migration file using the Knex CLI
2. Define the schema changes in the up/down functions
3. Run the migration to apply changes
4. Roll back if necessary

Example of a Knex.js migration:

// Creating a migration
$ npx knex migrate:make create_users_table

// Migration file: 20230510123456_create_users_table.js
exports.up = function(knex) {
  return knex.schema.createTable('users', function(table) {
    table.increments('id'); // creates auto-incrementing id column
    table.string('username').notNullable().unique();
    table.string('email').notNullable().unique();
    table.timestamp('created_at').defaultTo(knex.fn.now());
  });
};

exports.down = function(knex) {
  return knex.schema.dropTable('users');
};

// Running migrations
$ npx knex migrate:latest

// Rolling back migrations
$ npx knex migrate:rollback
                

Database Seeding with Knex

Seeds allow you to populate your database with initial or test data, making development and testing easier.

Seeding Best Practices

• Create separate seed files for different tables
• Seed in the correct order to respect foreign key constraints
• Use unique, predictable data for testing
• Clean up existing data before seeding to prevent duplicates

Example of Knex.js seeds:

// Creating a seed file
$ npx knex seed:make 01_users

// Seed file: 01_users.js
exports.seed = function(knex) {
  // Truncate all existing tables
  return knex.raw('PRAGMA foreign_keys = OFF;')
    .then(() => knex('users').truncate())
    .then(() => knex.raw('PRAGMA foreign_keys = ON;'))
    .then(() => {
      // Insert seed data
      return knex('users').insert([
        { 
          username: 'admin', 
          email: 'admin@example.com' 
        },
        { 
          username: 'test_user', 
          email: 'test@example.com' 
        }
      ]);
    });
};

// Running seeds
$ npx knex seed:run