SQLGrid

SQLGrid is a method of storing large files inside a SQL database.

Using RethinkDB? Try rethinkdb-regrid

Features

• Efficient - Save space with automatic inline deduplication.
• Easy - Read and write files as if they were on disk with developer friendly APIs.
• Revisions - Keeps multiple versions of files.
• Byte-range Capable - Supports byte ranges to allow for streaming media.
• Consistent - Sha256 hashes are calculated when the file is written, and verified when read back out.
• Fast - Automatically caches hot data to save your database unneeded effort.

View the Changelog

---

Special thanks to Arthur Andrew Medical for sponsoring this project.

Arthur Andrew Medical manufactures products with ingredients that have extensive clinical research for safety and efficacy. They specialize in Enzymes, Probiotics and Antioxidants.

---

Installation

Supports node v8.0+

npm install --save @internalfx/sqlgrid

TL;DR

var SQLGrid = require('sqlgrid')

var bucket = SQLGrid({dialect: 'postgres'})

// initBucket creates tables and indexes if they don't exist, returns a promise.
bucket.initBucket().then(function () {
  // We are now ready to read and write files
})

API Documentation

SQLGrid([connectionOptions, bucketOptions])

Parameters

key	default	type	description
connectionOptions	{}	Object	connectionOptions is passed directly to a sequelize constructor. If connectionOptions is an instance of sequelize it will be used directly.
bucketOptions	{}	Object	Optional parameters listed below

Options

key	default	type	description
bucketName	fs	String	The name of the bucket. Table names are prefixed by this.
concurrency	10	Number	When writing/reading a file, the number of concurrent queries in flight for a given stream.
cacheSize	100	Number	The cache size in megabytes. Setting to 0 will disable caching.

returns

Bucket instance

Description

Creates a new SQLGrid bucket instance.

Example

var SQLGrid = require('sqlgrid')

var bucket = SQLGrid({dialect: 'postgres'}, {bucketName: 'mybucket'})

---

initBucket()

Parameters

none

returns

Promise

Description

Verifies required tables and indexes exist and will create them if missing.

Example

bucket.initBucket().then(function () {
  // bucket is ready for use.....
})

---

writeFile(options)

Options

key	default	type	description
filename	required	String	The name of the file.
buffer	required	Buffer	A buffer of file contents.

returns

Promise

Description

Returns a promise that resolves to the newly written file.

Example

let fileBuffer = fs.readFileSync('./myVid.mp4')

let newFile = await bucket.writeFile({filename: '/videos/myVid.mp4', buffer: fileBuffer})

---

createWriteStream(options)

Options

key	default	type	description
filename	required	String	The name of the file.

returns

WriteStream

Description

Returns a write stream for storing a file in SQLGrid.

Example

var writeStream = bucket.createWriteStream({
  filename: '/videos/myVid.mp4'
})

writeStream.on('finish', function () {
  // File is now stored in SQLGrid
})

fs.createReadStream('./myVid.mp4').pipe(writeStream)

---

getFile(options)

Options

key	default	type	description
id	Null	String	The id of the file to retrieve.
filename	Null	String	Ignored if id != null. The filename of the file to retrieve
revision	-1	Number	Ignored if id != null. The revision of the file to retrieve. If multiple files are uploaded under the same filename they are considered revisions. This may be a positive or negative number. (see chart below) Passing 'all' will return an array of all revisions.

How revision numbers work

If there are five versions of a file, the below chart would be the revision numbers

Number	Description
0 or -5	The original file
1 or -4	The first revision
2 or -3	The second revision
3 or -2	The second most recent revision
4 or -1	The most recent revision

Description

If revision is a number a promise will be returned that resolves to an object of the files information. If revision is 'all' a promise will be returned that resolves to an array of all file revisions.

Example

let file1 = bucket.getFile({id: 'ca608825-15c0-44b5-9bef-3ccabf061bab'})
let file2 = bucket.getFile({filename: 'catVideo.mp4', revision: 2})

---

readFile(options)

Options

key	default	type	description
id	Null	String	The id of the file to retrieve.
filename	Null	String	Ignored if id != null. The filename of the file to retrieve
revision	-1	Number/String	Ignored if id != null. The revision of the file to retrieve. If multiple files are uploaded under the same filename they are considered revisions. This may be a positive or negative number. (see chart below) Passing 'all' will return an array of all revisions.
seekStart	Null	Number	The start of the byte range.
seekEnd	Null	Number	The end of the byte range. If omitted the stream will continue to the end of file.

How revision numbers work

If there are five versions of a file, the below chart would be the revision numbers

Number	Description
0 or -5	The original file
1 or -4	The first revision
2 or -3	The second revision
3 or -2	The second most recent revision
4 or -1	The most recent revision

Description

If revision is a number a promise will be returned that resolves to an object of the files information and contents. If revision is 'all' a promise will be returned that resolves to an array of all file revisions and contents.

Example

let file1 = bucket.readFile({id: 'ca608825-15c0-44b5-9bef-3ccabf061bab'})
let file2 = bucket.readFile({filename: 'catVideo.mp4', revision: 2})

---

createReadStream(options)

Options

key	default	type	description
id	required	String	The id of the file to retrieve
seekStart	Null	Number	The start of the byte range.
seekEnd	Null	Number	The end of the byte range. If omitted the stream will continue to the end of file.

returns

ReadStream

Description

Returns a read stream for reading a file from SQLGrid.

Example

var readStream = bucket.createReadStream({id: 'ca608825-15c0-44b5-9bef-3ccabf061bab'})

readStream.pipe(fs.createWriteStream('./mySavedVideo.mp4'))

---

deleteFileById(options)

Options

key	default	type	description
id	required	String	The id of the file to delete

returns

Boolean, true if successful, false otherwise.

Description

Deletes a file from SQLGrid.

Example

let result = await sqlGrid.deleteFileById({id: 1})

---

deleteFileByName(options)

Options

key	default	type	description
filename	Null	String	The filename of the file to delete
revision	all	Number	The revision of the file to delete. If multiple files are uploaded under the same filename they are considered revisions. This may be a positive or negative number (see chart below). The default is to delete all revisions.

How revision numbers work

If there are five versions of a file, the below chart would be the revision numbers

Number	Description
0 or -5	The original file
1 or -4	The first revision
2 or -3	The second revision
3 or -2	The second most recent revision
4 or -1	The most recent revision

returns

Boolean, true if successful, false otherwise.

Description

Deletes a file from SQLGrid.

Example

let result = await sqlGrid.deleteFileByName({filename: 'video.mp4'})
// Deletes all revisions of video.mp4

let result = await sqlGrid.deleteFileByName({filename: 'video.mp4', revision: -1})
// Deletes only the most recent revision of video.mp4

---

Thanks

Videos used in tests acquired from Pexels