i-tsx
About
simplifield-sql is a powerful Node.js module that allows you to easily interact with the MySQL Database.
Installation
Node.js 16.9.0 or newer is required.
npm install simplifield-sql
yarn add simplifield-sql
Example usage
Install simplifield-sql:
npm install simplifield-sql
yarn add simplifield-sql
Create new database connection:
const Database = require("simplifield-sql");
const db = new Database({
user: "root",
host: "localhost",
database: "myowndatabase",
password: "mysecretshellpassword",
});
db.on("connect", (connection) => {
console.log(`Connected to ${connection.config.database} database 👌`);
});
And now you're able to insert, update and delete the database rows
Methods
Connection
Table
Row
Documents
Connection
.destroy()Destroys the database connection.
Returns:
void
.ping()Pings the database to check the connection status.
Returns:
number
Table
.tables()Fetches the list of tables in the database.
Returns:
Table[]
.createTable(table, rows)Creates a new table in the database.
PARAMETER TYPE DESCRIPTION table string The new table name rows Row[] The new table rows Returns:
{ table: string, rows:Row[] }[]
.deleteTable(table)Drops a table from the database.
PARAMETER TYPE DESCRIPTION table string The MySQL table name Returns:
boolean
Row
.select(table, condetions)Retrieves data from a specific table with given conditions.
PARAMETER TYPE DESCRIPTION table string The MySQL table conditions object The conditions for data retrieval Returns:
Data?
.selectAll(table, condetions)Retrieves all data from a specific table with given conditions.
PARAMETER TYPE DESCRIPTION table string The MySQL table conditions object The conditions for data retrieval Returns:
Data[]
.insert(table, values)Inserts new data into a specific table.
PARAMETER TYPE DESCRIPTION table string The MySQL table values object The values to be inserted Returns:
Data
.update(table, conditions, values)Updates existing data in a specific table.
PARAMETER TYPE DESCRIPTION table string The MySQL table conditions object The conditions for data update values object The new values Returns:
Data
.delete(table, conditions)Deletes data from a specific table based on given conditions.
PARAMETER TYPE DESCRIPTION table string The MySQL table conditions object The conditions for data deletion Returns:
boolean
Types
Row
Represents a database row configuration.
Property Type Optional Description name string ✖ Column name dataType MySQLDataType ✖ Data type dataLength number ✔ Length of data (if applicable) characterSet MySQLCharacterSet ✔ Character set (if applicable) collation MySQLCollation ✔ Collation (if applicable) allowNull boolean ✔ Is NULL allowed defaultValue string | null ✔ Default value (if applicable) primaryKey boolean ✔ Is primary key autoIncrement boolean ✔ Is auto-incrementing unique boolean ✔ Is unique constraint MySQL Data Types
MySQLDataTypeRepresents various MySQL data types.
INTBIGINTFLOATDOUBLEDECIMALCHARVARCHARTEXTDATETIMEDATETIMETIMESTAMPYEARBOOLEANTINYINTSMALLINTMEDIUMINTENUMSETBINARYVARBINARYBLOBJSONJSONBGEOMETRYMySQL Character Sets
MySQLCharacterSetRepresents various MySQL character sets.
big5dec8cp850hp8koi8rlatin1latin2swe7asciiujissjishebrewtis620euckrkoi8ugb2312greekcp1250gbklatin5armscii8utf8ucs2cp866keybcs2maccemacromancp852latin7utf8mb4cp1251utf16utf16lecp1256cp1257utf32binarygeostd8cp932eucjpmsMySQL Collations
MySQLCollationRepresents various MySQL collations.
big5_chinese_cibig5_bindec8_swedish_cidec8_bincp850_general_cicp850_binhp8_english_cihp8_binkoi8r_general_cikoi8r_binlatin1_german1_cilatin1_swedish_cilatin1_danish_cilatin1_german2_cilatin1_binlatin1_general_cilatin1_general_cslatin1_spanish_cilatin2_czech_cslatin2_general_cilatin2_hungarian_cilatin2_croatian_cilatin2_binswe7_swedish_ciswe7_binascii_general_ciascii_binujis_japanese_ciujis_binsjis_japanese_cisjis_binhebrew_general_cihebrew_bintis620_thai_citis620_bineuckr_korean_cieuckr_binkoi8u_general_cikoi8u_bingb2312_chinese_cigb2312_bingreek_general_cigreek_bincp1250_general_cicp1250_czech_cscp1250_croatian_cicp1250_bingbk_chinese_cigbk_binlatin5_turkish_cilatin5_binarmscii8_general_ciarmscii8_binutf8_general_ciutf8_binutf8_unicode_ciutf8_icelandic_ciutf8_latvian_ciutf8_romanian_ciutf8_slovenian_ciutf8_polish_ciutf8_estonian_ciutf8_spanish_ciutf8_swedish_ciutf8_turkish_ciutf8_czech_ciutf8_danish_ciutf8_lithuanian_ciutf8_slovak_ciutf8_spanish2_ciutf8_roman_ciutf8_persian_ciutf8_esperanto_ciutf8_hungarian_ciutf8_sinhala_ciutf8_german2_ciutf8_croatian_ciutf8_unicode_520_ciutf8_vietnamese_ciutf8_general_mysql500_ciucs2_general_ciucs2_binucs2_unicode_ciucs2_icelandic_ciucs2_latvian_ciucs2_romanian_ciucs2_slovenian_ciucs2_polish_ciucs2_estonian_ciucs2_spanish_ciucs2_swedish_ciucs2_turkish_ciucs2_czech_ciucs2_danish_ciucs2_lithuanian_ciucs2_slovak_ciucs2_spanish2_ciucs2_roman_ciucs2_persian_ciucs2_esperanto_ciucs2_hungarian_ciucs2_sinhala_ciucs2_german2_ciucs2_croatian_ciucs2_unicode_520_ciucs2_vietnamese_ciucs2_general_mysql500_cicp866_general_cicp866_binkeybcs2_general_cikeybcs2_binmacce_general_cimacce_binmacroman_general_cimacroman_bincp852_general_cicp852_binlatin7_estonian_cslatin7_general_cilatin7_general_cslatin7_binutf8mb4_general_ciutf8mb4_binutf8mb4_unicode_ciutf8mb4_icelandic_ciutf8mb4_latvian_ciutf8mb4_romanian_ciutf8mb4_slovenian_ciutf8mb4_polish_ciutf8mb4_estonian_ciutf8mb4_spanish_ciutf8mb4_swedish_ciutf8mb4_turkish_ciutf8mb4_czech_ciutf8mb4_danish_ciutf8mb4_lithuanian_ciutf8mb4_slovak_ciutf8mb4_spanish2_ciutf8mb4_roman_ciutf8mb4_persian_ciutf8mb4_esperanto_ciutf8mb4_hungarian_ciutf8mb4_sinhala_ciutf8mb4_german2_ciutf8mb4_croatian_ciutf8mb4_unicode_520_ciutf8mb4_vietnamese_ciucs2mb4_general_ciucs2mb4_binucs2mb4_unicode_ciucs2mb4_icelandic_ciucs2mb4_latvian_ciucs2mb4_romanian_ciucs2mb4_slovenian_ciucs2mb4_polish_ciucs2mb4_estonian_ciucs2mb4_spanish_ciucs2mb4_swedish_ciucs2mb4_turkish_ciucs2mb4_czech_ciucs2mb4_danish_ciucs2mb4_lithuanian_ciucs2mb4_slovak_ciucs2mb4_spanish2_ciucs2mb4_roman_ciucs2mb4_persian_ciucs2mb4_esperanto_ciucs2mb4_hungarian_ciucs2mb4_sinhala_ciucs2mb4_german2_ciucs2mb4_croatian_ciucs2mb4_unicode_520_ciucs2mb4_vietnamese_cicp1251_bulgarian_cicp1251_ukrainian_cicp1251_bincp1251_general_cicp1251_general_cscp1251_general_mysql500_ciutf16_general_ciutf16_binutf16_unicode_ciutf16_icelandic_ciutf16_latvian_ciutf16_romanian_ciutf16_slovenian_ciutf16_polish_ciutf16_estonian_ciutf16_spanish_ciutf16_swedish_ciutf16_turkish_ciutf16_czech_ciutf16_danish_ciutf16_lithuanian_ciutf16_slovak_ciutf16_spanish2_ciutf16_roman_ciutf16_persian_ciutf16_esperanto_ciutf16_hungarian_ciutf16_sinhala_ciutf16_german2_ciutf16_croatian_ciutf16_unicode_520_ciutf16_vietnamese_ciutf16le_general_ciutf16le_bincp1256_general_cicp1256_bincp1257_lithuanian_cicp1257_binutf32_general_ciutf32_binutf32_unicode_ciutf32_icelandic_ciutf32_latvian_ciutf32_romanian_ciutf32_slovenian_ciutf32_polish_ciutf32_estonian_ciutf32_spanish_ciutf32_swedish_ciutf32_turkish_ciutf32_czech_ciutf32_danish_ciutf32_lithuanian_ciutf32_slovak_ciutf32_spanish2_ciutf32_roman_ciutf32_persian_ciutf32_esperanto_ciutf32_hungarian_ciutf32_sinhala_ciutf32_german2_ciutf32_croatian_ciutf32_unicode_520_ciutf32_vietnamese_cibinaryarmscii8_general_ciarmscii8_binascii_general_ciascii_bincp1250_general_cicp1250_czech_cscp1250_croatian_cicp1250_bincp1250_polish_cicp1251_bulgarian_cicp1251_ukrainian_cicp1251_bincp1251_general_cicp1251_general_cscp1251_general_mysql500_cicp1251_croatian_cicp1251_polish_cicp1251_serbian_cicp1251_macedonian_cicp1251_general_cscp1251_general_mysql500_cicp1251_croatian_cicp1251_polish_cicp1251_serbian_cicp1251_macedonian_cicp1256_general_cicp1256_bincp1257_lithuanian_cicp1257_bincp850_general_cicp850_bincp852_general_cicp852_bincp866_general_cicp866_bincp932_japanese_cicp932_bindec8_swedish_cidec8_bineuckr_korean_cieuckr_bingb2312_chinese_cigb2312_bingbk_chinese_cigbk_bingeostd8_general_cigeostd8_bingreek_general_cigreek_binhebrew_general_cihebrew_binhp8_english_cihp8_binkeybcs2_general_cikeybcs2_binkoi8r_general_cikoi8r_binkoi8u_general_cikoi8u_binlatin1_german1_cilatin1_swedish_cilatin1_danish_cilatin1_german2_cilatin1_binlatin1_general_cilatin1_general_cslatin1_spanish_cilatin2_czech_cslatin2_general_cilatin2_hungarian_cilatin2_croatian_cilatin2_binlatin5_turkish_cilatin5_binlatin7_estonian_cslatin7_general_cilatin7_general_cslatin7_binmacce_general_cimacce_binmacroman_general_cimacroman_binsjis_japanese_cisjis_binswe7_swedish_ciswe7_bintis620_thai_citis620_binucs2_general_ciucs2_binucs2_unicode_ciucs2_icelandic_ciucs2_latvian_ciucs2_romanian_ciucs2_slovenian_ciucs2_polish_ciucs2_estonian_ciucs2_spanish_ciucs2_swedish_ciucs2_turkish_ciucs2_czech_ciucs2_danish_ciucs2_lithuanian_ciucs2_slovak_ciucs2_spanish2_ciucs2_roman_ciucs2_persian_ciucs2_esperanto_ciucs2_hungarian_ciucs2_sinhala_ciucs2_german2_ciucs2_croatian_ciucs2_unicode_520_ciucs2_vietnamese_ciujis_japanese_ciujis_binutf16_general_ciutf16_binutf16_unicode_ciutf16_icelandic_ciutf16_latvian_ciutf16_romanian_ciutf16_slovenian_ciutf16_polish_ciutf16_estonian_ciutf16_spanish_ciutf16_swedish_ciutf16_turkish_ciutf16_czech_ciutf16_danish_ciutf16_lithuanian_ciutf16_slovak_ciutf16_spanish2_ciutf16_roman_ciutf16_persian_ciutf16_esperanto_ciutf16_hungarian_ciutf16_sinhala_ciutf16_german2_ciutf16_croatian_ciutf16_unicode_520_ciutf16_vietnamese_ciutf16le_general_ciutf16le_binutf32_general_ciutf32_binutf32_unicode_ciutf32_icelandic_ciutf32_latvian_ciutf32_romanian_ciutf32_slovenian_ciutf32_polish_ciutf32_estonian_ciutf32_spanish_ciutf32_swedish_ciutf32_turkish_ciutf32_czech_ciutf32_danish_ciutf32_lithuanian_ciutf32_slovak_ciutf32_spanish2_ciutf32_roman_ciutf32_persian_ciutf32_esperanto_ciutf32_hungarian_ciutf32_sinhala_ciutf32_german2_ciutf32_croatian_ciutf32_unicode_520_ciutf32_vietnamese_ciMySQL Default Values
MySQLDefaultValueRepresents possible default values for MySQL columns.
NONENULLCURRENT_TIMESTAMP
Solved Bugs 🐛
✔ The db wasn't accept the string characters
(', ", `)✔ When you delete a row you will got an error
✔ deleteAll doesn't work