Store Images in the Database or File System?
I intended for this post to be about how to use the SQL Server Driver for PHP to get images in an out of SQL Server Express, but the truth is that it’s fairly easy…so easy that I eventually thought it wasn’t a very interesting topic. (Besides, this topic is covered in the driver documentation). Then I came across this thread on Stackoverflow: https://stackoverflow.com/questions/1010652/store-imagesjpg-gif-png-in-filesystem-or-db. I knew that an ongoing debate existed over whether it is better to store images in a database or in a file system (with pointers to the images stored in the database), but I didn’t realize how many folks still come up against this debate or how nuanced the debate could be. I’ll be up front: I’m not going to take a side here. My opinion is that, as with most “what is the best way” questions , the best way depends on the application. (Of course, if you have an opinion about the best way, I’d like to hear it.) What I will do, however, is show you how the FILESTREAM capabilities in SQL Server 2008 Express let you have (some of) the best of both worlds.
What is FILESTREAM?
Technically, FILESTREAM is just an attribute you can apply to a varbinary(max) column when creating a database. What it does is allow you to manage the column like any other varbinary(max) column while the data is actually stored in the file system. In other words, FILESTREAM lets you pretend that you are storing the data in the database when you are, in fact, storing the data in the file system.
An example will help make this clear. First, I’ll set up a FILESTREAM-enabled database…
How to Set Up a FILESTREAM Database
The steps below will set up a FILESTREAM-enabled database that I will use later in this post. I’m assuming you have SQL Server Express 2008 Express installed (FILESTREAM isn’t supported in earlier versions). If you don’t have it installed, you can do so here: https://www.microsoft.com/express/Database/.
1. Enable FILESTREAM support. This is a few quick steps in the Configuration Manager, outlined here: How to: Enable FILESTREAM.
2. Create a folder called data on your C:\ drive (i.e. create C:\data).
3. Create a database that supports FILESTREAM by executing the following Transact-SQL:
CREATE DATABASE FilestreamDB
ON
PRIMARY (NAME = FS1, FILENAME = 'c:\data\FilestreamDB.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM(NAME = FS2, FILENAME = 'c:\data\filestream1')
LOG ON (NAME = FS_log1, FILENAME = 'c:\data\FilestreamDB_log.ldf')
This script creates a filegroup specifically for files that contain FILESTREM data (in addition to two other regular filegroups). For more detailed information, see How to: Create a FILESTREM-Enabled Database and Files and Filegroup Architecture.
4. Create a table for storing FILESTREAM data by executing the following Transact-SQL:
CREATE TABLE StreamTable
(
ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
Tag NVARCHAR(20),
Picture VARBINARY(max) FILESTREAM
)
Note that a UNIQUEIDENTIFIER column is required in tables that have a FILESTREM column. For more detailed information, see How to: Create a Table for Storing FILESTREAM Data.
Now we are ready to insert and retrieve data.
How to Insert and Retrieve Images with PHP
The nice thing here is that regardless of whether you are using a FILESTREM-enabled database, the code for inserting and retrieving images is the same. So, consider this a tutorial on inserting and retrieving images with the SQL Server Driver for PHP.
The following code displays a form that allows you upload a picture, sends the data to the database, and then displays the image (inline). Note the example uses Windows Authentication to connect to the database.
<html>
<head></head>
<body>
<form method="post" enctype="multipart/form-data" >
Picture <input type="file" name="picture" /></br>
Tag <input type="text" name="tag" /></br>
<input type="submit" name="submit" value="Submit" />
</form>
<?php
if(isset($_POST['tag']))
{
$conn = sqlsrv_connect(".\sqlexpress", array("Database"=>"FilestreamDB"));
if ($conn === false)
{
echo "Could not connect.";
die( print_r( sqlsrv_errors(), true));
}// Define a parameterized query to insert data.
$tsql = "DECLARE @id UNIQUEIDENTIFIER;
SET @id = NEWID();
INSERT INTO StreamTable (ID, Picture) VALUES (@id, ?);
SELECT @id AS ID";// Open data as a stream.
$pic = fopen($_FILES['picture']['tmp_name'], "r");// Define the parameter array.
// Note the specification of the PHPTYPE and SQLTYPE.
$params = array(
array(
&$pic,
SQLSRV_PARAM_IN,
SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY),
SQLSRV_SQLTYPE_VARBINARY('max')
)
);// Execute the query.
$stmt = sqlsrv_query($conn, $tsql, $params);
if ($stmt === false)
{
echo "Error in statement execution.</br>";
die( print_r( sqlsrv_errors(), true));
}// Skip the rows affected result by moving to the next result.
if(sqlsrv_next_result($stmt) === false)
{
echo "Error in moving to next result.</br>";
die( print_r( sqlsrv_errors(), true));
}// Retrieve the first (only) row of the next result.
if(sqlsrv_fetch($stmt) === false)
{
echo "Error in retrieving row.</br>";
die( print_r( sqlsrv_errors(), true));
}// Get the first field of the row and assign the value to $id.
if( !($id = sqlsrv_get_field($stmt, 0)) )
{
echo "Error in retrieving field.</br>";
die( print_r( sqlsrv_errors(), true));
}// Define a parameterized query to retrieve the newly inserted data.
$tsql = "SELECT picture FROM StreamTable WHERE ID = ?";// Use the retrieved uniqueidentifier as the parameter value.
$params = array($id);// Execute the query.
$stmt = sqlsrv_query($conn, $tsql, $params);
if( $stmt === false )
{
echo "Error in statement execution.</br>";
die( print_r( sqlsrv_errors(), true));
}// Retrieve the results as a binary string.
if ( sqlsrv_fetch( $stmt ) )
{
$image = sqlsrv_get_field($stmt, 0, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY));
}
else
{
echo "Error in retrieving data.</br>";
die(print_r( sqlsrv_errors(), true));
}// Display inline image.
echo "<img src='data:image/jpg;base64,".base64_encode($image)."'/>";
}
?>
</body>
</html>
Note that the script above displays the image inline after it has been uploaded. Another way to display this that leverages the streaming capabilities of the sqlsrv driver. You could remove the code that retrieves the image and write another script for this:
<?php
$conn = sqlsrv_connect(".\sqlexpress", array("Database"=>"FilestreamDB"));
if ($conn === false)
{
echo "Could not connect.";
die( print_r( sqlsrv_errors(), true));
}// Define a parameterized query to retrieve the newly inserted data.
$tsql = "SELECT picture FROM StreamTable WHERE ID = ?";// Use the retrieved uniqueidentifier as the parameter value.
$params = array(&$_GET['ID']);// Execute the query.
$stmt = sqlsrv_query($conn, $tsql, $params);
if( $stmt === false )
{
echo "Error in statement execution.</br>";
die( print_r( sqlsrv_errors(), true));
}// Retrieve the results as a binary stream and display in the browser.
if ( sqlsrv_fetch( $stmt ) )
{
$image = sqlsrv_get_field($stmt, 0, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY));
fpassthru($image);
}
else
{
echo "Error in retrieving data.</br>";
die(print_r( sqlsrv_errors(), true));
}
?>
If you put this code into a file called GetPicture.php, then you need to change the code for displaying the image in the first script:
echo "<img src='GetPicture.php?ID=".$id."'/>";
Why Use FILESTREAM?
FILESTREAM would seem to offer the performance benefits of storing images in the file system while removing the burden of keeping metadata stored in the database in sync with the images. Of course, it’s not that simple. What if you are dealing with only small images (like thumbnails)? What if security is a top priority? What if the ability to scale out is a concern? These are all legitimate questions and and there are great arguments about what is the best way to go, database or file system. The Stackoverflow thread I mentioned earlier offers some good resources for exploring how FILESTREAM address the debate:
An MSDN article is touting the FileStream datatype in SQL 2008 as high performance.
SQL Skills has a great article with some SQL 2008 Filestream performance measurements.
Here is an article addressing varbinary vs. FileStream and performance of both datatypes.
If you are a SQL Mag subscriber, you can see a great article at SQL Mag on SQL 2008 FileStream.
I’d be interested in your thoughts as to how well you think FILESTREAM solves the “store images in the database or file system?” question.
Thanks.
-Brian
Comments
- Anonymous
May 26, 2010
Hi Brian, when the application has millions of images you believe that the best solution is store the images in database too? Thanks - Anonymous
May 26, 2010
Hi Adrian,The determining factor is not how many images your system stores but the average size of your images. If most of the images in your system are less than 1 MB in size then your database applications will perform better by keeping the images in the database. The primary reason is that the overhead of opening the image file is large in comparison to the time required to read the images pixels. By storing the images in the database the file open overhead does not occur. With images larger than 1MB the time required to open the image file is not as significant when compared to the time required to read the image pixels into memory. In this later case your database will perform better by keeping the images in the file system aka FILESTREAM.The MSDN whitepaper referenced in the link above explains all of this in more detail even adding some test run data about image size and performance when using the FILESTREAM technology. - Anonymous
May 26, 2010
Hey Adrian-Great question. My gut reaction was "it depends on the size of the images", but I talked with Kelly (who is on my team here) about your question (she's our FILESTREAM expert) to be sure. I think she's provided a more detailed answer than I could have. If her answer isn't clear, let us know.Thanks.-Brian - Anonymous
July 26, 2012
hi i write a brief article & a class for storing images in file system here is the link <a href="www.amirhoseinian.com/.../"> store & resize images in file system </a>