[ANNOUNCE] node-sqlite - Asynchronous SQLite3 driver for Node.js

[Orlando Vazquez]

Greetings Noders!

I'm please to announce the first release of a set of truly
asynchronous, non-blocking bindings for our favorite embedded RDBMS,
SQLite!

For more details, run, don't walk, to:

http://github.com/orlandov/node-sqlite

Thanks:

My employer, Joyent, for allowing me to work on this project as part
of my duties.

Ryan Dahl has been responsible for major code improvements and speed
optimizations. Thanks Ry!

The bindings started off as a fork Eric Fredricksen's great work on a
synchronous driver (http://github.com/grumdrig/node-sqlite), so I
would like to give him a special thank you. :)

As always, I'd be interested to get feedback, bug reports, patches from users.

Your friend,

--
Orlando Vazquez

[Akzhan Abdulin]

Very good news. I'll announce 'em in russian community to test this code wide.

2010/3/30 Orlando Vazquez <ovaz...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "nodejs" group.
To post to this group, send email to nod...@googlegroups.com.
To unsubscribe from this group, send email to nodejs+un...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nodejs?hl=en.

[John Wright]

If anybody is interested I am working on making this excellent fork of
node-sqlite more complete, better error-handling, and faster ala fmdb
and other solid SQLite libraries. There are places where I feel this
library can be just a bit more consistent with the spirit of SQLite
and Node, such as not throwing exceptions for regular SQLite errors
since its an async library, and exposing more sqlite calls, such as
lastError, reset, user defined functions and more, things very useful
for wrappers. I was also able to get about a 30% speed increase for
bulk inserts with a cache_statement switch (like using a
PreparedStatement in jdbc so that prepare_sql is not called if the
user doesn't want it). I will send pull requests over to you Orlando
as soon as I can make sure everything is well tested and consistent.
I am using on my own project and want to use in a lot more scenarios
first.

http://github.com/mrjjwright/node-sqlite.

Thanks again for those of you who put this excellent start to a solid
SQLite library in place.

John

[Liam]

I'm about to start playing with Node & SQLite...

I'd like to use the "recommended" SQLite driver, but I can't tell
whether that's orlandov's or mrjjwright's...

Any guidance?

Liam

On Apr 8, 6:11 pm, John Wright <mrjjwri...@gmail.com> wrote:
> If anybody is interested I am working on making this excellent fork of

> node-sqlitemore complete, better error-handling, and faster ala fmdb
> and other solidSQLitelibraries.  There are places where I feel this

> library can be just a bit more consistent with the spirit ofSQLite
> and Node, such as not throwing exceptions for regularSQLiteerrors

> since its an async library, and exposing moresqlitecalls, such as

> lastError, reset, user defined functions and more, things very useful
> for wrappers.  I was also able to get about a 30% speed increase for
> bulk inserts with a cache_statement switch (like using a
> PreparedStatement in jdbc so that prepare_sql is not called if the
> user doesn't want it).  I will send pull requests over to you Orlando
> as soon as I can make sure everything is well tested and consistent.
> I am using on my own project and want to use in a lot more scenarios
> first.
>
> http://github.com/mrjjwright/node-sqlite.
>

> Thanks again for those of you who put this excellent start to a solidSQLitelibrary in place.

[mde]

Howdy.

I've got SQLite support built into Geddy (github.com/mde/geddy,
http://wiki.github.com/mde/geddy/install-sqlite-and-node-sqlite), and
wanted to use Orlando's async driver, but encountered all kinds of
breakage.

I fell back to forking the original sync driver, and updating it not
to use process.mixin, so it will work with newer versions of Node.
Works fine.

It still uses a callback-based API, so it didn't take a lot of effort
to wrap it to conform to the Nodely convention of callback(err,
result) instead of throwing on an error. (I'm using Ry's node_postgres
for the Postgres support, so the more code reuse I can get, the
better.)

Would be cool to rally around a single SQLite project -- and I really
like the idea of a consistent interface for callbacks from SQL queries
to avoid rewriting a bunch of code.

John, any thoughts on this?


Matthew

[r...@tinyclouds.org]

On Sun, Jun 6, 2010 at 1:58 PM, mde <m...@fleegix.org> wrote:
> Howdy.
>
> I've got SQLite support built into Geddy (github.com/mde/geddy,
> http://wiki.github.com/mde/geddy/install-sqlite-and-node-sqlite), and
> wanted to use Orlando's async driver, but encountered all kinds of
> breakage.
>
> I fell back to forking the original sync driver, and updating it not
> to use process.mixin, so it will work with newer versions of Node.
> Works fine.
>
> It still uses a callback-based API, so it didn't take a lot of effort
> to wrap it to conform to the Nodely convention of callback(err,
> result) instead of throwing on an error. (I'm using Ry's node_postgres
> for the Postgres support, so the more code reuse I can get, the
> better.)
>
> Would be cool to rally around a single SQLite project -- and I really
> like the idea of a consistent interface for callbacks from SQL queries
> to avoid rewriting a bunch of code.

Orlando's SQLite binding is probably the best
http://github.com/orlandov/node-sqlite
It's fully non-blocking, using the thread pool to execute all of the
library calls.

[Liam]

On Jun 6, 2:32 pm, r...@tinyclouds.org wrote:
> Orlando's SQLite binding is probably the besthttp://github.com/orlandov/node-sqlite

> It's fully non-blocking, using the thread pool to execute all of the
> library calls.

Right, but John Wright forked that and did a bunch of work ("more
complete, better error-handling, and faster") -- is that going to be
patched in?

Matthew what's the breakage you saw with Orlando's driver?

Liam

[mde]

It was throwing up errors from the underlying library, but it looks
like it has been fixed.

I'll look at porting my work over to Orlando's async driver, and
watching for John's changes to make it upstream. The API is different
from the sync SQLite and node_postgres. What I really want is a common
interface to talk to all the SQL DBs -- but it looks like I'l be
writing that. :)


M.

[Ryan Gahl]

On Mon, Jun 7, 2010 at 4:15 PM, mde <m...@fleegix.org> wrote:

I'll look at porting my work over to Orlando's async driver, and
watching for John's changes to make it upstream. The API is different
from the sync SQLite and node_postgres. What I really want is a common
interface to talk to all the SQL DBs -- but it looks like I'l be
writing that. :)

Have you taken a good look at Pintura/Persevere yet?  http://github.com/kriszyp/pintura 

Also, the underlying storage manager in Pintura, Perstore: http://github.com/kriszyp/perstore

I'm not sure if it's exactly what you're looking for, but might be of interest.

[Dean Landolt]

On Mon, Jun 7, 2010 at 5:15 PM, mde <m...@fleegix.org> wrote:

It was throwing up errors from the underlying library, but it looks
like it has been fixed.

I'll look at porting my work over to Orlando's async driver, and
watching for John's changes to make it upstream. The API is different
from the sync SQLite and node_postgres. What I really want is a common
interface to talk to all the SQL DBs -- but it looks like I'l be
writing that. :)

That's one of the primary goals of perstore [1] so if you're going down that path we'd love to help. A good place to start would be to wrap these libraries with the Web Database API [2] -- that'd be pretty sweet and would allow you to drop in other database drivers folks may create using this API. Then, of course, you could write your own sugar on top of it.

[1] http://github.com/kriszyp/perstore/

[2] http://dev.w3.org/html5/webdatabase/

[Dean Landolt]

Most of the SQL stuff only implemented using JDBC so it's rhino-only for now. But it would should be a nice example where to start when implementing WebDatabase. But it'd be awesome if we could all collaborate on sql db drivers using this common interface, perhaps in a separate package.

[Timothy Caswell]

Also, I started a similar project many months ago called node-persistence.  Most the code in there is now stale, except for the pure-js postgres driver that's in it's own repo.  Feel free to steal ideas from my spec.  I especially like the simple queries in node-persistence.

http://github.com/creationix/node-persistence

http://github.com/creationix/postgres-js

[Dean Landolt]

I especially like the simple queries in node-persistence.

Cool -- that's the kind of thing I was thinking of when I said you can layer your own sugar on top -- we all have different ideas about what our queries should look like and how to handle things like schema migrations and such, but under the hood the async WebDatabase API is pretty nice and general enough for all of us.

[Liam]

Are folks aware that the HTML5 SQL effort has stalled, and the focus
has shifted to the IndexedDB proposal...

http://news.cnet.com/8301-30685_3-20000376-264.html

Liam

[Matt Ranney]

On Tue, Jun 8, 2010 at 11:34 AM, Liam <networ...@gmail.com> wrote:

Are folks aware that the HTML5 SQL effort has stalled, and the focus
has shifted to the IndexedDB proposal...

That's kind of too bad, because every iPhone and most Android device out there support web database.  What is that, like 60 million devices now?

[Liam]

Actually Matthew you might want to consider offering node-sqlite
patches to John's fork, since he's doing the most work on this.

http://github.com/mrjjwright/node-sqlite

On Jun 7, 2:15 pm, mde <m...@fleegix.org> wrote:
> It was throwing up errors from the underlying library, but it looks
> like it has been fixed.
>
> I'll look at porting my work over to Orlando's async driver, and
> watching for John's changes to make it upstream. The API is different

> from the syncSQLiteand node_postgres. What I really want is a common

> interface to talk to all the SQL DBs -- but it looks like I'l be
> writing that. :)
>
> M.
>
> On Jun 6, 5:02 pm, Liam <networkimp...@gmail.com> wrote:
>
> > On Jun 6, 2:32 pm, r...@tinyclouds.org wrote:
>

> > > Orlando'sSQLitebinding is probably the besthttp://github.com/orlandov/node-sqlite

[Dean Landolt]

On Tue, Jun 8, 2010 at 2:34 PM, Liam <networ...@gmail.com> wrote:

Are folks aware that the HTML5 SQL effort has stalled, and the focus
has shifted to the IndexedDB proposal...

Yes, I'm aware (Kris Zyp designed the common perstore interface was designed around IndexedDB FWIW). But if we are to collaborate on sql engines we need a low-level interface and WebDatabase fits the bill nicely. The primary complaints about WebDatabase (dependency on a particular, unspecified sql dialect and the general argument that sql doesn't fit in the browser) are completely irrelevant in this case.

[mde]

Dean,

Perstore looks really interesting -- definitely a lot of overlap
there, so I'll take a closer look.

What I'm doing with Geddy's model code hews more closely to existing
ORM solutions in Ruby or Python (even though it may sit on top of
something non-relational, and have no actual "R" :)), but I definitely
want to incorporate good ideas from everywhere.


Matthew

On Jun 7, 2:27 pm, Dean Landolt <d...@deanlandolt.com> wrote:

[mde]

Tim,

The API looks very, very similar. Definitely the direction I want to
go with Geddy's model code. I will dig into this.

Thanks.


Matthew

On Jun 7, 2:30 pm, Timothy Caswell <t...@creationix.com> wrote:
> Also, I started a similar project many months ago called node-persistence.  Most the code in there is now stale, except for the pure-js postgres driver that's in it's own repo.  Feel free to steal ideas from my spec.  I especially like the simple queries in node-persistence.
>

> http://github.com/creationix/node-persistencehttp://github.com/creationix/postgres-js

[mde]

Liam,

John had said he'd be pushing his patches upstream to Orlando's fork
when it's more baked.

And since Ryan had pointed us to Orlando's branch as well, it seems to
make sense to think of that as the canonical branch.

Maybe John or Orlando can chime in as to which branch we should be
using?

Thanks.


Matthew

[Dean Landolt]

On Wed, Jun 9, 2010 at 2:28 PM, mde <m...@fleegix.org> wrote:

Dean,

Perstore looks really interesting -- definitely a lot of overlap
there, so I'll take a closer look.

What I'm doing with Geddy's model code hews more closely to existing
ORM solutions in Ruby or Python (even though it may sit on top of
something non-relational, and have no actual "R" :)), but I definitely
want to incorporate good ideas from everywhere.

Great. It'd be really nice for javascript to have a sound ORM. But there are two distinct pieces to this puzzle -- SQL generation and SQL execution. The WebDatabase gives us a common API for execution but I could care less about that -- I can write adapters if I have to. But if we could all collaborate on a library to do SQL generation right the javascript community at large would win big (even in the browser for those clients with a WebDatabase available). The way I see it the mapping work can be broken down into three distinct pieces...

The most important piece is SELECT but this could be a little tricky. In perstore we have resource-query -- its AST is incredibly flexible so it could be used to express the whole range of SQL semantics (play with a browser demo here [1] to see). Using a simple structure similar to this would also mean someone like Marak could come along and write JSLINQ2SQL -- a whole range of query styles could be adapted, but that's beside the point.

INSERT/UPDATE/DELETE semantics are relatively consistent between various SQL dialects, so that's helpful (you wouldn't believe the hoops you have to jump through to get MSSQL to do the equivalent of LIMIT/OFFSET). Insert and update could just take objects (or lists of objects) and delete could take a key or even the same kind of query object defined for our SELECT code.

Finally, it'd be awesome to have a means to generate CREATE/DROP/ALTER statements. This is less critical than the other stuff but still incredibly useful. Say you provided a simple json-schema to define your table, indexes, perhaps foreign key constraints. That simple object is enough info to generate a CREATE statement for any SQL dialect. Even cooler would be using two json-schemas to get the requisite ALTER statements necessary. The logic to do this exists in various ORM migration routines -- sql-alchemy-migrate would be a pretty strait forward port I bet.

As a bonus: test fixtures could be strait json -- they could be shared with any ORM...in any programming language. What's not to like?

This shit is totally doable, and it's been done dozens of times before. Come on SQL-sadists -- can we do this just once more, in javascript, so's we never have to think about SQL again?

[1] http://deanlandolt.com/array-tools/demo/

[Neville Burnell]

I just watched DHH's rails 3 presentation where he talks about the new
ActiveRecord SQL generation api. [1]

This looks like a nice philosophical api starting point.

[1] http://www.youtube.com/watch?v=b0iKYRKtAsA

On Jun 10, 12:12 pm, Dean Landolt <d...@deanlandolt.com> wrote:
> On Wed, Jun 9, 2010 at 2:28 PM, mde <m...@fleegix.org> wrote:
> > Dean,
>
> > Perstore looks really interesting -- definitely a lot of overlap
> > there, so I'll take a closer look.
>
> > What I'm doing with Geddy's model code hews more closely to existing
> > ORM solutions in Ruby or Python (even though it may sit on top of
> > something non-relational, and have no actual "R" :)), but I definitely
> > want to incorporate good ideas from everywhere.
>
> Great. It'd be really nice for javascript to have a sound ORM. But there are
> two distinct pieces to this puzzle -- SQL generation and SQL execution. The
> WebDatabase gives us a common API for execution but I could care less about
> that -- I can write adapters if I have to. But if we could all collaborate
> on a library to do SQL generation right the javascript community at large
> would win big (even in the browser for those clients with a WebDatabase
> available). The way I see it the mapping work can be broken down into three
> distinct pieces...
>
> The most important piece is SELECT but this could be a little tricky. In
> perstore we have resource-query -- its AST is incredibly flexible so it
> could be used to express the whole range of SQL semantics (play with a
> browser demo here [1] to see). Using a simple structure similar to this
> would also mean someone like Marak could come along and write JSLINQ2SQL --
> a whole range of query styles could be adapted, but that's beside the point.
>
> INSERT/UPDATE/DELETE semantics are relatively consistent between various SQL

> dialects, so that's helpful (you wouldn't *believe* the hoops you have to

[Liam]

On Jun 9, 11:33 am, mde <m...@fleegix.org> wrote:
> And since Ryan had pointed us to Orlando's branch as well, it seems to
> make sense to think of that as the canonical branch.
>
> Maybe John or Orlando can chime in as to which branch we should be
> using?

I think we need a designated maintainer, and since John's been doing
the most work there in the past couple months, I suggested the role to
him the other day...

Liam

[Liam]

The ORM topic might merit its own thread at this point...

Liam

On Jun 10, 12:56 am, Neville Burnell <neville.burn...@gmail.com>
wrote:

[John Wright]

I am sorry, I just noticed this thread. I will improve the
documentation and release Orlando's node-sqlite as a standard
CommonJS, NPM package. I also am going to remove the HTML5 web db
wrapper out into a separate NPM package. Once it is in NPM, we can
stop worrying a bit about what fork is the right one, because the
right one will be in NPM. I will take responsibility for maintaining
it unless somebody else objects. I am working on a commercial product
dependent on this library so I have a vested interested in making sure
it is good.

Btw, I think it's ridiculous that people think the web db api is
dead. SQLite is perfect for the web and mobile experiences and is
very powerful. We need to fight to keep it there.

I will update this list as soon as I release this in NPM.

[John Wright]

+1 on investigating a relational algebra approach. I am working on
NoSQLite which is more about insert and pushing and pulling immutable
objects between multiple SQLite instances, not about selecting. But I
think porting the Active3 implementation might work in Javascript.
This might be a good start: http://github.com/brynary/arel. Also see
this article: http://magicscalingsprinkles.wordpress.com/2010/01/28/why-i-wrote-arel/.
Of course there might be some mismatch here or some other approach
that works better for JavaScript but this is worth investigating.

John

[Dean Landolt]

On Tue, Jun 15, 2010 at 3:16 PM, John Wright <mrjjw...@gmail.com> wrote:

+1 on investigating a relational algebra approach.  I am working on
NoSQLite which is more about insert and pushing and pulling immutable
objects between multiple SQLite instances, not about selecting.  But I
think porting the Active3 implementation might work in Javascript.
This might be a good start: http://github.com/brynary/arel.  Also see
this article: http://magicscalingsprinkles.wordpress.com/2010/01/28/why-i-wrote-arel/.
Of course there might be some mismatch here or some other approach
that works better for JavaScript but this is worth investigating.

It's been investigated -- the conclusion: hell yes it does. Kris added arel-style chaining to resource-query recently and it has a wide array of arel style operators (that can be applied lazily). It has some other neat tricks up its sleeve too, but what it doesn't do (very well, though there is a usable implementation) is compile down to various SQL SELECT statements. If anyone wants arel-like queries for SQL this would be a great head start.