{"id":42990,"library":"lego-sql","title":"Lego SQL Builder","description":"Lego is a lightweight SQL (string) builder for Node.js that uses ES6 template strings to parameterize queries. Instead of abstracting SQL, it embraces raw SQL while safely handling parameterization. Current stable version is 5.0.1, with active maintenance. Key differentiators: no ORM-like abstraction, uses tagged template literals for safe parameterized queries, supports nested queries with arrays of Lego instances, built-in row-to-object parsing, and transaction support via export. Unlike knex.js, Lego does not provide a full query building API but rather wraps SQL strings with parameter injection.","status":"active","version":"5.0.1","language":"javascript","source_language":"en","source_url":"https://github.com/martijndeh/lego","tags":["javascript","lego","sql","postgres","knex","sql builder","template strings"],"install":[{"cmd":"npm install lego-sql","lang":"bash","label":"npm"},{"cmd":"yarn add lego-sql","lang":"bash","label":"yarn"},{"cmd":"pnpm add lego-sql","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"PostgreSQL driver required for executing queries; used internally for pool and query execution.","package":"pg","optional":false}],"imports":[{"note":"ESM syntax requires named import since v3; CommonJS require may work but is not recommended.","wrong":"const Lego = require('lego-sql')","symbol":"Lego","correct":"import { Lego } from 'lego-sql'"},{"note":"Default export provides the same Lego object but not recommended; use named import for clarity.","wrong":"const lego = require('lego-sql').default","symbol":"lego","correct":"import lego from 'lego-sql'"},{"note":"Lego.sql is the tagged template function; directly calling Lego as a function does not work.","wrong":"Lego`SELECT 1`","symbol":"Lego.sql","correct":"import { Lego } from 'lego-sql'; await Lego.sql `SELECT 1`"},{"note":"Lego.raw escapes the raw value; only use with trusted input to avoid SQL injection.","wrong":"Lego.raw(column) inside regular string interpolation without template literal","symbol":"Lego.raw","correct":"import { Lego } from 'lego-sql'; const column = 'name'; const lego = Lego.sql `UPDATE users SET ${Lego.raw(column)} = ${value}`"},{"note":"Pass rows (array) and a definition object that maps column names to properties (string or array of subdefinitions).","wrong":"Lego.parse(rows, {})","symbol":"Lego.parse","correct":"import { Lego } from 'lego-sql'; const rows = []; const result = Lego.parse(rows, definition)"}],"quickstart":{"code":"import { Lego } from 'lego-sql';\nimport pg from 'pg';\n\n// Set DATABASE_URL environment variable or manually configure pg pool\n// Lego uses pg internally; it expects a default pool or explicit config.\n\n// Insert a user\nconst name = 'John';\nconst result = await Lego.sql `INSERT INTO users (name) VALUES (${name}) RETURNING *`;\nconsole.log(result.rows); // rows from INSERT\n\n// Query with parameter\nconst users = await Lego.sql `SELECT * FROM users WHERE name = ${name}`;\nconsole.log(users.rows);\n\n// Use first() to get single result\nconst user = await Lego.sql `SELECT * FROM users LIMIT 1`.first();\nconsole.log(user); // first row or null\n\n// Nest arrays of Lego instances for multiple values\nconst projects = ['Alpha', 'Beta'];\nawait Lego.sql `INSERT INTO projects (name) VALUES ${projects.map(p => Lego.sql`(${p})`)}`;\n\n// Parse flat rows to nested objects\nconst rows = [\n  { id: 1, test_id: 1, test_name: 'Test 1' },\n  { id: 1, test_id: 2, test_name: 'Test 2' }\n];\nconst objects = Lego.parse(rows, [\n  { id: 'id', tests: [{ id: 'test_id', name: 'test_name' }] }\n]);\nconsole.log(objects); // [{ id: 1, tests: [...] }]","lang":"typescript","description":"Shows basic usage: parameterized queries, INSERT with RETURNING, arrays of Lego instances, first(), and row-to-object parsing."},"warnings":[{"fix":"Use await only on the final .then() or use .then() to get the result. Example: await Lego.sql`...`.then(res => res.rows);","message":"Lego.sql returns a promise-like object, not a Promise. Calling .then() executes the query. Do not await the query before .then().","severity":"gotcha","affected_versions":">=0.0.0"},{"fix":"Use import { Lego } from 'lego-sql' or const { Lego } = require('lego-sql').","message":"In v3, import changed from default to named export. const lego = require('lego-sql') stopped working.","severity":"breaking","affected_versions":">=3.0.0"},{"fix":"Set DATABASE_URL env variable or create a pg Pool and pass it to Lego.createPool(pool).","message":"Lego.sql no longer automatically connects to DATABASE_URL; you must configure pg pool manually or provide an environment variable.","severity":"deprecated","affected_versions":">=5.0.0"},{"fix":"Only use Lego.raw with hardcoded values or trusted column names. For user input, always use the template literal parameter insertion.","message":"Lego.raw is for trusted input only. Using it with user-provided values can lead to SQL injection.","severity":"gotcha","affected_versions":">=0.0.0"},{"fix":"Access result.rows instead of expecting result as array. For DELETE/UPDATE without RETURNING, use result.rowCount.","message":"In v4, the result of a query is no longer the raw array of rows but an object with rows and command. Use .rows property.","severity":"breaking","affected_versions":">=4.0.0"},{"fix":"Correct: Lego.sql`INSERT INTO t (v) VALUES ${arr.map(x => Lego.sql`(${x})`)}`. Incorrect: Lego.sql`...`, arr.","message":"Nested arrays of Lego instances (e.g., for multiple values) must be inside the template literal, not as separate arguments.","severity":"gotcha","affected_versions":">=0.0.0"},{"fix":"Always pass an array (even empty) to Lego.parse. Example: Lego.parse(rows || [], definition).","message":"Lego.parse expects rows as array of objects; if rows is undefined or not an array, it throws silently.","severity":"gotcha","affected_versions":">=0.0.0"}],"env_vars":null,"search_vec":"'5.0.1':37 'abstract':23,47 'activ':39 'api':87 'array':60 'build':86 'builder':3,10,102 'built':65 'built-in':64 'current':33 'differenti':42 'embrac':26 'es6':15 'export':76 'full':84 'handl':31 'inject':95 'instanc':63 'instead':21 'javascript':96 'key':41 'knex':100 'knex.js':78 'lego':1,4,62,79,97 'lightweight':7 'like':46 'liter':51 'mainten':40 'nest':57 'node.js':12 'object':70 'orm':45 'orm-lik':44 'paramet':94 'parameter':19,32,54 'pars':71 'postgr':99 'provid':82 'queri':20,55,58,85 'rather':89 'raw':27 'row':68 'row-to-object':67 'safe':30,53 'sql':2,8,24,28,91,98,101 'stabl':34 'string':9,17,92,104 'support':56,74 'tag':49 'templat':16,50,103 'transact':73 'unlik':77 'use':14,48 'version':35 'via':75 'wrap':90","created_at":"2026-06-05T16:57:39.028155+00:00","updated_at":"2026-06-05T16:57:39.028155+00:00","problems":[{"fix":"Use import { Lego } from 'lego-sql' instead of import Lego from 'lego-sql' (if using ESM) or const { Lego } = require('lego-sql').","cause":"Using default import from CommonJS or wrong import syntax.","error":"TypeError: Lego.sql is not a function"},{"fix":"Call Lego.createPool(new pg.Pool({ connectionString: process.env.DATABASE_URL })) before any query. Or set DATABASE_URL env variable.","cause":"No pg pool configured before calling Lego.sql that executes a query.","error":"Error: No pool defined. Please call Lego.createPool(pool) or set DATABASE_URL."},{"fix":"Use const result = await Lego.sql`...`.then(r => r.rows); or await Lego.sql`...` and then access .rows property.","cause":"Using the result of Lego.sql directly without .then() or awaiting the wrong value.","error":"Query result is undefined"},{"fix":"Install pg: npm install pg.","cause":"pg is a peer dependency but not installed.","error":"Cannot find module 'pg'"},{"fix":"Use backticks: Lego.sql`SELECT * FROM users WHERE id = ${id}`","cause":"Missing backticks or using regular strings instead of template literals.","error":"SyntaxError: Unexpected template string"}],"ecosystem":"npm","meta_description":null,"install_score":null,"quickstart_score":null,"quickstart_tag":null,"pypi_latest":null,"cli_name":null,"cli_version":null,"type":"library","homepage":"https://github.com/martijndeh/lego#readme","github":"https://github.com/martijndeh/lego","docs":null,"changelog":null,"pypi":null,"npm":"lego-sql","openapi_spec":null,"status_page":null,"smithery":null,"categories":["database"],"base_url":null,"auth_type":null,"provenance":{"verified_status":null,"verified_at":null,"last_verified":"2026-06-05","next_check":"2026-09-03","install_tag":null}}