sqlgrid v0.6.0
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 v7.6+
npm install --save sqlgridTL;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.mp4Thanks
Videos used in tests acquired from Pexels