### Full Quickstart Example with MySQL

Source: https://github.com/neonerd/puresql/blob/master/README.md

A complete example demonstrating the integration of puresql with MySQL. It includes setting up a MySQL connection, creating a puresql adapter, loading queries from a .sql file, and executing a query asynchronously.

```javascript
const mysql = require("mysql")
const puresql = require("puresql")

// Create a connection the adapter will use
const connection = mysql.createConnection({
  host : '192.168.99.100',
  port : 3307,
  user : 'test',
  password : '',
  database : 'test'
})
// Create the adapter
const adapter = puresql.adapters.mysql(connection)

// Load our queries
const queries = puresql.loadQueries("user.sql")

// Do something
async function foo() {
  const rows = await queries.get_all({}, adapter)
  rows.map(row => {
    console.log('Name: ' + row.name)
  })
}
foo()
```

--------------------------------

### PureSQL: Dynamic Parameters Example

Source: https://github.com/neonerd/puresql/blob/master/README.md

Demonstrates dynamic parameter construction using the '~' modifier. This is useful for building complex WHERE clauses dynamically based on multiple conditions.

```javascript
// SELECT * FROM user WHERE :~conditions
queries.search_users({'~conditions':{
  operator: 'AND',
  parts: [
    ['position = :position', {position: 'manager'}],
    ['division = :division', {division: 'division'}]
  ]
}})
// SELECT * FROM user WHERE position = "manager" AND division = "division"
```

--------------------------------

### MSSQL Adapter Setup (v3.x.x)

Source: https://github.com/neonerd/puresql/blob/master/README.md

Initializes an MSSQL adapter for PureSQL, specifically compatible with mssql version 3.x.x. Requires 'mssql' and 'puresql'. The setup involves connecting to the database using mssql.connect and then creating the adapter with the established connection. Note: MSSQL version 4.x.x is not currently supported.

```javascript
// dependencies
const mssql = require('mssql')
const puresql = require('puresql')
// create a connection the adapter will use
mssql.connect(CREDENTIALS)
.then(function () {
  // create the adapter
  const adapter = puresql.adapters.mssql(mssql)
})
```

--------------------------------

### PureSQL: Conditioned Parameter Example

Source: https://github.com/neonerd/puresql/blob/master/README.md

Shows the usage of conditioned parameters ('*'). The parameter is only included in the query if it's not undefined, useful for optional clauses like LIMIT.

```javascript
// SELECT * FROM user ORDER BY name :*limit{LIMIT *!}
queries.get_users({'*limit': 10}, adapter)
queries.get_users({}, adapter)
// SELECT * FROM user ORDER BY name LIMIT 10
// SELECT * FROM user ORDER BY name
```

--------------------------------

### PureSQL: Object Parameter Example (UPDATE)

Source: https://github.com/neonerd/puresql/blob/master/README.md

Demonstrates using object parameters for UPDATE statements with the '@' modifier. This allows dynamically setting multiple columns in an UPDATE query.

```javascript
// UPDATE user SET :@user
queries.insert_user({'@user': {name: 'John', surname: 'Doe'}}, adapter)
// UPDATE user SET name = 'John', surname = 'Doe'
```

--------------------------------

### MySQL Adapter Setup

Source: https://github.com/neonerd/puresql/blob/master/README.md

Sets up a MySQL adapter for PureSQL. Requires the 'mysql' and 'puresql' npm packages. Initializes a MySQL connection and then creates the adapter. The adapter can optionally accept a debug function and exposes the lastInsertId property.

```javascript
// dependencies
const mysql = require('mysql')
const puresql = require('puresql')
// create a connection the adapter will use
const connection = mysql.createConnection({
  host : '192.168.99.100',
  port : 3307,
  user : 'test',
  password : '',
  database : 'test'
})
// create the adapter
const adapter = puresql.adapters.mysql(connection)
```

```javascript
await queries.insert({data:['foo', 'bar']}, mysqlAdapter)
console.log(mysqlAdapter.lastInsertId)
// should output the ID of the last inserted row if possible
```

--------------------------------

### PostgreSQL Adapter Setup

Source: https://github.com/neonerd/puresql/blob/master/README.md

Sets up a PostgreSQL adapter using PureSQL. It requires the 'pg' and 'puresql' npm packages. The process involves creating a PostgreSQL client instance and then passing it to the adapter. A debug function can also be optionally provided.

```javascript
// dependencies
const pg = require('pg')
const puresql = require('puresql')
// create a connection the adapter will use
const client = new pg.Client(config)
// create the adapter
const adapter = puresql.adapters.pg(client)
pg.connect((err) => {
  // do something
})
```

--------------------------------

### SQLite Adapter Setup

Source: https://github.com/neonerd/puresql/blob/master/README.md

Configures an SQLite adapter for PureSQL. Requires 'sqlite3' and 'puresql'. It involves creating an in-memory SQLite database connection and then initializing the adapter with this connection. An optional debug function can be passed.

```javascript
// dependencies
const sqlite3 = require('sqlite3')
const puresql = require('puresql')
// create the db adapter will use
const db = new sqlite3.Database(':memory:')
const adapter = puresql.adapters.sqlite(db)
```

--------------------------------

### SQL Query Definitions (user.sql)

Source: https://github.com/neonerd/puresql/blob/master/README.md

Example SQL file content demonstrating how to define multiple queries using puresql's naming convention. Queries are separated by '-- name:' comments. Placeholders for parameters are denoted by ':param_name' or ':?'.

```sql
-- name: get_by_id
SELECT *
FROM user
WHERE id = :id

-- name: get_all
SELECT *
FROM user

-- name: get_by_ids
SELECT *
FROM user
WHERE id IN :ids

-- name: get_or
SELECT *
FROM user
WHERE id = :? OR id = :?
```

--------------------------------

### PureSQL: Sub-Array Parameter Example (INSERT)

Source: https://github.com/neonerd/puresql/blob/master/README.md

Demonstrates using sub-arrays for inserting multiple rows with PureSQL. Each sub-array represents a row, and the parameter is automatically formatted for bulk inserts.

```javascript
// INSERT INTO user (name) VALUES :values
queries.create_users({values: [['john'], ['mark']]}, adapter)
// INSERT INTO user (name) VALUES ("john"), ("mark")
```

--------------------------------

### PureSQL: Unnamed Parameter Example

Source: https://github.com/neonerd/puresql/blob/master/README.md

Shows the usage of unnamed (anonymous) parameters in PureSQL. These are denoted by ':?'. Multiple unnamed parameters can be used, and their values are provided as an array within the parameters object.

```javascript
// SELECT * FROM user WHERE id = :? OR id = :?
queries.get_or({'?':[1, 2]}, adapter)
// SELECT * FROM user WHERE id = 1 OR id = 2
```

--------------------------------

### PureSQL: Object Parameter Example (INSERT)

Source: https://github.com/neonerd/puresql/blob/master/README.md

Illustrates using object parameters for INSERT statements. The '$' modifier allows passing an object where keys correspond to column names and values are inserted as parameters.

```javascript
// INSERT INTO user (name, surname) VALUES :$user
queries.insert_user({'$user': {name: 'John', surname: 'Doe'}}, adapter)
// INSERT INTO user (name, surname) VALUES ('John', 'Doe')
```

--------------------------------

### PureSQL: Array Parameter Example

Source: https://github.com/neonerd/puresql/blob/master/README.md

Illustrates how PureSQL handles array parameters, commonly used with the 'IN' clause. The array is automatically converted into its SQL representation (e.g., (1, 2, 3, 4)).

```javascript
// SELECT * FROM user WHERE id IN :ids
queries.get_by_ids({ids:[1, 2, 3, 4]}, adapter)
// SELECT * FROM user WHERE id IN (1, 2, 3, 4)
```

--------------------------------

### PureSQL: Dangerous Parameter Example (Unescaped)

Source: https://github.com/neonerd/puresql/blob/master/README.md

Shows how to use the 'dangerous parameter' modifier ('!'). This bypasses automatic sanitization and is intended for specific use cases like dynamic ORDER BY clauses where SQL injection is not a risk.

```javascript
// SELECT * FROM user ORDER BY :!order
queries.get_users({'!order': 'id ASC'}, adapter)
// SELECT * FROM user ORDER BY id ASC
```

--------------------------------

### PureSQL: Named Parameter Example

Source: https://github.com/neonerd/puresql/blob/master/README.md

Demonstrates how to use named parameters in PureSQL queries. The parameter is specified with a colon followed by its name (e.g., :id). The corresponding value is passed in a JavaScript object. PureSQL automatically sanitizes this parameter.

```javascript
// SELECT * FROM user WHERE id = :id
queries.get_by_id({id:1}, adapter)
// SELECT * FROM user WHERE id = 1
```

--------------------------------

### Define SQL Queries from File

Source: https://github.com/neonerd/puresql/blob/master/README.md

Loads SQL queries defined in a .sql file and exposes them as promisified JavaScript functions. It takes the file path and an adapter as input. The SQL file should use '-- name:' comments to define query names and ':param' syntax for placeholders.

```javascript
const puresql = require("puresql")

// Load queries from 'user.sql'
const queries = puresql.loadQueries('user.sql')

// Example usage (assuming adapter is already defined)
// async function example() {
//   const rows = await queries.get_all({}, adapter)
//   console.log(rows)
// }
```

--------------------------------

### PureSQL: Koa Integration (Async/Await)

Source: https://github.com/neonerd/puresql/blob/master/README.md

Demonstrates integrating PureSQL with the Koa framework (v2.0.0+). Since Koa uses async/await by default, PureSQL works seamlessly within Koa middleware.

```javascript
const koa = require("koa")
// Create a simple server
const app = koa()

app.use(async function() {
  // Like sync, but async!
  const rows = await queries.get_all({}, adapter)
  this.body = JSON.stringify(rows)
})

app.listen(3000)
```

--------------------------------

### MySQL Adapter Creation

Source: https://github.com/neonerd/puresql/blob/master/README.md

Provides a way to create a puresql adapter for MySQL using the `mysql` Node.js package. It takes an existing `mysql` connection object as input. This adapter facilitates the execution of puresql queries against a MySQL database.

```javascript
const mysql = require("mysql")
const puresql = require("puresql")

// Create a MySQL connection
const connection = mysql.createConnection({
  host : 'localhost',
  user : 'root',
  password : 'password',
  database : 'mydatabase'
})

// Create the puresql MySQL adapter
const adapter = puresql.adapters.mysql(connection)
```

--------------------------------

### Testing Adapter

Source: https://github.com/neonerd/puresql/blob/master/README.md

Provides a testing adapter for PureSQL. This adapter's purpose is to return the processed SQL query, including parameter substitutions, as its result, useful for testing query logic without database interaction.

```javascript
puresql.adapters.test()
```

--------------------------------

### Load Queries from File

Source: https://github.com/neonerd/puresql/blob/master/README.md

Loads SQL queries from a specified file using PureSQL. The `loadQueries` function parses the file and returns an object where keys are query names and values are corresponding functions.

```javascript
const queries = puresql.loadQueries('user.sql')
console.log(queries)

/*
{
  get_by_id : fn,
  get_all : fn,
  get_by_ids : fn,
  get_or : fn
}
*/
```

--------------------------------

### PureSQL: ES2015 Async/Await Integration

Source: https://github.com/neonerd/puresql/blob/master/README.md

Shows how to use PureSQL queries with async/await in ES2015 (Node.js 8+). This allows for a more synchronous-like code flow, avoiding callback or .then() chaining.

```javascript
// ES2015 (node.js >8)
async function test () {
  const rows = await queries.get_all({}, adapter)
  console.log(rows)
}
```

--------------------------------

### Define Individual SQL Query Manually

Source: https://github.com/neonerd/puresql/blob/master/README.md

Allows defining individual SQL queries directly within JavaScript code using the `puresql.defineQuery` function. This is an alternative to loading from a .sql file, useful for simpler or dynamically generated queries. The function returns a promisified function that accepts query parameters and an adapter.

```javascript
const puresql = require("puresql")

// Define a single query manually
const get_user_by_id = puresql.defineQuery(
  '-- name: get_user_by_id\nSELECT *\nFROM user\nWHERE id = :id'
)

// Example usage (assuming adapter is already defined)
// async function example() {
//   const rows = await get_user_by_id({id: 1}, adapter)
//   console.log(rows)
// }
```

--------------------------------

### Define Query from String

Source: https://github.com/neonerd/puresql/blob/master/README.md

Defines a SQL query dynamically from a string representation using PureSQL's `defineQuery` function. This function returns a query function that can be used with adapters.

```javascript
const query = puresql.defineQuery("SELECT * FROM user WHERE id = :id")
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.