Is it feasible to change databases in an existing system?

Question

I’m starting a project in ASP.NET MVC 5 with Entity Framework for a customer, where the customer has chosen to use a free database, in this case the Mysql. However, with the growth of the application, I foresee the best use of a database such as the SQL Server.

My suggestion is to use the SQL Server from the beginning, however, my dear client says "Let’s start with Mysql, anything we change later".

When in college, several professors said this was possible, and in projects where we separated the layers and responsibilities was even less painful to accomplish. Therefore, I never did and I have no idea, in an Asp.net mvc project, how should I proceed with this?

I wanted to know what the rework would be, thinking at the level of CRUD, if I use Controller’s and View’s generated using scaffold, where these directly use my context, initial in Mysql, when I change the application database to SQL Server, what would be the risks and rework?

Answer 1

I wanted to know what the rework would be, thinking at CRUD level, if I use Controller’s and View’s generated using scaffold, where these directly use my context, initial in Mysql, when I change the application database to SQL Server, what the risks and rework would be?

Responding from the point of view of technology:

None. Except that you would have to just install and reconfigure the data providers.

The Entity Framework was thought to be agnostic. Agnostic means that it does not implement any technological particularity of a specific database. The syntax and the way you work with it are always the same.

The only thing that can generate rework is if you resolve to use SQL along with the traditional logic of the Entity Framework, essentially if there is some element of the syntax that is particular to a particular database provider.

In Mysql you would configure the data provider in your Web.config sort of like this:

<entityFramework>
  <defaultConnectionFactory type="MySql.Data.Entity.MySqlConnectionFactory, MySql.Data.Entity.EF6" />
  <providers>
    <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" />
  </providers>
</entityFramework>

While in SQL Server, the configuration would be like this:

<entityFramework>
  <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
  <providers>
    <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
  </providers>
</entityFramework>

Answer 2

Therefore, I never did and I have no idea, in an Asp.net mvc project, how should I proceed with this?

If you haven’t developed an Asp.net MVC project yet this one introduces the concept and has some references to help you.

...if I use Controller’s and View’s generated using scaffold, where they directly use my context, starting in Mysql...

Using your context directly you will be tied to the technology, because at every point in the code where you have reference to this technology you would need to change. Depending on its implementation it is a very arduous rework, in addition to running the risk of code duplicity and having greater difficulty to test your application.

Exemplo:

Using direct access with Nhibernate to recover data on Controller I’d go with references like Nhibernate, Nhibernate.Linq, in addition to specific objects of Nhibernate technology such as Isession, etc..

using System;
using System.Web.Mvc;
using NHibernate; //Código relacionado a tecnologia de acesso a BD
using NHibernate.Linq; //Código relacionado a tecnologia de acesso a BD
using System.Linq;
using NhibernateMVC.Models;

namespace NhibernateMVC.Controllers
{
    public class ExemploController : Controller
    {     
        public ActionResult Index()
        {
            //Você provavelmente iria duplicar código como esse em outros Controllers
            //gerando possíveis retrabalhos
            using (ISession session = NHibertnateSession.OpenSession())
            {
                var empregados = session.Query<Empregado>().ToList();
                return View(empregados);
            }                
        }
    }
}

One alternative is to use the design pattern Repository, it centralizes its access code to the database (insertion, update, deletion and data recovery), adding a separation layer so that when changing the data access technology, you change only one layer of your application with a very small rework compared to the question of using context directly.

Given the example, in the same way, directly using specific data access technologies in your Controllers, be it Entity Framework, Mysql, X, Y, etc... you would be left with references and codes that generate rework if you change the technology.

In short, instead of creating an Asp.net MVC project and developing your application, it would be interesting for you to create at least one more project in your solution to centralize your database access code, isolating domain (business-related) objects of access code details.

Answer 3

The problem is that you will have rework if you are working with the native language of the database, if you use any framework to work on the layer of the entity in your code, the job you will have is in the configuration only, because all its manipulations in the database will be mapped through its entities, an example is to use Querydsl, all its manipulations are done through its entities.

 public List<Sale> findSalesByPrice() {
    QSale sale = QSale.sale;

    return new JPAQueryFactory(entityManager)
            .selectFrom(sale)
            .where(sale.price.eq(20))
            .fetch();
} 

instead of using

select * from sale where price = 20;