mysql-shaman
Advanced tools
Readme
Let's be honest, the official MySql npm package leaves a lot to be desired. While it is a solid library that works as advertised, it requires users to submit queries in string form, sacrificing discoverability for the sake of simplicity. To compensate for this, many different ORM packages have been developed, with the intent to make MySql database management simpler. Unfortunately, almost all of there ORMS are extremely bulky, and often opinionated, forcing users to decide between writing hard-coded string queries, or using a bulky ORM.
The purpose of mysql-shaman is to provide a standardized ORM interface on top of the core mysql package, without adding any additional bulkiness. Now, insteading of writing hard-coded string queries, you can write your CRUD operations using javascript expressions. This significantly improves the developer experience, allowing users to leverage IDE technology to anaylze their data-access operations.
Additionally, mysql-shaman includes a CLI that lets developers perform common database management operations, including scaffolding databases, running scripts, and more.
To use the mysql-shaman ORM in a Node JS project:
npm install mysql-shaman --save
To use myysql-shaman CLI tool:
npm install -g mysql-shaman --save
Once you have installed mysql-shaman in your typescript project, the first thing you need to to is define your data models. Each model should represent a table (or view) in the database. Data models can be defined as classes or interfaces, but we reccommend classes, as this will allow future features to leverage metadata reflection. For the purposes of this demonstration, we will use the following model:
export class User {
userId?: string;
email: string;
firstName: string;
lastName: string;
}
Next, you need to setup a "data context" class; this class will extend mysql-shaman's abstract class 'DatabaseContext'. Below is an example implementation:
import { User } from './user.ts';
export class SampleDatabaseContext extends DatabaseContext {
models = {
user: new Collection<User>()
}
}
IMPORTANT!! The name of each collection should be an exact match to a table (or view) in your database (case sensitive). For example, the property 'models.user' in the class "SampleDatabaseContext" implies there is a MySql table named "user".
Finally, you need to create an instance of your database context, then call it's initialization method. The initialization method takes a "PoolConfig" interface parameter (from official mysql package).
var database = new SampleDatabaseContext();
database.initialize({
connectionLimit: 10,
host: "localhost",
user: "user_name",
password: "password_goes_here",
database: "database_name",
waitForConnections: false
});
Now you are ready to go! Here is a sample of some of the operations you can perform; for a full list of operations, see ORM Reference section.
var database = new SampleDatabaseContext();
database.initialize({
connectionLimit: 10,
host: "localhost",
user: "user_name",
password: "password_goes_here",
database: "database_name",
waitForConnections: false
});
// OUTPUT LIST OF ALL USERS
database.models.user.find().then(console.dir);
// OUTPUT LIST OF USERS WITH LAST NAME OF 'Smith'
database.models.user
.find({
conditions: ['lastName = ?'],
args: ['Smith']
})
.then(console.dir);
// FIND SINGLE USER WITH ID OF '1'
database.models.user
.findOne({
identity: 'userId',
args: [1]
})
.then(console.dir);
// INSERT NEW USER
let user = new User();
user.email = 'test@test.com';
database.models.user.insertOne(user);
// UPDATE USER
database.models.user
.findOne({identity: 'email', args: ['test@test.com']})
.then(user => {
user.firstName = 'John';
user.lastName = 'Smith';
return database.models.user.updateOne(user, {
identity: 'userId',
args: [user.userId]
});
});
// DELETE USER
database.models.user.deleteOne({
identity: 'userId',
args: [1]
});
The mysql-shaman package uses the "PoolConfig" interface, from the official mysql package, for a database configuration object. Below is a snippet of the PoolConfig interface, truncated to only show the most imporant options. For a full list of options, please visit their github page.
export interface PoolConfig {
connectionLimit: number;
host: string;
user: string;
password: string;
database: string;
waitForConnections: boolean;
}
The database context is an abstract class that provides a convenient interface to access data. Below is the specification for the "DataContext" class:
import { PoolConfig } from 'mysql';
import { Collection } from './collection';
export declare abstract class DatabaseContext {
abstract models: {
[name: string]: Collection<any>;
};
initialize: (config: PoolConfig) => void;
beginTransaction: () => Promise<void>;
endTransaction: (rollback?: boolean) => Promise<void>;
protected query: <T>(query: string, args: any) => Promise<T>;
protected callProcedure: <T>(procedure: string, args: any[]) => Promise<T>;
}
A collection is a generic class representation of a data model. Think of collections as someting you perform operations on: find, insert, update, delete, etc. Each collection should represent a table (or view) in your database. Below is the specification for the "Collection" class:
import { PoolConnection } from 'mysql';
import { EntityQuery } from './entity-query';
export declare class Collection<T> {
initialize: (name: string, connectionFactory: () => Promise<PoolConnection>) => void;
find: (query?: EntityQuery) => Promise<T[]>;
findOne: (query: EntityQuery) => Promise<T>;
insert: (query: EntityQuery) => Promise<void>;
insertOne: (model: T, debug?: boolean) => Promise<number>;
update: (model: T, query: EntityQuery) => Promise<void>;
updateOne: (model: T, query: EntityQuery) => Promise<void>;
delete: (query: EntityQuery) => Promise<void>;
deleteOne: (query: EntityQuery) => Promise<void>;
first: (columnName: string, query?: EntityQuery) => Promise<T>;
last: (columnName: string, query?: EntityQuery) => Promise<T>;
exists: (query: EntityQuery) => Promise<boolean>;
}
Most of the collection methods have an "EntityQuery" object parameter, and each method uses different properties. Below is a list of all properties of the "EntityQuery" interface, but please reference each method's description for implementation specifics.
export interface EntityQuery {
identity?: string;
args?: any[];
columns?: string[];
conditions?: string[];
limit?: number;
}
This is used by the "DatabaseContext" abstract class to initialize each collection. You should probably never call this manually, unless you are working with collections outside of a data context (not reccommended).
Takes an optional "EntityQuery" object and returns an array of objects (T). If no query parameter is provided, it will return a list of all entities. Below is a list of all "EntityQuery" properties that are available (all properties are optional):
find: (query?: EntityQuery) => Promise<T[]>;
Takes a required "EntityQuery" object and returns a single object (T); null if no object found. Below is a list of all "EntityQuery" properties that are available (* indicates a required parameter):
findOne: (query: EntityQuery) => Promise<T>;
Inserts one-to-many new object(s) (T) into a table. Below is a list of all "EntityQuery" properties that are available (* indicates a required parameter):
insert: (query: EntityQuery) => Promise<void>;
Insert one object (T) into a table.
insertOne: (model: T) => Promise<number>;
Takes an object (T) and an "EntityQuery" object and updates the corresponding database values. Below is a list of all "EntityQuery" properties that are available (all properties are required):
update: (model: T, query: EntityQuery) => Promise<void>;
Takes an object (T) and an "EntityQuery" object and updates the corresponding database values. Below is a list of all "EntityQuery" properties that are available (all properties are required):
updateOne: (model: T, query: EntityQuery) => Promise<void>;
Deletes one-to-many object(s) (T) from a database table. Below is a list of all "EntityQuery" properties that are available (all properties are required):
delete: (query: EntityQuery) => Promise<void>;
Deletes an object (T) from a database table. Below is a list of all "EntityQuery" properties that are available (all properties are required):
deleteOne: (query: EntityQuery) => Promise<void>;
Takes a column name and an optional "EntityQuery" object and returns the first record, sorted by the provided column name. If query parameter are provided, the query will be modified accordingly before finding the first record. Below is a list of all "EntityQuery" properties that are available (all properties are optional):
first: (columnName: string, query?: EntityQuery) => Promise<T>;
Takes a column name and an optional "EntityQuery" object and returns the last record, sorted by the provided column name. If query parameter are provided, the query will be modified accordingly before finding the last record. Below is a list of all "EntityQuery" properties that are available (all properties are optional):
last: (columnName: string, query?: EntityQuery) => Promise<T>;
Takes a required "EntityQuery" object and returns true if an entity is found, and false if no entity is found. Below is a list of all "EntityQuery" properties that are available (* indicates a required parameter):
exists: (query: EntityQuery) => Promise<boolean>;
The mysql-shaman CLI provides a convenient way to perform common database operations, without having to login to mysql in a terminal, or though Workbench, etc. Store your database scripts in .sql files, configure the mysql-shaman CLI, then start running commands.
The mysql-shaman CLI follows the following format:
mysql-shaman [command] [...arguments]
Before configuring the mysql-shaman CLI, you should already have a project folder with database files, typically with .sql extensions. Inside this project folder, create a file called 'mysql-shaman.json'. This file should follow the below interface specification:
import { PoolConfig } from 'mysql';
export interface MySqlShamanConfig {
poolConfig: PoolConfig;
adminPoolConfig?: PoolConfig;
cwd?: string;
remote?: boolean;
scripts?: {
tables: string[];
primers?: string[];
views?: string[];
procedures?: string[];
};
}
(*) indicates a required field
The build command takes 2 arguments (databaseName and userName) and will perform the following actions:
The syntax for the build command is as follows:
mysql-shaman build [databaseName] [userName] [config path (optional)]
Note: to run this command you need to have a populated "adminPoolConfig" value in your mysql-shaman.json file, and the user credentials provided should have GRANT permissions, and the ability to create databases.
The scaffold command takes 1 optional argument then runs all the configured scripts (see above). The scripts are run in sequential order, based on the glob patterns provided in your configuration file's "scripts" property. The only required script type is "table", all others are optional.
mysql-shaman scaffold [config path (optional)]
Since certain types of scripts rely on certain other scripts, the scaffold command will run the 4 script types in this order:
If you need the scripts, inside of each category, to run sequentially, specify them explicity in the configuration file, in the order you wish them to run.
The add user command takes 1 arguments (userName) and will perform the following actions:
The syntax for the adduser command is as follows:
mysql-shaman adduser [userName] [config path (optional)]
Note: to run this command you need to have a populated "adminPoolConfig" value in your mysql-shaman.json file, and the user credentials provided should have GRANT permissions.
The grant command takes 3 arguments (userName, databaseName, and role) and will grant the provided user with all permissions associated with the provided role, on the provided database. The following roles are available:
The syntax for the build command is as follows:
mysql-shaman grant [userName] [databaseName] [role] [config path (optional)]
Note: to run this command you need to have a populated "adminPoolConfig" value in your mysql-shaman.json file, and the user credentials provided should have GRANT permissions.
The run command takes 1 required parameter and 1 optional parameter then runs the specified script on the configured database.
mysql-shaman run [script path] [config path (optional)]
FAQs
Access MySql databases using a simple, familiar ORM syntax.
The npm package mysql-shaman receives a total of 6 weekly downloads. As such, mysql-shaman popularity was classified as not popular.
We found that mysql-shaman demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Tea.xyz, a crypto project aimed at rewarding open source contributions, is once again facing backlash due to an influx of spam packages flooding public package registries.
Security News
As cyber threats become more autonomous, AI-powered defenses are crucial for businesses to stay ahead of attackers who can exploit software vulnerabilities at scale.
Security News
UnitedHealth Group disclosed that the ransomware attack on Change Healthcare compromised protected health information for millions in the U.S., with estimated costs to the company expected to reach $1 billion.