easy-sql-builder

this tools is used to generate sql for database

Installation

1npm install easy-sql-builder

Example

1const { databaseManager, columnBuilder, commandBuilder } = require('easy-sql-builder');
2const db = new databaseManager();
3
4const host = 'localhost';
5const user = 'root';
6const password = 'root';
7const databaseName = 'test';
8
9db.setDatabaseConnectionConfig(host, user, password, databaseName);
10db.connect();
11
12//create table
13const columns_createTable = [
14    new columnBuilder().setColumnName('id').integer().notNull().autoIncrement().build(),
15    new columnBuilder().setColumnName('name').varchar(255).notNull().build(),
16    new columnBuilder().setColumnName('age').integer().notNull().build(),
17    new columnBuilder().setColumnName('address').text().notNull().build(),
18];
19
20var { sql, params } = new commandBuilder().createTable("users", columns_createTable).build();
21
22console.log(sql,params);
23
24db.query(sql, params).then((result) => {
25    console.log(result);
26}).catch((error) => {
27    console.log(error);
28});
29
30
31//insert
32const values_insert = {
33    name: 'John',
34    age: 20,
35    address: 'USA',
36};
37
38var { sql, params } = new commandBuilder().insert("users", values_insert).build();
39
40console.log(sql,params);
41
42db.query(sql, params).then((result) => {
43    console.log(result);
44}).catch((error) => {
45    console.log(error);
46});
47
48
49//update
50
51const values_update = {
52    name: 'John',
53    age: 20,
54    address: 'USA',
55};
56
57var { sql, params } = new commandBuilder().update("users", values_update).where('id = ?', 1).build();
58
59console.log(sql,params);
60
61db.query(sql, params).then((result) => {
62    console.log(result);
63}).catch((error) => {
64    console.log(error);
65});
66
67
68//delete
69
70var { sql, params } = new commandBuilder().delete("users").where('id = ?', 1).build();
71
72console.log(sql,params);
73
74db.query(sql, params).then((result) => {
75    console.log(result);
76}).catch((error) => {
77    console.log(error);
78});
79
80
81//select
82
83var { sql, params }= new commandBuilder().select("users", ['name', 'age', 'address']).where('id = ?', 1).build();
84
85console.log(sql,params);
86
87db.query(sql, params).then((result) => {
88    console.log(result);
89}).catch((error) => {
90    console.log(error);
91});
92
93
94//select all
95
96var { sql, params } = new commandBuilder().selectAll("users").build();
97
98console.log(sql,params);
99
100db.query(sql, params).then((result) => {
101    console.log(result);
102}).catch((error) => {
103    console.log(error);
104});
105
106
107//select count
108
109var { sql, params } = new commandBuilder().selectCount("users").build();
110
111console.log(sql,params);
112
113db.query(sql, params).then((result) => {
114    console.log(result);
115}).catch((error) => {
116    console.log(error);
117});
118
119
120//drop table
121
122var { sql, params } = new commandBuilder().dropTable("users").build();
123
124console.log(sql,params);
125
126db.query(sql, params).then((result) => {
127    console.log(result);
128}).catch((error) => {
129    console.log(error);
130});
131
132
133//truncate table
134
135var { sql, params } = new commandBuilder().truncateTable("users").build();
136
137console.log(sql,params);
138
139db.query(sql, params).then((result) => {
140    console.log(result);
141}).catch((error) => {
142    console.log(error);
143});
144
145db.disconnect();
146