Part 3 - Connect the inventory service to Aurora

You just migrated the data from SQL Server to Aurora. However, the inventory service is still connecting to SQL Server. In this part of the lab, you will update the application to connect to the new Aurora database. You can read more about porting .NET Framework to .NET Core in this documentation.

Update appsettings.json

  1. Open the folder “SampleApp” on the desktop. Then open a solution file named “UnicornWorkshop.sln” in it.

    project sln

  2. On the right, expand the “InventoryService” project and double click the file named “appsettings.json.”

    appsettings.json

  3. In the “ConnectionStrings,” you will see the “RDSConnection” field has a value like “data source=db.unishop.local,1433…”. This connection string is for the MSSQL database. Let’s replace it with the connection string below, which is for the PostgreSQL database. Remember to REPLACE “YOUR_AURORA_ENDPOINT” with the value of “Aurora Database Writer Endpoint” in your cheat sheet. (If you forget to copy this value to your cheat sheet, please refer to this section.)

    "Host=YOUR_AURORA_ENDPOINT;Port=5432;Database=inventorydb;Username=postgres;Password=mYRyeT1IJamnWxAYowyR"
    

    change connection

    Storing passwords in source code is not good practice. AWS Secrets Manager helps you protect credentials needed to access your applications, services, and IT resources. The service enables you to easily rotate, manage, and retrieve database credentials, API keys, and other secrets throughout their lifecycle.

  4. We also need to specify the bucket to upload our images. In the Solution Explorer, go to “appsettings.json”, update the “S3BucketName” key value with the value of “Unishop UI S3 Bucket name” in your cheat sheet. The bucket name is in the format “unishopui-YOUR_ACCOUNT_ID”. (If you forget to copy this value to your cheat sheet, please refer to this section.)

    update appsetting

Install PostgresSQL Package

  1. Right-click on the “InventoryService” project and select “Manage NuGet Packages…”

    nuget

  2. In the “Browse” tab, search for Npgsql.EntityFrameworkCore.PostgreSQL. Select that package, and you will see detailed info about it on the right. Then, click the “Install” button.

    search package

  3. You may see a dialog box named “Preview Changes.” Please select “OK” for that. Then the package will be successfully installed for the project.

    Preview changes

Modify startup.cs

  1. In .NET Core, the database service is configured in Startup.cs, so you will change that configuration to using Aurora Postgres. Expand the “InventoryService” and double-click the file named “Startup.cs”

    Startup.cs

  2. Scroll down to the method named “ConfigureServices.” You will see a line which calls a method named “UseSqlServer” in a lambda function like this:

    services.AddDbContext<UnicornShop>(options => options.UseSqlServer(this.Configuration.GetConnectionString(ConnectionStringKey)));
    

    In that line, replace “UseSqlServer” with “UseNpgsql” to make the program connect to a postgres db:

    services.AddDbContext<UnicornShop>(options => options.UseNpgsql(this.Configuration.GetConnectionString(ConnectionStringKey)));
    

Modify UnicornShop.Context.cs

A PostgreSQL database contains one or more named schemas, which in turn contain tables. You must specify the schema that the tables are stored in.

  1. Expand the “InventoryService,” open “Data” folder, and double-click the file named “UnicornShop.Context.cs”

    Context.cs

  2. Scroll down to the method named OnModelCreating. This passes one argument “inventory” to the “ToTable” method to specify the inventory table. Add "unishop_dbo" (with quotes) as the second argument to specify the “unishop_dbo” schema in the Aurora database.

    unishop_dbo

Redepoloy the modified inventory service to ECS

  1. Rebuild the project before you redeploy the inventory service. Make sure the build succeed.

    rebuild

  2. Redeployment will be largely the same with the deployment process in the part 1 of Containerized Inventory Service. In the solution explorer, right-click the “InventoryService” and select “Publish Container to AWS…” In the wizard, simply click “Next” through the entire interaction since the settings have been saved.

    Choose publish

    After the redeployment, you may notice that the tasks you deployed in previous steps and created by auto-scaling are not instantly stopped. This is because the previous version will be retained for some time before being stopped in the process of updating.

Confirm Results

  1. You can confirm Unishop has connected to the Aurora database by uploading a unicorn. We provide a unicorn picture for you to upload. Please save the unicorn picture below.

    unicorn pic

  2. In your logged in Unishop user interface, click the “Upload” button on the top. Then, a dialog box will pop up.

    Open up pgAdmin 4

  3. Set the following values. Then click “Upload” button.

    • “Unicorn Name”: Enter AuroraUnicorn

    • “Unicorn Description”: Enter unicorn in aurora

    • “Unicorn Price”: Enter 0.01

    • “Unicorn Image”: Choose the unicorn picture you downloaded in step 1

    Open up pgAdmin 4

  4. Go to your Windows EC2 Instance and open up pgAdmin 4.

    Open up pgAdmin 4

    pgAdmin 4 View

  5. Click on “Servers”. Click “Object”, open the “Create” sidebar, and then click “Server”.

    Add New Server

  6. Under the General section, fill out the following:

    • Name: inventorydb

    General Settings

  7. Under the Connection section, fill out the following:

    • Host name/address: Enter the value of “Aurora Database Writer Endpoint” in your cheat sheet. (If you forget to copy this value to your cheat sheet, please refer to this section.)

    • Port: Enter 5432

    • Maintenance database: Enter postgres

    • Username: Enter postgres

    • Password: Enter mYRyeT1IJamnWxAYowyR

    Connection Settings

  8. After filling in the above settings, click “Save”.

  9. Now if you go to the left nav and open up the Servers list you will see your inventory database. Continue expanding the tree until you see the inventory table.

    InventDB List View

  10. Click on the “inventory” table. Then click “Object”, go to “View/Edit Data”, and click “All Rows”. You will now be able to see that all the data from the Microsoft SQL Server database has been migrated to the Aurora Postgres SQL database, along with the unicorn item you just uploaded in step 1.

    View All Rows

    Data Migration Confirmation

    Congratulations! You have completed the workshop! The unishop now has a way more modernized and easy-to-manage architecture than what we began with. If you feel like you haven’t had enough fun yet, we have prepared extra credit items for you to complete. Feel free to venture into the next section.