How to setup relations between tables ?

[ghost]

Hi guys,

I'm testing an app with Knex right now, and having a bit of a problem when preparing my schemas. Since app.configure doesn't wait to be finished to go on the next one, i'm getting "relation does not exist" errors half of the time when loading with my empty database.

I'm using the app structure from the generator for postgres (which is loaded with sequelize by default). So far, i got this :

// services/index.js

const knex = require( 'knex' );
const authentication = require( './authentication' );
const user = require( './user' );
const client = require( './client' );

module.exports = function () {
    const app = this;

    const knex_connection = knex( {
        client: 'pg',
        connection: app.get( 'postgres' )
    } );
    app.set( 'knex', knex_connection );

    app.configure( authentication );
    app.configure( user );
    app.configure( client );

}

// services/user/index.js

const service = require( 'feathers-knex' );
const user = require( './user-model' );
const hooks = require( './hooks' );
const auth = require( '../../filters/auth' );

module.exports = function () {
    const app = this;

    return user( app.get( 'knex' ) ).then( () => {

        const options = {
            Model: app.get( 'knex' ),
            name: 'users',
            paginate: {
                default: 1,
                max: 10
            }
        };

        // Initialize our service with any options it requires
        app.use( '/users', service( options ) );

        const userService = app.service( '/users' );
        userService.before( hooks.before );
        userService.after( hooks.after );
        userService.filter( [ auth.isAuthenticated(), auth.isOwner() ] );

    } );

};

// services/user/user-model.js

module.exports = function ( knex ) {

    const schema = function() {
         return knex.schema.createTable( 'users', table => {

            table.increments( 'id' ).primary();
            table.string( 'email' ).notNullable().unique();
            table.string( 'password' ).notNullable();
            table.specificType( 'roles', 'varchar(255)[]' ).notNullable();
            table.timestamp( 'created_at' ).notNullable().defaultTo( knex.raw( 'now()' ) );
            table.timestamp( 'updated_at' ).notNullable().defaultTo( knex.raw( 'now()' ) );

        } );
    }

    return knex.schema.hasTable( 'users' ).then( exists => !exists && schema() );

};

Is it possible to make app.configure wait or i am doing it the wrong way ?

Thanks.

[jayalfredprufrock]

I think the issue might be this line: exists => !exists && schema()

I'm no promise expert, but it looks to me that this lambda is returning a boolean and not a promise because of the &&. This causes the main promise to resolve before schema() resolves, which would explain why sometimes your tables aren't created in time. I think you'd want something more like the following:

return knex.schema.hasTable( 'users' ).then( exists => exists ? Promise.resolve() : schema() );

[ghost]

Hi @jayalfredprufrock ,

Unfortunately, this hasn't changed anything. The configure is still not waiting for the createTable to be finished.

I tried this way, but i'm still getting the relation errors sometimes :

const schema = new Promise( function( resolve ) {
        return knex.schema.createTableIfNotExists( 'users', table => {

            table.increments( 'id' ).primary();
            table.string( 'email' ).notNullable().unique();
            table.string( 'password' ).notNullable();
            table.specificType( 'roles', 'varchar(255)[]' ).notNullable();
            table.timestamp( 'created_at' ).notNullable().defaultTo( knex.raw( 'now()' ) );
            table.timestamp( 'updated_at' ).notNullable().defaultTo( knex.raw( 'now()' ) );

        } ).then( resolve );
    } );

    return knex.schema.hasTable( 'users' ).then( exists => !exists ? schema : Promise.resolve() );

[jayalfredprufrock]

Fair enough, I've never used configure() to set up my database before. I take advantage of knex's migration and seeding features which tie in nicely with npm scripts. Perhaps somebody with more experience with feathers can help you figure out what's going on. Good luck!

[ghost]

@jayalfredprufrock I'd be interested in knowing how you're doing it then, if you have time for a short explanation ? Or do you have some open source project where i could see how you're doing it ? Thanks.

[jayalfredprufrock]

Unfortunately I'm only using this approach on closed-source projects at the moment, but I'm really not doing anything special. I'm just utilizing the knex CLI to build and seed my database before testing and deploying. See http://knexjs.org/#Migrations-CLI

My knexfile looks something like this and is in a top level directory "db":

module.exports = {

  development: {
    client: 'sqlite3',
    connection: {
      filename: __dirname + '/development.sqlite3'
    },
    seeds: {
      directory: './seeds'
    },
    migrations: {
      tableName: 'migrations',
      directory: './migrations'
    },
    useNullAsDefault: true
  }
}

Then I connect everything with some scripts:

  "scripts": {
    "test": "npm run seed-db && npm run build-server && NODE_ENV=development tape -r babel-register \"test/**/*.js\" | tap-difflet",
    "develop": "npm run build-server && NODE_ENV=development node dist/",
    "start": "NODE_ENV=production npm run build-server && npm run build-client && NODE_ENV=production node dist/",
    "build-server": "babel --plugins transform-runtime -d dist/ src/",
    "build-client": "NODE_ENV=production webpack -p --progress --colors",
    "build-db": "rimraf db/development.sqlite3 && npm run knex -- migrate:latest",
    "seed-db": "npm run knex -- seed:run",
    "knex": "knex --knexfile='db/knexfile.js'"
  }

Hope that helps!

[ghost]

Ok, thanks for you help, i'll try this approach ;)

[daffl]

Looks like we can close this. Side note: feathers-bootstrap which will be used in the new generator will also allow asynchronous application configuration so it might make this a little easier.

[eric-burel]

Hello evrybody, sorry for the noob question but I absolutely don't manage to set this up. I have two models users and relations and I want to connect them. The use of Knex is mandatory (I use an already existing db so I need low level feature, not sequelize).

relations/user_roles.relation.js

module.exports = function(
  app,
  options = {
    usersTableName: 'users',
    rolesTableName: 'roles',
    usersForeignKey: 'role_id',
    rolesKey: 'id'
  }
) {
  const db = app.get('knexClient');
  db.schema
    .table('users', table => {
      table.integer(options.usersForeignKey).unsigned();
      table
        .foreign(options.usersForeignKey)
        .references(`${options.rolesTableName}.${options.rolesKey}`);
    })
    .then(() => console.log('Done setting up relations for users and roles'))
    .catch(console.log);
  return db;
};

app.js

// Configure other middleware (see `middleware/index.js`)
app.configure(middleware);
app.configure(authentication);
// Set up our services (see `services/index.js`)
app.configure(services);
// Set up event channels (see channels.js)
app.configure(channels);
// Set up relations between services and models
// FAIL because I can't wait for the services configuration to be done
app.configure(relations);

So basically I'd like to be able to wait for the services configuration to be done before setting up the relationships between all of them, but I can't find a working solution.

[ejmudrak]

@eric-burel @daffl Did you ever happen to find a solution to this issue? I'm having having issues with async configuration of knex models with relations.