This page explains how Portrait can be connected to a given SQL database.

Prerequisites

Before you can use SQL as a source for Portrait, we need to make sure that the following prerequisites are met.

SQL Server

Portrait will access the SQL instance via JDBC. Make sure the Portrait-App Instance has direct access to the SQL server - this especially relevant if the Portrait runs in a DMZ or external network.

Depending on the given SQL database and connection properties, the protocol and ports differ.

SQL User

Create a specific user for Portrait - we recommend the name portraitservice -, since it is only used for Portrait as a service user. Depending on your use-case, you should consider read-only writes.

Set up a connection

In the application.yml file, we need to add a new entry inside the connections section:

See Available connectors to see all available driverClass options. How the url is formatted, is depending on the used driver. Check the documentation of the given JDBC driver.

This example shows a connection to MS SQL Server with a named instance and port:

connections:
  connection:
    - id: NAVDEMO
      type: SQL
      driverClass: "com.microsoft.sqlserver.jdbc.SQLServerDriver"
      url: "jdbc:sqlserver://SQL01\\NAVDEMO:48981;database=Demo Database NAV (7-1);trustServerCertificate=true"
      username: "portraitservice"
      password: "s3cur3d"

You can have several connections, each of them must start with a “-”.

The yml-file is formatted with whitespaces. Ensure you have the correct intentation: first level = 2 whitespaces, second level = 4 whitespaces, and so on. You will get an error in the logs at startup if the yml is malformed.

After you added the connection, you can jump to the next chapter: “Adding an SQL source”.

Examples

This chapter provides you with examples for connections via JDBC URL’s.

Microsoft SQL Server

This example connects to a Microsoft Dynamics Business Central database. The database runs on a named instance.

    - id: NAVDEMO
      type: SQL
      driverClass: "com.microsoft.sqlserver.jdbc.SQLServerDriver"
      url: "jdbc:sqlserver://SQL01\\NAVDEMO:48981;database=Demo Database NAV (7-1);trustServerCertificate=true"
      username: "portraitservice"
      password: "s3cur3d"

Since the upgrade to the latest official SQL JDBC driver (10.x.x), you have to add the trustServerCertificate=true option.

SQLite

This example uses a SQLite database. We ship a SQLite database for demo uses or small use-cases.

    - id: internal
      type: SQL
      driverClass: "org.sqlite.JDBC"
      url: "jdbc:sqlite:./config/sample.db"

Portrait - 7.x

SQL connections