Want to run SQL on a CSV file? #

Now you can with q, a Python lib.

q allows performing SQL-like statements on tabular text data.

It seems this idea isn’t restricted to Python either. TextQL is a project written in Go that promises to do roughly the same thing.

Give any SQL database a REST interface with Sandman #

Let’s say you have a SQL database. You might wish it had a REST interface and a nice admin UI. That used to mean writing a bunch of code but now with Sandman it’s a one liner.

Zero boilerplate code is required. In fact, using sandmanctl, no code is required at all. Your existing database structure and schema is introspected and your database tables magically get a RESTful API and admin interface.

Sandman was created by Jeff Knupp and can be used with any database that SQLAlchemy supports.

Rails gets automatic EXPLAIN logging for slow SQL queries #

In a fresh commit, Rails edge now has the ability to automatically add query plan info to the standard Rails logger:

# Log the query plan for queries taking more than this (works
# with SQLite, MySQL, and PostgreSQL)
config.active_record.auto_explain_threshold_in_seconds = 0.5

… which will yield something like:

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | users | const | PRIMARY       | PRIMARY | 4       | const |    1 |             |
|  1 | SIMPLE      | posts | ALL   | NULL          | NULL    | NULL    | NULL  |    1 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

Hot.

rel: Arel-inspired SQL query builder for Node.js #

Arguably, Arel was one of the biggest
new features introduced in Rails 3. Arel simplifies building complex SQL
statements using idiomatic Ruby.

With Rel, Carl Woodward brings the power of Arel to Node.js. Written in
CoffeeScript, Rel makes quick work of building SQL statements for a variety of relational databases.

Installation

Rel can be installed via npm:

npm install rel

We can then begin building a query:

users = new Rel.Table 'users'

If we want all users in our CMS we could use the star method:

users.project(Rel.star()).toSql()

Rel really shines, however, when using several chained operators,
including joins:

users.join(photos).on(users.column('id').eq(photos.column('user_id')))
# => SELECT * FROM users INNER JOIN photos ON users.id = photos.user_id

For a complete list of features, check out the very readable
Vows-based
specs in the repo on GitHub.

[Source on GitHub]

sequelize: ORM for MySQL and Node.js #

Although NoSQL seems to get a lot of attention in Node.js circles, probably because so many document-oriented databases speak JavaScript fluently, wrappers and drivers for relational databases like MySQL are starting to emerge. The latest entry is Sequelize from Sascha Depold. Sequelize provides basic Object-Relational-Mapper (ORM) between your JavaScript objects and MySQL database records.

Install Sequelize via npm:

npm install sequelize

… and require the library:

var Sequelize = require("sequelize").Sequelize

Once the library has been loaded, set up your database connection by creating an instance of Sequelize, with optional host and port.

var sequelize = new Sequelize('database', 'username', 'password', {
  host: "my.server.tld",
  port: 12345
})

Call the define method to create your models, mapping fields to database types:

var Project = sequelize.define('Project', {
  title: Sequelize.STRING,
  description: Sequelize.TEXT
})

var Task = sequelize.define('Task', {
  title: Sequelize.STRING,
  description: Sequelize.TEXT,
  deadline: Sequelize.DATE
})

You can now work with instances of those models with new:

var project = new Project({
  title: 'my awesome project',
  description: 'woot woot. this will make me a rich man'
})

var task = new Task({
  title: 'specify the project idea',
  description: 'bla',
  deadline: new Date()
})

Keep in mind that Sequelize was built completely on the Node async architecture, so there are callbacks for most everything, including save():

// way 1
task.title = 'a very different title now'
task.save(function(){})

// way 2
task.updateAttributes({
  title: 'a very different title now'
}, function(){})

Sequelize also has very slick method chaining to let you batch save objects:

Sequelize.chainQueries([ 
  {save: project}, {save: task}
], function() {
  // woot! saved.
})

Be sure and check the documentation for more advanced features like Associations and Finder syntax.

[Source on GitHub] [Hompage] [Sascha on Twitter]