I design, AI code

I am always excited when it comes to designing database schemas with my colleagues. We draw on the whiteboard or collaborate via drawio to come out with a design that fits the application. However, turning the design into a long list of migration files is boring.

I will need to make sure the order of creation is correct, provides the relevant default values, and defines the correct data type, sizes, etc.

Now, how about I draw my design and get AI to do write all the migration files for me?


Prepare a database design

First, I draw a simple database design for a blog. The main table is articles, it has an author (user), it belongs to a category and it has multiple tags. I purposely leave an ambiguous statement in the yellow box, and see what will happen.


Send it to Anthropic

Next, I send this image to Anthropic, together with the following prompt.

Please turn this database design into rails migrations. By the way, I don't have exact idea on the yellow box yet, please do suggest something for me.

Here is the code it returns:

class CreateUsers < ActiveRecord::Migration[6.1]
  def change
    create_table :users do |t|
      t.string :name, limit: 100
      t.string :password, limit: 255
      t.string :email, limit: 255
      t.string :phone, limit: 15

      t.timestamps
    end
    add_index :users, :email, unique: true
  end
end

class CreateArticles < ActiveRecord::Migration[6.1]
  def change
    create_table :articles do |t|
      t.string :title, limit: 100
      t.text :content
      t.string :slug, limit: 150
      t.string :status, limit: 1, default: 'a'
      t.references :user, foreign_key: true

      t.timestamps
    end
    add_index :articles, :slug, unique: true
  end
end

class CreateCategories < ActiveRecord::Migration[6.1]
  def change
    create_table :categories do |t|
      t.string :name, limit: 20, null: false

      t.timestamps
    end
    add_index :categories, :name, unique: true
  end
end

class CreateTags < ActiveRecord::Migration[6.1]
  def change
    create_table :tags do |t|
      t.string :name, limit: 20, null: false

      t.timestamps
    end
    add_index :tags, :name, unique: true
  end
end

class CreateArticlesTags < ActiveRecord::Migration[6.1]
  def change
    create_table :articles_tags, id: false do |t|
      t.references :article, foreign_key: true
      t.references :tag, foreign_key: true
    end
    add_index :articles_tags, [:article_id, :tag_id], unique: true
  end
end

class CreateArticleMeta < ActiveRecord::Migration[6.1]
  def change
    create_table :article_meta do |t|
      t.references :article, foreign_key: true
      t.integer :total_words
      t.integer :recommended_reading_time
      t.integer :views_count, default: 0
      t.string :meta_description, limit: 160
      t.string :meta_keywords, limit: 255

      t.timestamps
    end
  end
end

Everything seems correct, except:

  1. It missed the relationship between article and category
  2. It would be better if ArticleMeta table is in plural - ArticleMetas

Fixing the mistakes

I get it to help me amend the missing relation with the following prompt.

i think you missed the relation between article and category?

It gives 2 choices - to edit if I have not run the migration yet, or to create a new migration. I choose the latter.

class AddCategoryToArticles < ActiveRecord::Migration[6.1]
  def change
    add_reference :articles, :category, foreign_key: true
  end
end

Adding more stuff...

Next, I decided to enhance my design, by adding a new table and modifying an existing table. I called it design v2.

I send the new image together with the prompt below.

thanks for your suggestion on the article meta. it is useful. on top of that, i also updated the diagram with some new changes. can you create the migration for the new changes?

From the response, some new migrations are generated.

class CreateComments < ActiveRecord::Migration[6.1]
  def change
    create_table :comments do |t|
      t.string :message, limit: 255
      t.references :article, foreign_key: true

      t.timestamps
    end
  end
end

class AddPriorityToCategories < ActiveRecord::Migration[6.1]
  def change
    add_column :categories, :priority, :integer
  end
end

class CreateArticleMetas < ActiveRecord::Migration[6.1]
  def change
    create_table :article_metas do |t|
      t.references :article, foreign_key: true
      t.integer :total_words
      t.integer :recommended_reading_time
      t.integer :views_count
      t.string :meta_description, limit: 160
      t.string :meta_keywords, limit: 255

      t.timestamps
    end
  end
end

It identified that:

✅ There is a new comments table

✅ A priority field is added to the categories table 

✅ I have reflected the article meta table into the design


And that is it. We just completed the database migrations with the help of AI. It has surprisingly high accuracy and is simple to use.


AI Summary
gpt-4o-2024-05-13 2024-07-16 00:40:09
AI can assist in transforming database designs into precise Rails migration files. By providing a visual schema to the AI, it generates accurate migration code, suggesting enhancements and corrections as needed. This streamlines the database setup process, making it more efficient and less tedious.
Chrome On-device AI 2024-07-20 06:04:30

Share Article