As I have built PHP and ColdFusion applications over the years, I have had to deal with image and file uploading from the application. Browsers were not designed for moving files from a client to a server; specifically, browsers were designed to receive files from a server to a client. To move files from a client to a server, an FTP program is used. (My favorite FTP client is WinSCP.) Now, a form can be written and used to upload a picture, movie, or other file from the client (you) to the server. You probably do this quite frequently with Flickr, Youtube and so forth. It is convenient from the enduser point of view because it removes the need to learn new software (the ftp client) and prevents having to get an administrator to create a username and password for each user on the server. Plus, by managing the files through the web application instead of the FTP program, you can programmatically control what is being allowed onto the server so that your user doesn’t maliciously upload an executable designed to damage the server rather than that family vacation photograph.
As a programmer, I have to make decisions about where to store these uploaded files. A common approach is to have a directory on the webserver which cannot be accessed by a browser (that is, the only way to get to the file is through the programming). The filename is then stored in the database. As an independent consultant, I work with different programmers/designers with varied skillsets and differing philosophies on coding. Some coders, like Eric Wise, actually prefer to store the files in the database itself. Eric claims:
…this is pretty sloppy and difficult to manage especially if for some reason you want to reorganize the data… [Source, Eric Wise, Images, Thumbnails, SQL Server, and ASP .NET – Level 200]
Eric describes a process of storing images in SQL Server 2005, automatically compressing the images, and generating thumbnails using ASP .NET. THis is interesting to me as I am wanting to learn C# to add to my skillsets.
I am concerned about database size and performance decreased caused by storing images/files in a database rather than storing the file in a directory and only storing the filename in the database. Jeffrey Palermo, a commenter on Eric Wise’s post, expresses the same concern:
Jeffrey Palermo said:
I’ve considered doing a photo album this way, but the main drawback is the size it makes the database. I have about 4GB of images. Do I really want that much in my database just for images. That also makes the bandwidth between web and db server highly used. Perhaps it’s no big deal, but to date I’ve kept my photos on the web server. Please post if you find no issues with the size of the data in the database when you have _lots_ of pictures there.
And Eric responds:
Eric Wise said:
Jeff,
With the compression tool I referenced, the original image shown was over 2MB, now it’s 165KB.
Having many large files would definately cause a performance issue, but with compression and the fact that it will probably be rare to have more than 2-3 pictures of an asset I’m not all that concerned about it.
What is your approach to storing images from a web application? See also.
I typically store files in the web tree rather than the database. It removes a whole extra level of complexity out of an application if you’re just passing around a URL/filename, and letting the browser and the webserver handle the messy details of getting the image. I honestly don’t want to spend the time figuring out how to properly store and retrieve image data in the database.
This, of course, means you have to plan for how you’re going to organize files on the server–if you have different types of file uploads, it helps to have a good directory structure for this. It also means (if your application is handling the file uploads) that you have to plan for handling duplicate file names, keeping the directory clean as image files get cleared out, and other bits of maintenance. (You can usually let the latter go, unless you’re getting a ton of traffic.)
Assuming the files you’re uploading shouldn’t be publicly available to all users, you also have to have some security, which may be as simple as requiring a login, or making certain images only available to certain users. The latter case can get tricky depending on what framework/platform you’re using, whereas it would be easier to control if you stored all the images in the database.
I’ve implemented applications that store images both in the file system and in a database with up to a terabyte of image data. There’s little difference in performance, and hardly any difference in complexity (if you’re using ASP.NET).
Surprisingly, there’s more disk overhead in storing images in the filesystem due to NTFS file allocation scheme (and even more so if you’ll split your images to subdirectories). In SQL Server, the overhead for storing images is a 16-byte blob pointer to an address in the image file area. When storing images in the file system, there’s also the possibility that the database and the file system go out of sync, leaving “stale” image data on the drive.
However, if you’re using SQL Server 2005, you’ll want to store images in a separate filegroup from the other table columns (CREATE TABLE … TEXTIMAGE_ON …). This way you can place the filegroup containing images on a separate physical drive from the primary filegroup for increased performance.
Having images in the database can be an advantage or a drawback as far as backups are concerned; on one hand, all image data will be contained in a database backup (instead of having to sync database backups with filesystem backupx), on another, the backup file size may grow unmanageable.
Ironically, I just got a project where I have to store images, and it’s going to be using custom application pages in SharePoint, so now I’m going to *have* to store images in the database.
Yes I Will