{"_id":"@awenk/model-builder","name":"@awenk/model-builder","dist-tags":{"latest":"1.5.0"},"versions":{"1.5.0":{"name":"@awenk/model-builder","version":"1.5.0","description":"Dynamic SQL query builder & mini ORM for Node + MySQL","main":"index.js","type":"commonjs","keywords":["sql","query-builder","mysql","nodejs"],"repository":{"type":"git","url":"git+https://github.com/badueny/model-builder.git"},"author":{"name":"Awenk"},"license":"MIT","scripts":{"test":"node test/model.test.js"},"peerDependencies":{"mysql2":">=3"},"_id":"@awenk/model-builder@1.5.0","bugs":{"url":"https://github.com/badueny/model-builder/issues"},"homepage":"https://github.com/badueny/model-builder#readme","_nodeVersion":"22.14.0","_npmVersion":"11.4.2","dist":{"integrity":"sha512-yRVCRbn1K5dckR8a7JiGvp07XNacQvLhQ5raZrfcfyHZNPu+y/28Ir07Y5ERUm7cuY1mJ8BFeiyFR4eLNwQI9g==","shasum":"6e60cc2fede8cf89a8202b7fb9298f4e586daaf9","tarball":"https://registry.npmjs.org/@awenk/model-builder/-/model-builder-1.5.0.tgz","fileCount":7,"unpackedSize":34162,"signatures":[{"keyid":"SHA256:DhQ8wR5APBvFHLF/+Tc+AYvPOdTpcIDqOhxsBHRwC7U","sig":"MEYCIQCL7BIt6MhPcKmC3H+P+nLDltIEQJUZRPJ+2eXF72RdRQIhAJZdek//QaaFzolfUNJjkeSVqFVkg0bmQBF0vRVx+J8h"}]},"_npmUser":{"name":"awenks","email":"badueny@gmail.com","actor":{"name":"awenks","email":"badueny@gmail.com","type":"user"}},"directories":{},"maintainers":[{"name":"awenks","email":"badueny@gmail.com"}],"_npmOperationalInternal":{"host":"s3://npm-registry-packages-npm-production","tmp":"tmp/model-builder_1.5.0_1751941620265_0.4495651116675383"},"_hasShrinkwrap":false}},"time":{"created":"2025-07-08T02:27:00.214Z","1.5.0":"2025-07-08T02:27:00.446Z","modified":"2025-07-08T02:27:00.731Z"},"maintainers":[{"name":"awenks","email":"badueny@gmail.com"}],"description":"Dynamic SQL query builder & mini ORM for Node + MySQL","homepage":"https://github.com/badueny/model-builder#readme","keywords":["sql","query-builder","mysql","nodejs"],"repository":{"type":"git","url":"git+https://github.com/badueny/model-builder.git"},"author":{"name":"Awenk"},"bugs":{"url":"https://github.com/badueny/model-builder/issues"},"license":"MIT","readme":"Modular **SQL Query Builder dan Helper Transaksi** untuk **Node.js + MySQL**.\nTerinspirasi dari *Laravel Eloquent dan Knex.js*, `model-builder` memungkinkan kamu membangun query SQL secara fleksibel dan elegan tanpa ORM besar.\n\n## Fitur Utama\n\n| Fitur | Deskripsi |\n| --------------------------- | --------------------------------------------------------- |\n| `select()` | Pilih kolom, bisa alias (`{ 'a.id': 'user_id' }`) |\n| `join()`, `leftJoin()` | JOIN tabel lain |\n| `where()`, `orWhere()` | Kondisi WHERE chaining |\n| `whereOp()` | WHERE dengan operator fleksibel (`>=`, `!=`, `LIKE`, dll) |\n| `whereIn()` | WHERE IN untuk array nilai |\n| `whereLikeAny()` | LIKE di banyak kolom secara OR |\n| `prependParam()` | mengatur urutan parameter Subquery SQL dalam select |\n| `groupBy()`, `having()` | GROUP BY dan HAVING dengan support placeholder |\n| `orderBy()`, `limit()` | Sorting dan pembatasan hasil |\n| `orderByMulti()`\t | Multiple Sorting Kolom\n| `insert()` | Simpan 1 data |\n| `insertMany()` | Simpan bulk array |\n| `insertUpdate()` | UPSERT (insert or update on duplicate) |\n| `upsertMany()` | Bulk UPSERT (insert or update ON DUPLICATE KEY UPDATE) |\n| `update()` | Update dengan WHERE (guarded) |\n| `delete()` | Hapus dengan WHERE (guarded) |\n| `increment()`,`decrement()` | Modifikasi nilai kolom tanpa ambil data dulu. |\n| `first()` | Ambil 1 baris data |\n| `get()` | Ambil semua hasil query |\n| `paginate()` | Ambil data per halaman + total count |\n| `count()`, `sum()`, `avg()` | Fungsi agregat |\n| `min()`, `max()` | Fungsi agregat |\n| `exists()` | Boolean cepat untuk cek data |\n| `pluck()` | Ambil satu kolom semua baris |\n| `withTransaction()` | Wrapper helper untuk transaksi otomatis dan |\n| | Commit otomatis jika sukses, rollback jika error. |\n| `enableAudit()`\t | Catatan log transaksi otomatis\t\t\t\t |\n\n## šŸ§± Method Builder\n\n### šŸ“„ Select\n\n```js\n.select('*') // semua kolom\n.select(['id', 'name']) // kolom tertentu\n.select({ 'u.name': 'nama', 'COUNT(*)': 'total' }) // kolom tertentu dengan alias dan fungsi\n```\n\n### šŸ” Where & Filter\n\n```js\n.where('status', 'active') // pencarian satu kolom -> status = 'active'\n.where({'id':1, 'status':'active'}) // pencarian banyak kolom -> id = 1 AND status = 'active'\n.whereOp('age', '>=', 18) // age >= 18\n.orWhere('role', 'editor') // OR role = 'editor'\n.whereIn('id', [1, 2, 3]) // WHERE id IN (1, 2, 3)\n.whereLikeAny(['name', 'email'], 'admin') // WHERE name LIKE 'admin' OR email LIKE 'admin'\n.whereMultiOp([{ column: 'status', operator: '=', value: 'active' }])\n```\n\n### šŸ”— Join\n\n```js\n.join('roles r', 'r.id = u.role_id')\n.leftJoin('profiles p', 'p.user_id = u.id')\n```\n\n### šŸ“¦ Group / Having / Order / Limit\n\n```js\n.groupBy('status') //single gruping\n.groupBy(['role', 'status']) //multiple gruping \n.having('COUNT(*) > ?', 5) \n.orderBy('created_at', 'desc') \t//single sort \n.orderByMulti(['desc', 'asc'], ['status','created_at']) //multiple sort\n.limit(10)\n.offset(5)\n```\n\n---\n\n## šŸ’³ Eksekusi\n\n| Method \t| Keterangan |\n|-----------------------|--------------------------------|\n| `.get()` \t| Ambil semua hasil |\n| `.first()` \t| Ambil 1 data (LIMIT 1) |\n| `.exists()` \t| Cek apakah ada data |\n| `.pluck(col_name)` \t| Ambil semua isi dari 1 kolom |\n| `.debug()` \t| Lihat query SQL & value |\n| `.clone()` \t| Duplikat instance builder |\n\n---\n\n## šŸ”¢ Increment / Decrement\n\n```js\nawait Model('produk')\n .where('id', 'PRD001')\n .increment('stok'); // stok + 1\n\nawait Model('produk')\n .where('id', 'PRD001')\n .decrement('stok', 3); // stok - 3\n```\n\n> āš ļø Wajib gunakan `.where()` agar aman. Tanpa `WHERE`, akan throw error.\n\n---\n\n## šŸ”„ Insert / Update / Delete\n\n```js\n.insert({ name: 'John' })\n.insertMany([{...}, {...}])\n.insertUpdate({ id: 1, name: 'Baru' }, ['id']) // upsert dengan on duplicate update kolom id (wajib unique)\n\n.update({ name: 'Update' }).where('id', 1)\n.delete().where('id', 1)\n```\n\n---\n\n## šŸ” Aggregate\n\n```js\n.count('id')\n.sum('jumlah')\n.avg('nilai')\n.min('stok')\n.max('harga')\n```\n\n---\n\n## šŸ“‘ Pagination\n\n```js\nconst result = await Model('users').paginate(2, 10);\nconsole.log(result);\n/*\n{\n data: [...],\n total: 123,\n page: 2,\n perPage: 10,\n lastPage: 13\n}\n*/\n```\n\n---\n\n## šŸ’” Subquery Support\n\n```js\n.select({\n 'ss.id': 'id',\n ['(SELECT COUNT(*) FROM queues q WHERE q.slot_id = ss.id AND DATE(q.waktu_booking) = ? AND q.status IN (\"booking\",\"proses\"))']: 'jumlah_booking'\n})\n.prependParam('2025-06-27')\n```\n\n---\n\n## šŸ§© Utilities\n\n```js\n.prependParam('value') // prepend 1 param\n.prependParam(['v1', 'v2']) // prepend multiple\n.debug() // log query SQL dan values\n.clone() // clone instance builder\n```\n\n---\n\n## šŸ” Keamanan\n\n- Query menggunakan parameter `?` ā†’ aman dari SQL injection\n- Subquery aman dengan `.prependParam()`\n- Tidak ada interpolasi nilai langsung ke query\n\n---\n\n## Contoh Penggunaan\n\n#### Basic Query\n```js\nconst { Model, withTransaction } = require('@awenk/model-builder');\n```\n```js\nawait Model('users')\n .select(['id', 'name'])\n .where('status', 'active')\n .orderBy('id', 'DESC')\n .paginate(1, 10);\n````\n```js\nawait withTransaction(async (conn, Model) => {\n const userId = await Model('users').insert({ name: 'Awenk' });\n await Model('orders').insert({ user_id: userId, total: 10000 });\n});\n```\n#### Pagination\n```js\n\nconst result = await Model('products')\n .where('category_id', 1)\n .paginate(2, 10); // halaman ke-2, 10 item per halaman\n\nconsole.log(result.total); // total semua\nconsole.log(result.data); // data halaman ini\n\n```\n\n#### Transaksi Otomatis\n\n```js\n\nconst { withTransaction } = require('@awenk/model-builder');\n\nawait withTransaction(async (conn, Model) => {\n const User = Model('users');\n const Order = Model('orders');\n\n const userId = await User.insert({ name: 'Awenk', email: 'a@e' });\n\n await Order.insert({ user_id: userId, total: 100000 });\n});\n\n```\n> āš ļø Definisikan Model di dalam `withTransaction`, Jangan Diluar.\n\n#### Insert atau Update (Upsert)\n\n```js\nawait Model('settings').insertUpdate(\n { key: 'site_name', value: 'AntrianKita' },\n ['key'] // kolom unik\n);\n\n```\n\n#### Insert atau Update Banyak (Bulk Upsert)\n```js\n\nawait Model('products').upsertMany(\n [\n { id: 1, name: 'Kopi', stock: 100 },\n { id: 2, name: 'Teh', stock: 80 }\n ],\n ['name', 'stock'] // kolom yg diupdate jika duplicate\n);\n\n````\n\n#### Increment decrement\n```js\n// tambah stok 5\nawait Model('products').where('id', pid).increment('stock', 5);\n\n// kurangi saldo 10.000,-\nawait Model('users').where('id', uid).decrement('balance', 10000);\n```\n\n#### Exist, Pluck, min, max\n```js\n\n// cek ada data?\nconst isExist = await Model('users').where('email', email).exists(); //output -> true|false\n\n// ambil array email saja\nconst emails = await Model('users').pluck('email'); //output -> ambil array satu kolom tanpa harus select\n\n// fungsi agregat lain\nconst lowest = await Model('orders').min('total'); //output -> nilai terendah\nconst highest = await Model('orders').max('total'); //output -> nilai tertinggi\n\n````\n#### prependParam Subquery SQL Support\nberguna untuk mengatur urutan parameter Subquery SQL didalam select.\n```js\nconst model = Model('table a');\nmodel.select(\n {'a.name':'name',\n ['(SELECT COUNT(*) FROM tableb WHERE extra_coloumn = ?']: 'total'\n})\n.where('id', 1)\n.prependParam('extra_value')\n.get();\n```\nakan mendapatkan hasil SQL:\n```sql\nSELECT \n\ta.name AS name, \n\t(SELECT COUNT(*) FROM tableb WHERE extra_coloumn = 'extra_value') AS total \nFROM table a \nWHERE a.id=1\n```\n\n#### Contoh Penggunaan Untuk DataTables Server-side\n\n```js\n\nconst { Model } = require('@awenk/model-builder');\n\nrouter.post('/datatable/users', async (req, res) => {\n const { start, length, search, order, columns } = req.body;\n\n const page = Math.floor(start / length) + 1;\n const perPage = parseInt(length);\n const keyword = search?.value || '';\n const orderCol = columns[order[0].column].data;\n const dir = order[0].dir.toUpperCase();\n\n const query = Model('users')\n .select(['id', 'name', 'email', 'role'])\n .whereLikeAny(['name', 'email', 'role'], keyword)\n .orderBy(orderCol, dir);\n\n const result = await query.paginate(page, perPage);\n\n res.json({\n draw: req.body.draw,\n recordsTotal: result.total,\n recordsFiltered: result.total,\n data: result.data\n });\n});\n\n```\n\n#### Audit Log\nStruktur Audit Table.\n```sql\nCREATE TABLE IF NOT EXISTS audit_log (\n id INT AUTO_INCREMENT PRIMARY KEY,\n table_name VARCHAR(50),\n action CHAR(250),\n record_id VARCHAR(36),\n before_data JSON,\n after_data JSON,\n user_id CHAR(36),\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP\n )\n```\nPenggunaan:\n.enableAudit(table, meta); \n```sql\nawait Model('users')\n .where('id', 5)\n .enableAudit('audit_log', { userId: 'admin123' })\n .update({ name: 'Awenk' });\n```\n## āœ… Instalasi\n\n```bash\nnpm install github:badueny/model-builder\n```\natau\n```bash\nnpm install git+https://github.com/badueny/model-builder.git\n```\n```yaml\nPastikan kamu sudah punya koneksi `config/db.js` yang mengekspor pool `mysql2/promise`.\n````\n#### Integrasi MySQL Pool\nIsi file -> `config/db.js`:\n```js\nconst mysql = require('mysql2/promise');\n\nconst pool = mysql.createPool({\n host: 'localhost',\n user: 'root',\n database: 'mydb',\n password: 'myuser-db-password',\n waitForConnections: true,\n connectionLimit: 10,\n queueLimit: 0\n});\n\nmodule.exports = pool;\n````\n\n## Cara Jalankan Contoh Lokal\n```bash\ngit clone https://github.com/badueny/model-builder.git\ncd model-builder\nnpm install\nnode examples/example.js\n\n```\nšŸ“œ Lisensi.\nMIT License ā€” Bebas digunakan dan dimodifikasi.\n\n","readmeFilename":"README.md","_rev":"1-55b0daf66f7bdb25cd77cdbe4b2355b3"}