Skip to main content
The Cat Data API uses PostgreSQL with Knex.js for database migrations and queries. The schema consists of four main tables that handle images, items, tags, and their relationships.

​
Database Tables

​
Images Table

The images table stores metadata about uploaded image files.
migrations/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');
};
The name field stores the UUID-based filename generated during upload. The unique constraint ensures no duplicate filenames.
Columns:
  • id - Auto-incrementing primary key
  • name - Unique filename (UUID + original name)
  • created_at - Timestamp of record creation
  • updated_at - Timestamp of last update

​
Tags Table

The tags table stores categorical labels that can be applied to items.
migrations/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');
};
Columns:
  • id - Auto-incrementing primary key
  • name - Unique tag name

​
Items Table

The items table stores the main data entities with references to images.
migrations/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');
};
The image_id foreign key has CASCADE delete behavior. Deleting an image will automatically delete all items referencing it.
Columns:
  • id - Auto-incrementing primary key
  • name - Unique item name
  • description - Optional text description
  • image_id - Foreign key to images table (CASCADE on delete)
  • created_at - Timestamp of record creation
  • updated_at - Timestamp of last update

​
Items to Tags Junction Table

The items_to_tags table implements a many-to-many relationship between items and tags.
migrations/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');
};
Columns:
  • id - Auto-incrementing primary key
  • item_id - Foreign key to items table (CASCADE on delete)
  • tag_id - Foreign key to tags table (CASCADE on delete)

​
Database Relationships

The schema implements the following relationships:
1

One-to-Many: Images to Items

Each image can be associated with multiple items, but each item references only one image. When an image is deleted, all associated items are automatically removed via CASCADE.
2

Many-to-Many: Items to Tags

Items and tags have a many-to-many relationship through the items_to_tags junction table. An item can have multiple tags, and a tag can be applied to multiple items.

​
Database Functions

The API provides helper functions for interacting with the images table:
src/db-functions.ts
export async function addImage(
  name: string
): Promise<number> {
  const [result] = await db('images')
    .insert({ name })
    .returning('id');

  return result.id ?? result;
}

export async function getImage(
  id: number
): Promise<{ id: number; name: string }> {
  const row = await db('images')
    .select('id', 'name')
    .where({ id })
    .first();

  return row;
}

export async function deleteImage(id: number): Promise<void> {
  await db('images').where({ id }).del();
}

export async function getAllImages(): Promise<{ id: number; name: string }[]> {
  return db('images').select('id', 'name');
}
Migrations must be run in the correct order due to foreign key dependencies:
  1. 20250717082325_create_images_table.js - Creates images table first
  2. 20250717082710_create_tags_table.js - Creates tags table
  3. 20250717082805_create_items_table.js - Creates items table (references images)
  4. 20250717083056_create_items_to_tags_table.js - Creates junction table (references items and tags)

​
Timestamps

The images and items tables use Knex’s timestamps() method with two boolean parameters:
  • First parameter (true) - Use timestamps
  • Second parameter (true) - Use CURRENT_TIMESTAMP as default
This automatically creates created_at and updated_at columns that are managed by the database.