The fastsqli library simplifies the management of database schemas and data extraction for MySQL databases. It helps users dynamically create tables, fetch data, and update schema configurations, all within a convenient CLI tool. fastsqli facilitates easier handling of SQL databases alongside MongoDB-like model definitions, providing an efficient approach to schema migrations, data extraction, and JSON exports.
project/
|-- fastsqli/
|-- createmodel/ # Directory for MongoDB model files that create new SQL tables
|-- data/ # Holds data tables in JSON format
|-- schema/ # Contains schema configuration files and models
This folder contains MongoDB model files, which define the structure of new models. These models are used to automatically generate corresponding tables in the SQL database.
Example MongoDB model:
// test.js
const mongoose = require('mongoose');
const testSchema = new mongoose.Schema(
{
id: { type: Number, required: true },
name: { type: String },
role: { type: String },
},
{
timestamps: true // MongoDB automatically handles createdAt and updatedAt
}
);
module.exports = mongoose.model('Test', testSchema);
This directory holds data tables in JSON format. These JSON files represent data extracted from SQL tables and can be used for exporting or manipulating data in a portable format.
Example data file:
// test.json
[
{
"id": 1,
"name": "Mawuli Stephen",
"role": "Admin"
}
]
Contains schema configuration files that define the structure and relationships of database tables.
Example schema file (schema.json
):
{
"test": {
"columns": [
{
"column_name": "id",
"column_type": "int",
"is_nullable": "NO"
},
{
"column_name": "name",
"column_type": "varchar",
"is_nullable": "YES"
},
{
"column_name": "category",
"column_type": "varchar",
"is_nullable": "YES"
},
{
"column_name": "createdAt",
"column_type": "datetime",
"is_nullable": "YES"
},
{
"column_name": "updatedAt",
"column_type": "datetime",
"is_nullable": "YES"
}
],
"relationships": []
}
}
This file represents a table with its columns, types, nullable attributes, and any relationships (foreign keys).
Contains MongoDB-like models for each table, helping structure the schema and assisting in the table creation process.
Example model for test
table:
// test.js
const mongoose = require('mongoose');
const testSchema = new mongoose.Schema({
id: { type: 'int', required: true },
name: { type: 'varchar' },
category: { type: 'varchar' },
createdAt: { type: 'datetime' },
updatedAt: { type: 'datetime' }
}, { timestamps: true });
module.exports = mongoose.model('test', testSchema);
For the test
table, the structure is as follows:
Column Name | Type | Attributes |
---|---|---|
id | int | PRIMARY KEY, AUTO_INCREMENT |
name | varchar | |
category | varchar | |
createdAt | datetime | |
updatedAt | datetime |
-
Dynamic Table Creation (via Mongoose Models)
Thecreatemodel
directory contains MongoDB-like model definitions that, when executed, will automatically create the corresponding SQL tables in your database. -
Schema Representation with
schema.json
Theschema.json
file in theschema
folder represents your database schema, which includes tables, columns, data types, and relationships. This provides a blueprint for creating or updating your schema in the SQL database. -
Data Extraction in JSON Format
You can extract data from your SQL tables and save it in JSON format using the scripts in thedata
directory. This functionality is useful for exporting data or generating datasets for further processing.
To install fastsqli, run the following npm command:
npm install -g fastsqli
Alternatively, you can install it locally within your project:
npm install --save-dev fastsqli
Before using the CLI tool, set up your environment with the necessary database credentials. You can define these credentials using environment variables or specify them directly in the CLI commands.
Create a .env
file in the root directory of your project to store the database credentials:
HOST=localhost
USER=root
PASSWORD=my-secret-password
DATABASE=my_database
These values can also be passed as flags in the CLI commands.
The CLI tool offers three main commands:
-
Migrate Command
This command generates or updates the database schema based on the provided configuration files.Usage:
fastsqli migrate --baseDir <directory> --host <db_host> --user <db_user> --password <db_password> --database <db_name>
Options:
-
--baseDir
or-b
: Directory for storing schema and data files (default isfastsqli
). -
--host
or-h
: Database host (default islocalhost
). -
--user
or-u
: Database username. -
--password
or-p
: Database password. -
--database
or-d
: Database name.
Example:
fastsqli migrate --baseDir ./fastsqli --host localhost --user root --password mysecretpassword --database mydatabase
This command will start the schema migration process and generate the necessary schema files.
-
-
Fetch Data Command
This command fetches data from the SQL tables and saves it as JSON files in the specified directory.Usage:
fastsqli fetch --baseDir <directory> --host <db_host> --user <db_user> --password <db_password> --database <db_name>
Options:
-
--baseDir
or-b
: Directory for storing schema and data files (default isfastsqli
). -
--host
or-h
: Database host (default islocalhost
). -
--user
or-u
: Database username. -
--password
or-p
: Database password. -
--database
or-d
: Database name.
Example:
fastsqli fetch --baseDir ./fastsqli --host localhost --user root --password mysecretpassword --database mydatabase
This will fetch the data from all tables in the specified database and save it as JSON files in the given directory.
-
-
Push Data Command
This command pushes data from JSON files to the database tables.Usage:
fastsqli push --table <table_name> --baseDir <directory> --host <db_host> --user <db_user> --password <db_password> --database <db_name>
Options:
-
--table
or-t
: The table to push data to (use"all"
for all tables). -
--baseDir
or-b
: Directory for storing schema and data files (default isfastsqli
). -
--host
or-h
: Database host (default islocalhost
). -
--user
or-u
: Database username. -
--password
or-p
: Database password. -
--database
or-d
: Database name.
Example:
fastsqli push --table test --baseDir ./fastsqli --host localhost --user root --password mysecretpassword --database mydatabase
This will push the data from the specified JSON file to the given table in the database.
-
To check the version or get help on the available commands, use the following commands:
fastsqli --version
fastsqli --help