mysql2-extended
v2.0.0
Published
Thin convenience layer for mysql2 library.
Downloads
39
Readme
MySQL2 Extended
Thin convenience extension for the
mysql2
library.
You may be interested in this if:
- You want something more than the raw
query
function provided bymysql2
. - You don't want a full-blown ORM.
- You like hand-crafted SQL.
- You want typed database results.
Installation:
npm install mysql2-extended mysql2
yarn add mysql2-extended mysql2
Usage
Setup
import { MySQL2Extended } from 'mysql2-extended';
import { createPool } from 'mysql2/promise';
const pool = createPool({
host: 'localhost',
user: 'root',
password: 'password',
database: 'test',
});
const db = new MySQL2Extended(pool);
Typings
Types can be passed to each query function in order to get typed data back without casting. Note that the types are not validated against the actual data, but just a convenient way for the developer to add types.
This package provides generic types to help with common workflows. For example, you probably want types for your tables, like this:
type User = {
id: number; // Auto increment in MySQL
email: string;
firstname: string | null;
lastname: string | null;
active: boolean; // Default 'true' in MySQL column definition
};
When creating (inserting) a new user, you don't want to be forced to
provide values for columns that have default values in the database. You
can use the OptionalDefaultFields
utility type:
type UserDefaultFields = 'id' | 'firstname' | 'lastname' | 'active';
// CreateUser will only enforce 'email' as required
type CreateUser = OptionalDefaultFields<User, UserDefaultFields>;
Querying
Note that all supplied parameters will use parameter binding in the underlying database driver.
Insert
Single
await db.insert<CreateUser>('users', {
email: '[email protected]',
firstname: 'Bob',
active: true,
});
INSERT INTO `users` (`email`, `firstname`, `active`) VALUES ('[email protected]', 'Bob', 1);
Bulk
await db.insert<CreateUser>('users', [
{ email: '[email protected]' },
{ email: '[email protected]' },
]);
INSERT INTO `users` (`email`) VALUES ('[email protected]'), ('[email protected]');
Select
Specific columns
const users = await db.select<Pick<User, 'firstname' | 'lastname'>>(
['firstname', 'lastname'],
'users',
);
SELECT `firstname`, `lastname` FROM `users`;
All columns
First column argument used above is optional. If no columns are specified, all columns will be selected:
const users = await db.select<User>('users');
SELECT * FROM `users`;
WHERE conditions
Conditions provided in the object will be combined with AND. For more complex use-cases, see Raw for now.
const users = await db.select<User>('users', { id: 1, active: true });
SELECT * FROM `users` WHERE `id` = 1 AND `active` = 1;
Limit and offset
const users = await db.select<User>(
'users',
{ id: 1, active: true }, // undefined/null if no conditions are present
{ limit: 10, offset: 5 },
);
SELECT * FROM `users` WHERE `id` = 1 AND `active` = 1 LIMIT 5, 10;
Ordering
const users = await db.select<User>(
'users',
{ id: 1, active: 1 },
{
limit: 10,
offset: 5,
order: ['id', 'desc'],
// order: [['firstname', 'desc'], ['lastname', 'asc']],
},
);
SELECT * FROM `users`
WHERE `id` = 1 AND `active` = 1
ORDER BY `id` DESC
LIMIT 5, 10;
Update
All
await db.update<User>('users', { active: true })
UPDATE `users` SET `active` = 1;
WHERE conditions
await db.update('users', { firstname: 'Bob' }, { id: 1 }, { limit: 1 });
UPDATE `users` SET `firstname` = 'Bob' WHERE `id` = 1 LIMIT 1;
Delete
All
await db.delete('users');
DELETE FROM `users`;
WHERE conditions
await db.delete<User>('users', { id: 1, active: true });
DELETE FROM `users` WHERE `id` = 1 AND `active` = 1;
Raw
// Multiple rows
const users = await db.query<{ firstname: string }>('SELECT firstname FROM users LIMIT 2');
// One row
const [user] = await db.query<User>('SELECT * FROM users LIMIT 1');
// Parameter binding
await db.query<User>('SELECT * FROM users LIMIT ?', [1]);
Transactions
Managed transaction
A managed transaction will automatically commit if the supplied callback doesn't throw/reject any error. If the callback throws/rejects, it will rollback the transaction, and re-throw the error that caused the rollback.
Successful example
await db.transaction(async t => {
await t.insert<CreateUser>('users', { email: '[email protected]' });
await t.insert<CreateUser>('users', { email: '[email protected]' });
});
BEGIN;
INSERT INTO `users` (`email`) VALUES ('[email protected]');
INSERT INTO `users` (`email`) VALUES ('[email protected]');
COMMIT;
Rollback example
try {
await db.transaction(async t => {
await t.insert<CreateUser>('users', { email: '[email protected]' });
throw new Error('Oops')
await t.insert<CreateUser>('users', { email: '[email protected]' });
})
} catch (err) {
// err === Error('Oops')
}
BEGIN;
INSERT INTO `users` (`email`) VALUES ('[email protected]');
ROLLBACK;
Manual transactions
// Manual transaction (user controls commit/rollback)
const transaction = await db.begin();
await transaction.insert<CreateUser>('users', { email: '[email protected]' });
await transaction.insert<CreateUser>('users', { email: '[email protected]' });
await transaction.commit(); // Or rollback()
BEGIN;
INSERT INTO `users` (`email`) VALUES ('[email protected]');
INSERT INTO `users` (`email`) VALUES ('[email protected]');
COMMIT;
Future/TODO
- Increase performance by optimizing hot code paths.
- Return affected row count.
- Make the library work with more databases.
- Be able to generate SQL queries in specific formats/flavours.
- Support for more complex condition objects.
- Run tests with an actual database connection.