Overview
The tags table stores tag definitions for the Cat Data API. Tags can be associated with multiple items through a many-to-many relationship using the items_to_tags junction table.
Schema
Primary key. Auto-incrementing integer identifier for the tag.
Unique tag name. Cannot be null and must be unique across all tags.
Constraints
- Primary Key:
id
- Unique:
name - Each tag must have a unique name
- Not Null:
name - Tag name is required
Relationships
Items Relationship (Many-to-Many)
Tags are linked to items through the items_to_tags junction table:
- Junction Table:
items_to_tags
- A tag can be associated with multiple items
- An item can have multiple tags
- Cascade Delete: When a tag is deleted, all associations in
items_to_tags are automatically removed
Junction Table Schema
The items_to_tags table has the following structure:
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');
item_id → items.id (CASCADE on delete)tag_id → tags.id (CASCADE on delete)
Migration
The tags table is created using Knex.js migration:
/**
* @param { import("knex").Knex } knex
* @returns { Promise<void> }
*/
exports.up = function(knex) {
return knex.schema.createTable('tags', (table) => {
table.increments('id').primary();
table.string('name').notNullable().unique();
});
};
/**
* @param { import("knex").Knex } knex
* @returns { Promise<void> }
*/
exports.down = function(knex) {
return knex.schema.dropTableIfExists('tags');
};
20250717082710_create_tags_table.js
Junction Table Migration
The items_to_tags junction table is created in a separate migration:
/**
* @param { import("knex").Knex } knex
* @returns { Promise<void> }
*/
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');
});
};
/**
* @param { import("knex").Knex } knex
* @returns { Promise<void> }
*/
exports.down = function(knex) {
return knex.schema.dropTableIfExists('items_to_tags');
};
20250717083056_create_items_to_tags_table.js
TypeScript Types
interface Tag {
id: number;
name: string;
}
interface ItemToTag {
id: number;
item_id: number;
tag_id: number;
}
interface TagWithItems extends Tag {
items?: Array<{
id: number;
name: string;
description?: string;
}>;
}
Example Queries
Add Tag to Item
await db('items_to_tags').insert({
item_id: itemId,
tag_id: tagId
});
const tags = await db('tags')
.select('tags.*')
.join('items_to_tags', 'tags.id', 'items_to_tags.tag_id')
.where('items_to_tags.item_id', itemId);
Get All Items for a Tag
const items = await db('items')
.select('items.*')
.join('items_to_tags', 'items.id', 'items_to_tags.item_id')
.where('items_to_tags.tag_id', tagId);
Remove Tag from Item
await db('items_to_tags')
.where({
item_id: itemId,
tag_id: tagId
})
.del();
Get Tag with Item Count
const tagsWithCount = await db('tags')
.select('tags.*', db.raw('COUNT(items_to_tags.item_id) as item_count'))
.leftJoin('items_to_tags', 'tags.id', 'items_to_tags.tag_id')
.groupBy('tags.id');