Easiest Way To Insert Scala Collection into PostgreSQL using Slick


Few days ago, I had a scenario, in which I had to insert scala collection into postgreSQL using Slick. My postgreSQL table has some columns with data types such as Arrays, hstore  etc..
I tried to do this using slick, but didn’t get success. After beating my head whole day, I found a solution.
I found a slick extension slick-pg, which supports following postgreSQL types:-

  • ARRAY
  • Date/Time
  • Enum
  • Range
  • Hstore
  • LTree
  • JSON
  • Inet/MacAddr
  • text Search
  • postgis Geometry

In this blog, I have explained how to insert Scala collection into PostgreSQL using Slick through an example.  In this example,  there is a student table, which has 4 columns

CREATE EXTENSION hstore;

CREATE TABLE student (
id     int,
name   varchar(254) NOT NULL,
hobbies  text[],
marks hstore
);

Here hobbies is Array of String and marks is hstore(Set of key-value pair).

As suggested here https://github.com/tminglei/slick-pg , I created a trait MyPostgresDriver  and integrated with PostgresDriver like this:-


trait MyPostgresDriver extends PostgresDriver
  with PgArraySupport
  with PgHStoreSupport {
 
  override lazy val Implicit = new ImplicitsPlus {}
  override val simple = new SimpleQLPlus {}
 
  trait ImplicitsPlus extends Implicits
    with ArrayImplicits
    with HStoreImplicits
 
  trait SimpleQLPlus extends SimpleQL
    with ImplicitsPlus
}

Then I mapped my table like this:-

class StudentTable(tag: Tag) extends Table[Student](tag, "student") {
  def id = column[Int]("id", O.PrimaryKey)
  def name = column[String]("name")
  def hobbies = column[List[String]]("hobbies", O.Default(Nil))
  def marks = column[Map[String, String]]("marks")
 
  def * = (id, name, hobbies, marks) <> (Student.tupled, Student.unapply)
}

After running command sbt run, I was able to store data in postgres.

Selection_039

For complete example, please go here https://gist.github.com/ayushmishra2005/d382801e0cb2d0df9e7c

Advertisements

About Ayush Mishra

Ayush is the Sr. Software Consultant @ Knoldus Software LLP. In his 5 years of experience he has become developer with proven experience in architecting and developing web applications. Ayush has a Masters in Computer Application from U.P. Technical University, Ayush is a strong-willed and self-motivated professional who takes deep care in adhering to quality norms within projects. He is capable of managing challenging projects with remarkable deadline sensitivity without compromising code quality. .
This entry was posted in Scala, sql and tagged , , , . Bookmark the permalink.

4 Responses to Easiest Way To Insert Scala Collection into PostgreSQL using Slick

  1. shuchi mishra says:

    Good..

  2. Reblogged this on Play!ng with Scala and commented:

    Easiest Way To Insert Scala Collection into PostgreSQL using Slick

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s