Create a Relational Database using ASP.NET Core (MVC) | Dear Coder

Dear Coder,

I made a thing and I want to share it with you.

A few months ago, I started an Instagram page where I share some comics that I draw. And I thought, "Wouldn't it be fun to make my own database where I can control the flow of content without my hack-job humor getting swallowed by the infinite-Insta-algorithm?"

So I decided to create a project that allows me to:

1. Upload my web comics to a database.
2. Sort and display them by genre, date, and popularity.
3. Allow verified users to upload their own content safely and securely to my database with the same capabilities.

If you're coding along, you need a couple of prerequisites:

1. Microsoft Studio 2019
2. PostgresSQL v13
3. PGAdmin 4 v5.2

Create a new ASP.NET Core MVC App in Visual Studio

Let's start with a brand new project in Visual Studio using the ASP.Net Core MVC Web App template.

Under Additional Information, I select "Individual Accounts" as my Authentication Type. I also want to enable Razor runtime compilation.

Clear Migrations Folder and Download Requisite NuGet Packages

I want to initiate a new migration when I'm ready to set up my database, so I need to delete the Migrations folder in my Solutions Explorer that comes boilerplate with this template.

Once that's done, I right-click the top level of my project to manage NuGet packages. This is where I bring in all the sweet sauce that allows me to build my database.

I make sure I've downloaded these dependencies before writing any code.

I open the Startup.cs file at the bottom of my Solution Explorer to add a Using statement to bring in my Npgsql. Then I delete the boilerplate options.Use text and insert options.UseNpgsql.

First Data Migration

Now that I've brought in my NpgSQL, I can initialize my Identity migration to create the data structure that will feed into the database.

I open the Package Manager Console by clicking Tools > NuGet Package Manager > Package Manager Console. Then I initialize the first migration with the following code:

Create Postgres Database

I'm almost ready to create my database, but first I need to change my connection string to wire Visual Studio to my postgres server.

I delete the default ConnenctionStrings and enter this string of code in my appsettings.json file near the bottom of my Solutions Explorer with the name of my project as the database and my database password as the password.

"ConnectionStrings": { "DefaultConnection": "Server=localhost; Port=5432; Database=[PROJECT-NAME]; User Id=postgres; Password=********" },

Finally, I open my Package Manager Console again, this time execute the command, update-database.

NOTE: I encountered an error message after the build succeeded. This was rectified after I opened pgAdmin, logged in to my server, and refreshed the server.

Check the Database

As a final check, I enter pgAdmin and check that my ToonSpace server is live and working.

And that's all there is to setting up a simple server using postgresSQL in an MVC template.

In this letter, I've only shared the beginning--I'm a long way off from having a functional web cartoon app, but my fingers are fading, and let's be real: our squirrel brains don't have the attention span for a 40 minute read.

So until next time, dearest Coder, godspeed in your keystrokes.

Clickity Clacks,

Kasey