In the past two months, whilst celebrating Chinese New Year, we have most of the available work force focused on enhancements about data types, validations, and compatibility issues about MySQL.
In February, we welcome two new contributors 👏👏 @LB4027221 and @luckydrq.
More data types were added in v2.2.x, for example those specific numeric types in MySQL like TINYINT, SMALLINT, or MEDIUMINT. Those types are supported in SQLite allegedly as well, whilst in PostgreSQL only SMALLINT is available, we’ve got those edge cases covered in the correponding database drivers. Hence the types can be used universally:
import { Bone, DataTypes } from 'leoric';
const { TINYINT, SMALLINT, BIGINT } = DataTypes;
export class User extends Bone {
static attributes = {
id: BIGINT,
age: SMALLINT,
sex: TINYINT(1),
}
}
For those who prefer leaving the model attributes initialized with database metadata, the newly added data types will be recognized as well. For Example, if the DDL of users table is like below:
CREATE TABLE users (
id BIGINT PRIMARY KEY,
age SMALLINT DEFAULT 0 NOT NULL,
sex TINYINT(1) NOT NULL,
);
The corresponding model attributes will be initialized by Leoric accordingly
class User extends Bone {};
await connect({ database: 'foo', models: [ User ] });
assert.equal(User.attributes.sex.type.toSqlString(), 'TINYINT(1)');
https://github.com/cyjake/leoric/pull/266
| DB/DataType (validate or not in SQL operations) | INTEGER | DATE |
|---|---|---|
| MySQL | 🔲query ✅insert | ✅query ✅insert |
| SQLlite | 🔲query 🔲insert | 🔲query 🔲insert |
| PostgresSQL | ✅query ✅insert | ✅query ✅insert |
We’ve enhanced the validation logics a lot in v2.0.2, most of the validations will be triggered in advance if the database used does so. For example, in MySQL all of the SQL below yield exception:
mysql> SELECT now() = '2022-13-12';
ERROR 1525 (HY000): Incorrect DATETIME value: '2022-13-12'
mysql> SELECT * FROM articles WHERE gmt_create >= '2022-13-12';
ERROR 1525 (HY000): Incorrect TIMESTAMP value: '2022-13-12'
mysql> INSERT INTO articles (gmt_create) VALUES ('2022-13-12');
ERROR 1292 (22007): Incorrect datetime value: '2022-13-12' for column 'gmt_create' at row 1
In v2.0.2 we will validate in advance to prevent the error query hit database.
In previous versions, when the models have Model.attributes left blank and have Leoric to initialize them accordingly, the default values won’t be set. The advantage side of this behavior is that when generating INSERT or UPDATE queries, database can take of the default values handling, the disadvantage side is that when coming to scenarios like below:
CREATE TABLE users (
id BIGINT PRIMARY KEY,
level INTEGER DEFAULT VALUE 0 NOT NULL
);
and the User model has left User.attributes blank:
class User extends Bone {}
const user = new User();
assert.equal(user.level, 0); // user.level -> null
the newly instantiated user won’t have user.level default to 0, which is fixed in v2.0.4
MySQL supports operations likeUPDATE ... ORDER ... LIMIT or DELETE ... ORDER ... LIMIT, which is not fully supported in versions before Leoric v2.x, such as use cases like below:
await User
.where({ status: STATUS_BLOCKED })
.update({ nickname: '*blocked*' })
.order('id', 'desc')
.limit(10);
// update the nickname of 10 users blocked recently
ORDER and LIMIT clause were omitted in previous versions, which is fixed in v2.0.2; the DELETE equivalent is fixed in v2.1.0. This feature is MySQL only, neither SQLite nor PostgreSQL supports it.
Ordering with alias column is fixed in v2.0.2
await User.count().group('sex').having('count > 0').order('count', 'desc');
the Sequelize equivalent of the query above is like below:
await User.find({
attributes: sequelize.fn('COUNT', '*'),
group: 'sex',
having: {
count: { $gt: 0 },
},
order: [[ 'count', 'desc' ]],
});
createdAt won’t be set when UPSERT in previous versions. For example,
Post.upsert({ title: 'yes' });
the SQL equivalent is like below:
INSERT INTO `posts`
(`title`, `updated_at`)
VALUES
('yes', `CURRENTTIME`)
ON DUPLICATE KEY UPDATE
`title` = VALUES(`title`),
`updated_at` = VALUES(`updated_at`)
which left the createdAt attribute empty, and might cause the database to complain if createdAt can not be NULL. With the issue fixed in v2.1.1, Leoric can now generate correct SQL like below:
INSERT INTO `posts`
(`title`, `created_at`, `updated_at`)
VALUES
('yes', `CURRENTTIME`, `CURRENTTIME`)
ON DUPLICATE KEY UPDATE
`title` = VALUES(`title`),
`updated_at` = VALUES(`updated_at`)
The type declarations of new Realm(), realm.connect(), and realm.define() were added in v2.1.1 and v2.2.0, but there still might be some overlooked corners. We’d recommend taking cnpm/cnpmcore or eggjs/egg-orm!examples/typescript as examples of writing Egg applications in TypeScript.
To better optimize the type declrations in models, we are experimenting TypeScript decorations. Here is what we’ve accomplished so far:
import { DataTypes } from 'leoric';
const { MEDIUMINT } = DataTypes;
class Post extends Bone {
@Column()
id: bigint;
@Column({ name: 'gmt_create' })
createdAt: Date;
@Column({ name: 'gmt_modified'})
updatedAt: Date;
@Column({ name: 'gmt_deleted' })
deletedAt: Date;
@Column(MEDIUMINT)
wordCount: number;
}