locked
What is best way to add another Database like POSTGRESQL RRS feed

  • Question

  • User-171003712 posted

    Dear Friends,

    I have one webapi in asp.net core 2.2 and i have huge database for LIKE and COMMENT so we decide to add another database like POSTGERSQL to manage only LIKE and COMMENT so we can reduce the database load on one database. here we are not thinking to microservice right now. Parallel we are working on but mean time we have to manage current database load.

    here is my some configuration .

    public void ConfigureServices(IServiceCollection services)
            {
                services.AddDbContext<DSObjectContext>(options =>
                {
                    options.UseSqlServer(
                        Configuration.GetConnectionString("DefaultConnection"));
                });
                services.AddHangfire(x => x.UseSqlServerStorage(Configuration.GetConnectionString("DefaultConnection")));
                services.AddHangfireServer();
                services.AddIdentity<IdentityUser, IdentityRole>()
                    .AddEntityFrameworkStores<DSObjectContext>()
                    .AddDefaultTokenProviders();
    
                services.AddApiVersioning(options =>
                {
                    options.ReportApiVersions = true;
                    options.AssumeDefaultVersionWhenUnspecified = true;
                    options.DefaultApiVersion = new ApiVersion(1, 0);
    
                });
                JwtSecurityTokenHandler.DefaultInboundClaimTypeMap.Clear();
                services.AddAuthentication(options =>
                {
                    options.DefaultAuthenticateScheme = JwtBearerDefaults.AuthenticationScheme;
                    options.DefaultScheme = JwtBearerDefaults.AuthenticationScheme;
                    options.DefaultChallengeScheme = JwtBearerDefaults.AuthenticationScheme;
    
                })
    
                    .AddJwtBearer(options =>
                    {
                        options.RequireHttpsMetadata = false;
                        options.SaveToken = true;
                        options.TokenValidationParameters = new TokenValidationParameters
                        {
                            ValidateIssuer = true,
                            ValidateAudience = true,
                            ValidateLifetime = true,
                            ValidateIssuerSigningKey = true,
                            ValidIssuer = Configuration["Jwt:Issuer"],
                            ValidAudience = Configuration["Jwt:Issuer"],
                            IssuerSigningKey = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(Configuration["Jwt:Key"]))
    
                        };
                    });
    
                services.AddTransient<IDbContext, DSObjectContext>();
                services.AddScoped(typeof(IRepository<>), typeof(Repository<>));
                services.Configure<MongoDBDatabaseSetting>(Configuration.GetSection(nameof(MongoDBDatabaseSetting)));
                services.AddSingleton<IMongoDBDatabaseSetting>(sp =>
                    sp.GetRequiredService<IOptions<MongoDBDatabaseSetting>>().Value);
               services.ConfigureApplicationServices(Configuration); // here add more services
                
                //services.AddResponseCompression();
                services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2)
                    .AddJsonOptions(options => options.SerializerSettings.ContractResolver = new DefaultContractResolver());
              
            }

    In this configuration you can see MongoDb we have used. but not getting idea to add postgre so plz give some idea.

    Thanks

    Dharmesh Sharma

    Friday, August 30, 2019 10:35 AM

All replies

  • User-821857111 posted

    How do you plan to access the database? Plain ADO.NET? Dapper? EF Core?

    Friday, August 30, 2019 3:40 PM
  • User1289604957 posted

    Hello,

    As Mikes said, we need to know either <g class="gr_ gr_287 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="287" data-gr-id="287">your</g> are using or not an ORM.

    Npgsql has an Entity Framework (EF) Core <g class="gr_ gr_15 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="15" data-gr-id="15">provider</g>. It behaves like other EF Core providers (e.g. SQL Server), so the general EF Core docs apply here as well. If you're just getting started with EF Core, those docs are the best place to start.

    Development happens in the Npgsql.EntityFrameworkCore.PostgreSQL repository, all issues should be reported there.

    To use the Npgsql EF Core provider, add a dependency <g class="gr_ gr_22 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="22" data-gr-id="22">on </g>Npgsql.EntityFrameworkCore.PostgreSQL<g class="gr_ gr_22 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style multiReplace" id="22" data-gr-id="22">.</g> You can follow the instructions in the general EF Core Getting Started docs.

    Below is a .csproj file for a console application that uses the Npgsql EF Core provider:

    <Project Sdk="Microsoft.NET.Sdk">
      <PropertyGroup>
        <TargetFramework>netcoreapp2.2</TargetFramework>
      </PropertyGroup>
      <ItemGroup>
        <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="2.2.0" />
      </ItemGroup>
    </Project>
    
    using System.Collections.Generic;
    using Microsoft.EntityFrameworkCore;
    
    namespace ConsoleApp.PostgreSQL
    {
        public class BloggingContext : DbContext
        {
            public DbSet<Blog> Blogs { get; set; }
    
            public DbSet<Post> Posts { get; set; }
    
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
                => optionsBuilder.UseNpgsql("Host=my_host;Database=my_db;Username=my_user;Password=my_pw");
        }
    
        public class Blog
        {
            public int BlogId { get; set; }
            public string Url { get; set; }
    
            public List<Post> Posts { get; set; }
        }
    
        public class Post
        {
            public int PostId { get; set; }
            public string Title { get; set; }
            public string Content { get; set; }
    
            public int BlogId { get; set; }
            public Blog Blog { get; set; }
        }
    }

    Modify the ConfigureServices method in Startup.cs:

    public IServiceProvider ConfigureServices(IServiceCollection services)
        => services.AddEntityFrameworkNpgsql()
                   .AddDbContext<BloggingContext>()
                   .BuildServiceProvider();

    The Npgsql EF Core provider also supports reverse-engineering a code model from an existing PostgreSQL database ("database-first"). To do so, use <g class="gr_ gr_36 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="36" data-gr-id="36">dotnet</g> CLI to execute the following:

    dotnet ef dbcontext scaffold "Host=my_host;Database=my_db;Username=my_user;Password=my_pw" Npgsql.EntityFrameworkCore.PostgreSQL

    Best regards,

    Maher

    Friday, August 30, 2019 4:05 PM
  • User-474980206 posted
    as your postgresql database is a relational, you will create a new dbcontext and repository for it. As you will need to do the joins with your own code, two repositories will be fine.
    Friday, August 30, 2019 6:52 PM
  • User-171003712 posted
    Dear sir,
    Currently in this we are using EF with MSQL so parallel want to use postgre db with same EF. We don't want to completely discontinued mssql. All operations working same only for like and comments we add Postgre. I hope you got me .

    Thanks
    Dharmesh
    Sunday, September 1, 2019 5:58 PM
  • User-171003712 posted
    Ok so as your code help can I add two or more dbcontext in service?
    Like one for Mssql as already we have and also can add another like mongodb we already have same for Postgre
    Sunday, September 1, 2019 6:00 PM
  • User-474980206 posted

    not sure why you would use postgresql and mssql in the same project as they have the same features. 

    Sunday, September 1, 2019 6:07 PM
  • User-171003712 posted
    In my application i have sone like and comments feature like facebook so one post haa multiple rows and that have IDs of post and user ID and comments data that why I'm looking saprate database so that can release my Database load and I have another database for like n comments so that why im thinking.
    Plz suggest it's good for now or we have to think about any other solution.
    Thanks
    Sunday, September 1, 2019 8:13 PM
  • User-1764593085 posted

    Hi dharmeshsharma,

    Ok so as your code help can I add two or more dbcontext in service?

    If you would like to use two databases,you need to implement two dbContexts.

    Register two contexts in ConfigureServices method using connection string keys from config file, or you can specify connection strings directly inside the method as arguments.

    Then you can resolve necessary contexts in constructors.

    With Regards,

    Xing

    Monday, September 2, 2019 2:09 AM