Introducing JStORM

UPDATE:Adobe AIR support is in svn trunk

I am happy to announce the first beta of JStORM,a complete rewrite of GearsORM to support multiple backends,currently supporting Jaxer(MySQL and Sqlite) and Google Gears(Sqlite).

This version is a complete rewrite of GearsORM,it changed the way it works internally and also the API.

The first change is that every model have it own connection to the database,this is in order to enable different models connected to different databases.

Another interesting change is the JStORM.Query class,this class allows for easily querying the models.
a query instance can be obtain by calling the all method of the model,lets define a simple model:

var Person = new JStORM.Model({
firstName:new JStORM.Field({type:"String",maxLength:25}),
lastName:new JStORM.Field({type:"String",maxLength:25}),

For example to print all the persons in the database:


What the all function do is to create a new instance of JStORM.Query for Person model.
Other function that return a query is filter,filter return a query filtered by a criteria,for example:

var katzFamily = Person.filter("lastName = ?","Katz");

A thing to note about Query is that it is lazy evaluated,so invoking the method filter or all doesn`t query the database until you start iterating over the result.

Queries can be filtered many times to create a AND expression between criterias(note that each call to filter create a new query object):

var uriels = Person.filter("firstName = ?","Uriel");
var urielKatzs = uriels.filter("lastName = ?","Katz");
console.log("People with first name Uriel: " + uriels.count());
console.log("People with name Uriel Katz: " + urielKatzs.count());

Query objects have various methods for example the count method:

var numberOfMembers = Person.filter("lastName = ?","Katz").count();
console.log("Katz family have " +numberOfFamilyMembers + " members");

The count method return the number of objects that matched the criteria of the Query object,it use the SQL COUNT function.

There is also the remove method,which delete all the objects that match the criteria:

Person.filter("firstName = ?","Uriel").remove();

The remove method uses the SQL DELETE statment.

This early release is to show the new api and support for Jaxer,there is some stuff lacking that were in GearsORM:

  • Many to many relations - still can be done but need to define tables manually(will be added in 0.4)
  • Foreign key constrains - this are a pain in Sqlite since they are implemented with triggers,i am considering dropping support for this

I can`t show the all api in this post,currently there is no documentation,so you can see examples for api usage in the unit tests.

In the next weeks i will focus on testing,writing documentation and improving JStORM for final release, keep checking the blog for updates on JStORM.



P.S.:As always bug reports and fixes are welcome,also if anyone wants to help either with the code and documentation(especially in this,since my english isn`t that good).

Google Gears ORM v0.2

UPDATE:a new version is here.

New features in this version:

  • transactions - used inside GearsORM itself
  • self relations(i.e. a relation to the same table/model) - very useful when working with Tree-like structures in the database
  • count method - so now u can do Person.count() instead of

The most important update in this version is of course transaction support.
SQLite is known for really bad write preformence when not working with transactions.
While developing transaction support for GearsORM i had to write a test,in that test it execute 100 inserts and 100 updates,this test take about 15 seconds for the inserts and about 10 seconds for the updates without transactions,when using transactions for each set it takes about 377ms for the inserts and 200ms for the updates that is about 39 times faster!

To use transactions just wrap all the code that you want to be in a transaction into a function and call GearsORM.Transaction with that function like this:

  new Person({name:"John"}).save();
  new Person({name:"Doe"}).save();

This will execute a BEGIN query before running the function and a COMMIT query if everything went ok ,else it will execute ROLLBACK.
This make all the queries between BEGIN to COMMIT to run in a transaction and to run faster :)

Self relations are a great way to use a tree structure in a database,this is how it look:

var TreeNode = new GearsORM.Model({
		data:new GearsORM.Fields.String({maxLength:25}),
		parent:new GearsORM.Fields.OneToMany(
		childs:new GearsORM.Fields.ManyToOne(

this model have both ManyToOne and OneToMany,the only required is OneToMany since it is a db field,the ManyToOne comes only to provide a easy way to get the child nodes.
one new option added to OneToMany is the allowNull this add a way to say that it is not related to any one,this is very important with self relations since the first row inserted to a table with self relation can`t be related to any row because there is no rows!
lets create a few nodes:

var root = new TreeNode({data:"i am the root",parent:null}).save();
var node1 = new TreeNode({data:"i am node 1",parent:root}).save();
var node2 = new TreeNode({data:"i am node 2",parent:root}).save();

in this example we have the root node which doesn`t have a parent,node1 and node2 both have a parent which is root.
this means that both node1 and node2 are childs of root.
getting the childs of root is like using a normal ManyToOne relation:

this will output to the console(if using firebug):

i am node1 i am node2.

we can also delete from a self relation:

root.childs.remove("data = ?",["i am node1"]);

this will remove node1 but it will make sure that node1 was related to root,if there was another node that had data set to i am node1 it wouldn`t be deleted since it is not a child of root.

a now for count,count is method to retrive how much objects(i.e. rows) match a query.
for example Person.count() will return how much pepole there is in the database,count can also use a where clause,like this:

Person.count("lastName = ?",["Katz"])

this will return the number of pepole that have the lastName set to Katz.
the difference between doing to using Person.count() is that count use the COUNT sql function therefore it is alot more efficient than doing a select then creating a array and then getting the count.

link to packed version here.
more information in the project homepage.
feature requests are always welcome :) (as well bug reports)

Google Gears ORM v0.1.1

UPDATE:a new version is here.

this is a minor update to GearsORM that fixes two bugs,one small bug reported by Evgeny Tyazhev and other known bug in which m2m tables forigen keys aren`t enforced.

another nice thing is a new utility class(or should i say object) GearsORM.Introspection which contain a model(sqliteMasterModel) which maps to sqlite_master table which provide information about the schema,also there is a function GearsORM.Introspection.doesTableExist used to check if a table exist when creating m2m triggers.

in this version i moved from JSDoc to NaturalDocs and added a auto-generated documentaion,also i finally setup up the svn with apache.

so with no futher delay,the links:

some things for the next version:

  • transactions - this is very important when working with SQLite,sqlite is very very slow when doing INSERT/UPDATEs so putting every thing in one transaction make it around 100 times faster(the number might not be exact but from my experience it is a big improvement)
  • ManyToOne relations to the same model - i have a proof of concept version working need some polish this is very helpful when need to deal with Tree like structures in database.
  • some code cleanup and resize(current size is 30Kb with documentaion unpacked and packed it is 8Kb)

feature requests are always welcomed.
big thanks goes to Evgeny Tyazhev for helping me in finding the bugs and fixing them.

Google Gears ORM v0.1

UPDATE:a new version is here.

So after more than a month since i released a proof of concept of a ORM for Google Gears,i am happy to release the first(official) version of GearsORM .

This is a complete rewrite of the code,this version support relations(both many to one and many to many with automatic table creation),easy loading and saving objects from JSON,real foreign keys(SQLite doesn`t support them) using triggers and many more stuff.

Since i am a big fan of examples i will try to show the new api by a example(the code is fully documented,but i still don`t have a official documentation).

for this example we will make a blog like system(Post,Comment and Tag models).
let define the models first:

var Post = new GearsORM.Model({
		title:new GearsORM.Fields.String({maxLength:256}),
		body:new GearsORM.Fields.String({maxLength:5000}),
		tags:new GearsORM.Fields.ManyToMany({related:"Tag"}),
		comments:new GearsORM.Fields.ManyToOne({
var Comment = new GearsORM.Model({
		name:new GearsORM.Fields.String({maxLength:50}),
		email:new GearsORM.Fields.String({maxLength:75}),
		body:new GearsORM.Fields.String({maxLength:1000}),
		post:new GearsORM.Fields.OneToMany({
var Tag = new GearsORM.Model({
		name:new GearsORM.Fields.String({maxLength:25}),
		posts:new GearsORM.Fields.ManyToMany({related:"Post"})	

now we have three models: Post,Comment and Tag.
a model have a function used to create the table for the model,if the table already exist nothing will happen.
so my recommendation is to put the calls to createTable right after models definition,like this:


This will insure that all the tables exist(but this doesn't insure that the table definition is correct,in case that the table already exist).
Now that we have everything configured(in terms of models and tables) we can start to insert some data.
For example to create a tag:

var testTag = new Tag(); = "TestTag";;

This will create a new Tag object,set its name and save it to the database(this happen when you call the save method of the Tag instance).
there is a shorter way to create a instance and set its values,you do it like this:

var testTag = new Tag({name:"TestTag"}).save();

Now for relations:

var myTestPost = new Post({
  title:"my test post",
  body:"my test post body"

This will create a new post and add a relation to testTag.
myTestPost.tags is a ManyToMany relation field,which have these methods:

  • add - create a relation between the object to other object(in our case between myTestPost and testTag)
  • remove - remove a relation between the object to other object
  • select - return a ResultIterator,can be filtered using a where expression like this:" <> 'NotPublish'")

select anywhere in GearsORM return a ResultIterator which is a helper class to iterate over the results of a select.
ResultIterator define these methods:

  • next - return the next object in the ResultSet as a instance of the model,return false when there is no more objects,this close the ResultSet when there is no more objects(if you don`t finish iterate the ResultSet you should call close method.
  • close - close the ResultSet return false always.
  • each - like Ruby(or most of the JavaScript frameworks) each function,get a function which is called for each object in the ResultSet,also get a second optional object used to bind the function. this functions also auto close the ResultSet when it finish iterating it.
  • getOne - return the first object in the result set and close it.
  • toArray - return a array of the objects in the ResultSet and close it.

a model(in our example Post,Tag and Comment) define these methods:

  • select - preform a SELECT on the model,get a two optional parameters:whereExpression and params.
    whereExpression - is a SQL where clasue like "Post.title CONTAINS 'test'" or using bind parameters(recommended) "Post.title CONTAINS ?".
    params - a array of objects used as bind parameters.
  • remove - preform a DELETE on the model get the same parameters as select
  • createTable - create the table for the model,for example: Post.createTable()
  • dropTable - drop the table for the model,for example: Post.dropTable()
  • createTriggers - create the triggers for the model,for example: Post.createTriggers() this is called by createTable
  • dropTriggers - drop the triggers for the model,for example: Post.dropTriggers() this is called by dropTable
  • load - load objects(or one object) from a array to the database,if the second parameter is true the objects will be saved to the database,in both cases return array of model instances made from the objects

this is just a glimpse of the api,for more info go to GearsORM homepage(there is no documentation yet but stay tuned).
a simple demo here,packed version of the source here and trac view of code here

Google Gears ORM

UPDATE:a new version is here.

i saw Google Gears and i thought :"nice finally i have some kind of client side storage in browsers other than Firefox".(i know that only IE and Firefox are supported,but safari support is on the way,and in gears group they have been talking about opera support,also this is a open source project)

but then i thought:"damn i hate to write sql",so i started to write a simple ORM something like SQLObject but way simpler.

this short script give you a way to declare your tables as objects and provide a nicer OO interface than the normal Google Gears api.
so to define a table you do it like this: var Person = new GearsOrm.Model("Person", { firstName:GearsOrm.Fields.String({maxLength:25}), lastName:GearsOrm.Fields.String({maxLength:25}) });

the first argument is the table name to use in the db.
the second argument is a object that define all the fields in the table, the keys are the field names and the values are the field types.
there 4 field types: Integer,Float,TimeStamp and String.
each of this get a object which define sql properties:

  • notNull - if this is set to true the field will be appended with "NOT NULL",meaning that the field cannot accept the value null
  • defaultValue - will use the value of this option as the default value of the field using the "DEFAULT" sql syntax
  • maxLength - this is used with String field type to define the max length of the field
  • unique - if this is set to true the field will be appended with "UNIQUE",meaning that values of this field will be unique

now that we have our tables defined,we need to create them,no problem just call the createTable() function of the model and the table will be created for you(if it is already created it won`t do anything),like this: Person.createTable();

now for some real work,to insert a row to the database: var myself = Person.insert(["Uriel","Katz"]); alert(myself.firstName); alert(myself.lastName);

that simple!,this will insert a Person with first name "Uriel" and last name "Katz" and will return a object that have firstName set to "Uriel" and lastName set to "Katz".
now that we have something in the db we can retrive it using select method of the model: var persons =; var current; while(current = { alert(current.firstName + ":" +current.lastName); }

what do is to return a object with a function next(like a iterator in some languages), when calling to next it return a person object,when there is no more persons to retrieve it will return null and by that will stop the while loop.

select can also get a expression that will be used as the WHERE in the sql query,like this:"firstName = 'Uriel'") or using bind variables(recommended,since it auto quote the values and prevent SQL injection attacks):"firstName = ?",["Uriel"])

now for delete,delete works like select but it must get a expression,let say we want to delete all the pepole with last name Cats(common misspell of the name Katz): Person.delete_("lastName = ?",["Cats"]) this will delete all the Persons(i.e. all the rows in the table Person) that have their last name is Cats.
NOTE: delete_ is used instead of delete since it is a keyword in JavaScript.

the model have also a function called dropTable that will drop the table if it exists.
note that there is no support for relations but that can be add easily.

a simple demo is here and source code here.

NOTE: by default GearsOrm use the database name "gears.orm" if you want to use different name just add a line just after the include of gears_orm.js like this: GearsOrm.dbName = "myDataBaseName";