DEV Community: Muhammad Azeez

Automatic periodic backups from PostgreSQL to S3 using Cron

Muhammad Azeez — Sat, 29 Jan 2022 15:04:42 +0000

If you're managing your own databases you'll need to make sure you database is backed up properly. A good option is to store your backups in an S3-compatible object storage.

Install s3cmd:

sudo apt install s3cmd

Configure it:

s3cmd --configure

It will ask you several questions, consult your S3 providers docs for more information.

Write a bash script to create a postgresql dump and upload it to S3:

DIR=$(dirname "${BASH_SOURCE[0]}")
DB_NAME=my_db
DUMP_PATH="$DIR/$DB_NAME_$(date +"%Y-%m-%d@%H-%M").dump"

# DUMP the database
pg_dump --encoding utf8 --format c --compress 9 --file $DUMP_PATH $DB_NAME

DUMP_KEY=$DUMP_PATH | cut -c 3- # Remove the ./ from the path

# Upload the dump file to S3
s3cmd put $DUMP_PATH s3://$DB_NAME-db-backups/$DB_NAME/$DUMP_KEY

# Remove the dump file from disk
rm $DUMP_PATH

For more information about taking PostgreSQL backups, checkout my previous post.

Write a crontab job to run your script periodically:

crontab -e

0 */3 * * * path/to/your/script/job.sh

Note: This expression means the job will be run every 3 hours. You can change it to whatever your want.

Supporting Bi-directional text in Html TextArea

Muhammad Azeez — Thu, 13 Jan 2022 12:58:01 +0000

A <textarea> is an HTML element used to capture multiline user input. By default, it's direction is either right to left or left to right. But what if we want each paragraph to have it's own direction. This is very useful when the text is a mix of multiple languages. For example: Kurdish and English.

I asked the quesiton on Twitter and my good friend Akam Foad came to the rescue:

have you tested applying unicode-bidi: plaintext on textarea?https://t.co/UC8A0ZKYKj
— Akam Foad (@akamfoad) January 13, 2022

It turns out, that you can easily support this by specifying unicode-bidi: plaintext in the styles of the <textarea>

<textarea style="unicode-bidi:plaintext"></textarea>

Mocking Authentication and Authorization in ASP.NET Core Integration Tests

Muhammad Azeez — Sun, 12 Dec 2021 21:27:31 +0000

ASP.NET Core makes writing integration tests very easy and even fun. One aspect that might be a bit tough to figure out is authentication and authorization. We might want to run integration tests under different users and different roles.

To get started, let's assume we have an endpoint like this:

app.MapGet("hi", (HttpContext httpContext) =>
{
    var userId = httpContext.User?.Claims?.FirstOrDefault(c => c.Type == ClaimTypes.NameIdentifier)?.Value;

    return $"Hello #{userId}";
}).RequireAuthorization();

It's a very simple endpoint. It gets the currently logged in user's ID and says hello to them.

To make it possible to mock auth, we have to register a custom AuthenticationHandler.

Here is a simple implementation of a mock Authentication Handler:

public class TestAuthHandlerOptions : AuthenticationSchemeOptions
{
    public string DefaultUserId { get; set; } = null!;
}

public class TestAuthHandler : AuthenticationHandler<TestAuthHandlerOptions>
{
    public const string UserId = "UserId";

    public const string AuthenticationScheme = "Test";
    private readonly string _defaultUserId;

    public TestAuthHandler(
        IOptionsMonitor<TestAuthHandlerOptions> options,
        ILoggerFactory logger,
        UrlEncoder encoder,
        ISystemClock clock) : base(options, logger, encoder, clock)
    {
        _defaultUserId = options.CurrentValue.DefaultUserId;
    }

    protected override Task<AuthenticateResult> HandleAuthenticateAsync()
    {
        var claims = new List<Claim> { new Claim(ClaimTypes.Name, "Test user") };

        // Extract User ID from the request headers if it exists,
        // otherwise use the default User ID from the options.
        if (Context.Request.Headers.TryGetValue(UserId, out var userId))
        {
            claims.Add(new Claim(ClaimTypes.NameIdentifier, userId[0]));
        }
        else
        {
            claims.Add(new Claim(ClaimTypes.NameIdentifier, _defaultUserId));
        }

        // TODO: Add as many claims as you need here

        var identity = new ClaimsIdentity(claims, AuthenticationScheme);
        var principal = new ClaimsPrincipal(identity);
        var ticket = new AuthenticationTicket(principal, AuthenticationScheme);

        var result = AuthenticateResult.Success(ticket);

        return Task.FromResult(result);
    }
}

The basic idea is this: by default authenticate every request with user id provided in the TestAuthHandlerOptions. If a test wants to send a request under on behalf of a different user, they can do so by sending the user ID in the UserId header of the HTTP request.

We also need to create a custom WebApplicationFactory that takes advantage of our mock Authentication Handler:

public class WebAppFactory : WebApplicationFactory<Program>
{
    public string DefaultUserId { get; set; } = "1";

    protected override void ConfigureWebHost(IWebHostBuilder builder)
    {
        builder.ConfigureTestServices(services =>
        {
            services.Configure<TestAuthHandlerOptions>(options => options.DefaultUserId = DefaultUserId);

            services.AddAuthentication(TestAuthHandler.AuthenticationScheme)
                .AddScheme<TestAuthHandlerOptions, TestAuthHandler>(TestAuthHandler.AuthenticationScheme, options => { });
        });
    }
}

We have defined a DefaultUserId property on the factory so that the individual test fixtures can specify their own default user ID.

And we can use the mock authentication in the test cases like this:

public class SimpleTest : IClassFixture<WebAppFactory>
{
    private HttpClient _httpClient;

    public SimpleTest(WebAppFactory factory)
    {
        factory.DefaultUserId = "5";

        _httpClient = factory.CreateClient();
        _httpClient.BaseAddress = new Uri("https://localhost/");
        // Use our mock Auth scheme 
        _httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Test");
    }

    [Fact]
    public async Task SayHiToNumber5()
    {
        _httpClient.DefaultRequestHeaders.Remove(TestAuthHandler.UserId);

        var response = await _httpClient.GetStringAsync("hi");
        Assert.Equal("Hello #5", response);
    }

    [Fact]
    public async Task SayHiToNumber1()
    {
        _httpClient.DefaultRequestHeaders.Add(TestAuthHandler.UserId, "1");

        var response = await _httpClient.GetStringAsync("hi");
        Assert.Equal("Hello #1", response);
    }

    [Fact]
    public async Task SayHiToNumber3()
    {
        _httpClient.DefaultRequestHeaders.Add(TestAuthHandler.UserId, "3");

        var response = await _httpClient.GetStringAsync("hi");
        Assert.Equal("Hello #3", response);
    }
}

And that's it! With a few lines of code, you now have a flexible mock authentication scheme that you can use in your tests. You can also customize it to match your needs.

You can download the source code on GitHub.

Testing Email Templates in ASP.NET Core

Muhammad Azeez — Thu, 09 Dec 2021 21:59:41 +0000

This post is my annual contribution to the 2021 C# Advent Calendar. Please check out all the great posts from our wonderful community!

Many systems require sending emails to notify users. And testing these notifications manually is a pain. So it's one of the best use cases for integration testing. First, let's create strongly typed model for our Welcome email:

public class Welcome
{
    public string FullName { get; set; }
}

And we create a Razor template for the email in EmailTemplates/Welcome.cshtml:

@model EmailSnapshotTesting.EmailTemplates.Welcome
@{
    Layout = "~/EmailTemplates/_Layout.cshtml";
}

<h1>Welcome @Model.FullName</h1>
<p>Welcome to our wonderful service!</p>

And this is how the layout is going to look like in EmaiTemplates/_Layout.cshtml:

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
</head>
<body>
    <div>
        @RenderBody()
    </div>
</body>
</html>

And then we create a service to send emails:

public class MailerService : IMailerService
{
    private readonly IEmailRenderer _renderer;
    private readonly IMailPostman _postman;

    public MailerService(
        IEmailRenderer renderer,
        IMailPostman postman)
    {
        _renderer = renderer;
        _postman = postman;
    }

    public async Task SendWelcomeEmail(string address, Welcome welcome)
    {
        await SendEmail($"Welcome {welcome.FullName}!", address, welcome);
    }

    public async Task SendEmail<T>(string subject, string address, T model)
    {
        var html = await _renderer.Render(model);

        await _postman.SendEmail(new Message
        {
            Subject = subject,
            Address = address,
            HtmlBody = html
        });
    }
}

The MailerService needs an IEmailRenderer to get HTML content from the strongly typed model and an IMailPostman to send the emails.

Here is an implementation of IEmailRenderer that renders the Razor template we specified above:

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Abstractions;
using Microsoft.AspNetCore.Mvc.ModelBinding;
using Microsoft.AspNetCore.Mvc.Razor;
using Microsoft.AspNetCore.Mvc.Rendering;
using Microsoft.AspNetCore.Mvc.ViewFeatures;

namespace EmailSnapshotTesting.Services;

// https://stackoverflow.com/a/49275145
// https://ppolyzos.com/2016/09/09/asp-net-core-render-view-to-string/

public class RazorEmailRenderer : IEmailRenderer
{
    private readonly IRazorViewEngine _razorViewEngine;
    private readonly ITempDataProvider _tempDataProvider;
    private readonly IServiceProvider _serviceProvider;

    public RazorEmailRenderer(
        IRazorViewEngine razorViewEngine,
        ITempDataProvider tempDataProvider,
        IServiceProvider serviceProvider)
    {
        _razorViewEngine = razorViewEngine;
        _tempDataProvider = tempDataProvider;
        _serviceProvider = serviceProvider;
    }

    public async Task<string> Render<T>(T model)
    {
        // Note: You can also support multiple languages by separating each locale into a folder
        var viewPath = $"~/EmailTemplates/{typeof(T).Name}.cshtml";
        var result = _razorViewEngine.GetView(null, viewPath, true);

        if (result.Success != true)
        {
            var searchedLocations = string.Join("\n", result.SearchedLocations);
            throw new InvalidOperationException($"Could not find this view: {viewPath}. Searched locations:\n{searchedLocations}");
        }

        var view = result.View;

        var httpContext = new DefaultHttpContext();
        httpContext.RequestServices = _serviceProvider;

        var actionContext = new ActionContext(
                httpContext,
                httpContext.GetRouteData(),
                new ActionDescriptor()
            );

        using (var writer = new StringWriter())
        {
            var viewDataDict = new ViewDataDictionary(
                new EmptyModelMetadataProvider(),
                new ModelStateDictionary());

            viewDataDict.Model = model;

            var viewContext = new ViewContext(
                actionContext,
                view,
                viewDataDict,
                new TempDataDictionary(
                    httpContext.HttpContext,
                    _tempDataProvider
                ),
                writer,
                new HtmlHelperOptions { }
            );

            await view.RenderAsync(viewContext);

            return writer.ToString();
        }
    }
}

Now let's create a fake implementation of the IEmailPostman for the integration tests:

public class FakePostman : IMailPostman
{
    public Task SendEmail(Message message)
    {
        LastMessage = message;
        return Task.CompletedTask;
    }

    public Message LastMessage { get; set; }
}

Let's now register all of our services:

builder.Services.AddScoped<IMailerService, MailerService>();
builder.Services.AddScoped<IEmailRenderer, RazorEmailRenderer>();
// In your project, you have to register a real postman in your app
// and swap it our with this fake postman in the integration tests
// by creating a custom WebApplicationFactory. For more info see: 
// https://docs.microsoft.com/en-us/aspnet/core/test/integration-tests?view=aspnetcore-6.0#customize-webapplicationfactory
builder.Services.AddScoped<IMailPostman, FakePostman>();

We create a test project called IntegrationTests using XUnit and inside the test project we create a folder called Snapshots to store the expected html results.

Then we can create our snapshot tests:

public class EmailTests : IClassFixture<WebApplicationFactory<Program>>
{
    private readonly IEmailRenderer _renderer;
    private readonly string _folderPath;

    public EmailTests(WebApplicationFactory<Program> factory)
    {
        // Get the path for the snapshots folder
        var environment = factory.Services.GetRequiredService<IWebHostEnvironment>();
        _folderPath = Path.Combine(environment.ContentRootPath, "../IntegrationTests/Snapshots");

        var scope = factory.Services.CreateScope();
        _renderer = scope.ServiceProvider.GetRequiredService<IEmailRenderer>();
    }

    [Fact]
    public async Task CanSendWelcomeEmail()
    {
        var postman = new FakePostman();

        var mailService = new MailerService(_renderer, postman);

        await mailService.SendWelcomeEmail("person@example.com", new Welcome
        {
            FullName = "Example Person"
        });

        Assert.Equal("person@example.com", postman.LastMessage.Address);
        Assert.Equal("Welcome Example Person!", postman.LastMessage.Subject);

        await SaveToFile("Welcome.actual.html", postman.LastMessage.HtmlBody);
        var expectedBody = await File.ReadAllTextAsync(Path.Combine(_folderPath, "Welcome.expected.html"));

        Assert.Equal(Sanitize(postman.LastMessage.HtmlBody), Sanitize(expectedBody));
    }

    private string Sanitize(string text)
    {
        return text
            .Replace("\r\n", "\n")
            .Replace('\r', '\n');
    }

    private async Task SaveToFile(string name, string content)
    {
        var fullPath = Path.Combine(_folderPath, name);
        Directory.CreateDirectory(Path.GetDirectoryName(fullPath));
        await File.WriteAllTextAsync(fullPath, content);
    }
}

The first time your run CanSendWelcomeEmail it's going to fail because IntegrationTests/Snapshots/Welcome.expected.html doesn't exist. But it has created IntegrationTests/Snapshots/Welcome.actual.html. So go ahead and take a look at it, it should be something like this:

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
</head>
<body>
    <div>
        <h1>Welcome Example Person</h1>
        <p>Welcome to our wonderful service!</p>
    </div>
</body>
</html>

You can test out the html using something like PutsMail or Testi@. If you like the result, rename it the file to IntegrationTests/Snapshots/Welcome.expected.html.

Because we don't want git to track the actual results, you'll have to add this line to your .gitignore file:

*.actual.html

Now you have snapshot tests for your email templates, whenever you change them, you can easily see the results without having to manually click through the UI to send the emails. This will make your feedback loop much faster.

You can download the source code on GitHub.

Why my console app freezes randomly and I need to press a key for it to continue

Muhammad Azeez — Mon, 09 Aug 2021 08:37:19 +0000

In my current project, we have a console app that runs in the background and sends data to a frontend application. The app works great but sometimes it stops working and we need to press a key for it to continue. It turned out it was because of Windows Console's Quick Mode feature. When a user clicks on the console window, it hangs the app execution to allow the user to select the text..

Fortunately, you can easily disable Quick Edit for your app:

// http://msdn.microsoft.com/en-us/library/ms686033(VS.85).aspx
[DllImport("kernel32.dll")]
public static extern bool SetConsoleMode(IntPtr hConsoleHandle, uint dwMode);

private const uint ENABLE_EXTENDED_FLAGS = 0x0080;

private static void DisableQuickEditMode()
{
    // Disable QuickEdit Mode
    // Quick Edit mode freezes the app to let users select text.
    // We don't want that. We want the app to run smoothly in the background.
    // - https://stackoverflow.com/q/4453692
    // - https://stackoverflow.com/a/4453779
    // - https://stackoverflow.com/a/30517482

    IntPtr handle = Process.GetCurrentProcess().MainWindowHandle;
    SetConsoleMode(handle, ENABLE_EXTENDED_FLAGS);
}

public static void Main(string[] args)
{
    DisableQuickEditMode();
    // Do stuff
}

ASP.NET Core API Checklist

Muhammad Azeez — Sun, 13 Jun 2021 19:54:05 +0000

Note: I have now made a repo containing everything in this post: https://github.com/mhmd-azeez/awesome-asp-net-core-api

Building modern APIs require a lot of things to make them reliable, observable, and scalable. In no particular order, here are some of them that help you build better APIs:

1. Healthchecks

Healthchecks are important in making sure that we know when anything happens to our APIs. We can setup dashboards to monitor them and setup alerting to let us know when one of the APIs is unhealthy. They are also important when deploying your apps to kubernetes. Kubernetes can monitor healthchecks of your APIs and automatically try to kill the unhealthy instances and create new instances to take their place.

There are two kinds of healthchecks:

Liveliness: indicates if your API has crashed and must be restarted.
Readiness: indicates if your API has been intialized and is ready for processing requests. When launching a new instance of an API, it might need some time to intialize dependencies and load data to be ready.

More Information:

2. Logging

Logging provides valuable information when trying to debug unexpected behavior. But too much logging can significantly slow down our APIs. For that reason we can set the logging level to Warning in production and only lower it when we need to.

By default ASP.NET Core provides an abstraction layer for logging that supports Structured Logging.

A very popular logging library that many people use with ASP.NET Core is Serilog. Serilog has more Sinks than the default ASP.NET Core loggging abstraction and can easily be integrated with ASP.NET Core.

More information:

3. Observability

This includes a few things:

Performance monitoring (P99, P95, P50)
Metrics: Specific counters you or your business cares about
Tracing: Being able to see the entire lifecycle of each request from frontend to API to data source.

OpenTelemetry is an open standard for doing all of the above and ASP.NET Core supports it. The good news is, if you use OpenTelemetry, there is a rich ecosystem of tools and services that you can integrate with.

All of the major cloud providers have services that you can use to view the captured data.

4. Error reporting

There are tools specifically for capturing, storing and showing exceptions that have been raised in your APIs. They the exceptions by their type and location and show how many times they have occurred. Some tools include:

5. Status Endpoint

It's also good to have a status endpoint in your API that shows the name of the API, version of the API and when was it started. This can be used to create a dashboard showing all of the different services and their versions. Something like this:

using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;

using System;
using System.Diagnostics;

namespace MyCoolApi.Controllers
{
    public class StatusResponse
    {
        public string Name { get; set; }
        public string Version { get; set; }
        public DateTime StartTime { get; set; }
        public string Host { get; set; }
    }

    [ApiController]
    [Route("status")]
    public class StatusController
    {
        [HttpGet]
        public StatusResponse Get()
        {
            var version = typeof(Startup).Assembly.GetName().Version;

            return new StatusResponse
            {
                Name = "my-cool-api",
                Version = $"{version.Major}.{version.Minor}.{version.Build}",
                StartTime = Process.GetCurrentProcess().StartTime,
                Host = Environment.MachineName
            };
        }
    }
}

6. Http Resiliency

Although it's generally preferred for your APIs to communicate with other APIs using asynchronous messaging, sometimes you need to call other APIs using HTTP calls.

We need to bake in some level of resiliency by automatically retrying transient failures. This can easily be done by using something like Polly.

More information:

7. Statelessness and Containerization

Containers are a great way to make sure your APIs can be easily scaled out and deployed to multiple environments in a repeatable manner. However to make sure you can get the most out of containerization you should try to make sure your APIs are stateless.

Being stateless means that they don't hold any critical data in memory. For caching you can use a centralized caching technology like Redis instead. This way you can start as many instances as you need without worrying about having stale cached data or data duplication.

You must also be careful about background jobs. You must make sure the different instances don't process background jobs multiple times. And for message queues, you have to implement the Competing Consumers pattern which some message buses support natively.

More information:

9. OpenAPI Spec / Swagger

Documenting your APIs is very important. Swagger integrates with ASP.NET Core and automatically finds all of the routes (Controller actions) and shows them in a beautiful dashboard that you can customize.

More information:

10. Configuration and Options

ASP.NET Core has an extensible configuration mechanism. It can pull configurations from json files, environment variables and command line arguments. You can also provide custom sources for configuration. It also provide ways to easily fetch the configurations in a type-safe manner. it also provides an easy mechanism to validate the configuration sections.

More information:

11. Integration and Unit Tests

ASP.NET Core has made it easy to write Unit tests by making the whole framework DI friendly. It has also made Integration tests easy by WebApplicationFactory Having automated tests saves a lot of time and makes your APIs more robust. And when writing integration tests, try to use the same database technology that you use for production. If you're using Postgres in production, don't use Sqlite or In-Memory DB Providers for integration tests.

More information:

12. Build beautiful REST APIs

If you're building REST APIs, there are some conventions that make your APIs more pleasant and intuitive to use.

More information:

13. Authentication and Authorization

Authentication is the process of identifying a user and authorization is knowing and enforcing what each user can and can't do. The most popular standards for authentication is OpenIDConnect which is an authentication layer on top of OAuth 2.

There are some popular Identity Providers that you can easily integrate with your API:

And there are some open source Identity and Access Management servers that you can run on-prem:

And there are some libraries that you can use to build your own OIDC server:

More information

14. Security

14.1 CORS

Cross-Origin Resource Sharing allows frontends to call your API even if they are not on the same domain as the API. By default in ASP.NET Core CORS is disabled.

More information

MS Docs - Enable Cross-Origin Requests (CORS) in ASP.NET Core

14.2 HTTPS Enforcing

For this there are two scenarios:

You're using Kestrel on edge: Then you have to make sure it's only listening to and respond over HTTPS.
You've put ASP.NET Core behind a reverse proxy: Then you generally terminate HTTPS on the proxy and it's the proxy's job to enforce HTTPS.

More Information

MS Docs - Enforce HTTPS in ASP.NET Core

14.3 Cross-Site Scripting (XSS)

Cross-Site Scripting (XSS) is a security vulnerability which enables an attacker to place client side scripts (usually JavaScript) into web pagesMore Information. You can prevent it by sanitizing inputs from the user.

Prevent Cross-Site Scripting (XSS) in ASP.NET Core

15. API Versioning

Versioning your APIs allow you to maintain backward compatibility when making breaking changes. You can maintaing multiple versions at the same time and then deprecate versions over time.

More Information

Overview of API Versioning in ASP.NET Core 3.0+

String similarity search and fast LIKE operator using pg_trgm

Muhammad Azeez — Tue, 11 May 2021 20:58:37 +0000

SQL supports wildcard search on strings using LIKE operator which accepts % and _ wildcards. The problem with LIKE is it's not very fast if you have a lot of rows and the query is non-sargable. And in some cases you need to provide fuzzy search capabilities where the results don't have to exactly match the query.

PostgreSQL has the pg_trgm extension that solves both problems:

It has gin and gist indexes for speeding up LIKE and other string operators
It has similarity function and % operator for string similarity search using trigrams.

Let's assume we have this table:

CREATE TABLE persons (
    id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
    forenames varchar(100) NOT NULL,
    surname varchar(100) NOT NULL,
    forenames_normalized varchar(100) NOT NULL,
    surname_normalized varchar(100) NOT NULL,
    CONSTRAINT persons_pk PRIMARY KEY (id)
);

Note: Normalized columns are lowercase versions of the normal columns and special characters are removed. You can also remove character accents. This is to make the search experience better for the user as they don't have to type in the exact case and punctuations.

I inserted 10M rows of fake data generated by Bogus into the table. You can download the dump here.

If we run a LIKE query on it:

select * from persons p 
where surname_normalized like '%tche%' and forenames_normalized like '%nde%'

On my laptop it takes PostgreSQL about a second to return the results:

Gather  (cost=1000.00..142174.75 rows=10 width=30) (actual time=9.719..639.460 rows=75 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on persons p  (cost=0.00..141173.75 rows=4 width=30) (actual time=3.425..605.240 rows=25 loops=3)
        Filter: (((surname_normalized)::text ~~ '%tche%'::text) AND ((forenames_normalized)::text ~~ '%nde%'::text))
        Rows Removed by Filter: 3333308
Planning Time: 0.097 ms
Execution Time: 639.494 ms

It seems like all of rows rows are scanned in the table. To speed things up, first we need to enable the pg_trgm extension on the database:

create extension if not exists pg_trgm;

Then we can use the gin index on the normalized columns:

create index if not exists idx_gin_persons_on_names on persons using gin (forenames_normalized gin_trgm_ops, surname_normalized gin_trgm_ops)

Note: gin index and gin_trgm_ops operator are part of pg_trgm.

Adding the gin index took about a minute on my laptop for 10M rows.

Now let's see if the results have improved:

Bitmap Heap Scan on persons p  (cost=54.20..3692.46 rows=995 width=30) (actual time=4.011..4.066 rows=75 loops=1)
  Recheck Cond: (((forenames_normalized)::text ~~ '%nde%'::text) AND ((surname_normalized)::text ~~ '%tche%'::text))
  Heap Blocks: exact=75
  ->  Bitmap Index Scan on idx_gin_persons_on_names  (cost=0.00..53.95 rows=995 width=0) (actual time=3.999..3.999 rows=75 loops=1)
        Index Cond: (((forenames_normalized)::text ~~ '%nde%'::text) AND ((surname_normalized)::text ~~ '%tche%'::text))
Planning Time: 0.092 ms
Execution Time: 4.120 ms

Instead of 639.494 ms for execution time, now it only takes 4.1 ms! That's because instead of sequentially scanning all of the rows in the document, it scanned the gin index.

Great, now let's take a look at how to do fuzzy search:

Let's say we are trying to find someone with forename(s) of anderson and surname of mitchell:

select id, forenames, surname, ((similarity('mitchel', surname_normalized) + similarity('andersen', forenames_normalized)) / 2) as score from persons p
order by score desc
limit 10

This query takes about 58 seconds to complete. The similarity function is expensive, so we have to try not to use it as much as possible. For that, we can use the similarity operator (%) to filter out the rows that are below a certain threshold. By default the threshold is 30% similarity (0.3) but you can change that using set_limit. Now let's use it:

select id, forenames, surname, ((similarity('mitchel', surname_normalized) + similarity('andersen', forenames_normalized)) / 2) as score from persons p
where forenames_normalized % 'andersen' and surname_normalized % 'mitchel'
order by score desc
limit 10

Now it takes about 100ms on my laptop. A huge improvement over 58 seconds :)

Edge Cases

pg_trgm uses tri-grams for indexing. It means that each string is broken into all possible 3 letter components. For example mitchel's trigrams are: mit,itc,tch,che,hel and michelle's trigrams are: mic,ich,che,hel,ell,lle. They share 2 trigrams so the similarity of mitchel with michelle is 30%.

This approach is not useful for words that are less than 3 letters. As you can't form any trigrams. So this query:

select * from persons p 
where surname_normalized like '%he%' and forenames_normalized like '%de%'

Takes the same amount of time on both the indexed table and the non-indexed table because PostgreSQL does sequential scan for both of them:

Gather  (cost=1000.00..147095.90 rows=49229 width=30) (actual time=1.169..655.329 rows=21216 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on persons p  (cost=0.00..141173.00 rows=20512 width=30) (actual time=0.397..583.521 rows=7072 loops=3)
        Filter: (((surname_normalized)::text ~~ '%he%'::text) AND ((forenames_normalized)::text ~~ '%de%'::text))
        Rows Removed by Filter: 3326261
Planning Time: 0.105 ms
Execution Time: 655.974 ms

There can be cases where the index makes things slower. So please test it for your own use case and weight the trade-offs. Also keep in mind that inserts and updates take longer with the index.

Benchmarks

I wrote some very simple benchmarks using BenchmarkDotNet and here is the results:

// * Summary *

BenchmarkDotNet=v0.12.1, OS=Windows 10.0.19041.928 (2004/?/20H1)
Intel Core i7-8550U CPU 1.80GHz (Kaby Lake R), 1 CPU, 8 logical and 4 physical cores
.NET Core SDK=5.0.201
  [Host]     : .NET Core 5.0.4 (CoreCLR 5.0.421.11614, CoreFX 5.0.421.11614), X64 RyuJIT
  DefaultJob : .NET Core 5.0.4 (CoreCLR 5.0.421.11614, CoreFX 5.0.421.11614), X64 RyuJIT


|            Method    |         Mean |      Error |     StdDev |     Median |
|---------------------:|-------------:|-----------:|-----------:|-----------:|
|    LikeOnGinIndex    |     5.398 ms |  0.7167 ms |   2.113 ms |   4.170 ms |
|              Like    | 1,035.140 ms | 55.0098 ms | 158.716 ms | 991.495 ms |
| SimilarityOnGinIndex |   137.339 ms | 14.7610 ms |  43.523 ms | 114.342 ms |

Note: Please download the database dump and code on GitHub.

Backing up and restoring databases in Postgres

Muhammad Azeez — Thu, 01 Apr 2021 13:18:07 +0000

To get a dump of a database you can use pg_dump or pg_dumpall for dumping an entire cluster. It supports 4 formats:

Format	Description	Restore via
`plain`	Output a plain-text SQL script file (the default).	`psql`
`custom`	Output a custom-format archive suitable for input into pg_restore. Together with the directory output format, this is the most flexible output format in that it allows manual selection and reordering of archived items during restore. This format is also compressed by default.	`pg_restore`
`directory`	Output a directory-format archive suitable for input into pg_restore. This will create a directory with one file for each table and blob being dumped, plus a so-called Table of Contents file describing the dumped objects in a machine-readable format that pg_restore can read. A directory format archive can be manipulated with standard Unix tools; for example, files in an uncompressed archive can be compressed with the gzip tool. This format is compressed by default.	`pg_restore`
`tar`	Output a `tar`-format archive suitable for input into pg_restore. The tar format is compatible with the directory format: extracting a tar-format archive produces a valid directory-format archive. However, the tar format does not support compression. Also, when using tar format the relative order of table data items cannot be changed during restore.	`pg_restore`

How to backup a database

To create a dump of sample-db in custom format and save it to sample-db.dump:

pg_dump -U postgres --encoding utf8 -F c -f sample-db.dump sample-db

To create a dump of sample-db in plain format and save it to sample-db.sql:

pg_dump -U postgres --encoding utf8 -F p -f stoplist.sql stoplist

How to restore a database dump

First create an empty database to restore the dump to.

# We use template0 because it is empty and it doesn't conflict with the schemas and tables in the dump.
createdb -U postgres restored-db --template=template0

Restore custom, directory, and tar format dumps using pg_restore:

pg_restore -U postgres -d restored-db < ./sample-db.dump

Restore plain format dumps using psql:

psql -U postgres -d restored-db < ./sample-db.sql

Note: In Powershell the < operator doesn't work. So you'll have to use cmd on Windows.

Errors you might come across:

Corrupted dumps

pg_restore: [archiver] found unexpected block id (x) when reading data -- expected y

pg_restore: error unrecognized data block type

This might mean the dump is corrupted. One possible reason is the database contained Unicode data and the dump was not encoded in utf8. Use --encoding utf8 when running pg_dump to fix that.

Restoring plain format dumps using pg_restore:

pg_restore: [archiver] did not find magic string in file header

pg_restore: [archiver] input file does not appear to be a valid archive

This happens if you run pg_restore on a plain format dump. Use psql to restore it instead.

If you have any other tips/tricks, please write the down in the comments!

Why I love Powershell as a scripting language

Muhammad Azeez — Sun, 14 Mar 2021 21:23:46 +0000

Every once in a while, I have to write a script to automate a task. Maybe it's part of a CI/CD pipeline, or it's part of my dev workflow. My favorite language for writing scripts is PowerShell. Here is why:

1. Powershell has an object pipeline

When you pass data (or pipe them) from one command to the next, the data is treated as an object, not as a string. Which means the data can have different properties and it retains these properties.

This is very powerful and unlocks all kinds of composability scenarios. Commands don't have to be single purpose, they don't have to provide different switches to get different outputs. They give you everything and you can use only the properties you need.

2. You have the full power of .NET

Powershell is a first class programming language on .NET. You can do almost everything with Powershell. that you can do with C# or F#. You can even create GUIs if you want to. This means that you have access to the coherent and well designed Base Class Library of .NET as well as the entire gallery of nuget packages. It also has a lot of modules of it's own.

3. You can use Select/Where/GroupBy

Because Powershell is built on top of .NET, it has access to .NET's version of map/filter/reduce. They make scripts much more pleasant to write and read. However the names are a bit different from Javascript or other languages:

JS Name	Powershell
Filter	Where
Map	Select
Reduce	Measure / ForEach
Reduce	GroupBy

4. It's now cross-platform and open-source

Powershell Core is a cross-platform and open-source version of Powershell built on top of .NET Core maintained by Microsoft.

5. Working with Data is easy

You can easily work with Excel, XML and JSON data which makes working with APIs much easier.

Some cool examples

Here are some cool examples that demonstrate the points above:

Get top 3 largest files in a folder

Get-Childitem 'C:\Windows\System32' | 
    Where Length -gt (10MB) | # Only files that are greater than (-gt) 10 MB (MB is a constant in PS!)
    Sort -Descending -Property Length | # Sort files by their length ascending
    Select -First 3 Name, Length # Only select name and length properties (projection)

Output

Name                    Length
----                    ------
MRT.exe              131005360
nvcompiler.dll        40444864
WindowsCodecsRaw.dll  32612880

Calling a REST API

In this example we call a JSON REST API and print a property

$uri = 'https://cat-fact.herokuapp.com/facts/random' # random cat fact API
$fact = Invoke-RestMethod -Uri $uri
Write-Host $fact.text

Output:

Cats make about 100 different sounds. Dogs make only about 10.

Export all process information as an excel sheet

Get-Process | Select-Object Company, Name, Handles | Export-Excel

Result:

Company	Name	Handles
Microsoft Corporation	Calculator	537
Google LLC	chrome	449
...	...	...

Note: You'll have to install ImportExcel module for this example to work.

Importing data from excel

Consider this excel sheet:

City	Population
Erbil	1500000
Sulaymani	739182
Duhok	1293000
Kirkuk	1598000
Halabja	245700

# Get the average poluation of cities in Kurdistan:
Import-Excel 'F:\cities.xlsx' | Measure -Average -Property population | Select -Property Average

Note: You'll have to install ImportExcel module for this example to work.

Note 2: Data is from Wikipedia.

Ouput:

1075176.4

While my preferred scripting language is Powershell, I strongly believe everyone should use whatever tools/languages they are productive in. There is no best language. Everyone has different preferences and that's okay.

If you're using Powershell on Windows, I suggest you read this post to make your experience even better.

Background Job Scheduling using Hangfire

Muhammad Azeez — Sat, 05 Dec 2020 00:00:00 +0000

Note: This post is part of C# Advent Calendar 2020.

Sometimes you've tasks that would take too much time to do in the request-response model. For example sending multiple emails. So you have to send the emails asynchronously (i.e. in the background) and returning a response before the jobs are done. Or maybe you want to do some task periodically. For example generating a resource-intensive report at midnight or sending monthly invoices to customers. In short, you want to be able to schedule jobs and be able to track their progress and see their results.

You can of course use an in-memory model and use ASP.NET Core's IHostedService. But then if the application restarts all of the scheduled jobs would be gone. So you need to persist the jobs so that they are not lost. And Maybe you want to have multiple workers (consumers) processing the background jobs and the works might be on different machines. And what about retrying jobs when they fail?

Hangfire is a library that helps you do all of that and more very easily. It's very popular and well tested. It's also customizable and supports different kinds of storage mechanisms. And it supports different styles and techniques of background job processing.

How to use Hangfire

We are going to host hangfire in an ASP.NET Core app and use SQLite for storage. You can also use MSSQL, PostgreSQL, MySQL and other database engines and host it in a console app. The official guide is very good but here are the steps:

Add these Nuget packages*:

<PackageReference Include="Hangfire.Core" Version="1.7.18" />
<PackageReference Include="Hangfire.AspNetCore" Version="1.7.18" />
<PackageReference Include="Hangfire.Storage.SQLite" Version="0.2.4" />

Add Hangfire to Dependency Container:

// Add Hangfire services.
services.AddHangfire(configuration => configuration
    .SetDataCompatibilityLevel(CompatibilityLevel.Version_170)
    .UseSimpleAssemblyNameTypeSerializer()
    .UseRecommendedSerializerSettings()
    .UseSQLiteStorage());

// Add the processing server as IHostedService
services.AddHangfireServer();

Define Hangfire Dashboard route:

app.UseEndpoints(endpoints =>
{
    endpoints.MapRazorPages();
    endpoints.MapHangfireDashboard();
});

Now you can run the app and go to /hangfire and see it. But there are no jobs yet.

Enqueuing Jobs

If you want to enqueue a job in a fire-and-forget fashion (i.e. you don't want to wait for the result and you don't care much about when exactly it's going to happen), you do something like this:

backgroundJobs.Enqueue(() => Console.WriteLine("Hello world from Hangfire!"));

What if you wanted to something that might require some dependencies. For example you need a connection to the database or access to configurations? Well, then you can create a class for the job and get your dependencies through Dependency Injection:

public class SendEmailsJob
{
    public SendEmailsJob(IConfiguration configuration)
    {
        // You can ask for configuration or any other
        // dependency the job might need via Dependency Injection
    }

    [JobDisplayName("Send {0} emails")]
    [AutomaticRetry(Attempts = 3)]
    public async Task Execute(int count)
    {
        for (int i = 0; i < count; i++)
        {
            await Task.Delay(1000);
        }
    }
}

As you can see we have defined a method called Execute which accepts a parameter. We have also decorated it with some attributes to control how it's displayed in the Hangfire dashboard or how many times Hangfire would automatically retry the job if it fails.

And this is how you'd enqueue the job:

_backgroundJobClient.Enqueue<SendEmailsJob>(job => job.Execute(5));

Scheduling Jobs

If you want a job to be executed periodically on a defined schedule, you can write something like this:

Hangfire.RecurringJob.AddOrUpdate<SendEmailsJob>(job => job.Execute(10), cronExpression: "*/5 * * * *");

Note: RecurringJob is a static class.

The job can be a simple expression or a class. And you define the schedule using a Cron Expression. You can construct Cron expressions through sites like this and this.

Extensions

Hangfire has a lot of extensions, you can check them out here. But my favorite extension is this one. Which lets you output real time logs :)

Note: The console is not real-time if you use SQLite as the storage engine.

Source code

I have put a sample application here which contains the codes in this article and the rest of the ASP.NET Core app.

How to include folders as link recursively in csproj files

Muhammad Azeez — Tue, 13 Oct 2020 14:07:32 +0000

Suppose we have a folder called dependencies with this structure:

 - dependencies
   - child-folder
     - file.txt
     - file2.txt
   - file3.txt

If you want to add the entire folder as link and preserve the directory structure, you can use this inside your .csproj file:

<ItemGroup>
    <None Include="..\dependencies\**\*">
      <Link>dependencies\%(RecursiveDir)/%(FileName)%(Extension)</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
</ItemGroup>

This will include dependencies folder inside your project along with all of its child folder and files.

GitHub Party Trick

Muhammad Azeez — Tue, 25 Aug 2020 08:25:20 +0000

GitHub associates commits with people via email addresses. Each commit is signed with a commit and a name. So when you push a repo to GitHub, it looks for a user with that email address and associates the commit with that user. This allows some cool tricks!

You can commit as your favorite programmer in your repositories! Example:

In a repository, change your email address to their email address:

git config user.email "email@example.com"

Optional: Change your name to their name too!

git config user.name "Famous Person"

And now you can commit and push to GitHub. Now GitHub associates the commits with their account!

Note: The commits don't show up on their profile page. GitHub has this to say about showing commits on a user's profile:

Commits will appear on your contributions graph if they meet all of the following conditions:

The email address used for the commits is associated with your GitHub account.

The commits were made in a standalone repository, not a fork.

The commits were made:

In the repository's default branch (usually master)

In the gh-pages branch (for repositories with project sites)

For more information on project sites, see "About GitHub Pages."

In addition, at least one of the following must be true:

You are a collaborator on the repository or are a member of the organization that owns the repository.

You have forked the repository.

You have opened a pull request or issue in the repository.

You have starred the repository.

DISCLAIMER:

While we used this as a fun trick, it can be used for nefarious reasons. One way to workaround this issue is to cryptographically sign your commits. So that people can be sure that you have made the commits.