SQL Server CRUD Tutorials in Ruby/Rails: A Step-by-Step Guide

SQL Server is a robust and widely-used relational database management system, and Ruby on Rails is a versatile web application framework. In this tutorial, we will explore how to perform CRUD (Create, Read, Update, Delete) operations with SQL Server using Ruby on Rails. By the end of this article, you’ll have a solid understanding of how to interact with a SQL Server database from your Ruby on Rails applications.

Prerequisites

Ruby installed on your machine.
Ruby on Rails installed on your machine.
A running SQL Server instance.
Basic knowledge of Ruby and Ruby on Rails.

Step 1: Create a New Ruby on Rails Application

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

rails new sqlservercrudapp

This command will create a new Rails application named sqlservercrudapp. Navigate to the project directory using cd sqlservercrudapp.

Step 2: Configure SQL Server Connection

In your Rails application, you need to configure the connection to SQL Server. Open the config/database.yml file and make sure it looks like this:

default: &default
  adapter: sqlserver
  encoding: utf8
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: your-username
  password: your-password
  host: your-sqlserver-host
  database: your-database-name

Replace 'your-username', 'your-password', 'your-sqlserver-host', and 'your-database-name' with your SQL Server details.

Step 3: Generate a Model and Migration

Now, let’s generate a model and a corresponding migration for our CRUD operations. In your terminal, run the following command:

rails generate model Product name:string price:decimal

This command generates a Product model with name and price fields.

Next, apply the migration to create the products table in your SQL Server database:

rails db:migrate

Step 4: Implement CRUD Operations

Create (C)

In your products_controller.rb file, add the following code to create a new product:

def create
  @product = Product.new(product_params)

  if @product.save
    redirect_to @product, notice: 'Product was successfully created.'
  else
    render :new
  end
end

This code defines a create action that creates a new product record in the products table.

Read (R)

To retrieve and display product records, add the following code to your index and show actions:

def index
  @products = Product.all
end

def show
  @product = Product.find(params[:id])
end

These actions allow you to display a list of products and view individual product details.

Update (U)

For updating product records, add the following code to your edit and update actions:

def edit
  @product = Product.find(params[:id])
end

def update
  @product = Product.find(params[:id])

  if @product.update(product_params)
    redirect_to @product, notice: 'Product was successfully updated.'
  else
    render :edit
  end
end

These actions allow you to edit and update product records.

Delete (D)

To delete product records, add the following code to your destroy action:

def destroy
  @product = Product.find(params[:id])
  @product.destroy

  redirect_to products_path, notice: 'Product was successfully deleted.'
end

This action deletes a product record.

Step 5: Create Views

Create views to display and interact with your products. In the app/views/products folder, create the following files:

index.html.erb: Display a list of products.
show.html.erb: Display an individual product.
new.html.erb: Create a new product.
edit.html.erb: Edit an existing product.

Here’s an example index.html.erb file:

<h1>Products</h1>

<ul>
  <% @products.each do |product| %>
    <li>
      <%= link_to product.name, product %>
    </li>
  <% end %>
</ul>

<%= link_to 'New Product', new_product_path %>

Step 6: Run Your Application

Start your Rails server by running:

rails server

Visit http://localhost:3000/products in your web browser to interact with your CRUD application for SQL Server.

Conclusion

In this article, we’ve covered how to perform CRUD operations with SQL Server using Ruby on Rails. You’ve learned how to create a Rails application, configure the SQL Server connection, generate models, controllers, and views, and implement the CRUD operations for managing product records. SQL Server is a powerful and scalable database system, and integrating it with Ruby on Rails allows you to build robust web applications. Remember to handle errors gracefully and adapt these examples to suit your specific project requirements.