Quick StartAPI Reference

DSSqlAdapter

Postgres/MySQL/MariaDB/SQLite3 adapter for js-data

Suggest Edits

js-data-sql Slack Status npm version Circle CI npm downloads Coverage Status Codacy

Quick Start

npm install --save js-data js-data-sql

The adapter will look for a table option on your resource definitions, or default to using the name of your resource to select from the right table.

var JSData = require('js-data');
var DSSqlAdapter = require('js-data-sql');

var store = new JSData.DS();
// see http://knexjs.org/#Installation-client for more details on the configuration options 
var adapter = new DSSqlAdapter({
  client: 'mysql', // or "pg" or "sqlite3"
  connection: {
    host: '123.45.67.890',
    user: 'ubuntu',
    password: 'welcome1234',
    database: 'db1'
  }
});

store.registerAdapter('sql', adapter, { default: true });

// "store" will now use the Sql adapter for all async operations

Community & Links

API

new DSSqlAdapter(options)

Options

client

Can be "mysql", "pg" or "sqlite3"

connection

An object containing connection details such as host, port, etc. See http://knexjs.org/#Installation-client for more details on the configuration settings

📘

Need help?

Want more examples or have a question? Post on the Slack channel or the mailing list then we'll get your question answered and probably update this wiki.

create(resourceConfig, attrs)

Creates a new item in your selected Sql database.

Returns a promise.

ArgumentTypeDescription
resourceConfigobjectThe Resource definition created by DS#defineResource.
attrsobjectThe properties from which to create the item.
Examples
var JSData = require('js-data');
var DSSqlAdapter = require('js-data-sql');

var store = new JSData.DS();
var adapter = new DSSqlAdapter({
  client: 'mysql',
  connection: {
    host: '123.45.67.890',
    user: 'ubuntu',
    password: 'welcome1234',
    database: 'db1'
  }
});

store.registerAdapter('sql', adapter, { default: true });

//will use the `document` table in your sql database
var Document = store.defineResource('document');

// bypass the data store
adapter.create(Document, { author: 'John' }).then(function (document) {
  document; // { id: 5, author: 'John' }
});

// Normally you would just go through the data store
Document.create({ author: 'John' }).then(function (document) {
  document; // { id: 5, author: 'John' }
});

destroy(resourceConfig, id)

Delete an item from your selected Sql database.

Returns a promise.

resourceConfigobjectThe Resource definition created by DS#defineResource.
idstring or numberThe primary key of the item to destroy.
Examples
var JSData = require('js-data');
var DSSqlAdapter = require('js-data-sql');

var store = new JSData.DS();
var adapter = new DSSqlAdapter({
  client: 'mysql',
  connection: {
    host: '123.45.67.890',
    user: 'ubuntu',
    password: 'welcome1234',
    database: 'db1'
  }
});

store.registerAdapter('sql', adapter, { default: true });

var Document = store.defineResource('document');

Document.inject({ id: 5, author: 'John' });

// bypass the data store
adapter.destroy(Document, 5);

// Normally you would just go through the data store
Document.destroy(5);

destroyAll(resourceConfig, params)

Delete a collection of items from your selected Sql database.

Returns a promise.

ArgumentTypeDescription
resourceConfigobjectThe Resource definition created by DS#defineResource.
paramsobjectQuery parameters for selecting the items to destroy. See Query Syntax. Default: { }
Examples
var JSData = require('js-data');
var DSSqlAdapter = require('js-data-sql');

var store = new JSData.DS();
var adapter = new DSSqlAdapter({
  client: 'mysql',
  connection: {
    host: '123.45.67.890',
    user: 'ubuntu',
    password: 'welcome1234',
    database: 'db1'
  }
});

store.registerAdapter('sql', adapter, { default: true });

var Document = store.defineResource('document');

var params = {
  author: 'John'
};

// bypass the data store
adapter.destroyAll(Document, params);

// normally you would go through the data store
Document.destroyAll(params);

find(resourceConfig, id[, options])

Get an item from your selected Sql database.

Returns a promise.

ArgumentTypeDescription
resourceConfigobjectThe Resource definition created by DS#defineResource.
idstring or numberThe primary key of the item to retrieve.
optionsobjectOptional configuration.
options.witharray of stringsArray of relation names or relation field names to eager load with the retrieved item. Supports nested relations, e.g. adapter.find(Post, 1, { with: ['user.organization'] })
Examples
var JSData = require('js-data');
var DSSqlAdapter = require('js-data-sql');

var store = new JSData.DS();
var adapter = new DSSqlAdapter({
  client: 'mysql',
  connection: {
    host: '123.45.67.890',
    user: 'ubuntu',
    password: 'welcome1234',
    database: 'db1'
  }
});

store.registerAdapter('sql', adapter, { default: true });

var Document = store.defineResource({
  name: 'document',
  relations: {
    belongsTo: {
      user: {
        localKey: 'userId',
        localField: 'user'
      }
    }
  }
});
var User = store.defineResource({
  name: 'user',
  relations: {
    hasMany: {
      document: {
        foreignKey: 'userId',
        localField: 'documents'
      }
    },
    belongsTo: {
      organization: {
        localKey: 'organizationId',
        localField: 'organization'
      }
    }
  }
});
var Organization = store.defineResource({
  name: 'organization',
  relations: {
    hasMany: {
      user: {
        foreignKey: 'organizationId',
        localField: 'users'
      }
    }
  }
});

// bypass the data store
adapter.find(Document, 5, { with: ['user'] }).then(function (document) {
  document; // { id: 5, content: 'foo', userId: 1, user: {...} }
  document.user; // { id: 1, name: 'John Anderson' }
});

// Normally you would just go through the data store
Document.find(5, { with: ['user', 'user.organization'] }).then(function (document) {
  document; // { id: 5, content: 'foo', userId: 1, user: {...} }
  document.user; // { id: 1, name: 'John Anderson', organizationId: 10 }
  document.user.organization; // { id: 10, name: 'Company' }
});

findAll(resourceConfig, params[, options])

Get a collection of items from your selected Sql database.

Returns a promise.

ArgumentTypeDescription
resourceConfigobjectThe Resource definition created by DS#defineResource.
paramsobjectQuery parameters for selecting the items to retrieve, limit, sort, etc. See Query Syntax. Default: { }
optionsobjectOptional configuration.
options.witharray of stringsArray of relation names or relation field names to eager load with the retrieved items. Supports nested relations, e.g. adapter.findAll(Post, null, { with: ['user.organization'] })
Examples
var JSData = require('js-data');
var DSSqlAdapter = require('js-data-sql');

var store = new JSData.DS();
var adapter = new DSSqlAdapter({
  client: 'mysql',
  connection: {
    host: '123.45.67.890',
    user: 'ubuntu',
    password: 'welcome1234',
    database: 'db1'
  }
});

store.registerAdapter('sql', adapter, { default: true });

var Document = store.defineResource({
  name: 'document',
  relations: {
    belongsTo: {
      user: {
        localKey: 'userId',
        localField: 'user'
      }
    }
  }
});
var User = store.defineResource({
  name: 'user',
  relations: {
    hasMany: {
      document: {
        foreignKey: 'userId',
        localField: 'documents'
      }
    },
    belongsTo: {
      organization: {
        localKey: 'organizationId',
        localField: 'organization'
      }
    }
  }
});
var Organization = store.defineResource({
  name: 'organization',
  relations: {
    hasMany: {
      user: {
        foreignKey: 'organizationId',
        localField: 'users'
      }
    }
  }
});

var params = {
  size: {
    '>': 15000000
  }
};

// bypass the data store
adapter.findAll(Document, params).then(function (documents) {
  documents[0].size; 15063940
});

// normally you would go through the data store
Document.findAll(params).then(function (documents) {
  documents[0].size; 94839687
});

adapter.findAll(Document, params, { with: ['user', 'user.organization'] }).then(function (documents) {
  documents[0].size;
  documents[0].user; // {...}
  documents[0].user.organization; // {...}
});

update(resourceConfig, id, attrs)

Update an item in your selected Sql database.

Returns a promise.

ArgumentTypeDescription
resourceConfigobjectThe Resource definition created by DS#defineResource.
idstring or numberThe primary key of the item to retrieve.
attrsobjectThe attributes with which to update the item.
Examples
var JSData = require('js-data');
var DSSqlAdapter = require('js-data-sql');

var store = new JSData.DS();
var adapter = new DSSqlAdapter({
  client: 'mysql',
  connection: {
    host: '123.45.67.890',
    user: 'ubuntu',
    password: 'welcome1234',
    database: 'db1'
  }
});

store.registerAdapter('sql', adapter, { default: true });

var Document = store.defineResource('document');

Document.inject({ id: 5, author: 'John' });

// bypass the data store
adapter.update(Document, 5, { author: 'Johnny' }).then(function (document) {
  document; // { id: 5, author: 'Johnny' }
});

// Normally you would just go through the data store
Document.update(5, { author: 'Johnny' }).then(function (document) {
  document; // { id: 5, author: 'Johnny' }
});

updateAll(resourceConfig, attrs, params)

Update a collection of items in your selected Sql database.

Returns a promise.

ArgumentTypeDescription
resourceConfigobjectThe resource definition created by DS#defineResource.
attrsobjectThe attributes with which to update the items.
paramsobjectQuery parameters for selecting the items to update. See Query Syntax. Default: { }
Examples
var JSData = require('js-data');
var DSSqlAdapter = require('js-data-sql');

var store = new JSData.DS();
var adapter = new DSSqlAdapter({
  client: 'mysql',
  connection: {
    host: '123.45.67.890',
    user: 'ubuntu',
    password: 'welcome1234',
    database: 'db1'
  }
});

store.registerAdapter('sql', adapter, { default: true });

var Document = store.defineResource('document');

Document.inject({ id: 5, author: 'John' });
Document.inject({ id: 6, author: 'John' });

// bypass the data store
adapter.updateAll(Document, { author: 'Johnny' }, { author: 'John' }).then(function (documents) {
  documents[0]; // { id: 5, author: 'Johnny' }
});

// Normally you would just go through the data store
Document.updateAll({ author: 'Johnny' }, { author: 'John' }).then(function (documents) {
  documents[0]; // { id: 5, author: 'Johnny' }
});

📘

License

The MIT License (MIT)

Copyright (c) 2014-2015 Jason Dobry

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Updated less than a minute ago