Defining data types, separate model files and sync with DB

[bookshelf]

Defining data types, separate model files and sync with DB

[tgriesser]

Hi @amitava82

1. This would be handled when you create your schema with knex - they are not defined on the models.

Knex.Schema.createTable(function(t) {
  t.string('identifier').primary();
  t.string('title').nullable();
  t.string('someUnique').unique();
}).then(function() {
  console.log('Table Created!');
});

2. You'd do it just like you would otherwise in node, by exporting/requiring objects in different files as appropriate. The library has no opinions on the structure of your project.

3. Right now, you'd need to define and call the schema changes and data inserts you want to make when starting the server. I'm looking into putting something together like rails' migrations, but that's not quite there yet. In my opinion these parts shouldn't be mixed in with the model layer.

[rstacruz]

I'd really love to see Bookshelf get migrations support!

[tgriesser]

@rstacruz - agreed! I've been planning on putting one together for it, I think I'm going to split it out as it's own CLI and standalone module outside of the bookshelf lib. Hopefully I'll get to it in the next few weeks, If you have any ideas for it, let me know.

[rstacruz]

Coolbeans! Let me throw a few suggestions. First, I think it's better implemented in Knex than Bookshelf. It should be possible to run migrations in just Knex: there's no reason to require models in a migration transactions.

Second, I think it needs to be designed for programmatic access. Consider:

var db = Knex.Initialize({ ... });

var migrator = new Knex.Migrator({
  path: './migrations',
  database: db,
  logger: console.log
});

migrator.currentVersion().then(...   /* "20130523" */

// Perform a migration
migrator.migrate().then(...
migrator.migrate({ to: "20130605" })

// Create a migration file
migrator.generate().then(...
migrator.generate("add_created_at_to_user")

This way, your bin only needs to be a thin wrapper around it.

I'm making a project right now that has bindings for Sequelize -- http://ricostacruz.com/expo/ . It tries to access Sequelize's migrator class programmatically (which it's not designed for).

[tgriesser]

I agree with all of those points - it should be based in Knex but be able to be aliased as a bin in both knex and Bookshelf. I'll definitely make sure there's support for programmatic access.

This is sort of an idea for what the module's api might look like:

I'll also look to provide sufficient flexibility for specifying different multiple independent migrations for the same db, easily passing different connections in the same project, etc.

[pward123]

I'm doing this with https://npmjs.org/package/migrate using migration files like the following:

for key, value of require("../lib/MyApp/common")
  eval("var #{key} = value;")

MyApp = require("../lib/MyApp")
bookshelf = MyApp.bookshelf()
nodefn = require("when/node/function")

exports.up = (next) ->
  nodefn.liftCallback(next)(
    bookshelf.spread((db) ->
      db.Knex.Schema.createTable("Users", (table) ->
        table.increments("id").primary()
        table.string("email").unique()
        table.string "passwordHash"
      )
    ).then(() ->
      console.log '  -> Users table created'
    ).catch((err) ->
      console.log "  -> Unable to create Users table " + err
    )
  )

exports.down = (next) ->
  nodefn.liftCallback(next)(
    bookshelf.spread((db) ->
      db.Knex.Schema.dropTable("Users")
    ).then(() ->
      console.log '  -> Users table dropped'
    ).catch((err) ->
      console.log "  -> Unable to drop users table " + err
    )
  )

It uses a json file to track migrations applied which can be ugly if you're switching configurations. I'd rather have it use a db table entry

[tgriesser]

All right... took me long enough, but there's now a (very) bare bones implementation of migrations in knex 0.5.0, which is now the required version for the latest 0.6.0... still needs better docs, options, and such, but it's a start - @rstacruz let me know what you think, I'm now bumping getting a nice AR interface to the top of the priority list... see #27

[esatterwhite]

One thing that would be insanely helpful would be helpers to deal with relationships to auto create the JOIN tables needed to deal with relations. Relations are really the primary benefit over knex, and doing a little model introspection to auto generate a migration would be fantastic. I basically have to hand write all of this anyway - with knex or with knex and manual tweaking.

And after 2 or 3 times of trial and error, it is usually faster and easier to just open a DB Shell and write the SQL...

I always liked the way the south project for Django did this.

Make a change to a model, auto generate a migration. done.

[rhys-vdw]

One thing that would be insanely helpful would be helpers to deal with relationships to auto create the JOIN tables needed to deal with relations.

This is not really appropriate for Bookshelf IMO. It's designed in such a way that it can be retrofitted to an existing database.

[esatterwhite]

So do all the hard work in SQL and then just lay bookshelf on the database you wrote manually? What benefit does bookshelf bring to the table at that point?

[rhys-vdw]

What benefit does bookshelf bring to the table at that point?

@esatterwhite an object oriented representation of your database and convenience functions for manipulating the data therein. I personally would not call designing a database schema and writing migrations "the hard work" of using with a database. As you pointed out, there are ORMs that are tightly coupled to the schema like you're suggesting (I believe the Django ORM generates migrations/schema from the models, for instance). Bookshelf takes a lot from ActiveRecord, a very popular ORM despite the fact it does none of the "hard work" you reference.

[rhys-vdw]

@esatterwhite if you want some help with migrations you can use Knex's migration system. It's not perfect of course, but you can always use knex.raw if you want to do some dialect-specific (or otherwise unsupported) operation.

[esatterwhite]

@rhys-vdw the issue is about migrations within bookshelf, not the merit of active record. The Model that bookshelf does provide makes it easier to do introspection into the database and what the data was / should look like. It should be able to make better decisions to help generate migrations. That would be awesome.

As it stands, the migration tool generates an empty file and is no more helpful than me writing a SQL file to make the changes to the database.

So, if I want to do anything interesting / complex or with large datasets or most anything outside of `Model.where().fetch()' ( as you have pointed out ) , I can't really use bookshelf, I have to use knex or write the SQL anyway.