How do I store the location of an image in a database?

Question

I have a list of colors, with textures that I want to show to particular users, so I need to load the image of the colors that a particular user has, the color's information is contained in a ObjecDTO and one of the properties is its image path. My question is how should I store the image path in the database, are there any special rules, for example since the database and the image file are on the same server should I store a complete file URL, or a relative url, what characters should I escape etcetera, or are there better approaches than mine?

I've searched the web but mostly point to storing BLOBs in the database, which I've found to be a bad practice.

Any guidance is really appreciated.

regards Tristian.

Answer 1

Sharing my thoughts over one intermediate solution that we have chosen for our application. I will discuss the pros/cons of some suggestions discussed here that will help to understand my intermediate solution.

Thoughts over saving specific domain path in Database

• If we save the specific domain path to image. (like: /user/{id}/avatar/img.png)

  • Pros: If we want to move the complete path to a new storage and we have a single (or countable) end points consuming our database, then it's easy, just move the images with same structure to new storage, and inform all the dependent applications/APIs to update the storage url to new storage url.

  • Cons: If suppose, we want to migrate to a new storage and we have multiple APIs interacting with our database, or we have exposed to third parties and internally they are redistributing to other APIs.

    Then it's not going to be easy if you have such dependencies while saving relative paths. You have to share the blob path, whenever you update the storage, to the application/third party to enable them to render your image. It will create a heavy dependency for all the APIs consuming your database directly. If your API, is publically exposed, then then you have to support and sync both the storages until all the APIs have updated.

Thoughts over saving complete path in Database

• If we save the complete path of the url in the database. (like: www.example.com/user/{id}/avatar/img.png)
  • cons: obviously, you have created a heavy dependency of your storage url.
  • pros: The application consuming your API or APIs consuming your database directly, need not to worry about the path of the image.

Intermediate solution:

• Today, I had a connect with my team to solve this problem for an application which has around 1 TB of database and heavy dependency on multiple APIs and public APIs. After observing both the pros/cons of both the scenarios, We thought of an intermediate solution, like this:

  • We will create a new rewrite url and point this url to our actual blob url. What does this mean: It means, we will have a url in between, that will just be mapped to the actual storage url. So, even if we save the complete path in the DB, we will save with the newly created url, and if in future we have to migrate to a new storage. Then from backend we will just update the rewrite url to point to new storage.
  • So, for our application we have decided to go with saving this complete intermediate url. This will help us to reduce the integration dependencies, where multiple APIs are calling our database directly. and if in future we have to migrate to new storage then we just need to update the rewrite url from backend.

Answer 2

Create a column called: image_path VARCHAR(255) NOT NULL

And it would contain data like this:

~/images/image1.jpg

Answer 3

This is really been asked a lot:

store image in database or in a system file?

Answer 4

I would really suggest to store relative paths and put the remaining as a config. That way, you can migrate more easily. For the datatype, I suggest something along the line of varchar(255) and ensure that restriction in your code.

And don’t forget to set it as UTF-8 if it’s a user field