Running Your First ASP.NET Core Web App with MySQL on Ubuntu 16.04

Beginner guide on how to integrate MySQL database into ASP.NET Core project with Identity and host it on Ubuntu Server

Since Microsoft open sourcing their .NET Platform, which is called .NET Core, they stole my attention again. I always love using .NET Framework. I develop desktop apps using it. I develop mobile apps using Xamarin. But I rarely use its web platform for one simple reason, it requires Windows-based Server to run.

Now that Microsoft and community actively develop its framework for cross-platform environment, I stumble upon and see the latest stable build which is version 1.1. I read their blog and found out about news that ASP.NET Core 1.1 with Kestrel was ranked as the fastest mainstream fullstack web framework in the TechEmpower plaintext benchmark. That’s such a nice claim and gave me attention to try their product.

Before we start typing commands and coding, I’ll give you a big picture of what we’re going to achieve. We’re going to create and run an empty web application. Yes, empty, but that doesn’t mean there is nothing to see. We’re going to start using their scaffold project with the ability for user to register and login out of the box by using ASP.Net Identity. Also, we’re going to use MySQL-based database for storing data instead of default SQLite. The goal is to make it ready and run on development/production Ubuntu server before doing any development.

Now let’s begin this tutorial.

Update! This tutorial now is updated using the latest csproj project file. In the future, .NET Core won’t be using project.json anymore.

Installation

We’re going to use Ubuntu 16.04 Server OS for this tutorial. So every step here is executed from the server itself. If you don’t have any server to use, you can just use your current machine installed with Ubuntu. But if you refuse to use Ubuntu, you must search the equivalent commands on Google if it doesn’t work on your machine.

Installing .NET Core

First thing we should install is .NET Core runtime itself. It won’t be just a runtime, but also a compiler. Just copy paste these commands to your terminal. This is the official installation step form their website.

$ sudo sh -c 'echo "deb [arch=amd64] https://apt-mo.trafficmanager.net/repos/dotnet-release/ xenial main" > /etc/apt/sources.list.d/dotnetdev.list'  
$ sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys 417A0893  
$ sudo apt-get update  
$ sudo apt-get install dotnet-dev-1.0.1

Now after it’s installed, try check it by typing which dotnet. If it show a path to dotnet path, then installation is success.

Installing Node with nvm

Wait, what? Installing Node? What’s the deal with it?

Don’t worry, we’re not going to use Node for development. Instead, we’re going to need some packages from it to speed up our development time.

To prevent you typing sudo command to install Node packages, let’s not install it via package manager. We’re going to use nvm, Node Version Manager. As the name suggests, it can install Node versions side by side. To install nvm, copy and paster following command to your terminal:

$ curl -o- https://raw.githubusercontent.com/creationix/nvm/v0.33.0/install.sh | bash

After it’s installed, let’s check which Node versions that available to install by typing following command:

$ nvm ls-remote

Now we’re going to install Node to our system. It is recommended to install latest LTS version of it. Until this tutorial is written, latest LTS version is 6.9.4. So we’re going to install it by typing following command:

$ nvm install v6.9.4

After Node is installed, let’s check it by typing node -v. If it shows you the correct version then we’re done with installing Node.

Installing yeoman aspnet generator and bower

As mentioned above, we’re going to need some packages from npm to speed up our development. We’re going to need ASP.Net project generator, generated with yeoman. We also need bower to install CSS framework and stuff like Bootstrap and jQuery. To install them all, just type following command:

$ npm install -g bower  
$ npm install -g yo  
$ npm install -g generator-aspnet

Installing Nginx

ASP.Net Core already has its own built-in web server called Kestrel. But it’s not recommended to expose our web app to public by just using it. Instead, we’re going to use nginx to expose our web app to the public by using its reverse proxy feature. That’s why we’re going to install nginx too. Here is the command to install nginx:

$ sudo apt-get install nginx

Installing Percona, better MySQL fork

Lastly, we’re going to install MySQL server. But instead of using MySQL server from Oracle, we’re going to use Percona server, a better MySQL fork with close compatibility with MySQL. You can use MariaDB though, but I prefer to choose a fork that has close compatibility with original project. To install Percona Server from official package, here is the commands you need to type:

$ wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb  
$ dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb  
$ sudo apt-get update  
$ sudo apt-get install percona-server-server-5.7  

Just follow their instructions for installation and configuration then you’re good to go.

Creating a New Web App

This step still not require any coding. Let us create our first ASP.Net Core web app. With yeoman, it’s simpler than ever to generate a new web app with user login and register feature.

Update! Also, creating a new project with yeoman isn’t necessary anymore. The latest dotnet tooling shipped with templating engine.

Let’s call our web app as MyFirstApp. We’re going to use Bootstrap as our UI Framework. To generate it, just type this command:

dotnet new mvc --name MyFirstApp --auth Individual --framework netcoreapp1.0

Or if you prefer using yeoman generator, type following command:

yo aspnet web MyFirstApp bootstrap

Now after it’s generated, move to MyFirstApp directory. Before we run the web app, we’re going to need restore all NuGet packages used by our app. Then, we need to create a new database from EF model. Finally, we can compile and run this web app. Those steps can be executed by typing this command on your terminal:

cd "MyFirstApp"  
dotnet restore  
dotnet ef database update  
dotnet run

After compilation successful, you can see your web app works by accessing http://localhost:5000 on your web browser. Try it yourself, you can add a new user and use it to logging it your web app.

Using MySQL Database

Now that we successfully run our first created app, next thing to do is change it MySQL backend. The default database used by generated project is SQLite. SQLite is actually good enough for medium traffic website. But in case you plan to scaling up your app or maybe dealing with big data, it is recommended to use database engine like MySQL.

Bad news is, the official Entity Framework from MySQL is not ready yet (still in pre-release). I’ve got bad experience trying it. But luckily, there is a stable connector developed by community of Pomelo Foundation.

To use this connector, first you must add Pomelo.EntityFrameworkCore.MySql to your MyFirstApp.csproj under ItemGroup with a bunch of PackageReference lines. The file should look like this:

<ItemGroup>  
  ...  
  ...  
  <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="1.1.0" />  
</ItemGroup>  

If you’re still using the old project.json file, put it under dependencies. The file should look like this:

"dependencies": {  
    ...  
    "Pomelo.EntityFrameworkCore.MySql": "1.1.0-*"  
  },  

Then we’re going to need to add our MySQL connection string inside appsettings.json under ConnectionStrings. The connection string format is like usual. Don’t forget to change each parameters, especially user and password.

"ConnectionStrings": {  
    ...  
    "MySQLConnection": "Server=localhost;User Id=root;Password=yourpassword;Database=MyFirstDb"  
  },  

Next open your Startup.cs. Then find following lines under ConfigureServices function.

// Add framework services.  
services.AddDbContext<ApplicationDbContext>(options =>  
    options.UseSqlite(Configuration.GetConnectionString("DefaultConnection")));  

Replace those lines with our new MySQL connection string. It should look like this:

// Add framework services.  
services.AddDbContext<ApplicationDbContext>(options =>  
    options.UseMySql(Configuration.GetConnectionString("MySQLConnection")));  

Now that we already change our code, let’s give it a test. Don’t forget to type dotnet restore every time you add new library in MyFirstApp.csproj. So after we configure our code to use MySQL database, let’s create the database by typing dotnet ef database update and see what happens.

Specified key was too long; max key length is 767 bytes

Yes, you’ll mostly get an error message above. So what’s the deal?

It turns out InnoDB has limitation for its primary/foreign key at maximum 767 bytes by default. Since the code using utf-8 by default it means each character is 3 bytes long. Let’s take a look at your file Data/Migrations/ApplicationDbContextModelSnapshot.cs. By quick reading you can see that all primary keys are using 256 characters as MaxLength, which mean it took 256 * 3 = 768 bytes! 1 byte longer than maximum key allowed by InnoDB.

To solve this issue, simply change all MaxLength property of primary/foreign keys to 255 instead of 256. To do that we need to edit Data/ApplicationDbContext.cs file. Find OnModelCreating function and replace it with this snippet:

protected override void OnModelCreating(ModelBuilder builder)  
{  
    base.OnModelCreating(builder);  
  
    builder.Entity<ApplicationUser>(entity => entity.Property(m => m.Id)  
        .HasMaxLength(255));  
    builder.Entity<ApplicationUser>(entity => entity.Property(m => m.NormalizedEmail)  
        .HasMaxLength(255));  
    builder.Entity<ApplicationUser>(entity => entity.Property(m => m.NormalizedUserName)  
        .HasMaxLength(255));  
  
    builder.Entity<IdentityRole>(entity => entity.Property(m => m.Id)  
        .HasMaxLength(255));  
    builder.Entity<IdentityRole>(entity => entity.Property(m => m.NormalizedName)  
        .HasMaxLength(255));  
  
    builder.Entity<IdentityUserLogin<string>>(entity => entity.Property(m => m.LoginProvider)  
        .HasMaxLength(255));  
    builder.Entity<IdentityUserLogin<string>>(entity => entity.Property(m => m.ProviderKey)  
        .HasMaxLength(255));  
    builder.Entity<IdentityUserLogin<string>>(entity => entity.Property(m => m.UserId)  
        .HasMaxLength(255));  

    builder.Entity<IdentityUserRole<string>>(entity => entity.Property(m => m.UserId)  
        .HasMaxLength(255));  
    builder.Entity<IdentityUserRole<string>>(entity => entity.Property(m => m.RoleId)  
        .HasMaxLength(255));  

    builder.Entity<IdentityUserToken<string>>(entity => entity.Property(m => m.UserId)  
        .HasMaxLength(255));  
    builder.Entity<IdentityUserToken<string>>(entity => entity.Property(m => m.LoginProvider)  
        .HasMaxLength(255));  
    builder.Entity<IdentityUserToken<string>>(entity => entity.Property(m => m.Name)  
        .HasMaxLength(255));  

    builder.Entity<IdentityUserClaim<string>>(entity => entity.Property(m => m.Id)  
        .HasMaxLength(255));  
    builder.Entity<IdentityUserClaim<string>>(entity => entity.Property(m => m.UserId)  
        .HasMaxLength(255));  
    builder.Entity<IdentityRoleClaim<string>>(entity => entity.Property(m => m.Id)  
        .HasMaxLength(255));  
    builder.Entity<IdentityRoleClaim<string>>(entity => entity.Property(m => m.RoleId)  
        .HasMaxLength(255));  
}

Even though previous produces error message, the database is still created, it just the tables aren’t complete. So before we use this new configuration, we need to drop the database. Just type following command and answer with y if it asks for confirmation.

$ dotnet ef database drop

Next step is to remove the old migration and snapshot generated by yeoman simply by typing following command:

$ dotnet ef migrations remove

After the old migration files removed, we need to create a fresh migration file with the new configuration. Type following command and it will produce new migration files and snapshot under Migrations directory.

$ dotnet ef migrations add "Initial"

Finally, we need to update the database again by typing dotnet ef database update again. Then if nothing nothing is wrong as it should be, our MySQL database is ready and you can run your web app again.

Deploying to Ubuntu Server 16.04 using Nginx

Finally, it’s time to expose our web app to the public. If you did steps above on your public server then good. If not, then you need to rent a server. I personally like to use DigitalOcean. Its pricing is simple and affordable. All you need is start a droplet, connect to it via ssh, and did steps above (except for the code, you can just upload it there).

You can get FREE $ 10 credit if you sign up to DigitalOcean using my referral link here.

Now that your server is ready and running, we need to edit our nginx configuration. To do that we need to update /etc/nginx/sites-available/default with reverse proxy setting. Open it using your favorite text editor.

$ sudo -E vim /etc/nginx/sites-available/default

The following snippet is basic configuration to use nginx reverse proxy. It’ll forward incoming traffics from port 80 to port 5000, which kestrel use to host your ASP.Net Core app. Don’t forget to change server_name with your own domain.

server {  
    listen 80;  
    server_name your.domain.com;  
    location / {  
        proxy_pass http://localhost:5000;  
        proxy_http_version 1.1;  
        proxy_set_header Upgrade $http_upgrade;  
        proxy_set_header Connection keep-alive;  
        proxy_set_header Host $host;  
        proxy_cache_bypass $http_upgrade;  
    }  
}  

Save the file and check it by typing sudo nginx -t. If everything is okay, it’ll show messages like this:

nginx: the configuration file /etc/nginx/nginx.conf syntax is ok  
nginx: configuration file /etc/nginx/nginx.conf test is successful

Finally we need to reload nginx to use the new configuration by typing sudo nginx -s reload.

Next step is on how to run your app. It splits into two categories, for development mode and production mode.

Development mode

For development mode, I personally like to run it under tmux. It is a terminal multiplexer which mean when you execute long awaited process, it won’t be killed even if you’re logged out from your server, because it’s running in background.

If tmux isn’t installed on your system, you can install by running sudo apt-get install tmux.

After it’s installed, you can run it by just typing tmux on terminal. A new console will show up and a green color appear at the bottom, which mean you’re under tmux environment. Inside tmux, move to your MyFirstApp directory and run it by typing dotnet run.

Now close tmux by using keyboard shortcut ctrl + b, d. This will close your green bar terminal window, but the process under it is still running. Now you can go to your.domain.com with your browser to check your app.

If you want to access that tmux window again, just type tmux attach form your terminal.

Production mode

To run your app in production mode, it must be published first in Release mode, not in Debug mode. From your terminal, you can publish it by typing this command:

$ dotnet publish -c Release

It should be published at this path MyFirstApp/bin/Release/netcoreapp1.0/publish. Now we need to move it to /var/www/. Here is an example:

sudo mv ~/MyFirstApp/bin/Release/netcoreapp1.0/publish/ /var/www/MyFirstApp

Now that the published directory moved, we need to create a service so systemd will execute it on reboot. To do that, we’re going to create a new service file named kestrel-myfirstapp.service.

sudo -E vim /etc/systemd/system/kestrel-myfirstapp.service

After you open the file, add following configuration. Don’t forget to change the path if you moved the published place to another directory.

[Unit]  
Description=MyFirstApp Kestrel Service  
  
[Service]  
WorkingDirectory=/var/www/MyFirstApp  
ExecStart=/usr/bin/dotnet /var/www/MyFirstApp/MyFirstApp.dll  
Restart=always  
RestartSec=10 # Restart service after 10 seconds if dotnet service crashes  
SyslogIdentifier=dotnet-myfirstapp  
User=www-data  
Environment=ASPNETCORE_ENVIRONMENT=Production  
  
[Install]  
WantedBy=multi-user.target  

Save the file and we need to enable this service by typing systemctl enable kestrel-myfirstapp.service command. After you rebooted, your app will be running.

If you want to start the app directly without reboot, just type systemctl start kestrel-myfirstapp.service.

Summary

Even though .NET Core is now version 1.1 and production ready, it’s still painful to set up. Still a lot of libraries on NuGet not compatible with .NET Core. Also I don’t think it’s ready for high traffic website for now.

But luckily I’ve got a chance to try this on small project so I can share my first time in this post.

I’ll make more updates if something good (or bad) came up. Anyway, thanks for stopping by. Hope this small tutorial useful for you.

References