Modifying columns

[knex]

Modifying columns

[tkellen]

Switching between MySQL and PostgreSQL seamlessly is a pipe dream that will never happen for anyone on a meaningful scale ever. All database systems have strengths and weaknesses. It makes absolutely no sense to limit yourself to a subset that works across both so you can arbitrarily decide to switch between one or the other.

ActiveRecord doesn't do migrations "right". There is a huge set of functionality it doesn't provide a DSL for. You are right that more human effort has been dumped into ActiveRecord's migration system though. The reason for that is simple numbers.

Knex:

ActiveRecord:

My question remains. Where is the value in these DSLs? Anything you want to migrate can be expressed in SQL. You don't need to build some half-baked sugar on top of it. People have been talking about altering columns here for 3 years. Meanwhile SQL has been trucking along doing the same thing just fine for 30.

Also, RE: switching between PostgreSQL and MySQL, what happens if you decide to move from javascript to ruby? How much time will it take you to transcribe all of your SQL migrations from one silly pointless DSL to another? Maybe we should write a portable DSL, you say? That's SQL.

[RobertWarrenGilmore]

I would love to see an API like table.modifyColumn('myCol').notNullable().defaultTo('hello').

[00defeat]

+1 for changing null & not null on columns

[niftylettuce]

Any update with this?

[tkellen]

My suggestion is to use something like careen, db-migrate, etc.

Basically, find the most minimal abstraction possible and use it. That means something to run arbitrary SQL files, a facility to track which have been run, and some functionality for rolling them up or down.

In my opinion, migrations never belonged in Knex. I tried to use them years ago, coming from a ruby background using Sequel and ActiveRecord. Like most people in this thread, I imagined that some day we'd have the same level of support here. I even landed some PRs adding functionality to the migration system.

At some point, I recognized two things:

1. A monumental effort would be required to achieve feature parity with more mature tools.
2. Mature tools are still wildly incomplete and provide zero value over SQL.

For example, here is a check constraint that prevents an employee from being double-booked on a schedule.

ALTER TABLE utilization ADD CONSTRAINT employee_over_utilized EXCLUDE USING gist (
  employee_id
  WITH =,COALESCE(sketch_calendar_id::text, 'null')
  WITH =,BOX(
    POINT(EXTRACT(EPOCH FROM first_day), EXTRACT(EPOCH FROM first_day)),
    POINT(EXTRACT(EPOCH FROM last_day), EXTRACT(EPOCH FROM last_day))
  )
  WITH &&
);

You can't build constraints like this fluently using any DSL I know of. Even if you could, I still wouldn't expend the effort learning it. SQL does everything you want it to. Use it. Putting a crappy javascript veneer over it isn't helping anyone get anything done.

[joepie91]

So, I've just read through the entire thread, and it's not clear to me what the current status is. Could a maintainer answer the following?

1. What is the current state of internals documentation? That is, information about how the codebase is structured, why it is structured that way, and so on.
2. What is the current state of this feature request? What parts have been implemented, what parts have not? Where are potential 'attachment points' for implementing the remainder?
3. What, if any, are the blockers at this moment for implementing this and sending in a PR? What parts of the internal architecture need changing and how, to make this a possibility?

I'm potentially interested in implementing this and submitting a PR, but before I can commit to doing that, I need to have a better idea of what the current situation is like, and how much work I can expect it to be.

[elhigu]

Implemented in #1759

[tgriesser]

@ErisDS the plan is to make the builder not constrained to normal statements but also add an api for altering the tables:

knex.alterTable(tableName).modifyColumn(colName, type)
knex.alterTable(tableName).drop(colName)

I'm also working on an API for defining what the table structure for a database should be so you can eventually use these objects to compare against what the current state of the database is (similar to what ghost does, but a bit more tested/spec'ed out)

const {database, table, column} = require('knex').Schema

var db = database({name: 'tryghost'}, [
  table('posts', [
    column('id', 'int'),
    column('slug', 'string'),
  ])
])

knex.select(db.posts).where(db.posts.id, 1)

// SELECT posts.* FROM posts WHERE posts.id = 1

Working on moving some stuff around to get to a better api than having to use raw

[elhigu]

@ricardograca We have been supporting APIs which doesn't work on every dialect. Specially with sqlite there are some features, which just throw an exception of not being supported or just ignored like .returning().

So I don't mind if e.g. certain column modify methods doesn't work on sqlite if it is actually pretty much impossible to implement without creating some temporary columns + data migrations to support it.

[tkellen]

Whilst switching a production system from one db to another is hard, it's not impossible. Nonetheless, I think you're totally missing an entire ORM use case here. What about software that allows you to choose your DB when you install it? That's what I'm working on 😉

If I were maintaining Ghost, I would have separate migration files for each RDBMS. Yes, you'd have some minor duplication in your codebase, but how much clarity would it provide for new contributors? Would your existing test suite easily cover failures? Would you be able to more easily leverage the strengths of each respective database you support by dropping this edifice?

More pedantically, this is not an ORM use case.

Knex works really nicely in most places as it provides a great API (or javascript veneer if that's what you want to call it) for the most common use cases, and then it has an escape valve of knex.raw that lets you dropdown to the magic of SQL when you need to go beyond those basics. All good JavaScript libraries work this way - they wrap things up in magic but let you escape when you need to.

Agreed--I see the value in a query builder over munging SQL strings, Knex is great for that!

When it comes to the migration system, this issue is talking about a handful of common cases that it would be great to have a veneer for because of the complexity of the differences between SQL versions - that is, there is no one SQL query that will work for multiple DBs. It doesn't propose that every possible migration ever should have an abstraction.

I see the point you're making here, but it's been three years and we're still talking about altering a column. Also, where do you draw the line at basic? For example, I consider robust check constraints to be a basic tenet of good database design.

[ricardograca]

@elhigu What you say is mostly right, but there is some resistance from some collaborators for getting this merged. Tim himself seems to be ok with it if anyone actually does all the work including tests.

The biggest challenge will be in regards to SQLite which doesn't natively support modifying columns, so it requires all sorts of workarounds.

[tgriesser]

Checklist:

• Renaming Columns
• Change Null
• Change Default
• Change Datatype

[gregberge]

We need this functionality too for @lemonde CMS.

Adding an index or alter an enum should be possible without executing raw queries.

[ErisDS]

Switching between MySQL and PostgreSQL seamlessly is a pipe dream that will never happen for anyone on a meaningful scale ever.

Whilst switching a production system from one db to another is hard, it's not impossible. Nonetheless, I think you're totally missing an entire ORM use case here. What about software that allows you to choose your DB when you install it? That's what I'm working on 😉

Knex works really nicely in most places as it provides a great API (or javascript veneer if that's what you want to call it) for the most common use cases, and then it has an escape valve of knex.raw that lets you dropdown to the magic of SQL when you need to go beyond those basics. All good JavaScript libraries work this way - they wrap things up in magic but let you escape when you need to.

When it comes to the migration system, this issue is talking about a handful of common cases that it would be great to have a veneer for because of the complexity of the differences between SQL versions - that is, there is no one SQL query that will work for multiple DBs. It doesn't propose that every possible migration ever should have an abstraction.

[fjorgemota]

The support to renaming columns is very basic, it does not support renaming fields that are a foreign key, for example, as I reported at #157.

[bendrucker]

Acknowledged—take a look at the checklist in Tim's post two above yours. If this is a priority for you and you need it immediately, we'd really appreciate a PR.

[bendrucker]

@neoziro Right now you're limited to to setting a particular column as an index. Other schema modifications are on the todo list, and as always PRs are appreciated if you urgently need something.

On another note, I just started a thread to collect featured use cases for Knex/Bookshelf. I'd love to hear how you guys are using knex at @lemonde. You can share over here: #170.

[ErisDS]

@bendrucker I understand completely your stance on submitting a PR. I am hoping to try to divert some of Ghost's resources to getting this done, but we have very limited resource at the moment.

Seeing as there are a few of us hoping to get this feature, perhaps it would be good to get some thoughts from @bendrucker / @tgriesser about what the API should look like (is my suggestion above correct) and perhaps brain dump any thoughts or ideas about how it can be achieved / the preferred approach.

At the moment, a lot of the ideas/principles about how stuff does and should work in knex/bookshelf are still (as far as I can find) in @tgriesser's head... I realise he doesn't have a great deal of time, but perhaps a wiki page full of where the inspiration for different bits came from, ideas for the future, clever-ass stuff that's in the library and why, all the insider info so we can all catch up to what you were thinking, and try to continue on in a way that you would approve of 😎

Or perhaps you could blog it all.. you know.. on Ghost 😉

[tgriesser]

Haha definitely... sorry @ErisDS and everyone... I really do need to dedicate some time to sit down and write a bunch of the stuff out that's still in my head. Hopefully a ton of stuff including all of the above will be cleared up once I get more internal consistency around building queries in Knex, at which point I can focus more on organizing features I have planned for Bookshelf (model typecasting, soft deletes, a composite key plugin, etc).

I think it'd also be really great to get an open source example app together (other than ghost) that showcases some of the different features of knex/bookshelf for folks to take a look at. I'd started on a hacker news clone, but haven't had the time to finish it... @bendrucker, @johanneslumpe, @tkellen or anyone else have any interest on helping with something like that?

[ErisDS]

@tgriesser I highly recommend just sitting down and spewing out whatever comes to mind, rather than trying to clarify through code. At a certain point, the principles are far easier communicated with words.

I personally find knex & bookshelf daunting to contribute to, because I don't feel I have enough background info or guidance on how to get stuck in - despite relying heavily on these libraries and knowing them reasonably well.

[bendrucker]

@ErisDS I second a wiki for "clever-ass stuff" 😄

@tgriesser I'd help w/ the OS example app for sure

Big changes should probably wait on other refactors, but I think it's worth consider a non-breaking refactor of the schema builder API as well. There's a weird mix right now of promises, chainable sync functions, and callbacks. Maybe table.column(name) returns a ChainableColumn and then all the type functions (integer, text, etc.) chain off of it.

Then there'd be methods create, drop, rename to set the actual SQL column operation (ADD, ALTER, DROP). create would be the default.

To me that fits better with the rest of Knex style-wise. Just chain up all your query components and let the magic happen rather than dealing with a ton of camelcased syntax like dropColumn et al.

[tkellen]

@tgriesser I could probably get a Knex/Bookshelf hacker news clone up at BocoupFest in Feburary (4th-7th). Maybe @tbranyen and I could whip one up. I'll let ya know.

[johanneslumpe]

@tgriesser Of course! I will help where I can!

Sent from my iPhone

On Jan 24, 2014, at 5:25 PM, Tyler Kellen [email protected] wrote:

@tgriesser I could probably get a Knex/Bookshelf hacker news clone up at BocoupFest in Feburary (4th-7th). Maybe @tbranyen and I could whip one up. I'll let ya know.

—
Reply to this email directly or view it on GitHub.

[p-baleine]

@tgriesser

As @ErisDS has mentioned by the original post, I think that it would be nice that if I could do dropping or renaming of columns on SQLite3 by knex's migration function.

lib/migration.js and lib/cli/migrate.js do not know what client they are targeting and what action is performed on migration time. They implement migration via knex's query building functions. But in order to implement dropping or renaming of columns on SQLite3, only when the client is SQLite3 and the performed action is one of renaming or dropping, we have to create a table for copy data, copy data from original table to the table for copy, drop the original table, create a new table, copy data to the new table and drop the table for copy data.

If this migration function were to be implemented, where do you think this switching stuff should be placed?

Note: currently on my project, as workaround, I rename(or drop) of columns in migration files as follow:

exports.up = function(knex, Promise) {
  if (require('config').database.client === 'mysql') {
    return knex.schema.table('posts', function(t) {
      t.renameColumn('foo', 'bar');
    });
  } else if (require('config').database.client === 'sqlite3') {
    // copy original table
    return knex.raw('create table copy_posts AS select * from posts')
      .then(function() {
        return knex.schema.dropTable('posts');
      })
      .then(function() {
        // create new posts table
        return knex.schema.createTable('posts', function(t) {
          // ...
          // other column definitions
          // ...
          t.string('bar');
        });
      })
      .then(function() {
        return knex.raw('insert into posts select * from copy_posts');
      })
      .then(function() {
        return knex.schema.dropTable('copy_posts');
      });
  }
};

[tgriesser]

Yeah, dropping/changing would be nice. Basically it's just a pain to implement, and because stuff hasn't been quite consistent internally I've held off, because the current implementation (I'm pretty sure rename column works currently) is sort of a huge hack.

This is the new version of renameColumn in the upcoming 0.6.0-wip branch:
https://github.com/tgriesser/knex/blob/6a2e3352a7cb9e6f989e7f572ef7ea1e363ef8e4/lib/clients/sqlite3/schema/tablecompiler.js#L81

I'm going to re-use most of the code there as well in dropping a column / changing types which is something I know @ErisDS was also after. Hopefully should have this release done in the coming weeks.

[p-baleine]

@tgriesser

Thanks for your reply.
Your new version of renameColumn is very great! 👍

I am looking forward to your new release.

[ErisDS]

This is excellent news 👍

Other than watching issues, is there a good way to follow along with progress? A roadmap, mailing list, irc channel or such where all this progress is organised? I'm super keen to keep up to date & help out where possible.

[tgriesser]

Yeah if you want to drop by #bookshelf I'm typically in there (need to advertise that channel a bit). Will look into a roadmap shortly after big changes with the internals have stabilized.

[nikku]

Looks like dropping foreign key columns is possible nowadays, too.

[ErisDS]

Not sure if being able to change the length of TEXT columns is on the radar as part of this, so just dropping a note that this is something Ghost needs.

I know that 0.7 is in the works, does that get us any closer towards the 'stablised internals' that were a pre-requisite for getting some 'clever-ass shit' documentation? 😜

[dkushner]

@tgriesser, @bendrucker: Has there been any update on this functionality? Writing raw queries in every migration I have that modifies a column (virtually all of them), is getting a bit frustrating.

[bendrucker]

No, sorry

[ricardograca]

Would it be possible to modify the renameColumn() method (and eventually other column modifying methods) so that it returns the modified column object so that one can chain any of the column chainable methods to it? Namely I'm trying to update a reference after renaming a column:

table.renameColumn('stuff_id', 'some_stuff_id').references('some_stuff.id')

This produces an error saying that:

Object #<TableBuilder_PG> has no method 'references'

If that would be a breaking change (looks like it, although I'm not sure if anyone is using the current return object) then at least provide a way to select a column that already exists.

[ErisDS]

Sorry to effectively be nagging on an ancient issue - but I'm desperate to be able to modify the length of text columns. I'd be more than happy to pitch in and try to contribute this if there's a consensus on what the API should be?

[olalonde]

Is it yet possible to modify column types or should I use .raw()?

[ricardograca]

@olalonde Nope, you should use .raw().

[factoidforrest]

Nullable exists but it isn't possible to select existing columns like

t.column('existing_column').modifySomehow()

But your team has said you are working on this.

[ErisDS]

Perusing the code it looks like there may have been some progress towards this? https://github.com/tgriesser/knex/blob/master/src/schema/columnbuilder.js#L71

Any chance of an update? Where are you at with these changes, what could be done to help?

[niftylettuce]

Looks like we can use .alterTable method or something?

[danswater]

Bump!

[ErisDS]

I'm as keen as anyone to see some movement on this issue. If you agree, and would like to inspire some movement on this issue, the best thing to do is to take the time to explain what your use case is and why you need knex to add support. Perhaps even offering some help - docs & QA are just as useful as code.

Adding a comment without content isn't likely to inspire any change in the status quo here, in fact I think it's more likely that the people you want to hear you hit "unsubscribe" on the issue as a result.

[kuanb]

Per @ErisDS suggestion, my situation is: I have a schema with col type string I would like to change to datetime. I'm open to running a process where I drop the existing string col and add a new one with datetime, and would appreciate documentation on how to go about doing this.

[rhys-vdw]

@kuanb This is a feature request thread - that means there is no specific API for doing this. At the moment you'll need to use other schema.table methods to create a new column, copy the data over, and then delete the original column. If you need more help with this I'd suggest opening a new issue or creating a StackOverflow question.

[kuanb]

@rhys-vdw apologies for any confusion I caused. My intent was to provide a "response" to @ErisDS comment that we should include use cases to demonstrate need for something like this to occur. I understand this is a feature request.

I currently am able to perform the migration fine using knex.raw( ... ) but wanted to demonstrate that I was in a situation where such a feature from the knex library would have been nice. Perhaps I misunderstood `ErisDS request.

[rhys-vdw]

Ah right, I was just responding to "and would appreciate documentation on how to go about doing this.". No stress. :)

[ivawzh]

+1 for column date type modification

[tkellen]

Honest question. Is this abstraction actually serving a useful purpose? Just write SQL.

Knex should be a query builder, not a migration system. Here is an example of a migration system that makes at least a little sense:

https://github.com/tkellen/node-postgres-ansible-api-boilerplate/blob/master/careen.js
https://github.com/tkellen/node-postgres-ansible-api-boilerplate/blob/master/package.json#L6-L9
https://github.com/tkellen/node-postgres-ansible-api-boilerplate/tree/master/migrations

[ivawzh]

Why do I have to touch SQL? Imagine a case that if one day I want to switch from PostgreSQL to MySQL. How long will it take for me to learn all the gotchas on both side and rewrite all the raw SQL migrations. Plus, if ActiveRecord can do migration right, why Knex can not?

[ricardograca]

@tkellen While I'm more inclined to agree with you these days, I think there is still value in having an interface for easily changing a database's structure from the javascript side.

While the argument about easily changing database servers often comes up, I think realistically that's not the main use case, due to the reasons you mentioned, and because when you create an application you do it with a set of dependencies in mind, including the database server. From my point of view the main benefit is just having a standard interface that allows this functionality in all your apps without having to constantly write the same thing over and over again, or constantly including your own private helper methods.

That is, instead of everybody writing their own code for this, and ending up with a lot of different ways it is accomplished, we would have a sort of standard way of doing it. I know that SQL is already something standard, but it's too generic. Using SQL alone you can do it anywhere and at any time, but this feature is about tailoring it to something more specific, making it more predictable.

Changing a database's structure is probably something that is shared by a great number of programs that use a database, so it makes a good candidate for having a module that does all the heavy lifting.

What's your suggestion to this problem?

[ivawzh]

Thanks, I'm trying out db-migrate now. So far so good.

[elhigu]

@joepie91

1. There is no other developer documentation, except the code. CONTRIBUTING.md describes some stuff how to run tests etc.
2. As far as I know there has been no effort to implement this. If one starts to implement this I can help by trying to find some related pull requests where e.g. column renaming is implemented.
3. I'm not aware of any blockers, why this couldn't be implemented. I don't believe that one has to change internal architecture for this. I would recommend to choose one specific thing what you like to modify and implement that first and then select next thing what to modify and implement that. I suppose that the biggest challenge is to find a correct query for every dialect for certain change.

People who knows better feel free to correct / add info here, I just wanted to answer something to start with so that question won't just stay hanging there in silence...

[RWOverdijk]

A huge 👍 here.

[ErisDS]

@elhigu I think this is a mistake? There are 4 features requested here:

1. rename a column
2. change the data type
3. add or drop the null constraint
4. change the default

Only 1 and 3 are implemented. 2 is still a pretty enormous missing feature?

Wait, ignore me - I see the PR that adds this is #1914 - the link confused me totally! This is super exciting

[elhigu]

@ErisDS yeah, I wanted to link the original PR with most off the discussion, should have linked both :)

[PierBover]

Ok, so we can alter a table, a column, etc.

Is there a simple way of seeding only to a column?

[elhigu]

@PierBover what do you mean by "seeding only to a column"?

[PierBover]

If for example we add a new column, is there a simple way of filling it with values other than using raw queries?

[elhigu]

@PierBover you either use defaultTo() when creating the column or first create data and then update it with normal knex queries. There is no additional magic in knex for this (and I don't think there should be either).

return knex.schema.alterTable('MyTable', t => {
  // deafault value / expression to seed (one cannot create select queries here)
  t.integer('newColumn').defaultTo(1);
  t.integer('newColumn2').defaultTo(knex.raw('some expression e.g to create random data')); 
}).then(() => {
  // just create populate query after creating new column like normally done with SQL
  return knex('MyTable').update({ newColumn: 2, newColumn2: 3 });
});

[ricardograca]

@PierBover Yes: http://knexjs.org/#Seeds-CLI

[PierBover]

Right @ricardograca but that is for seeding the whole table, no?

Thanks for the suggestion @elhigu 👍

[renerpdev]

I am starting a new project to seed tables with knex.js, here. Any help will be nice!