vidigami / backbone-sql Goto Github PK

View Code? Open in Web Editor NEW

14.0 6.0 8.0 929 KB

PostgreSQL, MySQL, and SQLite3 storage for BackboneORM

Home Page: http://vidigami.github.io/backbone-orm/backbone-sql.html

License: MIT License

CoffeeScript 100.00%

backbone-sql's Introduction

PostgreSQL, MySQL, and SQLite3 storage for BackboneORM.

BackboneSQL provides an interface for BackboneORM models to persist to SQL databases.

Please checkout the website for examples, documentation, and community!

Schema definition

Fields are specified in the models schema property. Each (non-relation) field corresponds to a database column.

Each field must have a type and may be provided with the additional options.

An auto-incrementing field named id is automatically created as the primary key for each model.

To supply options the field descriptor is passed as an array, with the first item being the field type and a settings object as the second.

Available types

All types supported by Knex are available. Add column specific options along with the settings object for the field. The first letter of the type name is optionally capitalized, while the remainder must be camelCase.

Common field options

These options may be applied to any field. Note that column options are currently only applied when the columns are created.

nullable: Defaults to true. Set to false to throw an error on null values.
indexed: Defaults to false. Set to true to create an index on the column.
unique: Defaults to false. Set to true to create a unique constraint on the column.

CoffeeScript schema example

SQLSync = require('backbone-sql').sync

class Project extends Backbone.Model

  # Database connection and table name are specified with the urlRoot
  urlRoot: 'postgres://username:password@localhost:27017/my_database/projects'

  # Schema defines the fields for the model's table
  schema:
    created_at: 'DateTime'
    type: ['Integer', nullable: false]
    name: ['String', unique: true, indexed: true]

  # Kick it off by setting the model's sync to an SQLSync
  sync: SQLSync(Project)

JavaScript schema example

var SQLSync = require('backbone-sql').sync;

var Project = Backbone.Model.extend({

  // Database connection and table name are specified with the urlRoot
  urlRoot: 'postgres://username:password@localhost:27017/projects',

  // Schema defines the fields for the model's table
  schema: {
    created_at: 'DateTime',
    type: ['Integer', {nullable: false}],
    name: ['String', {unique: true, indexed: true}]
  }
});

// Kick it off by setting the model's sync to an SQLSync
Project.prototype.sync = SQLSync(Project);

For Contributors

To build the library for Node.js and browsers:

$ gulp build

Please run tests before submitting a pull request:

$ gulp test --quick

and eventually all tests:

$ npm test

Test Variants

You can run the following fine-grained tests to resolve problems with specific application frameworks

$ gulp test-postgres
$ gulp test-mysql
$ gulp test-sqlite3

backbone-sql's People

Contributors

Stargazers

Watchers

Forkers

anodynos mateomurphy michaelbenin forivall-old-repos kmalakoff nicknaso backbone-orm jahbini

backbone-sql's Issues

Tests: self-reference tests do not pass

Relation lookups should use the orm schema relation name rather than the sql table names

With a schema like the following

foo.coffee:

class Foo extends Backbone.Model
  schema:
    bar: -> ['belongsTo', require('./bar')]
  url: 'postgres://root:[email protected]:5432/example/foos'

bar.coffee:

class Bar extends Backbone.Model
  schema:
    baz: 'Integer'
    buz: 'Integer'
  url: 'postgres://root:[email protected]:5432/example/bars'

Currently, queries look like this:

Foo.find {'bars.baz': 1, $sort: 'bars.buz'}, (err, foos) -> console.log foos

Ideally, they should look like this

Foo.find {'bar.baz': 1, $sort: 'bar.buz'}, (err, foos) -> console.log foos

Fix total_rows returned by using $unique with $page

tests are in updated backbone-orm repo

Fix test errors due to missing fields

Model.cursor (cache: false, query_cache: false) "before each" hook:
Error: ER_BAD_FIELD_ERROR: Unknown column 'foo' in 'field list', sql: insert into flats (boolean, created_at, json_data, name, updated_at) values (?, ?, ?, ?, ?), bindings: true,Wed May 21 2014 06:32:41 GMT-0700 (PDT),[object Object],flat_683,Wed May 21 2014 06:32:41 GMT-0700 (PDT)
self model relations (cache: false, query_cache: false, embed: false) "before each" hook:
Error: ER_BAD_FIELD_ERROR: Unknown column 'is_base' in 'field list', sql: insert into self_references (created_at, is_base, name, owner_id) values (?, ?, ?, ?), bindings: Wed May 21 2014 06:32:54 GMT-0700 (PDT),true,self_reference_3113,
Model.cursor (cache: false, query_cache: false) "before each" hook:
Error: column "json_data" of relation "flats" does not exist, sql: insert into "flats" ("boolean", "created_at", "json_data", "name", "updated_at") values (?, ?, ?, ?, ?) returning "id", bindings: true,2014-05-21T06:34:15.428-07:00,{"foo":{"bar":"baz"},"fizz":"buzz"},flat_14296,2014-05-21T06:34:15.428-07:00

Browserify breaks with inflection

dreamerslab/node.inflection#15

Implement unique on mysql and sqlite

Add support for websql

Model::each / eachC fails with $include

I'll be writing a test case for this in the near future

The error returned is Error: column reference "id" is ambiguous
model_each will need to be aware of joins and use the appropriate sort key, or sort will need to be aware of joins and automatically upgrade sort keys.

Invalid sql generated when using `{$in: []}` on a related field

Using the model types in the tests

Owner.cursor({'reverses.id': {$in: []}}).include('reverses').toModels (err, owners) ->
    ...

will cause the error error: syntax error at or near ")"

TypeError: Object [object Object] has no method 'type'

Got this error when trying out the SQLite backend.

TypeError: Object [object Object] has no method 'type'
    at One.module.exports.One.initialize (code\node_modules\backbone-sql\node_modules\backbone-orm\backbone-orm.js:6392:70)
    at Schema.module.exports.Schema.initialize (code\node_modules\backbone-sql\node_modules\backbone-orm\backbone-orm.js:2268:17)
    at SqlSync.initialize (code\node_modules\backbone-sql\lib\sync.js:84:19)

Can you help me?

Bug with associations on empty tables

I've run into a bug, when there's an association on an empty table. My (simplified) models:

class Wall extends Backbone.Model
  schema:
    created_at: 'DateTime'
    name: 'String'
    messages: -> ['hasMany', require('./message')]

class Message extends Backbone.Model
  schema:
    created_at: 'DateTime'
    text: 'Text'
    wall: -> ['belongsTo', require('./wall')]
    user: -> ['belongsTo', require('./user')]

When the message table is empty, I get Error: HasMany.set: Unexpected type to set messages. Expecting array: null

This seems to happen because SqlCursor._joinedResultsToJSON takes

[ { walls_created_at: null,
    walls_name: 'test wall',
    walls_id: 1,
    messages_created_at: null,
    messages_text: null,
    messages_wall_id: null,
    messages_user_id: null,
    messages_id: null } ]

and converts it to

[ { created_at: null, name: 'test wall', id: 1, messages: null } ]

rather than

[ { created_at: null, name: 'test wall', id: 1, messages: [] } ]

Port number isnt passed through

Hi guys

Noticed that the port number isnt passed through to Knex in your connection.js

e.g. if I change code to add port I can now connect to my non-standard mysql (1441) otherwise I get ECONNREFUSED as it uses default port (3306)

      if (protocol === 'sqlite3') {
        connection_info = {
          filename: database_url.host || ':memory:'
        };
      } else {
        connection_info = _.extend({
          host: database_url.hostname,

This is the line I added

          port: database_url.port,

          database: database_url.database,
          charset: 'utf8'
        }, database_url.parseAuth() || {});
      }

Could be dangerous for those hoping to reach a dev DB with a dev connection string!

Let me know if you want a pull request as would be nice to contribute something small to this great project, but looks like something simple for you guys, and Im not sure how to test

var Backbone = require('backbone');
var SQLSync = require('backbone-sql').sync;

var Room = Backbone.Model.extend({
  urlRoot: "sqlite://data.db/rooms",
  schema: {
    status: 'String',
    members: function() { return [ 'hasMany', User ] }
  }
});
Room.prototype.sync = SQLSync(Room);

var User = Backbone.Model.extend({
  urlRoot: "sqlite://data.db/users",
  schema: {
    rooms: function() { return [ 'hasMany', Room ] }
  }
});
User.prototype.sync = SQLSync(User);

var room = new Room({ status: 'test' });
room.save(function (err, ret) {
  if (err) throw err;
  console.log("save 1 finished with", err, ret);
});

However, when I run this code, it throws an error:

table rooms has no column named user_id, sql: insert into "rooms"
  ("status", "user_id") values (?, ?), bindings: test,

This implies to me that join tables aren't supported. Can you provide me some pointers as to how I might go about getting that functionality working for my project?

Thanks,
~ry

Document the calling order of ensureSchema

@marcelklehr encountered "TypeError: Cannot read property 'schema' of undefined" due to calling order of Model.db().ensureSchema(): vidigami/backbone-rest#11

Please document an example.

Paging broken when performing a join, when the table has a column with the same name as one of related table's columns

Prototype of a possible test case:

class A extends Backbone.Model
  schema:
    b: -> ['belongsTo', B]
    c: -> ['belongsTo', C]
  sync: require('backbone-sql').sync(A)
  url: 'postgres://test:[email protected]:3306/test/as'

class B extends Backbone.Model
  schema:
    name: 'String'
    as: -> ['hasMany', B]
    c: -> ['belongsTo', C]
  sync: require('backbone-sql').sync(B)
  url: 'postgres://test:[email protected]:3306/test/bs'

class C extends Backbone.Model
  schema:
    memberships: -> ['hasMany', A]
    roles: -> ['hasMany', B]
  sync: require('backbone-sql').sync(C)
  url: 'postgres://test:[email protected]:3306/test/cs'

A.cursor({$page: true, $offset:0, $limit:150, c_id:12}) # works fine
A.cursor({$page: true, $offset:0, $limit:150, c_id:12, 'b.name': 'foo'}) # fails
# Error: column reference "c_id" is ambiguous, sql: select count(*) from "as" left outer join "roles" on "as"."b_id" = "b"."id" where "c_id" = ? and "b"."name" = ?, bindings: 12,foo
A.cursor({$page: true, $offset:0, $limit:150, 'b.c_id':12, 'b.name': 'foo'}) # works; but suboptimal

$lte/$gte queries fail for Dates on MySQL

Model.find (cache: false, query_cache: false) Handles $ne for dates:
Uncaught AssertionError: all but START DATE found
Model.find (cache: false, query_cache: false) Handles $lt and $lte boundary conditions:
Uncaught AssertionError: first model found
Model.find (cache: false, query_cache: false) Handles $lt and $lte boundary conditions with step:
Uncaught AssertionError: two models found

Cordova SQLite compatibility

Hi,

is backbone-sql compatible to https://github.com/litehelpers/Cordova-sqlite-storage ?
I couldn't find any documentation to use a local Cordova SQLite db.