I am trying to create a table running flyway migration on Maria db and want to save images blob in the table?

Question

Any idea how i can save it without writing a big sql files. The images are less than the size of MEDIUMBLOB . But writing sql to create and insert data into table is very clumsy and not maintainable.

Current implementation: I am using spring boot with flyway migration on mariadb

resources
   db.migration
      - V2__init.sql
      - V3__insert_table_data.sql

Inside V2__init.sql

CREATE TABLE IF NOT EXISTS table_name
(
    id PRIMARY KEY,
    image MEDIUMBLOB
);

Inside V3__insert_table_data.sql

INSERT INTO table_name VALUES (‘some_id’,’<very_big_image_data>’)

How i can read the image file directly into sql and insert the entry in the table. Any help and idea is appreciated :)

Are the files stored on the server machine? – Georg Richter Jun 22 '22 at 08:26 — Georg Richter, Jun 22 '22 at 08:26
No from local stored in the repository itself – Loveneesh Singh Jun 27 '22 at 10:59 — Loveneesh Singh, Jun 27 '22 at 10:59

score 1 · Accepted Answer · answered Jun 22 '22 at 15:12

This issue was something similar that our team encountered and tried a couple of attempts to use sql-based flyway migration initially

Due to the complexity of storing the blob with using SQL statements, we moved to use spring JDBC templates in a java-based migration script. https://flywaydb.org/documentation/concepts/migrations#java-based-migrations

In regards to your question, we would need to create a separate folder called db.migration within your src/main/java folder in order for flyway to pick up the java based migration and run it sequentially according to the version numbers provided in the name.

src
   main
      java
        com.example.package
        db.migration
          - V3__insert_table_data.java
      resources
        db.migration
          - V2__init.sql

Sample implementation for using spring jdbc templates and reading of image files from machine. This assumes images are checked in together with source code

public class V3__insert_table_data extends BaseJavaMigration {
    public void migrate(Context context) {
      var image = getImageFromFile("filename.svg");
      var insertStatement = "INSERT INTO table_name VALUES ('id1', '" + image + "')";

      new JdbcTemplate(new SingleConnectionDataSource(context.getConnection(), true))
                .execute(insertStatement);
    }
}

//helper method to retrieve images from file system
private String getImageFromFile(String fileName) throws IOException {
   try(FileInputStream image = new FileInputStream("pathToImage/" + fileName)){
    var bytes = logo.readAllBytes();
     
    // You might want to Base64 encode the image to prevent any sql grammar insertion error happening
    var encodedBytes = Base64.getEncoder().encode(bytes);
    return new String(encodedBytes);
  }
}

I am trying to create a table running flyway migration on Maria db and want to save images blob in the table?

1 Answers1