Database Models and Migrations in Rails with SQL Server

Ruby on Rails is a powerful web application framework that provides a convenient way to interact with databases. When working with SQL Server as your database management system, it’s crucial to understand how to define database models and manage schema changes using Rails migrations. In this article, we’ll walk through the process of creating database models and migrations in Rails application while using SQL Server as our database backend.

Prerequisites

• Ruby on Rails installed on your system.
• A SQL Server instance set up and accessible.

Creating a New Rails Application

Let’s start by creating a new Rails application. Open your terminal and run the following command:

rails new sql_server_rails_app

This command will generate a new Rails application named sql_server_rails_app. Navigate to the project directory:

cd sql_server_rails_app

To configure SQL Server as the database, open the config/database.yml file and modify it as follows:

default: &default
  adapter: sqlserver
  host: localhost
  username: your_username
  password: your_password
  database: your_database_name
  port: 1433

Replace your_username, your_password, and your_database_name with your SQL Server credentials and the desired database name.

Creating a Database Model

Let’s create a simple database model for a blog application. Run the following command to generate a Post model with attributes:

rails generate model Post title:string body:text

This command will create a migration file in the db/migrate directory and a corresponding model file in the app/models directory.

Creating a Migration

Open the generated migration file in the db/migrate directory. It will have a name like xxxxxx_create_posts.rb, where xxxxxx is a timestamp. Add the necessary columns to the create_table block:

class CreatePosts < ActiveRecord::Migration[6.1]
  def change
    create_table :posts do |t|
      t.string :title
      t.text :body
      t.timestamps
    end
  end
end

This migration creates a posts table with title and body columns.

Running Migrations

To apply the migration and create the posts table in the SQL Server database, run the following command:

rails db:migrate

This command will execute all pending migrations and update the database schema accordingly.

Using the Model

Now that the Post model and table are created, you can use it in your Rails application. For example, you can create a new post in your controller:

class PostsController < ApplicationController
  def create
    @post = Post.new(post_params)
    if @post.save
      redirect_to @post, notice: 'Post was successfully created.'
    else
      render :new
    end
  end
  private
  def post_params
    params.require(:post).permit(:title, :body)
  end
end

Conclusion

In this blog post, we’ve learned how to create database models and migrations in a Ruby on Rails application using SQL Server as the database backend. You can now start building your application with the power and flexibility of Rails while leveraging the capabilities of SQL Server for data storage and retrieval.