0.3.1 • Published 1 year ago
musqrat v0.3.1
musqrat
A lightweight, strongly typed wrapper for using MySQL in Node.JS.
Inspired by using mongoose
, I wanted something with a similar chaining behaviour for MySQL plus the benefits of TypeScript while building queries.
Dependencies
- mysql2
- TypeScript
Currently Supports
- Connecting to a MySQL database
- Define tables using a TypeScript type/interface
- Basic Table Operations
Examples
Connecting to a MySQL database
import musqrat from "musqrat";
// Uses the mysql.Pool type: https://github.com/mysqljs/mysql#pool-options
musqrat.connect({
username: "username",
password: "password",
host: "localhost",
database: "testDB",
});
if (musqrat.connected) {
console.log("Woohoo!");
}
await musqrat.disconnect();
Defining a table
interface TableSchema = {
tableId: number;
column: string;
nullableColumn?: string;
}
const Table = musqrat.initTable<TableSchema>('TableName');
Building Queries vs Executing Queries
// Each query operation can be used to build a prepared statement or execute against the database if the
// connection object is given.
console.log(Table.delete().query);
await Table.delete().exec();
Deleting
// Deletes everything, no conditions. Use limit/orderBy/where to be more specific.
await Table.delete().exec();
Inserting
// Insert accepts a single object or array of objects.
// INSERT INTO TableName (tableId, column) VALUES (1, 'value');
await Table.insert({tableId: 1, column: 'value'}).exec();
// INSERT INTO TableName (tableId, column) VALUES (1, 'value'), (2, 'another');
await Table.insert([{tableId: 1, column: 'value'}, {tableId: 2, column: 'another'}]).exec();
// You can also identify primary keys when creating the table that can be excluded from insertion
// (i.e. if they are AUTO_INCREMENT)
const Table = musqrat.initTable<TableSchema, 'tableId'>('TableName');
// Now this is invalid
await Table.insert({tableId: 1, column: 'value'}).exec();
// This is valid
await Table.insert({column: 'value'}).exec();
Selecting
// SELECT tableId, column FROM TableName;
await Table.select("tableId", "column").exec();
// SELECT * FROM TableName;
await Table.select().exec();
Updating
// Update accepts a single object or array of objects
// UPDATE TableName SET column = 'new value';
await Table.update({ field: "column", value: "new value" }).exec();
// UPDATE TableName SET column = 'new value', nullableColumn = NULL;
await Table.update([
{ field: "column", value: "new value" },
{ field: "nullableColumn", value: null },
]).exec();
Group By
// Available on select only
// SELECT * FROM TableName GROUP BY column;
await Table.select().groupBy("column").exec();
Inner Join
// Available on select only
interface JoinSchema = {
joinId: number;
tableId: number;
}
// SELECT tableId, joinId FROM TableName INNER JOIN JoinTable ON tableId = tableId;
await Table.select<[JoinSchema]>('tableId', 'joinId').innerJoin('JoinTable', 'tableId', 'tableId').exec();
Limit
// Available on select, update, delete
// SELECT * FROM TableName LIMIT 1;
await Table.select().limit(1).exec();
Order By
// Available on select, update, delete
// SELECT * FROM TableName ORDER BY column ASC;
await Table.select().orderBy("column").exec();
// SELECT * FROM TableName ORDER BY column DESC;
await Table.select().orderBy("column", "DESC").exec();
Where
// Available on select, update, delete
// SELECT * FROM TableName WHERE tableId IN (1, 2, 3);
await Table.select().where("tableId", "in", [1, 2, 3]).exec();
// UPDATE TableName SET nullableColumn = NULL WHERE (column = 'specific' AND nullableColumn IS NOT NULL);
await Table.update({ field: "nullableColumn", value: null })
.where({
AND: [
{ field: "column", operator: "=", value: "specific" },
{ field: "nullableColumn", operator: "IS NOT", value: null },
],
})
.exec();
// WHERE conditions can be built using either a single where condition or an 'aggregation'
// which is an object like you see in the second example that looks like this: {AND?: <>, OR?: <>}
// You can nest those as far down as you need to.
TODO
- Bugs
groupBy
can't be called in a 'logical' (to me) order.where
is usually first,groupBy
isn't onQueryStatment
- Improvements
- Don't allow duplicates on
select
statement fields - Overload for
inner join
that accepts just 1 parameter that is a key on both types and uses the USING syntax?
- Don't allow duplicates on
- Options to calls
- Advanced uses
- Views
- Unions
- Sub queries
- Function calls/stored procs?
0.3.0
1 year ago
0.2.1
1 year ago
0.2.0
1 year ago
0.3.1
1 year ago
0.2.2
1 year ago
0.1.16
2 years ago
0.1.17
2 years ago
0.1.10
2 years ago
0.1.11
2 years ago
0.1.12
2 years ago
0.1.14
2 years ago
0.1.15
2 years ago
0.1.8
2 years ago
0.1.7
2 years ago
0.1.9
2 years ago
0.1.6
3 years ago
0.1.5
3 years ago
0.1.3
3 years ago
0.1.2
3 years ago
0.1.0
3 years ago