Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

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

0.00/5 (No votes)
23 Jan 2017 1  
Beginner guide on how to integrate MySQL database into ASP.NET Core project with Identity and host it on Ubuntu Server

ASP.Net Core on Ubuntu

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 the 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.

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.0-preview2.1-003177

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 paste the 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 are available to install by typing the 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 the 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 the following command:

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

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 the 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 the original project. To install Percona Server from the official package, here are 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 does 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.

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:

$ 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 is 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 for logging in your web app.

Using MySQL Database

Now that we successfully run our first created app, the 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 scale up your app or maybe deal with big data, it is recommended to use a database engine like MySQL.

The 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 project.json 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 parameter, especially user and password.

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

Next, open your Startup.cs. Then, find the 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 project.json. 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 a limitation for its primary/foreign key at maximum 767 bytes by default. Since the code is 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 the previous one produces an error message, the database is still created, it's just that the tables aren't complete. So before we use this new configuration, we need to drop the database. Just type the following command and answer with y if it asks for confirmation.

$ dotnet ef database drop

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

$ dotnet ef migrations remove

After the old migration files have been removed, we need to create a fresh migration file with the new configuration. Type the 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 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 the 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 do the steps above (except for the code, you can just upload it there).

You can get a 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.

The 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 the 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 one small project, so I can share my first time in this post.

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

References

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here