Create Hasura Database Migration

When to Use

Use this skill when:

• Adding new database tables
• Adding or removing columns
• Modifying constraints or indexes
• Any database schema change is required

Step 1: Generate Migration Timestamp

GNU/Linux:

bash
1date +%s%3N

macOS/BSD (alternative):

bash
1python3 -c "import time; print(int(time.time() * 1000))"

Note: The date +%s%3N command works on GNU/Linux systems but not on macOS/BSD due to differences in the date command. macOS users should use the Python one-liner instead.

Example output: 1753957404053

Step 2: Create Migration Directory

Create the migration folder with the timestamp:

bash
1mkdir -p backend/migrations/default/{timestamp}_{action}_{description}

Replace:

• {timestamp} with the generated timestamp
• {action} with the action type (e.g., add_column, remove_column, create_table)
• {description} with a snake_case description (e.g., scheduled_at_to_mail_log)

Step 3: Create up.sql

Create up.sql with the schema changes:

Adding a Column

sql
1ALTER TABLE "public"."TableName" ADD COLUMN "columnName" text;

Removing a Column

sql
1ALTER TABLE "public"."TableName" DROP COLUMN "columnName";

Creating a Table

sql
1CREATE TABLE "public"."TableName" (
2  "id" serial NOT NULL,
3  "name" text NOT NULL,
4  "created_at" timestamptz NOT NULL DEFAULT now(),
5  "updated_at" timestamptz NOT NULL DEFAULT now(),
6  PRIMARY KEY ("id")
7);
8COMMENT ON TABLE "public"."TableName" IS 'Brief description of the table purpose';

Note: Always include a COMMENT ON TABLE statement to document the table's purpose. This helps maintain database documentation and makes the schema more understandable for future developers.

Step 4: Create down.sql

Create down.sql to reverse the changes:

If up.sql adds a column

sql
1ALTER TABLE "public"."TableName" DROP COLUMN "columnName";

If up.sql removes a column

sql
1ALTER TABLE "public"."TableName" ADD COLUMN "columnName" text;

If up.sql creates a table

sql
1DROP TABLE "public"."TableName";

Step 5: Update Hasura Metadata (If Needed)

For new tables or relationships, create/update metadata files in: backend/metadata/databases/default/tables/

See the database-table-creation.md rule for metadata templates.

Step 6: Regenerate TypeScript Types (MANDATORY)

After ANY schema change that affects GraphQL, regenerate types using the regenerate-types skill.

Note: Hasura must be running at http://localhost:8080/v1/graphql for this to work. See the regenerate-types skill for the complete command and troubleshooting steps.

Naming Conventions

• Tables: PascalCase (UserProfile, CourseEnrollment)
• Columns: camelCase (firstName, organizationId)
• Timestamps: snake_case (created_at, updated_at)
• Foreign keys: {tableName}Id (userId, courseId)
• Migration folders: {timestamp}_{action}_{description} (1753957404053_add_column_status_to_mail_log)

Checklist

Before considering a schema-change task complete:

• Migration up.sql created with correct SQL
• Migration down.sql created to reverse changes
• Hasura metadata updated (if adding tables/relationships)
• TypeScript types regenerated
• GraphQL queries updated (if field names changed)