We are planning to use Spring Boot with JPA for our next project and I am wondering how much flexibility JPA gives in reality. If we start developing using a self-hosted PostgreSQL server and later want to change to Amazon RDS PostgreSQL or SQL Server, will we run into a lots of problems? On paper, JPA should serve as an abstraction layer, but If we start to use Database specific data types, views, functions/stored procedures we quickly lose the ability to change. Generally what is the best practice? Use only JPA even if It means performance loss or dive hard into database programming with plSQL or T-SQL and if platform change is needed, rewrites of those functions needs to happen.Another problem is the data types. PostgreSQL for example can be extended with ltree and it provides a good foundation for hierarchical data structures, but to use it, one need to extend JPA to use the ltree data type or solve every interaction with functions.
Reserving database independence using Spring JPA
database;spring;jpa
If we start developing using a self-hosted PostgreSQL server and  later want to change to Amazon RDS PostgreSQL or SQL Server, will we  run into lots of problems?Not really. We recently migrated from Oracle 11g to Azure SQLServer and it just took us to change some Spring properties.spring.datasource.url=jdbc:sqlserver://<host>:<port>;databaseName=<DB>spring.datasource.username=<user>spring.datasource.password=<pass>spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriverspring.jpa.database-platform=my.project.persistence.CustomSQLServer2012DialectWe had to review some mappings too. For example, we had to change @Column(name = READ) by @Column(name = [READ]). I guess that READ is a reserved word.Look at the properties an pay attention to spring.jpa.database-platform=my.project.persistence.CustomSQLServer2012Dialect.Spring Data JPA allows us to implement our dialects extending those provided by Hibernate. We usually want to take advantage of the built-in functions and DB server features. Think about these date or string functions that operate at DB level and make our life simpler.Take a look to how we can incorporate these functions into JPApublic class PrestigeSQLServer2012Dialect extends SQLServer2012Dialect {    /**     * Default constructor     */    public PrestigeSQLServer2012Dialect() {        super();        registerFunction(convertStringToDate,                new SQLFunctionTemplate(StandardBasicTypes.DATE, CONVERT(DATETIME,?1,?2)));        // Current SQLServer Date's end of month day (without time)        registerFunction(endOfCurrentMonth, new SQLFunctionTemplate(StandardBasicTypes.DATE, EOMONTH(GETDATE())));        // Current SQLServer Date's end of month. Add or Remove        registerFunction(endOfCurrentMonthAdd,                new SQLFunctionTemplate(StandardBasicTypes.DATE, EOMONTH(GETDATE(),?1)));        // Date's end of month        registerFunction(endOfMonth, new SQLFunctionTemplate(StandardBasicTypes.DATE, EOMONTH(?1)));    }}Later we can use these functions with JPA queries@Query(select kpi from KPIAccounts kpi where kpi.created between endOfCurrentMonthAdd(:months * -1) and endOfCurrentMonth())    Iterable<KPIAccounts> findKPIByMonthRangeBackforward(@Param(months) int months);You will find Spring Data JPA to be very sophisticated and flexible.Generally what is the best practice? Use only JPA even if It means  performance loss or dive hard into database programming with pl SQL or  T-SQL and if platform change is needed, rewrites of those functions  needs to happen.Depends totally on your requirements and needs. As I commented, we usually want to take profit of all the possible DB server features. If you find yourself needing full support of these functions and crafting complex queries, probably JPA won't help you. Right the opposite, it will generate unnecessary overhead. ORMs usually don't fit well in data-first domain. If that were the case, row mappers like JDBCTemplate or myBatis are better alternatives.Performance could be one more concern to take into account because ORMs usually have lower performance than mappers. If I were asked, I would not put the business into the DB. I didn't during the last decade and I didn't need it either. Placing logic into the DB tie business to the DB capabilities and constraint the way our system evolves. One more undesired drawback is vendor lock-in. I find these strategies to make us loose cohesion. One more concern could be testing. I find hard to make unit testing of T-SQL or pl/SQL procedures.Another problem is the data types. PostgreSQL, for example, can be  extended with ltree and it provides a good foundation for hierarchical  data structures, but to use it, one need to extend JPA to use the  ltree data type or solve every interaction with functions.As I commented above, if you find that the project relies heavily on the DB server features and capabilities, probably ORMs is not the proper tool for you. However, regarding this question (data types), I have found several references to how to map ltree structures with JPA.I guess, the question here is Should we implement ORMs?The only possible answer is depends on your needs and requirements. Before making a decision, invest time in researching and implementing tests of proof. Get familiar with various frameworks and find which one suits best your needs. Don't implement frameworks just because everyone else does. It's usually a mediocre argument that leads to mediocre solutions.