Unique together

[colegleason]

It would be nice to specify that two or more columns have to be unique together, but not unique themselves.

[notheotherben]

You can do this by specifying multiple primary keys, which will cause the database engine to treat their combination as a unique primary key.

db.define('model', {
    field1: String,
    field2: String
}, {
    id: ['field1', 'field2']
});

[dxg]

I've also just improved the unique validator to support scope. See the wiki.

We should also at some point add the following syntax:

db.define('item', {
  name:     { type: 'string', unique: 'name_cat' },
  category: { type: 'string', unique: 'name_cat' }
});

// CREATE UNIQUE INDEX item_name_cat ON item (name, category);

[dresende]

Thank you @dxg , that idea is exactly what I was thinking when I read this issue.

[notheotherben]

Surely it should be possible to use the suggested new syntax to generate something along the lines of

INDEX IDX_scope (`name`, `category`)

Thus ensuring the uniqueness on the database itself...

Disclaimer: I haven't actually checked the syntax there, just guessing at it from memory.

[dxg]

Question is though, should adding those unique: 'name_cat' options cause a unique validator to be automatically added to the model? Adding required: true automatically adds a required validator.

[notheotherben]

I think, if possible, we should avoid client side validators and rather rely on the database engine (since it will be a factor faster). When we add required: true we are also including that information in the database definition by appending NOT NULL to any columns.

I'd recommend that if the database is able to, we avoid adding the validator and instead work with appending unique index statements to the database instantiation SQL. (Obviously we'd need to mention this, since some people with pre-written databases without these indexes would not experience any difference between unique: false and unique: true or unique: 'name').

[dxg]

The question is though, do the supported database servers return parseable enough errors for that to be possible?

There's another problem; afterValidate and beforeSave will both fire before the unique validation error comes back. This is not what the user will expect.

[notheotherben]

I'm not sure about how parsable the errors would be, but I don't think we necessarily have to wrap them in anything special. Ideally the user shouldn't be running queries which violate the DB schema in the first place.

Maybe we can have both the validator and the unique property and allow them to fulfil different tasks. The validator can act as a stopgap between bad input into the user's application and the database queries, while the unique property can act as a database schema restriction. I think given the way the two would be used that it makes sense to do it that way, and the user would expect schema errors to be returned by the database engine post query.

Essentially we could have the following

db.define('model', {
    id: { type: 'text', required: true, unique: true },
    first_name: { type: 'text', required: true, unique: 'name' },
    last_name: { type: 'text', required: true, unique: 'name' },
    email: { type: 'text', unique: true }
}, {
    id: 'id',
    validations: {
        first_name: [orm.enforce.required(), orm.enforce.unique('name')],
        last_name: [orm.enforce.required(), orm.enforce.unique('name')],
        email: [orm.enforce.patterns.email(), orm.enforce.unique().orUndefined()]
    }    
});

It should then generate the following MySQL table creation query, using the model name and unique key if present, or the name of the field if unique = true to generate the index name.

CREATE TABLE model (
    id VARCHAR(255) NOT NULL, 
    first_name VARCHAR(255) NOT NULL, 
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    PRIMARY KEY(id),
    UNIQUE INDEX IX_model_name (first_name, last_name),
    UNIQUE INDEX IX_model_email (email)
    );

We can then keep the enforce validator for unique and allow that to do pre-query validation on the fields if the user wishes.

How does that sound to you guys?

[dxg]

So the idea is to stick with what we have, and add the { type: 'text', required: true, unique: 'name' } syntax.
I'm happy with that.

[dresende]

I think the required option of a property, although it's validated in the database, it never gets there because there's code to check this. I think this is good if we can avoid the database (because the link can be a lot slower than an if statement). For consistency, we should remove that if and:

1. Add an automatic required validation to the property (user will then see an error he might expect, with detailed information);
2. Or rely on database (although not bad, I don't think this is the best option).

The unique option should then follow the same pattern.

[notheotherben]

I think the difference with the unique option is that it requires a query to the database to find any duplicate keys, which negates the efficiency bonus that we get with required. In the case of required we can avoid a query by implementing the logic in ORM whereas with unique we would be moving from a single insert query (with an error response) to two queries (one to determine whether there are any duplicate keys in the database and one to perform the insert/update) instead of the single update query which would be issued otherwise.

It also introduces the issue of handling NoSQL datastores, or databases which don't support whatever query we use to check for uniqueness - which then requires each database driver to define their own implementation of an isUnique function.

My vote would be to split database logic and application logic up into the properties and opts parameters for db.define. Thus, anything defined within properties will only affect how the database is generated and queried while opts.validations would allow application side validation of data. It makes it easy to comprehend where the validations will take place, and what error messages (and when) you can expect.

[dresende]

I agree with you that unique validator could be avoided by just trying to insert it. Perhaps we could just check what kind of error is returned for each driver and have a way for instance to know that. If we can do this, we might deprecate the unique validator since it might be inefficient on large databases. I have to check how to do this in NoSQL datastores.

[notheotherben]

Certainly is an option, maybe have a central error type wrapper within node-sql-query or ORM which translates, on a per driver basis, error codes into a number of standardized forms. Would allow us to easily interop for other features in the future, and allow the database engines to handle what they're best at while ORM acts as an interface layer (which it's best at). As for NoSQL, at the moment I've added a bit of code which disables the unique validator for NoSQL database engines, however from what I've gathered so far it would be extremely difficult to implement such a thing given the emphasis on horizontal scalability present in most of those engines (which attempts to do away with keys and relationships). I'm currently taking a course on MongoDB in Node.js and I'll see what (if any) of that I can apply to this project.