### Complete Application Example with lib-sql

Source: https://context7.com/enonic/lib-sql/llms.txt

A full application example demonstrating database initialization, CRUD operations (Create, Read, Update, Delete), and integration with Enonic XP controllers. It includes a database utility module for managing connections and schema, and controller files for handling API requests.

```javascript
// lib/database.js - Database utility module
var sqlLib = require('/lib/sql');

var handle = sqlLib.connect({
    url: 'jdbc:h2:file:./build/tmp/data/db',
    driver: 'org.h2.Driver',
    user: 'sa',
    password: '',
    maxPoolSize: 10,
    minPoolSize: 2
});

// Initialize schema
function init() {
    handle.execute('DROP TABLE IF EXISTS persons');
    handle.execute(`
        CREATE TABLE persons (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            email VARCHAR(255) UNIQUE,
            age INT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    `);

    // Insert sample data
    handle.insert("INSERT INTO persons (name, email, age) VALUES ('Alice', 'alice@example.com', 30)");
    handle.insert("INSERT INTO persons (name, email, age) VALUES ('Bob', 'bob@example.com', 25)");
    handle.insert("INSERT INTO persons (name, email, age) VALUES ('Charlie', 'charlie@example.com', 35)");
}

exports.getHandle = function() {
    return handle;
};

exports.init = init;


// controllers/persons.js - RESTful API controller
var db = require('/lib/database');
var handle = db.getHandle();

// GET /api/persons - List all persons
exports.get = function(req) {
    if (req.params.id) {
        // GET single person by ID
        var person = handle.queryFirst('SELECT * FROM persons WHERE id = ' + req.params.id);

        if (!person) {
            return {
                status: 404,
                body: {error: 'Person not found'},
                contentType: 'application/json'
            };
        }

        return {
            status: 200,
            body: person,
            contentType: 'application/json'
        };
    }

    // GET all persons with optional pagination
    var limit = req.params.limit ? parseInt(req.params.limit) : null;
    var result = handle.query('SELECT * FROM persons ORDER BY id', limit);

    return {
        status: 200,
        body: result,
        contentType: 'application/json'
    };
};

// POST /api/persons - Create new person
exports.post = function(req) {
    var body = JSON.parse(req.body);

    try {
        var sql = "INSERT INTO persons (name, email, age) VALUES ('" +
                  body.name + "', '" + body.email + "', " + body.age + ")";
        var rows = handle.insert(sql);

        return {
            status: 201,
            body: {
                success: true,
                rowsInserted: rows,
                message: 'Person created successfully'
            },
            contentType: 'application/json'
        };
    } catch (e) {
        return {
            status: 400,
            body: {error: 'Failed to create person: ' + e.message},
            contentType: 'application/json'
        };
    }
};

// PUT /api/persons/:id - Update person
exports.put = function(req) {
    var personId = req.params.id;
    var body = JSON.parse(req.body);

    var sql = "UPDATE persons SET name = '" + body.name + 
              "', email = '" + body.email + 
              "', age = " + body.age + 
              " WHERE id = " + personId;

    var rowsAffected = handle.update(sql);

    return {
        status: rowsAffected > 0 ? 200 : 404,
        body: {
            success: rowsAffected > 0,
            rowsUpdated: rowsAffected,
            message: rowsAffected > 0 ? 'Person updated' : 'Person not found'
        },
        contentType: 'application/json'
    };
};

// DELETE /api/persons/:id - Delete person
exports.delete = function(req) {
    var personId = req.params.id;
    var rowsDeleted = handle.update("DELETE FROM persons WHERE id = " + personId);

    return {
        status: rowsDeleted > 0 ? 200 : 404,
        body: {
            success: rowsDeleted > 0,
            rowsDeleted: rowsDeleted,
            message: rowsDeleted > 0 ? 'Person deleted' : 'Person not found'
        },
        contentType: 'application/json'
    };
};

```

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

### JavaScript Example: Connect and Query SQL Database

Source: https://github.com/enonic/lib-sql/blob/master/README.md

A JavaScript example for use within an Enonic XP controller. It demonstrates connecting to an H2 database using lib-sql, performing a query, and returning the results.

```javascript
// Include the library
var sqlLib = require('/lib/sql');

// Create a handle for the connection to the database
var handle = sqlLib.connect({
    url: 'jdbc:h2:file:./build/tmp/data/db',
    driver: 'org.h2.Driver',
    user: 'sa',
    password: 'password',
    maxPoolSize: 10,
    minPoolSize: 0
});

// Output data from database in controller
exports.get = function(req) {

    // Query the database
    var result = handle.query('SELECT * FROM persons');

    // Return the result as JSON
    return {
        status: 200,
        body: result,
        contentType: 'application/json'
    }

};
```

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

### Configure Gradle Dependencies for Enonic lib-sql and JDBC Drivers

Source: https://context7.com/enonic/lib-sql/llms.txt

This Gradle configuration snippet demonstrates how to add the Enonic XP SQL library and various JDBC drivers to your project's dependencies. It includes the necessary repositories and lists example dependencies for PostgreSQL, MySQL, H2, and SQL Server. Ensure you select the appropriate JDBC driver for your target database.

```gradle
repositories {
    maven {
        url 'http://repo.enonic.com/public'
    }
    mavenCentral()
}

dependencies {
    // Include SQL library
    include 'com.enonic.lib:lib-sql:1.0.0'

    // Include JDBC driver (choose one or more)
    include 'org.postgresql:postgresql:42.7.1'        // PostgreSQL
    include 'com.mysql:mysql-connector-j:8.2.0'      // MySQL
    include 'com.h2database:h2:2.4.240'              // H2 (embedded)
    include 'com.microsoft.sqlserver:mssql-jdbc:12.4.2.jre11'  // SQL Server
}
```

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

### Include H2 Database and lib-sql in Gradle

Source: https://github.com/enonic/lib-sql/blob/master/README.md

Example of including both the lib-sql library and the H2 database JDBC driver in your Gradle build. This setup is for using an embedded H2 database.

```gradle
dependencies {
    include 'com.enonic.lib:lib-sql:<version>'
    include 'com.h2database:h2:1.4.190'
}
```

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

### Execute UPDATE/DELETE Statements with lib-sql

Source: https://context7.com/enonic/lib-sql/llms.txt

Executes SQL UPDATE or DELETE statements, returning the number of rows affected. This is useful for modifying existing records or removing data. The examples demonstrate usage in PUT and DELETE controllers.

```javascript
// Update single row
var updated = handle.update("UPDATE persons SET email = 'newemail@example.com' WHERE id = 1");
// Returns: 1 (one row updated)

// Update multiple rows
var massUpdate = handle.update("UPDATE persons SET status = 'active' WHERE created_date > '2024-01-01'");
// Returns: 42 (if 42 rows matched the condition)

// Delete rows
var deleted = handle.update("DELETE FROM persons WHERE status = 'inactive'");
// Returns: number of deleted rows

// Using in PUT controller with error handling
exports.put = function(req) {
    var personId = req.params.id;
    var body = JSON.parse(req.body);

    var sql = "UPDATE persons SET name = '" + body.name + "'
              " + ", email = '" + body.email + "' WHERE id = " + personId;

    var rowsAffected = handle.update(sql);

    if (rowsAffected === 0) {
        return {
            status: 404,
            body: {error: 'Person not found'}
        };
    }

    return {
        status: 200,
        body: {
            success: true,
            rowsUpdated: rowsAffected
        },
        contentType: 'application/json'
    };
};

// Using in DELETE controller
exports.delete = function(req) {
    var personId = req.params.id;
    var rowsDeleted = handle.update("DELETE FROM persons WHERE id = " + personId);

    return {
        status: rowsDeleted > 0 ? 200 : 404,
        body: {
            success: rowsDeleted > 0,
            rowsDeleted: rowsDeleted
        }
    };
};
```

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

### Establish Database Connection with Pooling

Source: https://context7.com/enonic/lib-sql/llms.txt

Connects to a SQL database using JDBC. It requires connection parameters like URL, driver, user, and password. Optional parameters control connection pooling behavior (maxPoolSize, minPoolSize) and pool naming. The returned handle manages sessions and query execution.

```javascript
var sqlLib = require('/lib/sql');

// Basic H2 embedded database connection
var handle = sqlLib.connect({
    url: 'jdbc:h2:file:./build/tmp/data/db',
    driver: 'org.h2.Driver',
    user: 'sa',
    password: 'password',
    maxPoolSize: 10,    // Maximum concurrent connections
    minPoolSize: 0,     // Minimum idle connections
    poolName: 'myPool'  // Optional pool identifier
});

// PostgreSQL production database connection
var pgHandle = sqlLib.connect({
    url: 'jdbc:postgresql://localhost:5432/mydb',
    driver: 'org.postgresql.Driver',
    user: 'dbuser',
    password: 'dbpass',
    maxPoolSize: 20,
    minPoolSize: 5
});

// MySQL connection
var mysqlHandle = sqlLib.connect({
    url: 'jdbc:mysql://localhost:3306/mydb?useSSL=false',
    driver: 'com.mysql.jdbc.Driver',
    user: 'root',
    password: 'secret'
});
```

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

### Database Connection API

Source: https://context7.com/enonic/lib-sql/llms.txt

Establishes a database connection handle with connection pooling, enabling all subsequent query operations. Requires JDBC URL and driver, with optional pooling and authentication parameters.

```APIDOC
## POST /lib/sql/connect

### Description
Creates a database connection handle with connection pooling. The handle manages database sessions and provides all query execution methods. Required parameters include the JDBC URL and driver class name; optional parameters control connection pooling behavior and authentication.

### Method
POST

### Endpoint
/lib/sql/connect

### Parameters
#### Request Body
- **url** (string) - Required - The JDBC URL for the database connection.
- **driver** (string) - Required - The fully qualified class name of the JDBC driver.
- **user** (string) - Optional - The username for database authentication.
- **password** (string) - Optional - The password for database authentication.
- **maxPoolSize** (number) - Optional - Maximum number of concurrent connections in the pool. Defaults to 10.
- **minPoolSize** (number) - Optional - Minimum number of idle connections in the pool. Defaults to 0.
- **poolName** (string) - Optional - An identifier for the connection pool.

### Request Example
```json
{
  "url": "jdbc:h2:file:./build/tmp/data/db",
  "driver": "org.h2.Driver",
  "user": "sa",
  "password": "password",
  "maxPoolSize": 10,
  "minPoolSize": 0,
  "poolName": "myPool"
}
```

### Response
#### Success Response (200)
- **handle** (object) - A connection handle object with methods for executing queries.

#### Response Example
```json
{
  "handle": {}
}
```
```

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

### Dispose Database Connections with lib-sql

Source: https://context7.com/enonic/lib-sql/llms.txt

Demonstrates how to manually or automatically dispose of database connections using the lib-sql library. Manual disposal is shown within a try-finally block for explicit cleanup, while automatic disposal is recommended and handled by Enonic XP on application unload.

```javascript
var sqlLib = require('/lib/sql');

// Manual cleanup after operations
var handle = sqlLib.connect({
    url: 'jdbc:h2:file:./build/tmp/data/db',
    driver: 'org.h2.Driver'
});

try {
    var result = handle.query('SELECT * FROM persons');
    // Process results...
} finally {
    sqlLib.dispose(); // Explicit cleanup
}

// Automatic cleanup (recommended)
// No need to call dispose() - it's called automatically when app unloads
var handle = sqlLib.connect({...});
// Use handle throughout your application lifecycle
// Connections automatically cleaned up on app unload

```

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

### Include lib-sql Dependency in Gradle

Source: https://github.com/enonic/lib-sql/blob/master/README.md

Demonstrates how to include the lib-sql library in your application's Gradle dependencies. Replace `<version>` with the desired version of the library.

```gradle
dependencies {
    include 'com.enonic.lib:lib-sql:<version>'
}
```

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

### Add Enonic Repository to Gradle

Source: https://github.com/enonic/lib-sql/blob/master/README.md

Specifies how to add the Enonic repository to your Gradle build file to access Enonic libraries. This is a prerequisite for including the lib-sql dependency.

```gradle
repositories {
    maven {
        url 'http://repo.enonic.com/public'
    }
}
```

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

### Include PostgreSQL JDBC Driver in Gradle

Source: https://github.com/enonic/lib-sql/blob/master/README.md

Shows how to add the PostgreSQL JDBC driver as an 'include' dependency in your Gradle build. This allows your application to connect to PostgreSQL databases.

```gradle
dependencies {
    include 'org.postgresql:postgresql:9.4.1208'
}
```

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

### Execute SELECT Query API

Source: https://context7.com/enonic/lib-sql/llms.txt

Executes a SELECT SQL query and returns all matching rows along with metadata. An optional limit parameter can be provided to restrict the number of returned rows.

```APIDOC
## POST /lib/sql/query

### Description
Executes a SELECT query and returns all matching rows with metadata. The response includes a count field indicating the number of rows returned and a result array containing row objects. An optional limit parameter restricts the maximum number of rows returned.

### Method
POST

### Endpoint
/lib/sql/query

### Parameters
#### Path Parameters
- **handle** (object) - Required - The connection handle obtained from `sqlLib.connect()`.

#### Request Body
- **sql** (string) - Required - The SQL SELECT statement to execute.
- **limit** (number) - Optional - The maximum number of rows to return.

### Request Example
```json
{
  "sql": "SELECT * FROM persons",
  "limit": 10
}
```

### Response
#### Success Response (200)
- **count** (number) - The number of rows returned.
- **result** (array) - An array of objects, where each object represents a row from the query result.

#### Response Example
```json
{
  "count": 3,
  "result": [
    {"id": 1, "name": "John Doe", "email": "john@example.com"},
    {"id": 2, "name": "Jane Smith", "email": "jane@example.com"},
    {"id": 3, "name": "Bob Johnson", "email": "bob@example.com"}
  ]
}
```
```

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

### Execute INSERT Statements with lib-sql

Source: https://context7.com/enonic/lib-sql/llms.txt

Executes SQL INSERT statements to add data to a database. Supports single and multiple row insertions. Returns the number of rows inserted. It's used within controllers to handle data creation requests.

```javascript
// Single row insert
var rowsInserted = handle.insert("INSERT INTO persons (id, name, email) VALUES (1, 'John Doe', 'john@example.com')");
// Returns: 1

// Multiple inserts in sequence
handle.insert("INSERT INTO persons VALUES (1, 'John', 'john@example.com')");
handle.insert("INSERT INTO persons VALUES (2, 'Jane', 'jane@example.com')");
handle.insert("INSERT INTO persons VALUES (3, 'Bob', 'bob@example.com')");

// Using in POST controller
exports.post = function(req) {
    var body = JSON.parse(req.body);
    var name = body.name;
    var email = body.email;

    try {
        var rows = handle.insert(
            "INSERT INTO persons (name, email) VALUES ('" + name + "', '" + email + "')"
        );

        return {
            status: 201,
            body: {
                success: true,
                rowsInserted: rows,
                message: 'Person created successfully'
            },
            contentType: 'application/json'
        };
    } catch (e) {
        return {
            status: 500,
            body: {error: 'Failed to insert: ' + e.message}
        };
    }
};
```

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

### Execute SELECT Query and Retrieve All Rows

Source: https://context7.com/enonic/lib-sql/llms.txt

Executes a standard SQL SELECT query and returns all matching rows along with metadata. An optional limit can be specified to restrict the number of returned rows. The result includes a 'count' field and a 'result' array of row objects. Useful for fetching lists of data or paginated results.

```javascript
// Query all rows
var result = handle.query('SELECT * FROM persons');
/* Returns:
{
    "count": 3,
    "result": [
        {"id": 1, "name": "John Doe", "email": "john@example.com"},
        {"id": 2, "name": "Jane Smith", "email": "jane@example.com"},
        {"id": 3, "name": "Bob Johnson", "email": "bob@example.com"}
    ]
}
*/

// Query with LIMIT for pagination
var page1 = handle.query('SELECT * FROM persons ORDER BY id', 10);
// Returns first 10 rows

// Query with WHERE clause
var filtered = handle.query("SELECT * FROM persons WHERE email LIKE '%@example.com'");

// Using in Enonic XP controller
exports.get = function(req) {
    var result = handle.query('SELECT id, name, email FROM persons');
    return {
        status: 200,
        body: result,
        contentType: 'application/json'
    };
};
```

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

### Execute DDL Statements with lib-sql

Source: https://context7.com/enonic/lib-sql/llms.txt

Executes Data Definition Language (DDL) statements such as CREATE TABLE, DROP TABLE, and ALTER TABLE. This function does not return a value and is used for structural database changes and schema management.

```javascript
// Create table
handle.execute('CREATE TABLE persons (id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(255))');

// Drop table
handle.execute('DROP TABLE IF EXISTS old_table');

// Add column to existing table
handle.execute('ALTER TABLE persons ADD COLUMN phone VARCHAR(20)');

// Create index
handle.execute('CREATE INDEX idx_email ON persons(email)');

// Database initialization script
function initializeDatabase(handle) {
    // Drop existing tables
    handle.execute('DROP TABLE IF EXISTS orders');
    handle.execute('DROP TABLE IF EXISTS customers');

    // Create customers table
    handle.execute(`
        CREATE TABLE customers (
            id INT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            email VARCHAR(255) UNIQUE,
            created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    `);

    // Create orders table with foreign key
    handle.execute(`
        CREATE TABLE orders (
            id INT PRIMARY KEY,
            customer_id INT,
            amount DECIMAL(10,2),
            order_date TIMESTAMP,
            FOREIGN KEY (customer_id) REFERENCES customers(id)
        )
    `);

    // Create indexes for performance
    handle.execute('CREATE INDEX idx_customer_email ON customers(email)');
    handle.execute('CREATE INDEX idx_order_date ON orders(order_date)');
}

// Application startup: ensure tables exist
var sqlLib = require('/lib/sql');
var handle = sqlLib.connect({
    url: 'jdbc:h2:file:./build/tmp/data/db',
    driver: 'org.h2.Driver'
});

initializeDatabase(handle);
```

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

### Execute SELECT Query for a Single Row

Source: https://context7.com/enonic/lib-sql/llms.txt

Executes a SQL SELECT query and returns only the first matching row as a plain JavaScript object, omitting the 'count' wrapper. This method is efficient for queries that are expected to return a single result, such as aggregate functions or lookups by a unique ID.

```javascript
// Get total count
var countResult = handle.queryFirst('SELECT COUNT(*) as total FROM persons');
// Returns: {"total": 3}

// Get single row by ID
var person = handle.queryFirst('SELECT * FROM persons WHERE id = 1');
// Returns: {"id": 1, "name": "John Doe", "email": "john@example.com"}

// Get aggregate statistics
var stats = handle.queryFirst('SELECT COUNT(*) as total, AVG(age) as avg_age FROM persons');
// Returns: {"total": 100, "avg_age": 32.5}

// Using in controller for detail page
exports.get = function(req) {
    var personId = req.params.id;
    var person = handle.queryFirst('SELECT * FROM persons WHERE id = ' + personId);

    if (!person) {
        return {
            status: 404,
            body: {error: 'Person not found'}
        };
    }

    return {
        status: 200,
        body: person,
        contentType: 'application/json'
    };
};
```

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

### Execute SELECT Query (Single Row) API

Source: https://context7.com/enonic/lib-sql/llms.txt

Executes a SELECT SQL query and returns only the first matching row as a plain object. This is ideal for queries expecting a single result, such as aggregate functions or lookups by unique identifiers.

```APIDOC
## POST /lib/sql/queryFirst

### Description
Executes a SELECT query and returns only the first matching row as a plain object without the count wrapper. Ideal for single-row queries like aggregate functions (COUNT, SUM, AVG) or lookups by unique identifiers.

### Method
POST

### Endpoint
/lib/sql/queryFirst

### Parameters
#### Path Parameters
- **handle** (object) - Required - The connection handle obtained from `sqlLib.connect()`.

#### Request Body
- **sql** (string) - Required - The SQL SELECT statement to execute.

### Request Example
```json
{
  "sql": "SELECT COUNT(*) as total FROM persons"
}
```

### Response
#### Success Response (200)
- **object** (object) - A plain object representing the first row returned by the query.

#### Response Example
```json
{
  "total": 3
}
```
```

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