Today I Learned how to Create Rails Docker Container Without Ruby Installed

I was curious to see if I could create a Docker container for a new Rails project without having Ruby or Rails installed on the host machine.  To do this I created a new Ubuntu 18 virtual machine with the bare minimum installed for the OS.  I also install Docker, RubyMine, and DataGrip.

Initially I was hoping I could just create the new project inside RubyMine.  Unfortunately I couldn’t get it working.  As you will see setting up the initial Docker container requires more then just running the “docker-compose up” command.

My main resource in setting up the image was Docker Rails Quickstart Guide.   The basic plan is:

  1. Setup Ruby Docker container.
  2. Add default Gemfile for Rails.  Required to create new Rails application.
  3. Create a new Rails application but don’t build the container yet.
  4. Update the configuration files.
  5. Build the docker container to install the Gems.

I started off as they recommended and created an empty folder with Dockerfile.

FROM ruby:2.5
RUN apt-get update -qq && apt-get install -y build-essential libpq-dev nodejs
RUN mkdir /website
WORKDIR /website
COPY Gemfile /website/Gemfile
COPY Gemfile.lock /website/Gemfile.lock
RUN gem install bundler
RUN bundle install
COPY . /website

Dockerfile for Rails Container

I then created a basic Gemfile and empty Gemfile.lock file.

source 'https://rubygems.org'
gem 'rails', '5.2.0'

Initial Gemfile

Finally I created the docker-compose file.

You might have noticed this file is a bit different then the one in the Docker Quickstart.  I made these changes after the contain failed to build and/or I couldn’t connect to it with RubyMine.

version: '2'
services:
  db:
    image: postgres
    ports:
      - "5432:5432"
    environment:
      POSTGRES_PASSWORD: password1234
    volumes:
      - ./tmp/db:/var/lib/postgresql/data
  web:
    build: .
    command: bundle exec rails s -p 3000 -b '0.0.0.0'
    volumes:
      - .:/website
    ports:
      - "3000:3000"
    depends_on:
      - db

Docker Compose

The first change I made was downgrading the version from 3 to 2 as RubyMine currently only supports 2.  I also added ports and a password for the Postgres database so I can access from DataGrip.  If you don’t set the port and password your Rails application will be able to access the database but nothing else will.

I then created the rails application as recommended by the quickstart guide.  Since this is the first time running it pulls down the docker files.  Then I ran into the first of many permission errors.

docker-compose run web rails new . --force --database=postgresql

Creating new Rails App Cmd

First Permission Error

This permission error was caused by the Postgres container.  The files created Docker images are usually owned by root but some of the temporary Postgres files where also owned by VBoxAdd.

Database File Owned by VBoxAdd

After a while I figured out the way to fix this was to run the following command:

sudo chown -R $USER:$USER .

I got several permission errors during my trails and error of setting up the Rails container.  Every time I would just run the above command to fix it.

Once the permission errors went away I was able to create a new rails application.  Before the files for the new rails application where created it needs to build the Docker container so don’t be surprised to see the below.

Building Rails Container

Your newly created rails files might be owned by root.  In this case run the command you have probably become very familiar with:

sudo chown -R $USER:$USER .

Notice that the Gemfile has been updated and populated with all the Gems needed to run a new rails application.

Gemfile Populated by Rails New

Since we just called run on the web Docker container non of the installed Gems where saved in the container.  To prevent having to re-install the gems every time the container is run we need to build it.

Build Containers

Now the web container is ready but our database isn’t.  Open up the database config file and set the host, username, and password.

Database Config File Settings

Now create the Rails databases.

docker-compose run web rake db:create

Create the Rails Databases

Again if you get permission errors trying to create the databases run change permissions command.

sudo chown -R $USER:$USER .

Now bring up the containers and you should be see the Welcome to Rails website.

docker-compose up

Docker Compose Up

Yay Your are on Rails

The site is working but now we need to get it working with RubyMine.  If you use a different editor then your steps might be different.

First stop the containers using Ctrl-C.

Stopping Containers

To safe us some steps later lets update the Gemfile.  By default Rails installs the Byebug gem for debugging.  RubyMine likes the Ruby Debug IDE gem.  Just don’t run them both at the same time as they don’t play well together.

Remove ByeBug from the Gemfile and add Ruby Debug IDE.  Your Gemfile will look like:

Ruby Debug IDE Added to Gemfile

Now rebuild the Docker container so the new gems are installed.

docker-compose build

Then open up RubyMine and open up your project.  Once it’s open we need to tell RubyMine about our Docker containers.  To this go to File–>Settings.  Then go the Build, Execution, Deployment–>Docker in the Settings dialog and make sure Docker is setup correctly.  If it is you should see something similar to the below.

Docker Settings

Docker Settings Registry

You might be missing Docker Machine in which case you can install it following these instructions.  Try re-opening RubyMine and if it still does not find Docker Machine then you will need to tell it the exact path.

Now that Docker is setup correctly in RubyMine we need to setup the Ruby SDK by going to Languages & Frameworks –> Ruby  SDK and Gems in the Settings dialog.  Assuming you don’t have Ruby installed on your local workstation the list of Ruby SDKs should be empty.  Click the green plus sign to add one and choose New Remote.

Add New Remote Ruby SDK in RubyMine

In the Configure Remote Ruby Interpreter select Docker Compose and enter in the settings shown below.  This tells RubyMine what Docker Compose file to use to build the containers and container is the website.

Configure Remote Ruby Interpreter Dialog

After you click OK RubyMine will run the Docker containers and attempt to find Ruby in the container.  It will also attempt to find all the gems installed on the container.  This can take a couple minutes but if everything works correctly you should see something like:

Remote Docker Compose Ruby SDK Correctly Configured

Sometime the correct Ruby version will be listed by the gems won’t be shown.  To fix you might have to remove the newly added Ruby SDK by clicking the minus sign and try again.

If RubyMine can’t load the Docker containers then it will probably display a very unhelpful error message.  Try going back to the terminal and running docker-compose up manually.  If you get any errors fix them then try configuring RubyMine again.

Assuming everything is working correctly you should be able to run and debug the application.  To test that debugging works put a break point then try debugging.

Application Breakpoint

Run Debug Development

Application Breakpoint Hit

Now you can develop your new app without installing Ruby or Rails on your local workstation.  If you have any tips for dealing with the Docker Prostgres permission issues let me know at chris.cumming@saturdaymp.com.

P.S. – My wife recently introduced me to the band Walk of the Earth which we get to see live shortly.  They are most famous for all the band members covering Gotye’s “Somebody That I used Know” a single guitar but my favourite song is an original called “Rule the World“.

They said no way
I say I rule the world
(Ain’t afraid of the walls, I’mma break them down)
They stay the same
Well, I’m feelin’ high as a bird
(Ain’t afraid of the ground, I’mma stand up)

Posted in Code Examples, Software Development, Today I Learned | Tagged , , , , | Comments Off on Today I Learned how to Create Rails Docker Container Without Ruby Installed

Introduction to ORMs for DBAs: Part 4 – Create Game CRUD

I gave the Introduction to ORMs for DBAs at SQL Saturday 710 but it didn’t go as well as I would have liked (i.e. I had trouble getting my demo working).  Since the demo didn’t work live I thought I would show you what was supposed to happen during the demo.  You can find the slides I showed before the demo here.

The goal of this demo is create a basic website that tracks the games you and your friends play and display wins and losses on the home page.  I used a MacBook Pro for my presentation so you will see Mac screen shots.  That said the demo will also work on Windows.

In the previous post we created the Games table and the post before that we created the Players table.  In this post we will create the CRUD methods to access the Games table.  

If you skipped the previous post but want to follow along open up 04 – Create Game CRUD.  Then run the migrations to create the Players and Games table in the database so your database similar to the below screen shot.  The migration IDs can be different but the tables should exist.

Player and Games Table Exist

Lets get right into it and create the CRUD scaffolding to access the games table.  I’ll create the scaffolding using the same command line I used to the create the Players CRUD scaffolding.

dotnet aspnet-codegenerator controller -name GamesController -outDir Controllers -m Game -dc GameTrackerContext -udl

Create Game CRUD

Just like the Player CRUD scaffolding you should see a GamesController and a bunch of Games views.  If you don’t see the views you might need to close Visual Studio and re-open it.

Game CRUD Created

I just realized we never talked the specifics of this command when we created the player CRUD so let’s do that now.  The first argument says we want to create a controller because the ASP.NET code generator can generate more then just controllers.  Then the “name” argument tells it the name of the controller.

Notice that we pluralize the name?  The model is singular because it represents on thing, in this case a game.  The Table is plural because it holds multiple games.  Finally the controller is plural because it’s the controller for all the games.  Of course you can pick your own standard.  Just be consistent.

Back to the command.  The “outDir” argument says where we want the controller to be created.  The “m” argument tells the scafolding what model to base the controller and views on.  The “dc” argument tells the code generator what data context to use.  Remember you can have more then one data context.  Finally the “udl” argument tells the generator to use our default layout file when creating the views.

Back to our application, we should run it to make sure it works but before we do that lets add the menu item to the Games page.  Open up the _Layout.cshtml file and add the following line:

<li><a asp-area="" asp-controller="Games" asp-action="Index">Games</a></li>

Add Games Menu

Now that the menu item has been added run the application and you should see the Games menu on the home page.

Games Menu Added

Click it an will take you to the Games Page index which shows a list of all the games.  Since there are no games yet the list is empty.  Click the Create New link and add a new game to make sure everything works.

Creating a new Game

New Game Created

Now that everything works lets get back to talking about the code and the ORM methods the controller.  For the PlayersController we talked about the Index, Details, and Create methods.  That leaves the Edit and Delete methods.

First up the Edit methods.  Notice I said methods as there are two Edit methods.  One Edit method to get the information about the game to be edited and the other saves the changes.  Lets start with the Get Edit method.

public async Task<IActionResult> Edit(int? id)
{
  if (id == null)
  {
    return NotFound();
  }

  var game = await _context.Games.SingleOrDefaultAsync(m => m.Id == id);
  if (game == null)
  {
    return NotFound();
  }
  return View(game);
}

 

Get Edit Game Method

The id argument passed into the method is the ID of the game to be displayed.  There is some error checking to make sure the id is not NULL.  Remember this is a website on the Internet and the Internet will often send you garbage (i.e. IDs that are not numbers).

if (id == null)
{
  return NotFound();
}

Assuming the id is a number we use Entity Framework to try and find the game in the database.

var game = await _context.Games.SingleOrDefaultAsync(m => m.Id == id);

This line of code tells Entity Framework to look in the games table and find any records that has the match the ID we are looking for.  The SingleOrDefaultAsync returns the game record if found or NULL if no record is found.  If multiple records are found then an exception is raised.  Basically it generates the following query:

Select *
From Games
Where Id = #

The found game record is then displayed to the user via the web page.  The user makes changes to the game, in this case changing the name, then submits the changes.  This submit calls the second Edit method which we call the Post Edit method.

[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Edit(int id, [Bind("Id,Name")] Game game)
{
  if (id != game.Id)
  {
    return NotFound();
  }

  if (ModelState.IsValid)
  {
    try
    {
      _context.Update(game);
      await _context.SaveChangesAsync();
    }
    catch (DbUpdateConcurrencyException)
    {
      if (!GameExists(game.Id))
      {
        return NotFound();
      }
      else
      {
        throw;
      }
    }
    return RedirectToAction(nameof(Index));
  }
  return View(game);
}

Post Edit Game Code

Quick aside, why am I calling the methods Get and Post?  They are terms used in the HTML protocal.  A GET request is used to retrieve data from the web server.  So in the case of the GET Edit method we want to “get” information about the game.  Post request is sending data to the webserver.  In this case we want to “post” the changes made to the game.

Back to the code.  First off what do these arguments mean?

public async Task<IActionResult> Edit(int id, [Bind("Id,Name")] Game game)

 

This first argument is the ID of the game we want to edit.  The second argument is the new values of the game.  The Bind in square brackets tells our application what fields to map to the Game object.  In this case it’s all the fields, the ID and Name.  In more complicated objects you might not want the user, especially a malicious user, to set certain fields.

Like the Get Edit method this method starts with some basic error handling.  First making sure the ID of the object to edit and the ID in the game object with the updated values are the same.  Remember we are dealing with Internet requests that might not be valid.

if (id != game.Id) 
{
  return NotFound(); 
}

Then it checks to make sure the updated values in the Game object are valid.  This is where you could also do business logic checking.

if (ModelState.IsValid)

Assuming all the error handling passes then the Game record in the database is updated.  The first line adds the updated Game object to the context.  Remember that changes to the context are written to the database when a save method is called.  In this case there is only one change.

_context.Update(game); 
await _context.SaveChangesAsync();

The actual saving is wrapped in a Try-Catch block to handle any errors in saving the edits.

That is it for the Edit methods.  Let us move on to the Delete methods.  First up the Get Delete method.

public async Task<IActionResult> Delete(int? id)
{
  if (id == null)
  {
    return NotFound();
  }

  var game = await _context.Games.SingleOrDefaultAsync(m => m.Id == id);
  if (game == null)
  {
    return NotFound();
  }
  return View(game);
}

 

Get Delete Game Method

Similar to the Get Edit method the Get Delete method simply finds the game we want to delete and returns it.  Presumably so the user can view the game before it’s deleted.  Nothing new to say so lets jump to the Post Delete method.

[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public async Task<IActionResult> DeleteConfirmed(int id)
{
  var game = await _context.Games.SingleOrDefaultAsync(m => m.Id == id);
  _context.Games.Remove(game);
  await _context.SaveChangesAsync();
  return RedirectToAction(nameof(Index));
}

 

Post Delete Game Controller Method

Again the Post Delete method is very similar to the Post Edit method but in this case we don’t check if the game exists before deleting it.  The delete method with remove the game if it exists and if it does not exist then there is nothing to delete and no error is raised.

The SQL generated would look something like:

Delete From Games
Where Id = #

That is all for now.  In the next part we will create a table that has a one-to-many relationship and talk creating one-to-many ORM models.   If you got stuck you can find completed Part 4 here.  Finally if you have any questions or spot an issue in the code I would prefer if you opened a issue in GitHub but you can e-mail (chris.cumming@saturdaymp.com) me as well.

Posted in Code Examples, Introduction to ORMs for DBAs, Software Development | Tagged , , , , , , | Comments Off on Introduction to ORMs for DBAs: Part 4 – Create Game CRUD

Introduction to ORMs for DBAs: Part 3 – Player CRUD

I gave the Introduction to ORMs for DBAs at SQL Saturday 710 but it didn’t go as well as I would have liked (i.e. I had trouble getting my demo working).  Since the demo didn’t work live I thought I would show you what was supposed to happen during the demo.  You can find the slides I showed before the demo here.

The goal of this demo is create a basic website that tracks the games you and your friends play and display wins and losses on the home page.  I used a MacBook Pro for my presentation so you will see Mac screen shots.  That said the demo will also work on Windows.

In the previous post we created the Games table and the post before that we created the Players table.  In this post we will create the CRUD methods to access the Players table.  

If you skipped the previous post but want to follow along open up 03 – Player CRUD.  Then run the migrations to create the Players and Games table in the database so your database similar to the below screen shot.  The migration IDs can be different but the tables should exist.

Player and Games Table Exist

In this post we will create some Create, Retrieve, Update and Delete (CRUD) methods to access the Players table.  To quickly create the CRUD methods we will use auto-generated scaffolding.  Scaffolding, in this context, is basic CRUD code.  In production you would expand upon the scaffolding but for this demo we will mostly leave it as is.  It’s a great way to get something up and running.

We will run the scaffolding from the command line but before we do that we need to add the Microsoft.VisualStuido.Web.CodeGeneration.Design NuGet package.

Adding Code Generation NuGet Package

Code Generation NuGet Package Added

Now that the package is added go the terminal and run the following command to make sure the new NuGet package is installed correctly.

dotnet restore

Now we can run the scaffolding commands.  Lets run it to create the Players CRUD methods.

dotnet aspnet-codegenerator controller -name PlayerController -outDir Controllers -m Player -dc GameTrackerContext -udl

Add Player Controller Scaffolding

Now go back to Visual Studio and you should see the PlayerController file and a bunch of Player views.  If you don’t see the player views then you might need to close Visual Studio and reopen it.

Player Controller and Views

Now lets test the scaffold pages.  Run the application and you will get the home page.  You need to manually enter the URL for the Players page.

http://localhost:port#/players

Player Index Page

You should be able to add, edit, and delete players as you like.

Creating Player Page

Let’s add a link to he Player pages on the home page.  Stop the application and open up the _Layout.cshtml file and add the following line just under the Home menu item:

<li><a asp-area="" asp-controller="Players" asp-action="Index">Players</a></li>

Adding Player Menu Item

Now run the application again you should see the Players link.  Click it to make sure it works.

Home Page with Player Menu Item

Now that the code is working we should talk about it a bit.  Since this is a ORM talk we will focus on the ORM code but we should quickly talk about the Model-View-Controller (MVC) pattern.

MVC is a software pattern that separates an application into three separate  parts.  You can probably guess what they are.  It’s easiest shown in the below diagram.

MVC Diagram

In this demo the views are the web pages the end user will see.  The files names created by the scaffolding say what the page does.  If we open one up, say the Index.cshtml, we will see mostly HTML mixed with some C# code as shown below.

Player Index View Code

When a web page is requested all the C# code is run on the webserver and translated into HTML before being sent to the browser.  In this example the C# code creates a row in the HTML table for every record it finds in the Players table.

@foreach (var item in Model)

The controller class are responsible for populating the views with data from the model.  They are also responsible for updating the model with new data submitted by the user.  If we look at the PlayerController we see there is scaffolding code to view all the players (the index method), create new players, view/edit existing players, and finally delete players.

Player Controller Code

Do you remember the GameTrackerContext class?  It is the link between the database and the application.  You can see it it passed into the constructor, which is the first bit of code that is executed when a class is instantiated.

public PlayersController(GameTrackerContext context)
{
  _context = context;
}

In this case the constructor takes the context and saves it so other methods in the class can use the context.  For example, the Index method uses the context class to retrieve all the records from the Player table.

 
// GET: Players
public async Task<IActionResult> Index()
{
  return View(await _context.Players.ToListAsync());
}

Player Controller Code Index Method

It’s the same as writing:

Select * From Players

The context takes the player data and maps it to the Player models.  The mapping is done by the ORM, in this case by Entity Framework.  The query is also generated by the ORM.  This is one on of the reasons developers like ORMs so much.  All this grunt work is handled for them.

Yes, I know selecting all the records from a table is not a feasible long term.  Remember this is scaffolding code.

Now let’s take a short look at the Details method.

// GET: Players/Details/5
public async Task<IActionResult> Details(int? id)
{
  if (id == null)
  {
    return NotFound();
  }

  var player = await _context.Players
    .SingleOrDefaultAsync(m => m.Id == id);
  if (player == null)
  {
    return NotFound();
  }

  return View(player);
}

Player Controller Code Details Method

This little bit of code finds on particular Player based on their ID.  It’s the same as writing:

Select * From Players Where Id = #

In this case it either finds the player in the database and maps it to the player model or it returns Null.  Again ORM did all the grunt work.  The developer didn’t have to write the query or the mapping code.

You are starting to get the idea but let’s look at one more, the Create method.  There are two Create methods, the first one simply returns the page for the user to enter the new player.  The second method is what is called when the user submits the create player page.  We are interested in the second method.

// POST: Players/Create
// To protect from overposting attacks, please enable the specific properties you want to bind to, for 
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Create([Bind("Id,Name")] Player player)
{
  if (ModelState.IsValid)
  {
    _context.Add(player);
    await _context.SaveChangesAsync();
    return RedirectToAction(nameof(Index));
  }
  return View(player);
}

Player Controller Code Create Method

This method creates a new player in the database.  In this case the ASP.NET MVC populates the model from the information the user entered on the web page.  Then the code adds the new player to the context.  The context does not actually save the new player to the database until the SaveChangesAsync method is called.

As a DBA you can probably guess why the context does not save the changes immediately to the database.  It would result in lots of connections and queries which is very inefficient.  Instead the context the takes note of the changes should be made and then batch executes them in one connection.  If possible the ORM will also minimize the number of queries it needs send.  This is known as the Unit of Work pattern.

In this example we are just adding a single record in a Unit of Work but I’m sure you can imagine more complex scenario where several records in several tables are updated in one unit of work.

I think that is enough for now.  In the next post we will create the Games CRUD similar to above and discus the Edit and Delete methods.  If you got stuck you can find completed Part 3 here .  Finally if you have any questions or spot an issue in the code I would prefer if you opened a issue in GitHub but you can e-mail (chris.cumming@saturdaymp.com) me as well.

Posted in Code Examples, Introduction to ORMs for DBAs, Software Development | Tagged , , , , , , | Comments Off on Introduction to ORMs for DBAs: Part 3 – Player CRUD

Introduction to ORMs for DBAs: Part 2 – Games Table

I gave the Introduction to ORMs for DBAs at SQL Saturday 710 but it didn’t go as well as I would have liked (i.e. I had trouble getting my demo working).  Since the demo didn’t work live I thought I would show you what was supposed to happen during the demo.  You can find the slides I showed before the demo here.

The goal of this demo is create a basic website that tracks the games you and your friends play and display wins and losses on the home page.  I used a MacBook Pro for my presentation so you will see Mac screen shots.  That said the demo will also work on Windows.

In the previous post we created the Players table and in this post we will create the Games table.  If you skipped the previous post but want to follow along open up 02 – Player Table.  Then run the migrations to create the Players table in the database so your database similar to the below screen shot.

Player Table With Max Length

Your migration IDs will be different but you should have a player table.

We will create the Games table the same way we created the Players table in part 1.  First create the Game model and add the Id and Name fields.  Since this is a simple database we will only track the name of the game.  No need to track the publisher, type, etc.

using System.ComponentModel.DataAnnotations;
namespace SaturdayMP.GameTracker.Models
{
  public class Game
  {
    public int Id { get; set; }

    [MaxLength(50)]
    public string Name { get; set; }
  }
}

Game Model

When setting up the Player table we intentionally forgot to set the max length of the field.  This time we won’t forget and set it to 50 again.  If you get an error that MaxLength does not exist then make sure you have the following at the top your file:

using System.ComponentModel.DataAnnotations;

Now that the model exists we need to tell the database context about it.  Open up the GameTrackerContext and add our new Game model.

public DbSet&lt;Game&gt; Games { get; set; }

Game Added To Database Context

Compile the application just to make sure there are no typos.  Now switch to the terminal and create the migration like we did in part 1.

dotnet ef migrations add CreateGamesTable

Create Game Table Migration

Take a quick look at the migration to make sure it was created and is what we expect.  Should be very similar to the Players migration.

Create Game Table Migration

Back in lets apply the migration on the database.

dotnet ef database update

Run Game Table Migration

Remember this command will run all the migrations that have not already been run on the database yet.  So the CreatePlayersTable migration will be skipped because it has already been run.

Now open up your database client and check that the table was created.

Games Table in Database

That was much shorter then creating the Player table in part 1.  If you got stuck you can find completed Part 2 here.  In Part 3 we will CRUD methods and pages to access the Player table.  Finally if you have any questions or spot an issue in the code I would prefer if you opened a issue in GitHub but you can e-mail (chris.cumming@saturdaymp.com) me as well.

Posted in Introduction to ORMs for DBAs, Software Development | Tagged , , , , , , | Comments Off on Introduction to ORMs for DBAs: Part 2 – Games Table

Introduction to ORMs for DBAs: Part 1 – Create Player Table

I gave the Introduction to ORMs for DBAs at SQL Saturday 710 but it didn’t go as well as I would have liked (i.e. I had trouble getting my demo working).  Since the demo didn’t work live I thought I would show you what was supposed to happen during the demo.  You can find the slides I showed before the demo here.

The goal of this demo is create a basic website that tracks the games you and your friends play and display wins and losses on the home page.  I used a MacBook Pro for my presentation so you will see Mac screen shots.  That said the demo will also work on Windows.

This post creates a Player table and all the setup to for Entity Framework.  To follow along open up the 01 – Create Player Table project.  If you need a hand getting the tools installed check out Part 0 – Setup.

We start with a blank ASP.NET MVC application.  It’s similar to what you get when you create a default ASP.NET MVC application in Visual Studio but I’ve deleted some unneeded files related to the About and Contacts pages.

Empty ASP.NET Visual Studio

If we run the application we will a home page where we eventually want to display the Win/Lose table for the players.

Starting Web Project Home Page

Now lets make sure our database is up and running.  Since we running the demo on a Mac we need to run SQL Server inside Docker.  There is Docker Compose file in the root folder so we can run the below command to start SQL Server:

 
$ docker-compose up

Bring Up SQL Server

We will use DataGrip as our SQL Client because it runs on Mac.  You could also use Microsoft SQL Operations Studio.  If we look at our database server we see it has nothing but the master database.

Empty Database Server

Everything seems to be working so lets start creating our application starting with the Player.

Define Player Model

To track what games people have played we will need to store information about the player and games being played.  Lets start with the player.

If you are a DBA you are itching to open up your SQL Client and create the Players table but we aren’t going to do that.  Instead we will use a feature in most ORMs called Code First.  You define your database model in code and let the ORM handle the database changes for you.

Define the Player model by creating a new class in the Models folder called Player.

Add Player Model

This class is our model and represents the Players table in the database.  Since this is a simple application used by friends we only need their name.   So our model will look like:

namespace SaturdayMP.GameTracker.Models
{
  public class Player
  {
    public int Id { get; set; }

    public string Name { get; set; }
  }
}

 

Player Model

This model defines that we have a Players table and it has two fields: Id and Name.  Notice I said the table name is Players with an “s” but the model name is singular.  Most ORMs have default conventions they use for dealing with names.  Entity Framework assumes your model name is singular and your table will be plural.  Entity Framework also assumes any field named Id will be a primary key.  Of course you can override the conventions if you want.

Setup Connection to Database

The model is now defined but before we can create the table we need a way for our application to access the database.

The first step is to create the database context.  The database context is how most of our code will interact with the database.  We will put the context class in the a new folder called Data and call our context GameTrackerContext.  If your application accesses more then one database you will have multiple contexts.

using SaturdayMP.GameTracker.Models;
using Microsoft.EntityFrameworkCore;

namespace SaturdayMP.GameTracker.Data
{
  public class GameTrackerContext : DbContext
  {
    public GameTrackerContext(DbContextOptions<GameTrackerContext> options) : base(options)
    {
    }

    public DbSet<Player> Players { get; set; }
  }
}

Game Tracker Context

The important part is the DBSet line.  This tells the database context to map the Player model to the underlying Players table.  As we add more tables we will add additional DbSet lines.

Next lets set the connection string.  Do DBAs have to deal with connection strings or is it a developer thing?  Anyway, the connection string is set in the aspsettings.json file:

"ConnectionStrings": {
  "DefaultConnection": "Server=(local);Database=GameTrackerDemo;Trusted_Connection=False;User ID=sa;Password=Password1234!"
}

Setting Connection String

We won’t see the effects of this next step till later but we will do it now as we are doing all our setup now.  We need to tell the website that we have a database context and that it should automatically include it in controllers.  This is done in the Startup.cs file.  Add the following to the ConfigureServices method.

services.AddDbContext&lt;SaturdayMP.GameTracker.Data.GameTrackerContext&gt;(
 options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

Adding DB Context To Service

Setup Command Line Tools

This next step is a bit of pain.  As of this writing all the cool menu items in Visual Studio on Windows are not available for Visual Studio for Mac.  We have to use the command line and this requires us to manually edit the project file.

Close Visual Studio then find the SaturdayMP.GameTracker project file and open it up in a text editor.

Game Tracker Project File

Then add the following two lines, one to the PackageReference ItemGroup and the other to the DotNetCliToolReference ItemGroup:

<ItemGroup>
  <PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.6" />
  <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.0.2" />
</ItemGroup>

<ItemGroup>
  <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="2.0.3" />
  <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.2" />
</ItemGroup>

 

Add EF CLI Tool to Project File

Now reopen the the solution in Visual Studio and rebuild the solution.  If you got any errors check that you put the lines in the correct spot and didn’t forget a double quote or greater/lesser then character.

Open up a new terminal and navigate to the folder where your project is located.  Then run the following command to make sure the command line tools are loaded and installed:

 
$ dotnet restore

To test if everything works run the below command.  It should show something similar to the screenshot below.

 
$ dotnet ef

 

.NET EF Command Success

Generate Player Migration

Now we can generate the Player table.  Actually we will generate a migration that will generate the Player table.  A migration is file that contains changes we want to make to the database such as creating tables, add/removing columns on existing tables, adding indexes, etc.

dotnet ef migrations add CreatePlayerTable

Create Player Migration File

Make sure you are in the project folder when you try to run the command or it will fail.  Assuming it succeeded you should see a Migrations folder in Visual Studio and one file in it that ends in “CreatePlayerTable”.

Player Table Migration File

If we open up the file we see it does not contain SQL but C# code.  The reason for using C# is so the same migration file can be run against different databases such as Oracle, PostgreSQL, etc.  Even if you don’t understand C# it should be pretty clear that this file is creating the Player with the Id and Name fields.

A migration file contains contains changes you want to apply to a database.  They can be manually created but in this case we used Entity Framework to create it.  It created the migration file by comparing what the database currently looked like to what it should look like based on the models defined in code.  In this case the the database is empty (non-existent) but we have a Player model defined thus the thus the migration file creates the player table.

Run the Player Table Migration

Now we can run the migration to create the table in the database.

dotnet ef database update

Run Player Table Migration

The above command will actually run all the migrations we have defined.  Currently we only have one so it only runs the one.  If we switch to our SQL client and refresh we should see the new Player table in the database.

Show Game Tracker Demo Database

Player Table in DataGrip

Notice the _EFMigrationHistory table?  This table tracks what migrations have been run on this database.  If a migration has already been run on the database it won’t be run a second time.

Drop and Recreate the Migration File

One thing you DBAs probably noticed is the Name field is set to max length, which is not good.  Lets change it to 50 characters.

In this case we haven’t shared our migration with anyone else yet so we can delete the migration and try again.  If we had pushed this migration to our code repository we would have to create a new migration to undo our previous one.

If you remember the migration file had an up and a down.  The down part is run if you un-apply the migration.  In this case it will drop our table.

Lets ask Entity Framework to unapply our migration.  In the below example we say we want to undo all the migrations but you can specify specific migrations to undo.

dotnet ef database update 0

Reversing Create Player Migration

Now if we refresh in our SQL Client the database still exists but the Players table has been dropped.

Player Table Dropped

The Player table has been removed from the database but we still need to remove the migration.  It’s best if we let Entity Framework remove the migration for us.  The below command will remove the last migration file created.

dotnet ef migrations remove

Remove Create Player Migration

Migration Removed

Since it was the only migration file the Migrations folder was also removed.  If we had other migrations the folder would not have been removed.

Now that we have cleaned everything up go to the Player model and set the max length to 50 and save your changes.

[MaxLength(50)]
public string Name {get; set; }

 

Add Max Length to Name on Player Model

Now recreate the migration file.

 
dotnet ef migrations add CreatePlayerTable

Create Player Table Migration With Max Length

Create Player Migration With Max Length

Notice that the max length is set in the migration file?  Now apply this migration.

dotnet ef database update

Run Create Player Migration With Max Length

Refresh our database client and you should see Players table with a correct Name length field.

Player Table With Max Length

Whew, that was a lot of work.  The next part won’t be as long as we can skip a lot of the first time setup.

If you got stuck you can find completed Part 1 here and the completed application here.  In Part 2 we will create Games table similar to what we did above.  Finally if you have any questions or spot an issue in the code I would prefer if you opened a issue in GitHub but you can e-mail (chris.cumming@saturdaymp.com) me as well.

Posted in Code Examples, Introduction to ORMs for DBAs, Software Development | Tagged , , , , , , | Comments Off on Introduction to ORMs for DBAs: Part 1 – Create Player Table

Introduction to ORMs for DBAs: Part 0 – Setup

SQL Saturday LogoI gave a presentation at SQL Saturday 710 that didn’t go as well as I liked (i.e. I had trouble getting my demo working).  For those that attended thank you for patience.  Hopefully it was still worth attending and the discussion without the live coding was worth while.

Since the demo didn’t work live I thought I would show you what was supposed to happen during the demo.  The source code for the demo is here.

I used a MacBook pro for my presentation but the demo will also work on Windows.  Just be prepared for Mac screenshots.

First thing you need is Visual Studio 2017.  The community edition is free to use.  To install just keep clicking next.  It should install .NET Core 2.0, or higher for you.

Next you need to setup SQL Server.  You can find the steps to run SQL Server inside a Docker container here.  If you are on Windows the steps are similar just use the Windows SQL Server Docker image instead.

I used DataGrip as my client to access SQL Server but you can use Microsoft SQL Operations Studio instead.

Intro to ORM for DBAs Demo Steps

That is all you need to run the demo.  Future posts all walk through demo steps as outlined above.  If you have any questions open a issue in the GitHub repository.

Posted in Code Examples, Introduction to ORMs for DBAs, Software Development | Tagged , , , , , , | Comments Off on Introduction to ORMs for DBAs: Part 0 – Setup

Today I Learned how to Run SQL Server on a Mac using Docker

Working on my code example for my upcoming SQL Saturday 710 talk I ran into a performance issue.  My laptop is an older MacBook Pro and I was trying to run my example on a Windows virtual machine…

What is SQL Saturday?  Good question.

SQL Saturday Logo

SQL Saturday is an entire day of talks, training, and socializing about databases and data storage.  SQL Saturday 710 is on May 5th in Edmonton but there are SQL Saturdays, with different numbers at the end, all around the world on different dates.

Back to my problem which was running my example on a Windows virtual machine running on my older MacBook Pro. I’ve upgraded my MacBook Pro over the years with more RAM and a SSD hard drive the virtual machine is still not responsive enough for my liking with SQL Server and Visual Studio running on it.  I’ll be doing some live coding and database querying and want a nice experience for the attendees.

My options were procure a Windows laptop for the presentation, dual boot my existing laptop, or see if I could run my example natively on my Mac.  I didn’t want to borrow a laptop and setting up dual boot and installing Windows, Visual Studio, etc sounded like a lot of work.

I knew I could run the website part of my example on my Mac thanks to .NET Core but what about SQL Server?  I could use a different database but wanted to stick with SQL Server because it’s the database most of the SQL Saturday attendees are familiar with.

Some Googling reviled that SQL Server can run on a Docker container and that Microsoft has a SQL Server Docker image.  I tried it an it worked.

First install Docker for MacOS which you can find here.  Once installed you should see a whale in the status menus at the top right.

Docker Running on Mac

Next open up a terminal and pull down the SQL Server Docker image.

 
$ docker pull microsoft/mssql-server-linux

Pull SQL Server Docker Image

Now run the Docker image.

 
$ docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Password1234!' -p 1433:1433 --name SQLServerForDemo -d microsoft/mssql-server-linux:latest

Run SQL Server Docker Container

List Running Docker Images

Notice the port number and don’t forget to set the name so it’s easier to find your image.

Now you can access SQL Server via the command line but I prefer a GUI client.  Plus a GUI client would look better during the presentation.  You can’t install SQL Server Management Studio on a Mac but there other clients you can use.  My preference is DataGrip because it works well and it’s included in my JetBrains subscription.  If you use ReSharper check your subscription level and see if it’s includes DataGrip.

In DataGrip you need to add a new DataSource.

DataGrip add SQL Server Data Source

For the address you can use localhost but you need to specify the port you used when running the SQL Server Docker container.  Your password is the one you used to run the Docker image.  Also install any missing drivers if prompted.

DataGrip Setup SQL Server Data SourceNow you should be able to see your SQL Server database server in DataGrip.  You can then add new databases as needed.

DataGrip Create TestDb

Any changes you make to the database will be persisted even when you start and stop the image.

P.S. – Picked this song because Docker has a whale as it’s mascot and this video is about a whale and got a whale in the video.

And we are far from home, but we’re so happy
Far from home, all alone, but we’re so happy

 

Posted in Software Development, Today I Learned | Tagged , , , | Comments Off on Today I Learned how to Run SQL Server on a Mac using Docker

Chris, have you created that Ruby on Rails 5 project? What Does it Look Like I’m Doing!

In the above video you can replace the question with:

Chris, have created that Ruby on Rails 5 project?

What dose it look like I’m Doing!

Scene 1: Create new Ruby on Rails project using Ruby Mine.

New Ruby Mine Project

Scene 2: Try to install the Gems find out I need Ruby 2.5.

Bundler Error

Scene 3: Try to install Ruby 2.5 but it fails with a weird curl error message.

Curl Error Installing Ruby

Scene 4: With help from the world’s greatest detective I figure out my version of OpenSSL is out of date.  Try running updates to get the latest version.

Updating OpenSSL

Scene 5: Didn’t fix the problem.  Figure out the version of OpenSSL being used is the Centrify version.  Yes I always think the “which” command is “where”.  Why is the command called “which”?

Which OpenSSL

Scene 6: Investigate the best way to fix this problem.  Decide it’s easiest to update Centrify.

Upgrading Centrify Express Part 1

Upgrading Centrify Express Part 2

Upgrading Centrify Express Part 3

Scene 7: Problem is fixed and I can install Ruby 2.5.

Installed Ruby 2.5

I still need to install and/or update a couple other packages, such as nodejs, but that is what I expected.  I didn’t expect to have to waste an hour diagnosing an old OpenSSL issue.

Posted in Code Examples, Software Development | Tagged , , , , | Comments Off on Chris, have you created that Ruby on Rails 5 project? What Does it Look Like I’m Doing!

Today I Learned how to Run SpinRite on a UEIF Motherboard

I have a Synology NAS that has starting acting up.  By acting up I mean I can’t connect to it and it requires a unplugged reboot to fix it.  Power button no work.  After doing some detective work I’m not sure if the problem is with the hard drives or the Synology it’s self.

To help me narrow down the problem I thought I’d run SpinRite which is a hard drive diagnostic tool.  I heard about SpinRite from the Security Now podcast.  It was created by Steve Gibson, one of the hosts and they talk about it’s virtues often on the podcast.

I bought and download the application and then was bit stumped on what to do next.  The documentation on the website is dated so it took me a while to figure out how to get the software to run.  I had an especially difficult time because the software was written before UEIF motherboards where common place and my mother board has UEIF enabled.  So I documented my steps so I remember and hopefully it will help others as well.

First off I couldn’t just run SpinRite on my Synology.  I had to remove the hard drives from the Synology and connect them to a motherboard using a SATA cable.

Connect Hard Drive to SATA Cable

Once connected I needed to create bootable USB drive.  This took some trial and error because at first I just created the bootable USB using the SpinRite program and tried disabling the UEIF on my mother board.  This didn’t work.  I also tried creating a ISO image using SpinRite.  This also didn’t work.  In both cases the USB drive would not boot, just a black screen.

In summary don’t do the below screen shots.  Just ignore them.

SpinRite Home Screen

SpinRite Create Boot Disk

SpinRite Create ISO

I think the problem is the SpinRite application uses FreeDOS which does not play well with UEIF.  At least it didn’t play well with my BIOS, your mileage might vary.

What did work was creating a bootable USB stick using the Rufus program.  The settings I used are outlined below.

Rufus Create Bootable USB

Once you have the bootable USB stick copy over the SpinRite executable to the USB stick.  Then reboot your computer and make sure you boot from the USB stick.

Boot From USB

This should load MS-DOS.  Select your keyboard and then run the SpinRite program.  For you young wippersnapper DOS was the first operating system developed by Microsoft before Windows.

Run SpinRite in MS-DOS

Press any key once you are done reading the SpinRite splash screen.

SpinRite Splash Screen

Then choose 4 for maintenance.

SpinRite Levels

Then prepared to be really sad because your hard drive is 3TB and SpinRite 6 can’t handle drives larger then 2TB.

SpinRite Invalid Partition

There has been talk of SpinRite 7 which should fix this issue but for now I’m out of luck.

So this is really a BOGO Today I Learned.  Not only did I learn how to run SpinRite on a modern BIOS but I also learned that it can’t handle drive sizes larger then 2TB.

Sorry for the surprise and somewhat sad ending.

 

P.S. – A Boy Named Sue is great song with a surprise ending.  Not necessarily sad but still surprising.

P.S.S. – I just learned that this song was written by Shel Silverstein.  You know, that scary guy at the back of your favourite children’s book.

And I think about him, now and then,
Every time I try and every time I win,
And if I ever have a son, I think I’m gonna name him…

 

 

Posted in Hardware, Software Development, Today I Learned | Tagged , , | Comments Off on Today I Learned how to Run SpinRite on a UEIF Motherboard

Just Enjoy the Show

I’m on optimist and generally think things will work out for the best.  I like to say:

The future is awesome!

That said it’s easy to forget how awesome individual days can be.  How many small but amazing things occur each day that we gloss over.  Our day is 99% good and 1% bad but we focus on that one minor bad moment.

Think about your conversations at the end of the day.  Does it focus on the negatives of the day?  That boozo that cut me off in traffic.  The Internet was down for 5 minutes.  I had to stay at work 10 minutes late.  Etc.

Imagine changing the conversation to focus on all the good things that happened that day.  The person that let me merge into their lane.  Technology let send a e-mail to relative half-way around the world.  I solved several work problems with the help of my co-workers.  Etc.

My inspiration for this post was the last Burnie Vlog at Rooster Teeth.  It is a, heavily edited, example of his day.  It does a great job of showing how a normal day is full of awesomeness.  Awesomeness that we often take for granted.  At least that is what I got out of it.

The Vlog caused me reflect on my last Friday.  A day that contained many awesome moments that I didn’t fully appreciate at the time:

  • Woke up a bit late because I had a remote work meeting late the night before people from the United States and India.
  • Still got up in time to make my family breakfast and see them off.
  • Cleaned myself up.
  • OfficeNo commute because I’m lucky enough to work from my basement office.
  • Had remote meeting with co-workers in Eastern Canada and States.  Great group to work with.
  • Worked on my first official code inspection.  I’ve done code reviews and informal code inspections but not a structured official one.  I’m really excited.
  • Watched the end of Altered Carbon on my couch while eating lunch.
  • Went for a walk because it was nice winter day in Edmonton.
  • Greeted my amazing daughter and beautiful wife when they got home from school and work.
  • DreamcastHad some time before dinner so my daughter and I packed up board games, old TV, and Dreamcast in the car.  I have Tony Hawk Pro Skater for the Dreamcast.
  • Ate dinner with my family.  Talked about what live would be like if could move our conscious to other bodies like in Altered Carbon.
  • Drove to the local community hall and setup for the monthly Games Night.  I bring a bunch of board games and setup a TV with a an old video game console.  This night was Dreamcast but previous months it’s been NES, Super NES, Intellivision, etc.
  • Played a new game Terraforming Mars.
  • BoardgamesVisited with friends and neighbours.  Sang happy birthday to a neighbour and ate cake.  My beautiful wife dropped in for a bit as well which always makes me smile.
  • Watched my daughter teach and play Zombicide and The Dragon and Flagon.  Still not used to seeing my daughter and her friends play complicated games without adults.
  • Packed up a bit earlier then usual (i.e. before midnight) but for a good reason: we are going skiing the next day with friends.  Just to a local Edmonton hill but it’s to practice for our upcoming mountain ski trip.
  • Sent my daughter to bed then snuggled up with my wife and drifted off to sleep.

All in all it was great day.  Preceded by many great days before it and many great days to come.  Just enjoy the show and don’t ask for your money back.  Also, the future is awesome.

 

P.S. – After watching the Final Vlog video above I had The Show song stuck in my head and kept singing it much to my family’s chagrin.  At least singing the couple lines I knew.  Below is the cover sung in the Final Vlog.

I’m just a little bit caught in the middle
Life is a maze and love is a riddle
I don’t know where to go; can’t do it alone; I’ve tried
And I don’t know why

I want my money back
I want my money back
I want my money back
Just enjoy the show

Posted in Fun | Tagged , , , | Comments Off on Just Enjoy the Show