I wish to upload a file or multiple files (PDF, JPG., etc…) directly into a MySQL Database. I do not wish to store them in a file directory. Any suggestions? And, yes, I am aware of the pros and cons of doing it in this manor.
About 15 years ago I decided to do the same against the advice of many seasoned developers. To this day, I regret that I ever did. Bloated and slowing down of the database.
Still want to do it? Have a look at this discussion: https://www.quora.com/Can-the-MySQL-database-store-JPEG-images-videos-and-PDF-documents-How
1 Like
I cannot in good conscience explain you how to do it. I wouldn’t be able to rest at nights.
1 Like
This is not supported in Wappler. Use the file uploader to upload your files in a folder on your server and store references to them in your database.
As others have already suggested - what you are trying to do is not really a good practice.
I would like to thank everyone for their responses. It was appreciated and expected. I just wanted to say that you are all correct in your thinking. Storing files on the server would eliminate bloat in the database. But in some circumstances it would not be necessary. Let me share an experience.
Saving Image to a table:
I have recently created a PHP/MySQL app which stores PDFs/Word files in a MySQL table (as big as 40MB per file so far).
Pros:
• Uploaded files are replicated to backup server along with everything else, no separate backup strategy is needed (peace of mind).
• Setting up the web server is slightly simpler because I don’t need to have an uploads/ folder and tell all my applications where it is.
• I get to use transactions for edits to improve data integrity - I don’t have to worry about orphaned and missing files
Cons:
• mysqldump now takes a looooong time because there is 500MB of file data in one of the tables.
• Overall not very memory/CPU efficient when compared to filesystem
I’d call my implementation a success, it takes care of backup requirements and simplifies the layout of the project. The performance is fine for the 20-30 people who use the app.
Saving to a File-System
I generally store files on the file-system, since that’s what its there for, though there are exceptions. For files, the file-system is the most flexible and performant solution (usually).There are a few problems with storing files on a database - files are generally much larger than your average row - result-sets containing many large files will consume a lot of memory. Also, if you use a storage engine that employs table-locks for writes (ISAM for example), your files table might be locked often depending on the size / rate of files you are storing there.
In summary, there are situations where storing files in the database is okay but it comes with caveats. Best practice in 99% of the cases are to store files on a server and save the path in the database. The circumstances in which storing the image file in a database are: 1. if you have small image sizes, 2. You don’t have 100s of images stored in you database, 3. You are not having search on the image.
1 Like
It seems you know the caveats of doing it. I haven’t done it myself as I am a true believer of letting the filesystem do what it does best(store and serve files).
It might not be straightforward and you may have to bypass Wappler’s UI at some point but it can be done for sure. After all you can always resort to vanilla php and store the blobs in your database connecting the pieces with Wappler’s UI and framework.
Pretty sure all the bits and pieces of information you need to accomplish that are scattered around the forum.
I just put up a quick fiddle to encode a file via JS.
Here you have it.
https://jsfiddle.net/819vxguf/
There are other ways and probably better but I didn’t have time for more. If you have a form in Wappler I would probably call a js function on submit/click that encodes the file using the code in my fiddle and then I would probably call(xhr?) a server connect file so I can upload the blob to the database.
As I said there are probably dozens of ways to accomplish this but it can be done for sure. I can’t think of and out-of-the-box solution using Wappler UI. I believe you will need to resort to coding at some point.
Thank you, Jon. I appreciate your response.