Connecting .NET API To ApsaraDB With postgreSQL

This is a guide to creating a .NET API connected to ApsaraDB with PostgreSQL

1. Create the API using the command "dotnet new webapi"

2. Add the nugets for Entity framework and PostgreSQL

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

3. Create the class TodoItem, TodoItem.cs 

namespace ApiPostgre;
public class TodoItem
{
    public int Id { get; set; }
    public string Title { get; set; }
    public bool IsCompleted { get; set; }
}

4. Create TodoItemContext.cs for Entity framework

using Microsoft.EntityFrameworkCore;
namespace ApiPostgre;
public class TodoItemContext : DbContext
{    
    public DbSet<TodoItem> TodoItems { get; set; }

    public TodoItemContext(DbContextOptions<TodoItemContext> options) : base(options) 
    {
    }        
}

5. Add TodoItemController including all method (get, post, put, delete)

using Microsoft.AspNetCore.Mvc;
namespace ApiPostgre.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class TodoItemController : ControllerBase
    {
        TodoItemContext bd;
        public TodoItemController(TodoItemContext context)
        {
            bd = context;
            bd.Database.EnsureCreated();
        }

        [HttpGet("")]
        public ActionResult<IEnumerable<TodoItem>> GetTodoItems()
        {
            return  bd.TodoItems;
        }

        [HttpGet("{id}")]
        public ActionResult<TodoItem> GetTodoItemById(int id)
        {
            var currentItem = bd.TodoItems.FirstOrDefault(p=> p.Id == id);

            if(currentItem == null) return NotFound();

            return currentItem;
        }

        [HttpPost("")]
        public async Task PostTodoItem(TodoItem model)
        {
            bd.Add(model);
            await bd.SaveChangesAsync();
        }

        [HttpPut("{id}")]
        public async Task<IActionResult> PutTodoItem(int id, TodoItem model)
        {
            var currentItem = bd.TodoItems.FirstOrDefault(p=> p.Id == id);

            if(currentItem == null) return NotFound();

            currentItem.Title = model.Title;
            currentItem.IsCompleted = model.IsCompleted;
            await bd.SaveChangesAsync();

            return NoContent();
        }

        [HttpDelete("{id}")]
        public ActionResult<TodoItem> DeleteTodoItemById(int id)
        {
            return null;
        }
    }
}

6. ApsaraDB RDS is a service created by alibaba to host relationational database like SQL Server, PostgreSQL y MySQL

Let's create a new ApsaraDB RDS instance in Alibaba Cloud

select Create Instance

You can select a subscription if you want to reserver the database for a long time and pay the same fee. Pay as you go if you only want to pay for the specific consume or perform a demo (for example 2 hours)

Complete clicking on Pay Now

7. After waiting for the creation of the new instance go to ApsaraRDS instance and create a new account for this instance. Click on the instance created that is running. 

select Create Account and set a user and password

8. Now create a new database and assign the account created before

9. Finally, you can set up your IP or add 0.0.0.0/0 in the white list in order to create open access to this new database (This is only for test propose but in production, you must add only your IP or IP to the services that need access to the DB)

10. Now, You can setup the PostgreSQL connection and the EF service in your API go to (program.cs):

// Add services to the container.
builder.Services.AddDbContext<TodoItemContext>(options => 
options.UseNpgsql("server=myserver.pg.rds.aliyuncs.com;database=todoitems;user id=user_1;password=mypassword")
);

11. Execute your API using "dotnet run" or run the API using Visual Studio and it should works!!

Check the repo for more details and the sample code:  github.com/Mteheran/dotnetapiapsaradb