Skip to main content

​
Overview

The Cat Data API uses Knex.js as a SQL query builder and migration tool. Migrations allow you to version control your database schema and make changes in a consistent, repeatable way.

​
Knex Configuration

The database configuration is defined in knexfile.js at the project root:
knexfile.js
require('dotenv').config();

module.exports = {
  development: {
    client: 'pg',
    connection: {
      host: process.env.PGHOST,
      port: Number(process.env.PGPORT),
      user: process.env.PGUSER,
      password: process.env.PGPASSWORD,
      database: process.env.PGDATABASE,
    },
    migrations: {
      directory: './migrations',
    },
    seeds: {
      directory: './seeds',
    },
  },
};
The configuration uses environment variables from your .env file to connect to PostgreSQL.

​
Running Migrations

1

Run all pending migrations

Apply all migrations that haven’t been run yet:
npx knex migrate:latest
This executes all migration files in the migrations/ directory in chronological order.
2

Check migration status

See which migrations have been applied:
npx knex migrate:status
3

Rollback migrations

Undo the last batch of migrations:
npx knex migrate:rollback
Rolling back migrations will drop tables and delete data. Use with caution, especially in production.

​
Existing Migrations

The Cat Data API includes four migrations that define the database schema:

​
1. Create Images Table

File: 20250717082325_create_images_table.js 
exports.up = function(knex) {
  return knex.schema.createTable('images', (table) => {
    table.increments('id').primary();
    table.string('name').notNullable().unique();
    table.timestamps(true, true); // created_at, updated_at
  });
};

exports.down = function(knex) {
  return knex.schema.dropTableIfExists('images');
};
Creates the images table for storing uploaded cat image metadata.

​
2. Create Tags Table

File: 20250717082710_create_tags_table.js 
exports.up = function(knex) {
    return knex.schema.createTable('tags', (table) => {
        table.increments('id').primary();
        table.string('name').notNullable().unique();
    });
};

exports.down = function(knex) {
    return knex.schema.dropTableIfExists('tags');
};
Creates the tags table for categorizing cat data with labels.

​
3. Create Items Table

File: 20250717082805_create_items_table.js 
exports.up = function(knex) {
  return knex.schema.createTable('items', (table) => {
    table.increments('id').primary();
    table.string('name').notNullable().unique();
    table.text('description');
    table.integer('image_id').unsigned().references('id').inTable('images').onDelete('CASCADE');
    table.timestamps(true, true);
  });
};

exports.down = function(knex) {
  return knex.schema.dropTableIfExists('items');
};
Creates the items table for cat data entries with foreign key reference to images.

​
4. Create Items-to-Tags Junction Table

File: 20250717083056_create_items_to_tags_table.js 
exports.up = function(knex) {
    return knex.schema.createTable('items_to_tags', (table) => {
        table.increments('id').primary();
        table.integer('item_id').unsigned().references('id').inTable('items').onDelete('CASCADE');
        table.integer('tag_id').unsigned().references('id').inTable('tags').onDelete('CASCADE');
    });
};

exports.down = function(knex) {
  return knex.schema.dropTableIfExists('items_to_tags');
};
Creates a many-to-many relationship between items and tags.

​
Creating New Migrations

1

Generate a migration file

Create a new migration with a descriptive name:
npx knex migrate:make add_featured_column_to_items
This creates a timestamped file in the migrations/ directory:
migrations/20260305120000_add_featured_column_to_items.js
2

Write the migration

Edit the generated file to define the schema changes:
exports.up = function(knex) {
  return knex.schema.table('items', (table) => {
    table.boolean('featured').defaultTo(false);
  });
};

exports.down = function(knex) {
  return knex.schema.table('items', (table) => {
    table.dropColumn('featured');
  });
};
  • exports.up - Applies the migration (adds the column)
  • exports.down - Reverts the migration (removes the column)
3

Run the new migration

Apply your migration:
npx knex migrate:latest

​
Migration Best Practices

Always include both up and down functions - This allows migrations to be rolled back if needed.
  • Use descriptive names - Migration names should clearly describe what they do
  • One change per migration - Keep migrations focused on a single logical change
  • Never modify existing migrations - Once a migration has been run in production, create a new migration instead
  • Test rollbacks - Verify that your down function properly reverses the up function
  • Use transactions - Knex wraps migrations in transactions by default to ensure atomicity

​
Database Schema Overview

After running all migrations, the database contains:
TablePurposeKey Columns
imagesStores uploaded image metadataid, name, created_at, updated_at
tagsCategorization labelsid, name
itemsCat data entriesid, name, description, image_id
items_to_tagsMany-to-many relationshipsid, item_id, tag_id
All foreign keys use CASCADE deletion, so deleting a parent record automatically removes related child records.

​
Troubleshooting

​
Migration fails with “relation already exists”

The table may already exist from a previous migration. Check the status: 
npx knex migrate:status

​
Connection errors

Verify your database credentials in .env match your PostgreSQL configuration. See Environment Variables.

​
Permission denied errors

Ensure your PostgreSQL user has permissions to create tables: 
GRANT ALL PRIVILEGES ON DATABASE cat_data TO your_username;

Build docs developers (and LLMs) love

Get started for freeTalk to us