Despite knowing JavaScript quite well, I'm confused what exactly these three projects in Node.js ecosystem do. Is it something like Rails' Rack? Can someone please explain?
[Update: As of its 4.0 release, Express no longer uses Connect. However, Express is still compatible with middleware written for Connect. My original answer is below.]
I'm glad you asked about this, because it's definitely a common point of confusion for folks looking at Node.js. Here's my best shot at explaining it:
Node.js itself offers an http module, whose createServer method returns an object that you can use to respond to HTTP requests. That object inherits the http.Server prototype.
Connect also offers a createServer method, which returns an object that inherits an extended version of http.Server. Connect's extensions are mainly there to make it easy to plug in middleware. That's why Connect describes itself as a "middleware framework," and is often analogized to Ruby's Rack.
Express does to Connect what Connect does to the http module: It offers a createServer method that extends Connect's Server prototype. So all of the functionality of Connect is there, plus view rendering and a handy DSL for describing routes. Ruby's Sinatra is a good analogy.
Then there are other frameworks that go even further and extend Express! Zappa, for instance, which integrates support for CoffeeScript, server-side jQuery, and testing.
Here's a concrete example of what's meant by "middleware": Out of the box, none of the above serves static files for you. But just throw in connect.static (a middleware that comes with Connect), configured to point to a directory, and your server will provide access to the files in that directory. Note that Express provides Connect's middlewares also; express.static is the same as connect.static. (Both were known as staticProvider until recently.)
My impression is that most "real" Node.js apps are being developed with Express these days; the features it adds are extremely useful, and all of the lower-level functionality is still there if you want it.