Kotlin + Spring + Vue: Database Connection

One of the most important features of any application
is an ability to interact with database. Let's implement that in our application.

Objectives

Configure database source (PostgreSQL) in Heroku Environment
Implement JPA using Spring Data
Configure data repository
Implement access to database stored data using REST API

Configure PostgreSQL with Heroku

Postgres Resource

#1 Go to your application account in Heroku and then Application -> Resources -> Add-ons -> Heroku Postgres:

#2 Select Plan:

#3 Now you can see the added Postgres resource. Click on it:

#4 Settings -> View Credentials

Here you can see your database credentials.

NOTE: These credentials are not permanent. Heroku rotates credentials periodically and updates applications where this database is attached.

You can access your database using Heroku CLI or pgAdmin.

Environment Variables

#1 Application -> Settings -> Reveal Config Vars:

#2 Add the followings variables using this format:
SPRING_DATASOURCE_URL = jdbc:postgresql://hostname:port/db_name
SPRING_DATASOURCE_USERNAME = username
SPRING_DATASOURCE_PASSWORD = password

Create Test database

Create table:

CREATE TABLE public."person"
(
id serial NOT NULL,
name character varying,
PRIMARY KEY (id)
);

And fill it with the some data:

INSERT INTO person (name) VALUES ('John'), ('Griselda'), ('Bobby');

Configure Backend

pom.xml

Add to backend pom.xml:

<properties>
...

<postgresql.version>42.2.5</postgresql.version>
...
</properties>
...
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>${postgresql.version}</version>
</dependency>

<plugin>
<groupId>org.jetbrains.kotlin</groupId>
<artifactId>kotlin-maven-plugin</artifactId>
<configuration>
<args>
<arg>-Xjsr305=strict</arg>
</args>
<compilerPlugins>
<plugin>spring</plugin>
<plugin>jpa</plugin>
</compilerPlugins>
</configuration>

<dependency>
<groupId>org.jetbrains.kotlin</groupId>
<artifactId>kotlin-maven-noarg</artifactId>
<version>${kotlin.version}</version>
</dependency>

IMPORTANT: you can see pom.xml completely at the Git repo

Application Properties

Add following configs to application.properties:

spring.datasource.url=${SPRING_DATASOURCE_URL}
spring.datasource.username=${SPRING_DATASOURCE_USERNAME}
spring.datasource.password=${SPRING_DATASOURCE_PASSWORD}

spring.jpa.generate-ddl=true
spring.jpa.properties.hibernate.temp.use_jdbc_metadata_defaults = false
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQL9Dialect
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

NOTE:

Name, password and database connection URL are read from environment variables
Use command line arguments to run application locally

Entity Class

Add package jpa and class Person:

Repository

Add package repository and class PersonRepository:

Controller

And now we can create a method to return data from database:

import com.kotlinspringvue.backend.jpa.Person
import com.kotlinspringvue.backend.repository.PersonRepository
import org.springframework.beans.factory.annotation.Autowired

…

@Autowired
lateinit var personRepository: PersonRepository

…

@GetMapping("/persons")
fun getPersons() = personRepository.findAll()

Run

Visit https://localhost:8080/api/persons to check controller works fine:

[{"id":1,"name":"John"},{"id":2,"name":"Griselda"},{"id":3,"name":"Bobby"}]

Ways to Improve

Implement controllers with specified database queries
Implement INSERT, UPDATE and DELETE database queries

vaadim

Search This Blog