How to use JHipster with MS SQL Server

Tip submitted by @Zyst

Goal: By the end of this tutorial you will have the default JHipster application running on your SQL Server, with everything being functional.

Start by running JHipster normally with yo jhipster, select the options to use token based authentication, SQL, MySQL as the dev. database, MySQL as the prod. database, Yes with ehcache, No Elasticsearch, No clustered HTTP, No Websockets, Maven, Grunt, and no Sass.

We then add the MS SQL Server JDBC dependency to the project pom.xml file.

pom.xml

[...]
<!-- Microsoft JDBC -->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>sqljdbc41</artifactId>
    <version>4.1</version>
</dependency>
<!-- Liquibase MS SQL Server extensions -->
<dependency>
    <groupId>com.github.sabomichal</groupId>
    <artifactId>liquibase-mssql</artifactId>
    <version>1.4</version>
</dependency>
[...]

I am using Sql JDBC 4.1, and already have it installed to my personal repository, but if you do not this will not work without some further configuration, check out this stackoverflow question for further reference.

The Liquibase MS SQL Server extension allows you to do some neat stuff we will be using later on in this tutorial.

##Database modification

Go into src\main\resources\config\application-dev.yml and change your application to use the new datasource, and your Hibernate configuration to use the SQL Server dialect as seen below:

application-dev.yml

spring:
    profiles:
        active: dev
    datasource:
        driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDataSource
        url: jdbc:sqlserver://localhost:1433;databaseName=test
        databaseName:
        serverName:
        username: myuser
        password: supersecretpassword
        cachePrepStmts: true
        prepStmtCacheSize: 250
        prepStmtCacheSqlLimit: 2048
        useServerPrepStmts: true

    jpa:
        database-platform: org.hibernate.dialect.SQLServerDialect
        database: SQL_SERVER
        openInView: false
        show_sql: true
        generate-ddl: false
        [...]

This assuming your database is called test, change your connection url as necessary.

Now go into *\src\main\resources\config\liquibase\changelog\00000000000000_initial_schema.xml and at the top of the file change the following properties:

00000000000000_initial_schema.xml

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <property name="now" value="now()" dbms="mysql,h2"/>
    <property name="now" value="current_timestamp" dbms="postgresql"/>
    <property name="now" value="GETDATE()" dbms="mssql"/>

    <changeSet id="00000000000000" author="jhipster" dbms="postgresql">
        <createSequence sequenceName="hibernate_sequence" startValue="1000" incrementBy="1"/>
    </changeSet>
    [...]

First, make sure you changed your xml databaseChangeLog property to include the ext. Now inside src\main\resources\config\liquibase\changelog\00000000000000_initial_schema.xml find the data entries and change them:

00000000000000_initial_schema.xml

<ext:loadData encoding="UTF-8"
          file="config/liquibase/users.csv"
          separator=";"
          tableName="JHI_USER" identityInsertEnabled="true">
    <column name="activated" type="boolean"/>
    <column name="created_date" type="timestamp"/>
</ext:loadData>
<dropDefaultValue tableName="JHI_USER" columnName="created_date" columnDataType="datetime"/>

<ext:loadData encoding="UTF-8"
              file="config/liquibase/authorities.csv"
              separator=";"
              tableName="JHI_AUTHORITY"
              identityInsertEnabled="true" />

<ext:loadData encoding="UTF-8"
              file="config/liquibase/users_authorities.csv"
              separator=";"
              tableName="JHI_USER_AUTHORITY"
              identityInsertEnabled="true" />

Adding the identityInsertEnabled="true" is the same as wrapping your Inserts with IDENTITY_INSERT ON and IDENTITY_INSERT OFF which will allow you to insert the project autogenerated identities directly. This is why we are using the MS SQL Server Liquibase for.

Now try running your application! Everything should be working and you should be on your way to continue using your JHipster application with SQL Server.