How to keep your database schema updated with Simple.Migrations

Do you email SQL scripts to a coworker to update your database schema? Do you update your database schema by hand? I’ve being there. Let’s find out about database migrations with Simple.Migrations.

A database migration is a uniquely identified operation to create, update or delete database objects. Migrations are a more scalable and maintainable alternative to running SQL scripts directly into your database to update its schema.

Let’s migrate

Migrations allow you to create and setup testing and development environments easily.

Have you ever needed to create a new environment to reproduce an issue? But, you forgot to run one or two scripts to create columns in a table and your application couldn’t even start. Then, you realized you had two problems.

Migrations help you to keep your database schema and your SQL scripts under version control in sync.

No more emails with database scripts!

Simple.Migrations

Simple.Migrations “is a simple bare-bones migration framework for .NET Core”. It provides “a set of simple, extendable, and composable tools for integrating migrations into your application”.

Simple.Migrations has out-of-the-box database providers for SQL Server, SQLite, PostreSQL and MySQL. But, you can create your own provider too.

Let’s create our first migration for an employees database using SQL Server.

1. Create a new Employees table

First, in a Console application, install Simple.Migrations NuGet package. Then, create a class CreateEmployees inheriting from the Migration base class. Don’t forget to add the using SimpleMigrations; statement.

With Simple.Migrations, all migrations should override two methods: Up and Down.

The Up() method should contain database operation to apply. For example, creating a new table, adding a new column to an existing table, etc. And, the Down() method should contain the steps to rollback that operation. Remember, we want to apply and rollback migrations.

For our first migration, the Up() method will have the SQL statement to create the Employees table. And, the Down() method, the statement to remove it.

You can use the Execute() method from the Migration class to run your SQL statements. But, you have a Connection property of type DbConnection to bring your own database layer or ORM of choice.

A migration should use be uniquely identified.

You need to annotate your migration with a version number using the [Migration] attribute. Either a consecutive number or a timestamp-like number is fine.

Make sure to not to repeat the same version number. Otherwise, you will get a MigrationLoadFailedException.

This is our CreateEmployees migration with CREATE TABLE and DROP TABLE statements.

using SimpleMigrations;

[Migration(1)]
public class CreateEmployees : Migration
{
    protected override void Up()
    {
        Execute(@"CREATE TABLE Employees
                (
                    [Id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
                    [SSO] [varchar](24) NOT NULL,
                    [FirstName] [varchar](255) NOT NULL,
                    [MiddleName] [varchar](255) NOT NULL,
                    [LastName] [varchar](255) NOT NULL,
                    [Salary] [decimal](18) NOT NULL,
                    [CreatedDate] [datetime] NULL,
                    [UpdatedDate] [datetime] NULL,
                )");
    }

    protected override void Down()
    {
        Execute(@"DROP TABLE Employees");
    }
}

I know, I know…Yes, I copied the SQL statement from SQL Server Management Studio Database Designer.

2. Apply our first migration

The next step is to update the Console application to run this migration.

Inside the Main() method of your console application, create a connection to your database and use the SimpleMigrator class. Its constructor needs the assembly containing the migrations and a database provider.

For our example, the MssqlDatabaseProvider is the appropriate provider.

With the SimpleMigrator class, you can use two methods: MigrateTo() and MigrateToLatest().

MigrateTo() applies an specific version into your database. And MigrateToLatest(), all versions up to the latest one. Before using any of these two methods, make sure to call the Load method.

The Main() method of our console application looks like this.

class Program
{
    static void Main(string[] args)
    {
        var connString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Payroll;Integrated Security=True;";
        using (var connection = new SqlConnection(connString))
        {
            var databaseProvider = new MssqlDatabaseProvider(connection);
            var migrator = new SimpleMigrator(typeof(AssemblyWithYourMigrations).Assembly, databaseProvider);
            migrator.Load();
            migrator.MigrateToLatest();
        }
    }
}

Run your console application to apply your first migration.

Simple.Migrations creates a dbo.VersionInfo table on your database. This table keeps track of the applied migrations. It should look like this one.

3. Add a column to an existing table

Now, suppose you need to add a Type column to the Employees table.

This time, create an AddTypeToEmployee class with the SQL statements needed. Remember, you need a different version number.

For example, the AddTypeToEmployee will look like this.

[Migration(2)]
public class AddTypeToEmployee : Migration
{
    protected override void Up()
    {
        Execute(@"ALTER TABLE Employees
                  ADD Type VARCHAR(8) NULL");
    }

    protected override void Down()
    {
        Execute(@"ALTER TABLE Employees
                  DROP COLUMN Type");
    }
}

Again, run the console application. Notice how the Employees and VersionInfo tables have changed on your database.

4. A runner

Finally, you can create a runner to apply your migrations. Simple.Migrations has a predefined console runner.

We have a Console application that always applies all the migrations to the latest. We need more flexibility to apply any migration or rollback any other.

Let’s use .NET Core configuration options to move the connection string to a settings file. We have ours hardcoded into our Console application.

For this, you need two install two NuGet packages:

• Microsoft.Extensions.Configuration, and
• Microsoft.Extensions.Configuration.Json

Then, create an appsettings.json file with your connection string. Mine looks like this.

{
  "ConnectionStrings": {
    "YourConnString": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=Payroll;Integrated Security=True;"
  }
}

If you’re using different environments (QA, Staging, for example), you can read the environment name from an environment variable.

Then, with the ConfigurationBuilder class, you can load the appropiate json file with our connection string per environment.

After using the console runner, our console application looks like this.

class Program
{
    static void Main(string[] args)
    {
        var connString = Configuration().GetConnectionString("YourConnString");
        using (var connection = new SqlConnection(connString))
        {
            var databaseProvider = new MssqlDatabaseProvider(connection);
            var migrator = new SimpleMigrator(typeof(AssemblyWithYourMigrations).Assembly, databaseProvider);

            var consoleRunner = new ConsoleRunner(migrator);
            consoleRunner.Run(args);

            Console.ReadKey();
        }
    }

    public static IConfigurationRoot Configuration()
    {
        var environmentName = Environment.GetEnvironmentVariable("DOTNET_ENVIRONMENT");

        var configurationBuilder = new ConfigurationBuilder()
            .AddJsonFile("appsettings.json")
            .AddJsonFile($"appsettings.{environmentName}.json", optional: true);
        return configurationBuilder.Build();
    }
}

Simple.Migrations default ConsoleRunner reads the commands up to migrate to the latest version, to to migrate to an specific version and down to revert back to a version. If the command arguments you provide are invalid, you will get a help message.

Conclusion

Voilà! That’s how we can keep our database schema up to date with migrations and Simple.Migrations.

Migrations are a better alternative to running scripts directly into your database. You can use migrations to create constraints and indexes too. With migrations, your database structure is under version control and reviewed as it should be.

Your mission, Jim, should you decide to accept it, is to add a Payments table with a relation to the Employees table. It should contain an id, a date, a paid value and the employee id. This post will self-destruct in five seconds. Good luck, Jim.

To learn more about reading configuration files in ASP.NET Core, read Configuration and the Options pattern in ASP.NET Core. Speaking of ASP.NET Core and databases, check How to create a CRUD API with Insight.Database.

Also, check how to simplify your migrations by squashing old migration files.

Happy migration time!

How to create fakes with Moq. And what I don't like about it

A recurring task when we write unit tests is creating replacements for collaborators. If we’re writing unit tests for an order delivery system, we don’t want to charge a credit card every time we run our tests. This is how we can create fakes using Moq.

Fakes or test doubles are testable replacements for dependencies and external systems. Fakes could return a fixed value or throw an exception to test the logic around the dependency they replace. Fakes can be created manually or with a mocking library like Moq.

Think of fakes or test doubles like body or stunt doubles in movies. They substitute an actor in life-threatening scenes without showing their face. In unit testing, fakes replace external components.

How to write your own fakes

We can write our own fakes by hand or use a mocking library.

If we apply the Dependency Inversion Principle, the D of SOLID, our dependencies are well abstracted using interfaces. Each service receives its collaborators instead of building them directly.

To create a fake, we create a class that inherits from an interface. Then, on Visual Studio, from the “Quick Refactorings” menu, we choose the “Implement interface” option. Et voilà! We have our own fake.

But, if we need to create lots of fake collaborators, a mocking library can make things easier. Mocking libraries are an alternative to writing our own fakes manually. They offer a friendly API to create fakes for an interface or an abstract class. Let’s see Moq, one of them!

Moq, a mocking library

Moq is a mocking library that ” is designed to be a very practical, unobtrusive and straight-forward way to quickly setup dependencies for your tests”.

Moq, ” the most popular and friendly mocking library for .NET”

From moq

Create fakes with Moq…Action!

Let’s see Moq in action! Let’s start with an OrderService that uses an IPaymentGateway and IStockService. This OrderService checks if an item has stock available to charge a credit card when placing a new order. Something like this,

public class OrderService 
{
    private readonly IPaymentGateway _paymentGateway;
    private readonly IStockService _stockService;
    
    public OrderService(IPaymentGateway paymentGateway, IStockService stockService)
    {
        _paymentGateway = paymentGateway;
        _stockService = stockService;
    }
    
    public OrderResult PlaceOrder(Order order)
    {
        if (!_stockService.IsStockAvailable(order))
        {
            throw new OutOfStockException();
        }
        
        _paymentGateway.ProcessPayment(order);
            
        return new PlaceOrderResult(order);
    }
}

To test this service, let’s create replacements for the real payment gateway and stock service. We want to check what the OrderService class does when there’s stock available and when there isn’t.

For our test name, let’s follow the naming convention from The Art of Unit Testing. With this naming convention, a test name shows the entry point, the scenario, and the expected result separated by underscores.

Of course, that’s not the only naming convention. There are other ways to name our tests.

[TestClass]
public class OrderServiceTests
{
    [TestMethod]
    public void PlaceOrder_StockAvailable_CallsProcessPayment()
    {
        var fakePaymentGateway = new Mock<IPaymentGateway>();

        var fakeStockService = new Mock<IStockService>();
        fakeStockService
            .Setup(t => t.IsStockAvailable(It.IsAny<Order>()))
            .Returns(true);
        var orderService = new OrderService(fakePaymentGateway.Object, fakeStockService.Object);

        var order = new Order();
        orderService.PlaceOrder(order);

        fakePaymentGateway
            .Verify(t => t.ProcessPayment(order), Times.Once);
    }
}

What happened here? First, it creates a fake for IPaymentGateway with new Mock<IPaymentGateway>(). Moq can create fakes for classes too.

Then, it creates another fake for IStockService. This fake returns true when the method IsStockAvailable() is called with any order as a parameter.

Next, it uses the Object property of the Mock class to create instances of the fakes. With these two instances, it builds the OrderService.

Finally, using the Verify() method, it checks if the method ProcessPayment() was called once. A passing test now!

Cut!…What I don’t like about Moq

Moq is easy to use. We can start using it in minutes! We only need to read the README and quickstart files in the documentation. But…

For Moq, everything is a mock: Mock<T>. Strictly speaking, everything isn’t a mock. There’s a difference between stubs and mocks.

The XUnit Tests Patterns book presents a detailed category of fakes or doubles: fakes, stubs, mocks, dummies, and spies. And, The Art of Unit Testing book reduces this classification to only three types: fakes, stubs, and mocks.

Other libraries use Fake, Substitute, or Stub/Mock instead of only Mock.

Moq has chosen this simplification to make it easier to use. But, this could lead us to misuse the term “mock.” So far, I have deliberately used the word “fake” instead of “mock” for a reason.

For Moq, MockRepository is a factory of mocks. We can verify all mocks created from this factory in a single call. But, a repository is a pattern to abstract creating and accessing records in a data store. We will find OrderRepository or EmployeeRepository. Are MockSession or MockGroup better alternatives? Probably. Naming is hard anyway.

Conclusion

Voilà! That’s how we create fakes and mocks with Moq. Moq is a great library! It keeps its promise. It’s easy to set up dependencies in our tests. We need to know only a few methods to start using it. We only need: Setup, Returns, Throws, and Verify. It has chosen to lower the barrier of writing tests. Give it a try! To mock or not to mock!

If you use Moq often, avoid typing the same method names all the time with these snippets I wrote for Visual Studio.

For more tips on writing unit tests, check my posts on how to write good unit tests by reducing noise and writing failing tests. And don’t miss the rest of my Unit Testing 101 series where I cover more subjects like this one.

Happy mocking time!

Ten lessons learned after one year of remote work

It’s been a year since I started to work remotely. These are the lessons I learned.

For employers, leaders, or managers

1. Give clear instructions

State what you expect and when you expect it. Make sure to say the day, time, and timezone, if needed. “Tomorrow morning” is at a different time in different parts of the world. Have in mind that probably not everyone in your team is a native speaker of your language.

2. Don’t expect new team members to follow unclear coding conventions

A new developer will pick coding conventions from existing code instead of an old document. Have a perfectly formatted and crafted sample code.

3. Make new developers pair with another team member

On the onboarding process, a new member will feel more comfortable talking to another developer. Assign an onboarding buddy. Make new developers free to ask questions about the new team, company product, or anything else.

4. Have your developers up and running as quickly as possible

You don’t want your developers to scratch their heads trying to install the right version of your tools and projects. Create README files, installation scripts, or anything else that helps to set up the working environment. Anyone should start using your projects by pressing a button or running a single script.

For employees or workers

5. Control noise and light in your environment

Have a high-quality headset. Reduce the background noise as much as you can. Make sure to mute and unmute your microphone when appropriate. You’re muted, John! Position yourself in a way people don’t appear walking in the back. Light up your environment to clear your face.

6. Strive to have the best level you can in the language spoken at work

Have good pronunciation. This would make things easier for everyone. You don’t want to be the guy nobody wants to work with because of his poor speaking skills.

7. Separate work and personal spaces

At the end of your day, do something that signals the end of the day. Sit on the couch, meditate, go for a walk or change your clothes.

8. Schedule regular pauses

When working in an office, you can grab a coffee or tap into a colleague’s shoulder. But, while at home, you could be sitting for hours without even realizing it. Set an alarm, stand, and walk! Your body will thank you later.

9. Connect with others

You could be working from home for weeks without getting outside. Don’t be home alone! Interact with others! Call your friends, go out with your family, work from a coworking space.

At the time of writing, we are at home due to COVID-19. But, this last piece of advice is still applicable, staying at home and keeping social distancing.

10. Before presentations, turn off auto-updates and notifications

You don’t want your computer to restart updating the operating system in the middle of a presentation or an important meeting. This is a true story that happened to a friend of a friend of mine.

Voilà! Ten lessons I learned after my first year of remote work. For more career lessons, check the lessons I learned after five years as a software engineer and the things I wished I knew before becoming a software engineer. Don’t miss my takeaways from The Clean Coder. That’s a good one on career advice.

Happy remote work!

Two C# idioms: On Dictionaries

This post is part of the series "C# idioms"

1. Two C# idioms
2. Another two C# idioms
3. Two C# idioms: On Dictionaries This post
4. Two C# idioms: On defaults and switch

This part of the C# idioms series is only about dictionaries. Let’s get rid of exceptions when working with dictionaries.

Instead of checking if a dictionary contains an item before adding it, use TryAdd

TryAdd() will return if an item was added or not to the dictionary. Unlike Add(), if the given key is already in the dictionary, TryAdd() won’t throw any exception. It will simply do nothing. The item is already there.

Before, if we added an item that already exists on the dictionary, we got an ArgumentException.

var myDictionary = new Dictionary<string, string>();
myDictionary.Add("foo", "bar");

myDictionary.Add("foo", "baz");
// ^^^
// System.ArgumentException:
//     An item with the same key has already been added. Key: foo

After, we checked first if the dictionary contains the item.

var myDictionary = new Dictionary<string, string>();

if (!myDictionary.ContainsKey("foo"))
  myDictionary.Add("foo", "bar");

Even better, let’s use TryAdd().

var myDictionary = new Dictionary<string, string>();
myDictionary.TryAdd("foo", "bar"); // true

myDictionary.Add("foo", "baz");
myDictionary.TryAdd("foo", "bar"); // false

Avoid KeyNotFoundException with TryGetValue or GetValueOrDefault

At least now, the KeyNotFoundException message contains the name of the not-found key. The old days chasing the not-found key are over.

On one hand, TryGetValue() uses an output parameter with the found value. It outputs a default value when the dictionary doesn’t contain the item. TryGetValue() dates back to the days without tuples.

On another hand, GetValueOrDefault() returns a default value or one you provide if the key wasn’t found.

Before, if we tried to retrieve a key that didn’t exist on a dictionary, we got a KeyNotFoundException.

var dict = new Dictionary<string, string>();

dict["foo"];
// ^^^
// System.Collections.Generic.KeyNotFoundException:
//     The given key 'foo' was not present in the dictionary.

After, we used TryGetValue().

var dict = new Dictionary<string, string>();

dict.TryGetValue("foo", out var foo); // false, foo -> null

dict.Add("foo", "bar");
dict.TryGetValue("foo", out foo); // true, foo -> "bar"

Even better, we use GetValueOrDefault().

var dict = new Dictionary<string, string>();

dict.GetValueOrDefault("foo"); // null
dict.GetValueOrDefault("foo", "withoutFoo"); // "withoutFoo"

dict.Add("foo", "bar");
dict.GetValueOrDefault("foo", "withoutFoo"); // "bar"

Voilà! That’s how to get rid of exception when working with dictionaries. Use TryAdd() and GetValueOrDefault(). Or, if you prefer output parameters, TryGetValue().

Don’t miss the previous C# idioms to separate view models into versions and the next two C# idioms to better work with defaults and switches.

Happy coding!

Another two C# idioms

This post is part of the series "C# idioms"

1. Two C# idioms
2. Another two C# idioms This post
3. Two C# idioms: On Dictionaries
4. Two C# idioms: On defaults and switch

In this part of the C# idioms series, we have one idiom to organize versions of commands, events or view models. And another idiom, on coditionals inside switch statements.

Separate versions of commands and events using namespaces and static classes

Sometimes you need to support versions of your objects to add new properties or remove old ones. Think of, commands and queries when working with Command Query Responsibility Segregation (CQRS), or request and response view models in your API projects.

One alternative to organize classes by version is to encode the version number in the class name itself. For example, DoSomethingCommandV2.

For better organization, separate your commands and queries inside a namespace named with the version number.

namespace Commands.V2
{
  public class DoSomethingCommand
  {
  }
}

But, someone could use one version instead of the other by mistake. Imagine someone writing the class name and using Ctrl + . in Visual Studio to resolve the using statement blindly.

Another option to group classes by vesion is to wrap your commands and queries inside an static, partial class named after the version number.

namespace Commands
{
    public static partial class V2
    {
        public class DoSomethingCommand
        {
        }
    }
}

When using static classes to separate classes by version, you will use the version number up front. Something like, V2.DoSomethingCommand. This time, it’s obvious which version is used.

But, if you use a partial classes and you keep your commands and events in different projects, you will end up with a name conflict. There will be two V2 classes in different projects. Then you would need to use an extern alias to differentiate between the two.

Finally, you can take the best of both worlds, namespaces and wrapper static classes.

namespace Commands.V2
{
    public static partial class V2
    {
        public class DoSomethingCommand
        {
        }
    }
}

Conditional cases in switch statements

When working with switch statements, you can use a when clause instead of an if/else in your case expressions.

Before, we used if inside switches

switch (myVar)
{
  case aCase:
    if (someCondition)
    {
      DoX();
    }
    else
    {
      DoY();
    }
    break;
    
    // other cases...
}

After, we use when in our case expressions

switch (myVar)
{
  case aCase when someCondition:
      DoX();
      break;
  
  case aCase:
      DoY();
      break;
      
  // other cases...
}

Order is important when replacing if/else inside cases with when clauses. The case/when should be higher than the corresponding case without when.

Voilà! Keep your command, queries and view models organized by versions with namespaces, static classes or both. Use when in switch statements.

Don’t miss the previous C# idioms to refactor conditionals and the next two C# idioms to get rid of exception when working with dictionaries.

Happy C# time!