{"_id":"@arekx/teeql","_rev":"3-9a71a6c3c32f6f29cb8ed75e94672b2f","name":"@arekx/teeql","dist-tags":{"latest":"1.0.2"},"versions":{"1.0.0":{"name":"@arekx/teeql","version":"1.0.0","keywords":["sql","query","builder"],"author":{"name":"Aleksandar Panic"},"license":"Apache-2.0","_id":"@arekx/teeql@1.0.0","homepage":"https://github.com/ArekX/teeql","bugs":{"url":"https://github.com/ArekX/teeql/issues"},"dist":{"shasum":"8f689d62f2635fc9aaf854bab67e5b5512d744d1","tarball":"https://registry.npmjs.org/@arekx/teeql/-/teeql-1.0.0.tgz","fileCount":22,"integrity":"sha512-peNCE5DCyOKnzPCxd8dnHJ8vcO/4BnoZddLFVBvgK0kicZdJNuidgCEBShEF5BeaVYVY5rC1d1HwMcstTLRF5Q==","signatures":[{"sig":"MEUCIGIebRvT3NHbgM9uTGtxeSiOV3qJKaThByAMwxDh7hOPAiEA9JtZo4iVKvHqM2+NSE/TnviOrrWDhPDzwwCBX8FphIo=","keyid":"SHA256:jl3bwswu80PjjokCgh0o2w5c2U4LhQAE57gj9cz1kzA"}],"unpackedSize":63840},"main":"dist/index.js","types":"dist/index.d.ts","gitHead":"b7d2ff1d997cbf9343ac3507db6cf1c02a8e760d","scripts":{"test":"jest","build":"tsc","start":"tsc -w","coverage":"jest --coverage"},"_npmUser":{"name":"arekx","email":"arekusanda1@gmail.com"},"repository":{"url":"git+https://github.com/ArekX/teeql.git","type":"git"},"_npmVersion":"10.8.1","description":"Simple and powerful Query Builder using pure SQL","directories":{"test":"tests"},"_nodeVersion":"20.15.0","_hasShrinkwrap":false,"devDependencies":{"jest":"^29.7.0","ts-jest":"^29.1.2","typescript":"^5.4.5","@types/jest":"^29.5.12","@types/node":"^20.12.11"},"_npmOperationalInternal":{"tmp":"tmp/teeql_1.0.0_1719918258846_0.14603764226078786","host":"s3://npm-registry-packages"}},"1.0.1":{"name":"@arekx/teeql","version":"1.0.1","keywords":["sql","query","builder"],"author":{"name":"Aleksandar Panic"},"license":"Apache-2.0","_id":"@arekx/teeql@1.0.1","homepage":"https://github.com/ArekX/teeql","bugs":{"url":"https://github.com/ArekX/teeql/issues"},"dist":{"shasum":"fe68b47ecdc2f98fd8803d67cb175c2f8bff421f","tarball":"https://registry.npmjs.org/@arekx/teeql/-/teeql-1.0.1.tgz","fileCount":21,"integrity":"sha512-fMge192oIVG5jeOtuTvb6VFbcvAqxut/t+BNwKKnTouGm6vVPcw+2dCg6e4wfcpoYl8zl2PjpgKdTvP+rNPssw==","signatures":[{"sig":"MEQCIBnf/ZsBhkQCdYqnKxK6LMtNU3a5j2MUdN+1u88W08pxAiBrsUL4EmfBXlA4xZbrcWRPvA3WHgrzQi43H8zOoQPMnw==","keyid":"SHA256:jl3bwswu80PjjokCgh0o2w5c2U4LhQAE57gj9cz1kzA"}],"unpackedSize":65362},"main":"dist/index.js","types":"dist/index.d.ts","gitHead":"2a5f6a62371f0245b0c1766fa586c126bed74b88","scripts":{"test":"jest","build":"tsc","start":"tsc -w","coverage":"jest --coverage"},"_npmUser":{"name":"arekx","email":"arekusanda1@gmail.com"},"repository":{"url":"git+https://github.com/ArekX/teeql.git","type":"git"},"_npmVersion":"10.7.0","description":"Simple and powerful Query Builder using pure SQL","directories":{"test":"tests"},"_nodeVersion":"20.15.0","_hasShrinkwrap":false,"devDependencies":{"jest":"^29.7.0","ts-jest":"^29.1.2","typescript":"^5.4.5","@types/jest":"^29.5.12","@types/node":"^20.12.11"},"_npmOperationalInternal":{"tmp":"tmp/teeql_1.0.1_1719918398352_0.4332291025763295","host":"s3://npm-registry-packages"}},"1.0.2":{"name":"@arekx/teeql","version":"1.0.2","description":"Simple and powerful Query Builder using pure SQL","author":{"name":"Aleksandar Panic"},"license":"Apache-2.0","main":"dist/index.js","types":"dist/index.d.ts","keywords":["sql","query","builder"],"repository":{"type":"git","url":"git+https://github.com/ArekX/teeql.git"},"homepage":"https://github.com/ArekX/teeql","bugs":{"url":"https://github.com/ArekX/teeql/issues"},"scripts":{"start":"tsc -w","build":"tsc","test":"jest","coverage":"jest --coverage"},"devDependencies":{"@types/jest":"^29.5.12","@types/node":"^20.12.11","jest":"^29.7.0","ts-jest":"^29.1.2","typescript":"^5.4.5"},"directories":{"test":"tests"},"_id":"@arekx/teeql@1.0.2","gitHead":"ff140971c8420d096af681b998265613f11fe7f1","_nodeVersion":"20.19.4","_npmVersion":"10.8.2","dist":{"integrity":"sha512-KtG0JE7V3VxjT8F8RDdkr9JwMTb7TH/pqp19NqnD7kcjz0iy0HsjPWkY2/cLENY+YdFeuNURIqvKAKhQcKgcTg==","shasum":"06b95b102d043a1bec1811b69b73ffc7d9b02f49","tarball":"https://registry.npmjs.org/@arekx/teeql/-/teeql-1.0.2.tgz","fileCount":21,"unpackedSize":65362,"signatures":[{"keyid":"SHA256:DhQ8wR5APBvFHLF/+Tc+AYvPOdTpcIDqOhxsBHRwC7U","sig":"MEYCIQDNbzhtFut9PiBji+kOyowHhF80E1oSswSj1O6kwU7XDQIhANbfY9NbA+zh3CFTyiNkl05OQeasmh11GNnrOTNqt75y"}]},"_npmUser":{"name":"arekx","email":"arekusanda1@gmail.com"},"maintainers":[{"name":"arekx","email":"arekusanda1@gmail.com"}],"_npmOperationalInternal":{"host":"s3://npm-registry-packages-npm-production","tmp":"tmp/teeql_1.0.2_1756212787345_0.16515369371484434"},"_hasShrinkwrap":false}},"time":{"created":"2024-07-02T11:04:18.717Z","modified":"2025-08-26T12:53:07.717Z","1.0.0":"2024-07-02T11:04:18.964Z","1.0.1":"2024-07-02T11:06:38.496Z","1.0.2":"2025-08-26T12:53:07.537Z"},"bugs":{"url":"https://github.com/ArekX/teeql/issues"},"author":{"name":"Aleksandar Panic"},"license":"Apache-2.0","homepage":"https://github.com/ArekX/teeql","keywords":["sql","query","builder"],"repository":{"type":"git","url":"git+https://github.com/ArekX/teeql.git"},"description":"Simple and powerful Query Builder using pure SQL","maintainers":[{"name":"arekx","email":"arekusanda1@gmail.com"}],"readme":"# teeql\n\nteeql is a simple yet powerful query builder for SQL languages. It is designed to simplify the process of writing and managing SQL queries in Typescript and Javascript applications. It leverages JavaScript's template literals to provide a clean, intuitive syntax for constructing queries.\n\nThis library as it written right now it does not depend on any specific database engines like MySQL, Postgres, etc. It only generates a prepared SQL string and gives you the parameters used. The only place it actually generates SQL on its own is in the general SQL dialect which is a configuration which can be overridden by you if you are in some specific case where you need something generated which is unsupported by common SQL database engines.\n\n## Motivation\n\nObject query builders have become very complicated to learn and use properly. ORMs start as a good thing but in larger projects they become a liability since they usually load more data than needed.\n\nDevelopers usually try to run away from writing SQL because it is clunkly to write in code and also to maintain.\n\nSo what if we had a way to simplify writing the SQL itself and also to make it maintainable? This is what teeql does. And support for Typescript is also a plus.\n\n## Security\n\nteeql follows a simple principle which also allows for best possible security from SQL injection. By using javascript template literal language itself provides a barrier\nto separate what should be a parameter and what should be a query string.\n\nTake for example this query for retriving login details:\n\n```typescript\nconst username = request.body.get('username')\ntql`SELECT id, username, password FROM users WHERE username = ${username}`; \n```\n\nIn template literal syntax this will be processed as:\n\n```typescript\nstrings = [\"SELECT id, username, password FROM users WHERE username = \", \"\"];\nargs = [username]\n```\n\nWhen compiling this, teeql converts this into prepared string format:\n\n```typescript\n{\n  sql: \"SELECT id, username, password FROM users WHERE username = :p_1\",\n  params: {\n    \":p_1\": username\n  }\n}\n```\n\nteeql also looks at what the type of the parameter is and only if its another query\ncreated by `tql` it will allow it to be treated as a part of the query string, so this:\n\n```typescript\nconst subquery = tql`SELECT id FROM user_profile WHERE active = 1`;\nconst maliciousUsername = \"' --\"; // this would be a malicious exploit from user input.\nconst query = tql`SELECT id, username, password FROM users WHERE username = ${maliciousUsername} AND profile_id IN (${subquery})`;\n\n\nconst compiled = compile(query);\n```\n\nWould result in:\n\n```typescript\n{\n  sql: \"SELECT id, username, password FROM users WHERE username = :p_1 AND AND profile_id IN (SELECT id FROM user_profile WHERE active = 1)\",\n  params: {\n     ':p_1': \"' --\"\n  }\n}\n```\n\nBecause anything which is not a query created by `tql` is always treated as it is\na parameter.\n\n# Installation\n\n## JSR\n\nCheck https://jsr.io/@arekx/teeql to see installation commands for your runtime.\n\n# Basic Usage\n\nAt its simplest, teeql can be used to construct and compile SQL queries using the `tql` template literal and the `compile` function. Here's an example:\n\n```ts\nimport { tql, compile } from 'teeql';\n\nconst query = tql`SELECT * FROM users WHERE id = ${1}`;\nconst compiledQuery = compile(query);\n\n// Pass compiledQuery.sql and compiledQuery.params into your database\n```\n\nIn this example, compiledQuery will be an object of type CompiledQuery, which includes the SQL query string and the parameters used in the query.\n\n# Operators\n\nteeql has operators which can be used to simplify the whole proces of writing queries.\n\nOperators in a nutshell are set of helper functions which end up passing or generating additional `tql` so that your query can be built based on some additional\nparameters.\n\nFollowing is a quick summary of operators you can use in `tql`\n\n## when\n\n`when` operator is used to conditionally add additional SQL to your query based\non whether a parameter passed to it is true or false. This is very useful for building\nfilters or adding different joins and conditions based on what data is needed.\n\nFor example:\n\n```typescript\nconst productSearch = request.get('search') ?? null;\nconst query = compile(tql`\n  SELECT id, name FROM product\n  WHERE\n    active = 1\n    ${when(productSearch, tql`AND name LIKE ${`%${productSearch}%`}`)}\n`);\n```\n\nWill add `AND LIKE :p_1` (where `:p_1` is the `'%searchTerm%'`') to the compiled\nquery only when productSearch is actually passed in the request.\n\nIt can also be used as a ternary operator:\n\n```typescript\nconst onlyActiveProducts = request.get('active_products') ?? null;\nconst query = compile(tql`\n  SELECT id, name FROM product\n  WHERE\n    active = 1\n    ${when(onlyActiveProducts, \n      tql`AND status = 'active'` // will be returned if onlyActiveProducts is true,\n      tql`AND status IN ('pending', 'active')` // will be returned if onlyActiveProducts is false\n    )}\n`);\n```\n\n### Parameters vs functions\n\nParameters passed to `when` can be values or functions returning those values. For\nperformance and memory reasons it is useful to prefer functions over just returning\nthe values directly:\n\n```typescript\nconst active = 1;\nconst queryUsingValues = compile(tql`SELECT id FROM users ${when(\n  active == 1, \n  tql`WHERE active = 1` // this will always be generated here then only passed if active = 1\n)}`);\n\nconst queryUsingFunctions = compile(tql`SELECT id FROM users ${when(\n  () => active == 1, \n  () => tql`WHERE active = 1` // this will only be generated and passed when active = 1\n)}`);\n```\n\n## glue\n\nglue operator allows you to stictch two or more queries together, it is useful when\nyou want to build list of columns, filters or union queries or just join multiple queries together.\n\nExample:\n```typescript\nconst columnsToInclude = [\n  tql`id`, \n  tql`name`, \n  tql`password`\n];\nconst query = compile(tql`\n  SELECT\n    ${glue(\n      tql` ,`,\n      columnsToInclude\n    )}\n  FROM\n    users\n`); \n```\n\nWill return:\n```typescript\n{\n  sql: 'SELECT id, name, password FROM users',\n  params: {}\n}\n```\n\nThere are specialized operators for common glue operations:\n* `glueAnd` - Glues queries with ` AND `. Useful when building filters\n* `glueOr` - Glues queries with ` OR `. Useful when building filters\n* `glueComma` - Glues queries with `, `. Useful when combining queries like columns or parameters.\n* `glueUnion` - Glues queries with ` UNION `. Useful when making union queries.\n\nPlease note that `glue` can work together with `when` perfectly to only add things\nwhich are needed.\n\nExample using `glueAnd` when building filters:\n\n```typescript\nconst search = request.get('search'); // filter for search\nconst activeOnly = request.get('active_only'); // filter to show only active products\n\nconst query = compile(tql`\n  SELECT \n    id, name, cost\n  FROM product\n  WHERE\n  ${glueAnd(\n    tql`is_published = 1`,\n    when(search.length > 0, tql`name LIKE ${`%${search}%`}`),\n    when(activeOnly == 1, tql`active = 1`),\n  )}`;\n```\n\nThis will produce all three filters are set:\n\n```typescript\n{\n  sql: \"SELECT id, name, cost FROM product WHERE is_published = 1 AND name LIKE :p_1 AND active = 1\", \n  params: {\n    ':p_1': search // value from search with % and % on the sides.\n  }\n}\n```\n\n## prepend\n\nPrepend operator prepends a source query with another query if that query is not empty. This is useful when\nyou want for instance to apply `WHERE` when there is a filter set:\n\n```typescript\nconst search = request.get('search'); // filter for search\n\nconst query = compile(tql`\n  SELECT \n    id, name, cost\n  FROM product\n  ${prepend(tql`WHERE `, glueAnd(\n    tql`is_published = 1`,\n    when(search.length > 0, tql`name LIKE ${`%${search}%`}`),\n  )})}`;\n```\n\n## match\n\nMatch operator is similar to `when` except it accepts an array of parameters and returns the query on the first matched value:\n\n```typescript\nconst STATE_ACTIVE = 10;\nconst STATE_PENDING = 20;\nconst STATE_DELETED = 30;\nconst activeState = request.get('active_state');\nconst query = compile(tql`\n  SELECT \n    id, name\n  FROM users\n  WHERE\n  status = ${match(\n    [() => active == STATE_ACTIVE, () => tql`'active'`],\n    [() => active == STATE_PENDING, () => tql`'pending-activation'`],\n    [() => active == STATE_DELETED, () => tql`'deleted'`],\n  )}\n`)\n```\n\nMatch will run the function from the first index of each array and return the\nquery if its true.\n\nSo in case when active = 30, the result will \n\n```typescript\n{\n  sql: \"SELECT id, name FROM users WHERE status = 'deleted'\",\n  params: {}\n}\n```\n\n**Note:** Match, like `when` can accept parameters either as a function returning a value or the value itself.\n\n# Unsafe operations\n\nReal world is never simple so it is not possible to create a perfect query builder for all possible use cases and make it fully secure from SQL operations. This is why these additional operations are added.\n\nKeep note that these operations allow strings to be added to the query so they allow for SQL injection to \nhappen. This means that these operations should be used only with full knowledge of their effects and with extreme caution not to let any user input to be passed to these methods in any way (either from another method\nor from database, etc.). These methods are prefixed with `unsafe` to denote that they are not fully secure.\n\n## unsafeRaw\n\nAs the name says, this just passes whatever the string is passed to this directly into the query without\nany sanitization or processing:\n\n```typescript\nconst username = request.get('username'); // The passed value in request here is \"' --\"\nconst query = compile(tql`SELECT id FROM users WHERE username = '${unsafeRaw(username)}' AND is_active = 1`);\n```\n\nWould output:\n\n```typescript\n{\n  sql: \"SELECT id FROM users WHERE username = '' -- AND is_active = 1\", // Anything after -- is considered a comment in SQL, meaning the SQL injection happened here.\n  params: {} // No parameters since unsafeRaw was used.\n}\n```\n\nUsecase for this function would be for internal, queries which work on constants or some cases where you\nneed SQL generated from a string and for whatever reason you cannot use `tql` to do it. These cases should be extremely rare so please make sure that you have a really good reason to use this method.\n\n## unsafeName\n\nThis allows you to reference table names, column names and other database objects by their name. This method\nattempts sanitization and it is a bit safer than `unsafeRaw`. Sanitization is performed by the dialect object\nitself which in general case means that anything which is not a:\n- letter\n- number\n- dot\n- underscore\n\nWill be removed from a name:\n\n```typescript\nconst tableName = tables.usersTable; // returns string \"public.users\"\nconst columnName = userRecord.targetColumn; // returns \"name!\"\nconst query = compile(tql`SELECT id, ${unsafeName(columnName)} FROM ${unsafeName(tableName)}`);\n```\n\nWill return:\n\n```typescript\n{\n  sql: \"SELECT id, name FROM users\", // \"!\" is removed due to sanitization\n  params: {}\n}\n```\n\nUsecase for this is similar to `unsafeRaw`, for internal use and not accepting user input, when you need to\npick a column or a table based on some internal logic.\n\nNote: While this method will not (probably) not allow SQL injection directly if user input is used (due to sanitization) it will still allow user input to specify whatever valid string they want which can cause unintended consequences like reading data from a column or a table which you did not intend.\n\n# Advanced Usage\nFor more complex use cases, teeql allows you to define your own parameter builder and SQL dialect. This can be useful for handling different SQL dialects or customizing how parameters are handled.\n\nHere's an example of how you might define a custom parameter builder and custom dialect:\n\n```typescript\nimport { tql, compile, createDialect, generalSqlDialect, ParameterBuilder } from 'teeql';\n\nconst params: ParameterBuilder = new ParameterBuilder();\nconst dialect: Dialect = {\n  ...generalSqlDialect,\n  getParameterName: (p) => `@${p}`, // In this example the database requires prepared parameters to be @p_1, @p_2, etc.\n  toPreparedParameters: builder => {\n      const result = {};\n\n      for(const [key, value] of Object.entries(builder.parameters)) {\n        result[\"@\" + key] = value;\n      }\n\n      return result;\n  };\n};\n\nconst query = tql`SELECT * FROM users WHERE id IN ${[1, 2, 3]}`;\nconst compiledQuery = compile(query, parameters, dialect);\n```\n\nThis would return:\n\n```typescript\n{\n  sql: \"SELECT * FROM users WHERE id IN (@p_1, @p_2, @p_3)\",\n  params: {\n     \"@p_1\": 1,\n     \"@p_2\": 2,\n     \"@p_3\": 3,\n  }\n}\n```\n\n# Testing\nThis project uses Jest for testing. \n\nYou can run the tests with the following command: `npm test`\nTo generate a coverage report run: `npm run coverage`\n\n# Building\nTo build the project, use the following command: `npm run build`\n\n# Contributing\nContributions are welcome! Please feel free to submit a pull request.\n\n# License\nThis project is licensed under the [Apache 2.0 License](LICENSE)","readmeFilename":"README.md"}