Skip to content

Modifying columns #46

Closed
Closed

Description

@ErisDS

I think it is a reasonable expectation that it would be possible to:

  • rename a column
  • change the data type
  • add or drop the null constraint
  • change the default

And probably other things that I have not thought of.

I would imagine the syntax for doing this would be something like

 knex.Schema.table('table_name', function (t) {
        t.string('my_column').renameTo('something_else');
        t.string('my_column').changeTo('text');
        t.string('my_column').nullable() < adds nullable if not already present
        t.string('my_column').notNullable() < removes nullable if present
        t.string('my_column').defaultTo('whatever') < adds or updates the default
  });

Maybe there needs to be something extra in the chain, possibly at the knex.Schema.table level to indicate that this is a modify statement not an add.

I realise that this is tricky to implement across various databases, especially in SQLite where you have to create a whole new table, but in a system which requires migrations, without these tools it is going to be necessary to use knex.raw and write all the migrations for each DB supported, which sort of defeats the point of having a nice ORM especially one which is about to support migrations.

Activity

  1. tgriesser commented on Sep 13, 2013

    @tgriesser
    Member

    Checklist:

    • Renaming Columns
    • Change Null
    • Change Default
    • Change Datatype
  2. fjorgemota commented on Jan 17, 2014

    @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.

  3. bendrucker commented on Jan 17, 2014

    @bendrucker
    Member

    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.

  4. gregberge commented on Jan 24, 2014

    @gregberge

    We need this functionality too for @lemonde CMS.

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

  5. bendrucker commented on Jan 24, 2014

    @bendrucker
    Member

    @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.

  6. ErisDS commented on Jan 24, 2014

    @ErisDS
    ContributorAuthor

    @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 😉

  7. tgriesser commented on Jan 24, 2014

    @tgriesser
    Member

    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?

  8. ErisDS commented on Jan 24, 2014

    @ErisDS
    ContributorAuthor

    @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.

  9. bendrucker commented on Jan 24, 2014

    @bendrucker
    Member

    @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.

  10. tkellen commented on Jan 24, 2014

    @tkellen
    Contributor

    @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.

  11. johanneslumpe commented on Jan 24, 2014

    @johanneslumpe
    Contributor

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

    Sent from my iPhone

    On Jan 24, 2014, at 5:25 PM, Tyler Kellen notifications@github.com 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.

  12. p-baleine commented on Feb 19, 2014

    @p-baleine
    Contributor

    @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');
          });
      }
    };
  13. tgriesser commented on Feb 19, 2014

    @tgriesser
    Member

    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.

  14. p-baleine commented on Feb 19, 2014

    @p-baleine
    Contributor

    @tgriesser

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

    I am looking forward to your new release.

  15. ErisDS commented on Feb 20, 2014

    @ErisDS
    ContributorAuthor

    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.

  16. 34 remaining items

  17. ricardograca commented on Sep 7, 2016

    @ricardograca
    Member

    @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.

  18. elhigu commented on Sep 8, 2016

    @elhigu
    Member

    @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.

  19. RWOverdijk commented on Oct 27, 2016

    @RWOverdijk
    Contributor

    A huge 👍 here.

  20. elhigu commented on Feb 16, 2017

    @elhigu
    Member

    Implemented in #1759

  21. ErisDS commented on Feb 17, 2017

    @ErisDS
    ContributorAuthor

    @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

  22. elhigu commented on Feb 17, 2017

    @elhigu
    Member

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

  23. PierBover commented on Jun 11, 2017

    @PierBover

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

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

  24. elhigu commented on Jun 20, 2017

    @elhigu
    Member

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

  25. PierBover commented on Jun 20, 2017

    @PierBover

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

  26. elhigu commented on Jun 20, 2017

    @elhigu
    Member

    @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 });
    });
  27. ricardograca commented on Jun 20, 2017

    @ricardograca
    Member
  28. PierBover commented on Jun 20, 2017

    @PierBover

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

    Thanks for the suggestion @elhigu 👍

  29. renerpdev commented on Jun 12, 2018

    @renerpdev

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

  30. added a commit that references this issue on Sep 23, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions