Build a dotnet core 2.1 MVC app, using PostgreSQL, for Raspberry Pi

Published in

NordicDev

10 min readJun 6, 2018

Dotnet core is cross platform, open source, and does your taxes. With the release of 2.1, Microsoft added all kinds of goodies — you can read the full details over here.

I just read over that link, and I guess dotnet core doesn’t do your taxes. Yet. Maybe that’s on tap for the 3.0 release. Don’t ditch your accountant just yet.

Anyway. The Raspberry Pi is a great little computer. For $35, you get a quad-core 1.4GHz ARM processor, 1GB RAM, baked-in Bluetooth and Wifi, 4 USB ports, an ethernet port, HDMI, audio out, and a boatload of GPIO pins.

While it’s not open source, Microsoft has ported the venerable SQL Server to Linux. They even provide a docker container to make running it super simple. While this is truly awesome, in this post we’re going to focus on using PostgreSQL for this app.

In case you’re unfamiliar with PostgreSQL, it’s a top notch database system. It’s also open source, and cross platform! It runs on Windows, Linux, MacOS, x86, ARM, and probably some more stuff that I don’t even know about. You can read more about it over here.

The folks over at Npgsql have built a great, open source, EF Core provider for PostgreSQL. They’ve really done a great job with this. It’s fast, simple, and drops right in with no issues. You can read all the details over here.

For this process, I’m going to assume you don’t have PostgreSQL installed yet on either your development machine or the target RPi. If you already have it installed, skip over the install bits. They’re not long. I assume that you’re using a Debian-based distro of Linux on your Raspberry Pi. Personally, I opt for Raspbian. The process for this project is like this

1. Install PostgreSQL on the Windows dev machine

Okay, enough fluff. Let’s get started. While we’re going to deploy this app to the RPi3, we’re going to build it on Windows. Let’s install PostgreSQL on our Windows dev box. Head on over to https://www.postgresql.org/ and download and install the Windows version (https://www.postgresql.org/download/windows/). I’m using version 10. Make note of your superuser password.

2. Create new PostgreSQL user, database, grant all privs to on the new DB to the new user

Let’s create a new user and database for our fancy application that we’re going to build. Open up a command prompt, and change directory (cd) to your PostgreSQL install. I’m using 64-bit version 10, so my install directory is `C:\Program Files\PostgreSQL\10\bin`.

cd C:\Program Files\PostgreSQL\10\bin

The superuser account on PostgreSQL is called postgres. So, from the command prompt, type

psql -U postgres

You’ll be prompted for your superuser password you entered during the install. Enter that, and we’re off to the races.

Let’s create our new user. I’m going to create a user named dotnetCorePostgresPi, with a password of `2mysjsTao3Kt9ZeAWVuUzRtvbWeDchMYn4wBafpX8d7PuoZWtB`. Here’s what the command looks like:

CREATE USER dotnetCorePostgresPi WITH PASSWORD '2mysjsTao3Kt9ZeAWVuUzRtvbWeDchMYn4wBafpX8d7PuoZWtB';

Swell. Let’s create the database. I’m going to use the name dotnetCorePostgresPiDb as the database name. From the console:

CREATE DATABASE dotnetCorePostgresPiDb;

Yay, we have a database. Now let’s grant All The Permissions! on the database to our user:

GRANT ALL PRIVILEGES ON DATABASE dotnetCorePostgresPiDb TO dotnetCorePostgresPi;

Bam. We’re all set. Let’s bail on this PostgreSQL console:

\q

3. Build the application

Now that we have our database all set, let’s move on to creating our application. You can use Visual Studio or the command line + whatever text editor/IDE you like for this part. I’m going to use Visual Studio. While I like VS Code, it doesn’t “feel” right to me when I’m writing C#. Very scientific, I know. So, fire up VS. We’re going to build a new project for this, so `File` -> `New` -> `Project`. Select `ASP.NET Core Web Application`. I’m naming this `dotnetCorePostgresPi`.

To quickly use PostgreSQL with EFCore and Npgsql EF Core, we’re going to use Identity and a local store for the user accounts. On the next screen, click the `Change Authentication` button, select `Individual User Accounts`, make sure `Store user accounts in-app` is selected on the drop down, and click `OK`.

Click the next `OK` button, and let Visual Studio do its magic and scaffold our shiny new app.

Once VS is done, open up the Package Manager Console and install the Npgsql EF Core Provider:

Install-Package Npgsql.EntityFrameworkCore.PostgreSQL

That will install the needed package to use PostgreSQL. Let’s see what that looks like in our project file — right click on the dotnetCorePostgresPi project and select Edit dotnetCorePostgresPi.csproj

We should have a fun new line in here now

<PackageReference Include=”Npgsql.EntityFrameworkCore.PostgreSQL” Version=”2.1.0" />

Moving on! We need to change Startup.cs to wire up the new provider. Open up Startup.cs. We need to edit the `ConfigureServices` method. We need to switch from using SQL Server to PostgreSQL. Find this block:

services.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer( Configuration.GetConnectionString("DefaultConnection")));

Update it to use the new provider we just installed:

services.AddDbContext<ApplicationDbContext>(options => options.UseNpgsql( Configuration.GetConnectionString("DefaultConnection")));

In case you missed it — we’re swapping out UseSqlServer for UseNpgsql. That’s it for this file!

Next we need to update our connection string. Since we’re a couple of top-notch, forward-thinking developers, we’re going to take advantage of the user-secrets functionality in our fancy new dotnet core app. This lets us keep an appsettings.json file unique to our machine, while never commiting any secrets to code. Eventually, maybe we’ll wire this app in with a nice CI/CD pipeline, but that’s a project for another day. Let’s keep rolling, with a nod to our CI/CD empowered future.

Open up the appsettings.json file in your project. We’re going to replace the SQL Server-oriented connection string with a placeholder. The default connection string probably looks something like this:

"DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=aspnet-dotnetCorePostgresPi-502D4D29-A36E-41DF-A9CC-A12D423904A6;Trusted_Connection=True;MultipleActiveResultSets=true"

Replace that, with this:

"DefaultConnection": "Server=DBHOST;Database=DBNAME;Username=DBUSER;Password=DBPASSWORD"

Unless we have a pretty strange computer, this connection string won’t connect to anything. Time to fire up our user-secrets. Right click on the `dotnetCorePostgresPi`file in the Solution Explorer, and select Manage User Secrets.

If you’re unfamiliar with how user-secrets work, this creates a file on your local machine, in the %appdata%\Microsoft\UserSecrets\<appName> directory. When the app loads up, it will load the settings from appsettings.json file first, and then your user-secrets file. For any settings that exist in both files, the user-secrets file takes priority.

Replace the entire contents of secrets.json with the following:

{"ConnectionStrings": {"DefaultConnection": "Server=localhost;Database=dotnetCorePostgresPiDb;Username=dotnetCorePostgresPi;Password=2mysjsTao3Kt9ZeAWVuUzRtvbWeDchMYn4wBafpX8d7PuoZWtB"}}

This assumes you’ve used the same username, database name, and password I used above.

4. Enable auto migrations

Open up Program.cs. We’re going to make quite a few changes here. The original class should look like this:

public class Program{public static void Main(string[] args){CreateWebHostBuilder(args).Build().Run();}public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>WebHost.CreateDefaultBuilder(args).UseStartup<Startup>();}

For the sake of brevity, you can paste in the following code to replace to above class:

public class Program{public static int Main(string[] args){try{var host = BuildWebHost(args);using (var scope = host.Services.CreateScope()){var services = scope.ServiceProvider;try{InitializeDatabase(services);}catch (Exception ex){// something bad happened}}host.Run();return 0;}catch (Exception ex){return 1;}}public static IWebHost BuildWebHost(string[] args) =>WebHost.CreateDefaultBuilder(args).UseStartup<Startup>()
.UseUrls("http://*:5000").Build();private static void InitializeDatabase(IServiceProvider services){using (var serviceScope = services.GetService<IServiceScopeFactory>().CreateScope()){var context = serviceScope.ServiceProvider.GetRequiredService<ApplicationDbContext>();context.Database.Migrate();}}}

What we’re doing here is re-wiring the startup process. When the app loads, we’ll call our fancy new private method InitializeDatabase method, and apply any migrations that are outstanding. Feel free to skip this step if you prefer to apply your migrations manually.

That’s it! Now, when your app runs, it will apply any migrations that have not been applied yet.

5. Test the app locally

Time to test. Press that famous F5 button, and let’s create a new account. The migration should be applied, and our new account should be created. To verify, you can check the database using any of a number of tools. pgAdmin 4 is a great tool for hooking up to a PostgreSQL database and seeing what the data looks like. I’m also a big fan of JetBrain DataGrip tool. It’s not free, but it’s another great option.

Okay, so now we have our app, it’s working locally. Before we package and deploy it to the RPi, we need to get the RPi ready.

6. Install dotnet core 2.1 on the Rpi

Installing dotnet core 2.1 on the RPi is a breeze. While I’d love to keep you here and walk you through the process, Microsoft has already done a top-notch job. Head on over here (https://www.microsoft.com/net/download/linux-package-manager/debian9/sdk-2.1.300) to get core installed on your RPi.

7. Install PostgreSQL on the Rpi

Now that we have dotnet core 2.1 installed, let’s install PostgreSQL.

# apt-get install postgresql postgresql-client

That was easy.

8. Create new PostgreSQL user, database, grant all privs on the new DB to the new user, on the RPi

Let’s create a new user, new database, assign all privs to on the new database to the new user. All on the RPi.

# su - postgres
$ psql
psql (9.6.7)
Type "help" for help.postgres=#

Note: You may notice that I’m using PostgreSQL 9.6.7 on my RPi, and 10 on my Windows machine. That’s okay. Let’s keep rolling.

We can handle the user and database creation with the same commands. To keep things simple, let’s use the same username and database name. Since we’re forward-thinking and top-notch developers, let’s use a new password.

postgres=# CREATE USER dotnetCorePostgresPi WITH PASSWORD 'GgsUrRnxduhtJr6VvhZrgTj3ZKsKRMohyze2x9LAUhsA8mM6pi';
CREATE ROLE
postgres=# CREATE DATABASE dotnetCorePostgresPiDb;
CREATE DATABASE
postgres=# GRANT ALL PRIVILEGES ON DATABASE dotnetCorePostgresPiDb TO dotnetCorePostgresPi;
GRANT
postgres=# \q

Great. We’re moving right along. The next step is going to be building the app to run on the RPi.

Since the RPi is running Linux on an ARM processor, we can’t just copy over the binaries to the RPi and run. We’ll need to head back to our Windows machine, and open up the command prompt.

Change the directory to the root folder of your project. For me, that’s `c:\Code\sandbox\dotnetCorePostgresPi\dotnetCorePostgresPi`.

cd c:\Code\sandbox\dotnetCorePostgresPi\dotnetCorePostgresPi

9. Publish the app for the RPi

Now we can build for the RPi. Remember — it’s running Linux on an ARM chip. Publish with a target of linux-arm:

dotnet publish -o ../publish-rpi -f netcoreapp2.1 --configuration "Release" -r linux-arm

The above command will build a release version of the app, using the dotnet core 2.1 framework, and put the artifacts in the `c:\Code\sandbox\dotnetCorePostgresPi\publish-rpi` directory. Easy! The -r linux-arm flag is what tells dotnet to build for Linux on an ARM chip.

10. Deploy to the RPi

Alright. We have our build for the RPi. Let’s copy that over to the RPi!

There are a wide variety of ways to copy the files over. FileZilla is great. I personally prefer to use the pscp.exe program that comes with a PuTTY install.

We can copy the entire app over with a single command. From your Windows machine, go back to the command prompt and type:

"C:\Program Files (x86)\PuTTY\pscp.exe" -r "c:\Code\sandbox\dotnetCorePostgresPi\publish-rpi" pi@<IP Address of your RPi>:/home/pi

You may need to adjust for your publish path, and pscp.exe path. You’ll be prompted for the password to your pi, if you haven’t setup passwordless login yet. Enter your password, and your project will be copied over to the RPi, in the `/home/pi/publish_rpi` directory.

Let’s switch over to the RPi. Before running the app, we need to do a few prep steps. First off, make the app executable:

$ chmod +x /home/pi/publish-rpi/dotnetCorePostgresPi

Next step: since we’re super top-notch devs, we set a seperate, secure, password for our RPi database user. We need to update /home/pi/publish-rpi/appsettings.json with our RPi database password. You can use whatever editor you like — I prefer vim.

$ vim /home/pi/publish-rpi/appsettings.json

Edit the password, save, and close vim. Let’s keep moving.

Now the fun part — run the app:

$ /home/pi/publish-rpi/dotnetCorePostgresPi

Look at that output!

$ /home/pi/publish-rpi/dotnetCorePostgresPi
: Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager[0]
      User profile is available. Using '/home/pi/.aspnet/DataProtection-Keys' as key repository; keys will not be encrypted at rest.
Hosting environment: Production
Content root path: /home/pi
Now listening on: http://[::]:5000
Application started. Press Ctrl+C to shut down.

To verify that everything is working, you can fire up your favorite browser, and navigate to `http://<ip of your RPi>:5000`. The app should load as expected. Create an account, and we’re in business!

We covered a lot of ground here. To recap, we:

Created an ASP.NET Core 2.1 app
Wired the app up to use PostgreSQL
Published the app, targeting the linux-arm platform
Deployed the app to a Raspberry Pi

11. Future stuff

In the future, we’ll be making some updates. We’ll add in CI/CD using GitLab, and a linux build server. In the meantime, play around, and have fun running dotnet core on a Raspberry Pi!