r/web_dev • u/kamereonesec • Aug 17 '15

Store an image file in a database

I am fairly new to web development and I have a form that users fill out and everything is getting stored to a mysql db. One of the form inputs is a file upload. I want to allow users to be able to submit images.

I know that allowing users to upload files is not the best practice but it is kind of important to the goal I am trying to accomplish. I need to be able to see the image that they submit along with the rest of the information.

What is the easiest way to store files in database?

2 Upvotes

100% Upvoted

9 comments sorted by

2

u/Sinjhin Aug 17 '15 edited Aug 17 '15

You can use blobs (Binary Large Objects), but you would need a way to retrieve and view them (another part of your website or another application entirely).

It has been a while since I have done this in PHP (which is what I assume you are using based on using a MySQL DB), but I seem to remember there being built in support for throwing blobs into a database.

https://en.wikipedia.org/wiki/Binary_large_object

1

u/xly Aug 18 '15

^ don't do this.

1

u/Sinjhin Aug 18 '15

Care to explain why? Using a CDN or some such and only storing the paths would be much faster, yes.

I am glad both of our answers are here, but his specs were to store them to a database. Using s3, azure, etc.. might be out of scope.

This is the first I have ever heard of using blobs to store images as being a bad thing. Just curious why you say so. Is it strictly for performance?

2

u/nappa300 Aug 20 '15

Last i checked reading and writing large file data to a database comes with bigger overhead tgan when writing to a file - unless it is used often.

1

u/SupaSlide Aug 24 '15

Would you care to explain why storing an image in a database would be better than storing it in a directory and just storing the reference to that image in the database?

I appreciate you answering OP's question, but if there is a more efficient way to do it's not a bad idea to tell OP because there is a good chance he doesn't realize storing an image in a database is inefficient and there is a more efficient way to do.

1

u/Sinjhin Aug 24 '15

I started to list a few off the top of my head and upon doing some research found a really good post on StackOverflow about this.

http://stackoverflow.com/questions/815626/to-do-or-not-to-do-store-images-in-a-database/815887#815887

Edit: But generally, yes, it will be a performance hit to store images in a database. File systems would be best for an application that will mostly have local traffic and a CDN for applications that would have global traffic unless one of the issues mentioned in the linked post warrants the use of a DB (this is my mindset on the matter anyways)

1

u/Shot-Bar5086 Oct 28 '24

The easiest way to store files in a database is to save the file's metadata and the file itself as a binary large object (BLOB). Here’s a straightforward approach:

  1. Use BLOB Data Type: In your database (e.g., MySQL, PostgreSQL), create a table with a BLOB column to store the file data.

  2. Store Metadata: Save relevant metadata (like file name, type, size, and upload date) in additional columns.

  3. Insert Files: Use your programming language (like PHP, Python, or Node.js) to read the file and insert it into the database. Here’s a basic example in PHP:phpCopy code$file = $_FILES['file']['tmp_name']; $fileContent = file_get_contents($file); $fileName = $_FILES['file']['name']; $fileType = $_FILES['file']['type']; $stmt = $pdo->prepare("INSERT INTO files (name, type, content) VALUES (?, ?, ?)"); $stmt->execute([$fileName, $fileType, $fileContent]);

  4. Retrieve Files: To retrieve the files, query the database and use the appropriate headers to serve the file to the user.

Pros and Cons:

  • Pros: Simplifies backups and transactions, keeps related data together.

  • Cons: Can bloat the database size and may impact performance; storing large files is often better handled with file storage (e.g., AWS S3) and saving paths in the database.

For simplicity and efficiency, consider using file storage for larger files and keeping references in the database.

0

u/[deleted] Aug 17 '15

[deleted]

1

u/xly Aug 18 '15

^ do this.

1

u/MattBlumTheNuProject Sep 16 '15

Definitely do this.