SQL ORM

Tech in T: depth + breadth‎ > ‎Tools - Business‎ > ‎JavaScript‎ > ‎node.js‎ > ‎

migration

http://stackoverflow.com/questions/21105748/sequelize-js-how-to-use-migrations-and-sync
http://stackoverflow.com/questions/18742962/add-data-in-sequelize-migration-script

$ sequelize db:migrate # Run pending migrations.
$ sequelize db:migrate:undo # Revert the last migration run.
$ sequelize help # Display this help text.
$ sequelize init # Initializes the project.
$ sequelize migration:create # Generates a new migration file.
$ sequelize version # Prints the version number.

tags: Sequelize.ARRAY(Sequelize.STRING)

Do your own geocoding for high volume and avoid paying to 3rd party providers: tiger-geocoder

look at postgres spatial indexing example here. Wow. pretty neat stuff! without index it takes Execution time: 774.878 ms after creating index Execution time: 0.452 ms!!!

$ psql -d my_test_db -U postgres
CREATE TABLE test ( position POINT );
INSERT INTO test (position) SELECT point( RANDOM() * 1000, RANDOM() * 1000) FROM generate_series(1,1000000); /*one million points*/
explain analyze select *, position <-> point(500,500) as distance from test order by position <-> point(500,500) limit 10; /* TOP-k-nearest neighbors And we can find some rows close to center of the points cloud ~700ms */
create index q on test using gist ( position ); /* try the query again and see the difference! resutls ready in 0.4ms */

===================================================

bounding box

create table MyTable (lat integer, lon integer, coor point);
insert into MyTable (lat, lon, coor) select random() * 1000, random() * 1000, point( random() * 1000, random() * 1000) from generate_series(1,1000000);
explain analyze select * from MyTable where lat < 505 and lat > 495 and lon < 505 and lon > 495 limit 10; -- using lat/long two numbers 80ms
explain analyze select * from MyTable where coor <@ box '((495,495),(505,505))' limit 10; -- using point: 24ms

create index MyTable_lat on MyTable (lat);
create index MyTable_lon on MyTable (lon);
create index MyTable_coor on MyTable using gist (coor);

explain analyze select * from MyTable where lat < 505 and lat > 495 and lon < 505 and lon > 495 limit 10; -- using lat/long two numbers 9ms
explain analyze select * from MyTable where coor <@ box '((495,495),(505,505))' limit 10; -- using point: 0.1ms

create index MyTable_latlon on MyTable (lat,lon);

explain analyze select * from MyTable where lat < 505 and lat > 495 and lon < 505 and lon > 495 limit 10; -- using lat/long two numbers -- 0.25ms

CREATE EXTENSION postgis;
SELECT * FROM MyTable ORDER BY coor::geometry <-> st_makepoint(500, 500) LIMIT 10; -- postgis operator takes very long time
SELECT * FROM MyTable ORDER BY ST_Distance(coor::geometry, ST_GeomFromText('POINT(500 500)')) LIMIT 10;

st_distance as well as st_area are not able to use indices. This is because both functions can not be reduced to questions like "Is a within b?" or "Do a and b overlap?". Even more concrete: GIST-indices can only operate on the bounding boxes of two objects.
explain analyze select * from MyTable order by point(lat,lon)::geometry <-> ST_PointFromText('point(500 500)') limit 10; -- top k nearest neighbors 2616.333 ms
explain analyze select * from MyTable order by point(lat,lon) <-> point(500, 500) limit 10; -- 1132.618 ms

Postgres+node

ORM

regarding postgres orm for nodejs that supports spatial stuff there is not much out there.
Very tricky on either of the approaches/no docs as you might endup being stuck somewhere you dont wanna be and wish you had gone bare-bone
Here are some links on that link, link, link. this guy also said ditch orm.

PLAIN nodejs postgres spatial

link, GeoJSON,

here you can see that sequelise spatial index is only for mysql

hybrid

link, link, link, nodejs+sequelize. sublink.

So, all in all looks like postgres is the way to go but 'point' feature is not ready for prime-time in sequelise. link, link, link. So we go for bare postgres queries that supports point and top-k-nearest-points as demonstrated above.

here and here, you can use sequelize like normal but then use hooks to alter table to add geometry column after sequelize migration is performed, then use proper getter/setters to keep those new columns up to date properly

Therefore you get the best of both worlds

to be able to use top-k-nearest-poitns of postgres. mysql doesn't offer top-k but there are hacks to first look into a region and then sort by lat/long distance then limit to k results. but this is dependent on region size which is fferent from a village to a dense city. I can mitigate this by exponentially increasing radius ===> up to here I can benefit from sequelize ORM!

so we go bare sql. but let's let the db take care of this so we go for postgres

To use the same design as the mysqlworkbench, we use sql power architect.

connection > Add source connection > New connection : creat a connection to postgres and one to mysql that has the latest schema design

drag from mysqlworkbench hp_db database to the right hand side.

click Auto Layout

Tools > Forward engineer to postgres

===============

sequelize seems to be the most mature of all. but it doesnt support geo. geo and its index is important for us link, link, link

here is on creating index in postgres http://fadeit.dk/blog/post/sqlalchemy-postgresql-earthdistance http://johanndutoit.net/searching-in-a-radius-using-postgres/

http://dba.stackexchange.com/questions/10744/postgresql-vs-mysql-advantages-disadvantages-with-a-spatial-component
http://stackoverflow.com/questions/14937742/mysql-vs-postgresql-spatial-application
http://stackoverflow.com/questions/3743632/gis-postgis-postgresql-vs-mysql-vs-sql-server

I believe I shoulndt go for ORM as it just makes things over complicated. specially that sequelize doesn't allow you to have your own db but you have to use sequelize modesl.

mysql is not robust in geo.

http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/
http://gis.stackexchange.com/questions/31628/find-points-within-a-distance-using-mysql

============================================================================

A FLOAT is for single-precision, while a DOUBLE is for double-precision numbers. DECIMAL data type to store exact numeric values

Sequelize.STRING // VARCHAR(255)

Sequelize.STRING(1234) // VARCHAR(1234)

Sequelize.STRING.BINARY // VARCHAR BINARY

Sequelize.TEXT // TEXT

Sequelize.INTEGER // INTEGER

Sequelize.BIGINT // BIGINT

Sequelize.BIGINT(11) // BIGINT(11)

Sequelize.FLOAT // FLOAT

Sequelize.FLOAT(11) // FLOAT(11)

Sequelize.FLOAT(11, 12) // FLOAT(11,12)

Sequelize.DECIMAL // DECIMAL

Sequelize.DECIMAL(10, 2) // DECIMAL(10,2)

Sequelize.DATE // DATETIME for mysql / sqlite, TIMESTAMP WITH TIME ZONE for postgres

Sequelize.BOOLEAN // TINYINT(1)

Sequelize.ENUM('value 1', 'value 2') // An ENUM with allowed values 'value 1' and 'value 2'

Sequelize.ARRAY(Sequelize.TEXT) // Defines an array. PostgreSQL only.

Sequelize.BLOB // BLOB (bytea for PostgreSQL)

Sequelize.BLOB('tiny') // TINYBLOB (bytea for PostgreSQL. Other options are medium and long)

Sequelize.UUID // UUID datatype for PostgreSQL and SQLite, CHAR(36) BINARY for MySQL (use defaultValue: Sequelize.UUIDV1 or Sequelize.UUIDV4 to make sequelize generate the ids automatically)

===========================================================================

sync is to create the table

Here we use sequelize:

$ npm install --save sequelize pg pg-hstore

var Sequelize = require("sequelize");
// make sure you have created the database using pg Admin III
//var sequelize = new Sequelize("postgres://postgres:postgres@localhost:5432/hoodpantry");
var sequelize = new Sequelize(
'hoodpantry', // db name
'postgres', // username
'postgres', // password
{
host: 'localhost',
dialect: 'postgres',
port:5432,
dialectOptions: {
ssl: false
},
logging: true, //verbose
pool: {
max: 5,
min: 0,
idle: 10000
}
}
);
var Person = sequelize.define('person', {
firstName: {
type: Sequelize.STRING,
},
lastName: {
type: Sequelize.STRING
}
});
Person.sync({force: true}).then(function () {
return Person.create({
firstName: 'KK',
lastName: 'Hancock'
});
}).then(
function(jane) {
console.log(jane.get({
plain: true
})
)}
);

Step by step

mkdir express-example
cd express-example
npm install express-generator
node_modules/.bin/express -f
npm install
npm install --save sequelize sequelize-cli sqlite3
node_modules/.bin/sequelize init

Created "config/config.json"
created migrations folder "./migrations".
created models folder "./models".
Loaded configuration file "config/config.json".

node_modules/.bin/sequelize model:create --name User --attributes username:string
node_modules/.bin/sequelize model:create --name Task --attributes title:string

EDIT FILES

EDIT config file accordingly
// task.js
// ...
classMethods: {
associate: function(models) {
Task.belongsTo(models.User);
}
}
// ...

// user.js
// ...
classMethods: {
associate: function(models) {
User.hasMany(models.Task)
}
}
// ...

//bin/www

//....
var debug = require('debug')('express-example');
var app = require('../app');
var models = require("../models");

app.set('port', process.env.PORT || 3000);

models.sequelize.sync().then(function () {
var server = app.listen(app.get('port'), function() {
debug('Express server listening on port ' + server.address().port);
});
});
//.....

npm install --save sequelize pg pg-hstore

http://docs.sequelizejs.com/en/latest/docs/models-definition/

var ValidateMe = sequelize.define('Foo', {
  foo: {
    type: Sequelize.STRING,
    validate: {
      is: ["^[a-z]+$",'i'],     // will only allow letters
      is: /^[a-z]+$/i,          // same as the previous example using real RegExp
      not: ["[a-z]",'i'],       // will not allow letters
      isEmail: true,            // checks for email format (foo@bar.com)
      isUrl: true,              // checks for url format (http://foo.com)
      isIP: true,               // checks for IPv4 (129.89.23.1) or IPv6 format
      isIPv4: true,             // checks for IPv4 (129.89.23.1)
      isIPv6: true,             // checks for IPv6 format
      isAlpha: true,            // will only allow letters
      isAlphanumeric: true      // will only allow alphanumeric characters, so "_abc" will fail
      isNumeric: true           // will only allow numbers
      isInt: true,              // checks for valid integers
      isFloat: true,            // checks for valid floating point numbers
      isDecimal: true,          // checks for any numbers
      isLowercase: true,        // checks for lowercase
      isUppercase: true,        // checks for uppercase
      notNull: true,            // won't allow null
      isNull: true,             // only allows null
      notEmpty: true,           // don't allow empty strings
      equals: 'specific value', // only allow a specific value
      contains: 'foo',          // force specific substrings
      notIn: [['foo', 'bar']],  // check the value is not one of these
      isIn: [['foo', 'bar']],   // check the value is one of these
      notContains: 'bar',       // don't allow specific substrings
      len: [2,10],              // only allow values with length between 2 and 10
      isUUID: 4,                // only allow uuids
      isDate: true,             // only allow date strings
      isAfter: "2011-11-05",    // only allow date strings after a specific date
      isBefore: "2011-11-05",   // only allow date strings before a specific date
      max: 23,                  // only allow values
      min: 23,                  // only allow values >= 23
      isArray: true,            // only allow arrays
      isCreditCard: true,       // check for valid credit card numbers

      // custom validations are also possible:
      isEven: function(value) {
        if(parseInt(value) % 2 != 0) {
          throw new Error('Only even values are allowed!')
        // we also are in the model's context here, so this.otherField
        // would get the value of otherField if it existed
        }
      }
    }
  }
})

isInt: {
  msg: "Must be an integer number of pennies"
}

or if arguments need to also be passed add anargsproperty:

isIn: {
  args: [['en', 'zh']],
  msg: "Must be English or Chinese"
}

var Pub = Sequelize.define('Pub', {
  name: { type: Sequelize.STRING },
  address: { type: Sequelize.STRING },
  latitude: {
    type: Sequelize.INTEGER,
    allowNull: true,
    defaultValue: null,
    validate: { min: -90, max: 90 }
  },
  longitude: {
    type: Sequelize.INTEGER,
    allowNull: true,
    defaultValue: null,
    validate: { min: -180, max: 180 }
  },
}, {
  validate: {
    bothCoordsOrNone: function() {
      if ((this.latitude === null) !== (this.longitude === null)) {
        throw new Error('Require either both latitude and longitude or neither')
      }
    }
  }
})



In this simple case an object fails validation if either latitude or 
longitude is given, but not both. If we try to build one with an 
out-of-range latitude and nolongitude, raging_bullock_arms.validate() might return
{
  'latitude': ['Invalid number: latitude'],
  'bothCoordsOrNone': ['Require either both latitude and longitude or neither']
}

Subpages (2): Associations Model Definition

Comments

What is this site?

Table of Contents

SQL ORM

	Search this site