Tag: PostgreSQL

RealWorld app + Spring + PostgreSQL

As WCE’s app is settling down, I’ve been itching to write a backend for my personal use. It’s just a simple database app to keep track of my tennis racquets. This can be done with Python which I’m very confortable with, and probably Django to go with it. Then, this is really a weekend project.
So, I decided to go with most unconfortable environment. Java + Spring. TBH, I was a bit impressed about Spring. When Java EE, Java Beans came out, I thought it’s a real hustle to write Java backend. You have to do so much boilerplate coding just to get things going. Looking at Spring, I can see that Spring is the thing that should have been for backend development.
So, I started writing in Java and Spring on Mac. I got totally wedged. I don’t know much about Spring, or MyBatis, or any of it. And realized – oh wait, this isn’t much different from RealWorld backend. I can probably start from the mother of all examples. Download front and backend repo, build, and it just works on Linux. Used this as a starting point, and see what it takes to make this work with PostgreSQL is a good way to go. This is my 2nd day of using Java and Spring. So, the struggles ensued. This post is about just that. But, without struggle, you cannot learn anything.

Using PostgreSQL JDBC driver

It is definitely not just a matter of setting database connection URL to PostgreSQL. First, you need to install JDBC driver. On Ubuntu, this would be
sudo apt install libpostgresql-jdbc-java
Then, tell gradle to use it.

diff --git a/build.gradle b/build.gradle
index 62d7690..7ff7df7 100644
--- a/build.gradle
+++ b/build.gradle
@@ -48,7 +48,7 @@ dependencies {
     compile('org.springframework.boot:spring-boot-starter-security')
    compile('joda-time:joda-time:2.10')
     compileOnly('org.projectlombok:lombok')
-   runtime('com.h2database:h2')
+   runtime('org.postgresql:postgresql:9.4.1212')
     testCompile 'io.rest-assured:rest-assured:3.1.1'
    testCompile 'io.rest-assured:spring-mock-mvc:3.1.1'
    testCompile 'org.springframework.security:spring-security-test'

URL for PostgreSQL

Point URL to the database. I didn’t want to run the PostgreSQL instance on my laptop, so it’s running on “nefertiti” which is my Linux desktop in house.

--- a/src/main/resources/application.properties
+++ b/src/main/resources/application.properties
@@ -1,4 +1,9 @@
 spring.jackson.deserialization.UNWRAP_ROOT_VALUE=true
+#
+spring.datasource.url=jdbc:postgresql://nefertiti:5432/my_db
+spring.datasource.username=my_db_user
+spring.datasource.password=my_db_password
+#
 image.default=https://static.productionready.io/images/smiley-cyrus.jpg
 jwt.secret=nRvyYC4soFxBdZ-F-5Nnzz5USXstR1YylsTd-mA0aKtI9HUlriGrtkf-TiuDapkLiUCogO3JOK7kwZisrHp6wA
 jwt.sessionTime=86400
@@ -11,3 +16,6 @@ mybatis.mapper-locations=mapper/*.xml
 logging.level.io.spring.infrastructure.mybatis.readservice.ArticleReadService=DEBUG
 # Uncomment the following line to enable and allow access to the h2-console
 #spring.h2.console.enabled=true
+#
+spring.flyway.baseline-on-migrate=true
+spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

spring.flyway.baseline-on-migrate=true

So, what’s the last two lines added. spring.flyway.baseline-on-migrate=true tells flyway it’s okay to create DB schema migration table when it doesn’t exist. Without this, the schema migration doesn’t take off – aka – no flyway. Oh, I’m actually not using flyway but I’m planning to use it.

spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

This tells hibernate to not use blob/clob. I probably have to come back to this. Right now, this makes a column to use varchar in place of blob. This is def. not what I want. I have to come back and figure this out to use binary array type of PostgreSQL.

With this, the backend starts, and I can see the backend created necessary tables from the DB migration.

With a lot of googling, it took me a day to get this point but it’s working, and limping. Went to play tennis, ruined my car tire (unfortunate,hate New England winter) and a lot of bad words are said to the pavement.

Select statement with limit does not work

Running the backend from IntelliJ, running query is not happy. It’s a SQL syntax error coming out when retriving the articles from the database. Breakpointing at the “bad SQL exception” gave me a WOT (how can Java people live with this?). Anyhow, the exception is “PostgreSQL does not support the LIMIT ?, ?”. I stepped through the code with debugger, and came to the conclusion that the SQL statement is coming from template, not actually auto-genned. Finally, I came to a conclusion that the source code somewhere is setting up the template SQL. Once I realized this, it took my literal 10 seconds to fix this up.

--- a/src/main/resources/mapper/ArticleReadService.xml
+++ b/src/main/resources/mapper/ArticleReadService.xml
@@ -55,7 +55,7 @@
             </if>
         </where>
         order by A.created_at desc
-        limit #{page.offset}, #{page.limit}
+        limit #{page.limit} offset #{page.offset}
     </select>
     <select id="countArticle" resultType="java.lang.Integer">
         select
@@ -93,7 +93,7 @@
         <foreach index="index" collection="authors" item="id" open="(" separator="," close=")">
             #{id}
         </foreach>
-        limit #{page.offset}, #{page.limit}
+        limit #{page.limit} offset #{page.offset}
     </select>
     <select id="countFeedSize" resultType="java.lang.Integer">
         select count(1) from articles A where A.user_id in
@@ -105,4 +105,4 @@
     <resultMap id="articleId" type="string">
         <id javaType="string" column="articleId"/>
     </resultMap>
-</mapper>

Conclusion

The demo app backend says it’s just a matter of setting URL to other database to use different JDBC driver. It’s pretty close to it, but not really. The source contains some dependancies to SQL dialect. PostgreSQL lacks blob/clob so this probably needs to be patched up. For now, I can move forward with my racquet database app.

P.S.

I just run the unit tests and a bunch are failing. I guess I need to wear the hardhat a little longer.

Fake Fake with Real

I wanted to learn something new. I know nothing about JavaScript, TypeScript or any of web framework. When I found time, I started taking a look at Angular. I don’t know I’d ever use it but it’s all the industry rage. (Friend of mine mentioned React is better, but I know nothing so I’ll see.)

I was happily following the tutorial, and when it came to the part of using fake RESTful in-memory database angular-in-memory-web-api, I hit a wall. The piece just doesn’t want to work at all. I know very little of anything at this point about npm or node or JavaScript. But, I understood that it’s mimicing the database. So, “fine. I’ll use real database instead.”.

So, I started a project, thanks to evaluation of IntelliJ, and googling like storm to find out how to write the RESTful service using PostgreSQL.

I found this post to be pretty much what I need. Just swap out the puppies with heroes. (Yes, the post uses puppy database.) But, I also wanted to use the express/router.

The part accessing database and returning records started working pretty quickly. I spent most of the day Googling and learning JavaScript, Observables, Express router, and then write a few lines of code to access database.

The pain started once I swapped out the web service URL from the fake URL of Angular Tutorial to my own. As a complete newbie, I didn’t know how to debug but it just doesn’t work at all. After a few more Google, someone said, “console output shows up on Chrome.”. I was hoping IntelliJ’s “debug” to work but I couldn’t manage it to work. (Another time to learn this.) So, I opened the tutorial’s endpoint, I can see the console log and errors! Horay!.

And the error says No Access-Control-Allow-Origin present in the header. What on earth? Yet another googling. (I don’t know how people write code before google.)

Now, I learned when you make a call from other domain (well, in this case port 4200 to port 3001.), it’s crossing boundaries and web services don’t like that kind of deal for security reason. The way to do this is to add the header. Rather than doing it by myself, I found a library “cors”. I confess that I googled. I have no clue what it does. Also, the location of app.use(cors()) seems to matter. When I moved it around, it got totally wedged. So, be careful using this piece. 

app.use(cors());
app.options('*', cors());  // enable pre-flight

Second missing piece was, the Angular’s example’s accessing service didn’t like the header that node’s service returns. Googling, googling and more googling. And here was the answer. 42! No. JK.

app.disable('etag')

Apparently, this “etag” thing needs to be suppressed. I’m not quite sure of what it is (I’ll learn some day), but disabling this bit made the Angular tutorial happy.

https://github.com/ntai/realherodb

Here is the result of it. I faked the fake database with real database. Named it “Real Hero DB”