### INSTALL EXTENSION example

Source: https://villagesql.com/docs/mysql-9.7/stable/create

Shows how to install an extension using the name from manifest.json. Underscores avoid backtick quoting; hyphens require backticks in SQL.

```sql
-- manifest.json has "name": "my_awesome_extension"
INSTALL EXTENSION my_awesome_extension;  -- ✅ Correct: no quoting needed
INSTALL EXTENSION `my-awesome-extension`;  -- ⚠️ Works, but requires backtick quoting
```

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

### Install build dependencies with setup_build_env.sh

Source: https://villagesql.com/docs/mysql-9.7/stable/source

Runs the setup script from the cloned repository to detect the operating system and install required build dependencies. On Linux it uses apt-get and asks for sudo; on macOS it uses Homebrew. The script supports Debian or Ubuntu Linux and macOS.

```bash
cd "$HOME/villagesql-server"
villagesql/bld_tools/setup_build_env.sh

```

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

### Install vsql_oauth2 extension

Source: https://villagesql.com/docs/mysql-9.7/stable/extensions/vsql-oauth2

Installs the vsql_oauth2 extension. Requires the server to be started with --vsql_allow_preview_extensions=ON.

```sql
INSTALL EXTENSION vsql_oauth2;

```

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

### Launch mysqld with preview flag

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

Pass the flag on the command line when launching mysqld directly, for example from an install script that starts the server for the first time, because mysqld-auto.cnf won't exist yet to carry the persisted value.

```bash
mysqld --vsql_allow_preview_extensions=ON

```

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

### Create database and table for vsql_rest example

Source: https://villagesql.com/docs/mysql-9.7/stable/extensions/vsql-rest

Creates the extdocs database, switches to it, creates the people table, and inserts sample rows. The listener caches the database layout when it starts and holds it for schema_ttl seconds, so create the table before starting the listener.

```sql
CREATE DATABASE extdocs;
USE extdocs;

CREATE TABLE people (id INT PRIMARY KEY, name VARCHAR(32));

INSERT INTO people VALUES
    (1, 'Jonathan Smith'),
    (2, 'John Smyth'),
    (3, 'Joan Smithe'),
    (4, 'Peter Brown');
```

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

### Install vsql_rest extension

Source: https://villagesql.com/docs/guides/rest-api-from-tables

Enables the vsql_rest extension. Requires starting the server with --vsql_allow_preview_extensions=ON, otherwise the command is refused.

```sql
INSTALL EXTENSION vsql_rest;
```

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

### Install vsql_duckdb extension

Source: https://villagesql.com/docs/mysql-9.7/stable/extensions/vsql-duckdb

Installs the vsql_duckdb extension after building it and copying the .veb file to the veb_dir directory. Requires the server to be started with --vsql_allow_preview_extensions=ON.

```sql
INSTALL EXTENSION vsql_duckdb;

```

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

### Start and manage a local dev server with villagesql CLI

Source: https://villagesql.com/docs/mysql-9.7/stable/testing

Initialize and start a local VillageSQL dev server instance, then check status, open a mysql shell, and stop the server. Run these commands from the directory where VillageSQL was installed.

```bash
./villagesql init    # initialize database and seed bundled extensions
./villagesql start   # start the server (default port 3307)
./villagesql status  # check the server is running
./villagesql connect # open a mysql shell
./villagesql stop    # stop the server
```

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

### Install sqlc with go install

Source: https://villagesql.com/docs/guides/go-sqlc-mysql

Install sqlc as a Go tool using `go install`. Useful for Go developers who prefer managing tools as Go modules.

```bash
go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest
```

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

### Create sample tables and insert data for JOIN examples

Source: https://villagesql.com/docs/guides/joins

Creates the customers and orders tables used throughout the JOIN guide, with orders.customer_id nullable to represent orders without a customer, and inserts sample data including an order with a NULL customer_id.

```sql
CREATE TABLE customers (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,    -- nullable: orders without a customer
    amount      DECIMAL(10,2) NOT NULL
);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO orders VALUES
    (1, 1, 99.00),   -- Alice's order
    (2, 1, 45.00),   -- Alice's second order
    (3, 2, 120.00),  -- Bob's order
    (4, NULL, 15.00); -- order with no customer
```

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

### Install sqlc with Homebrew

Source: https://villagesql.com/docs/guides/go-sqlc-mysql

Install sqlc on macOS using Homebrew. This is the recommended method for local setups.

```bash
brew install sqlc
```

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

### Correct manifest.json example

Source: https://villagesql.com/docs/mysql-9.7/stable/managing

Example manifest.json with matching name and version.

```json
// manifest.json
{
  "name": "my_extension",
  "version": "1.0.0"
}
```

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

### Install extension and query INFORMATION_SCHEMA.EXTENSIONS

Source: https://villagesql.com/docs/mysql-9.7/stable/reference

Shows how to install an extension and query the INFORMATION_SCHEMA.EXTENSIONS view. The output is illustrative; actual version strings depend on installed extensions. EXTENSION_NAME values are lowercase, matching the name passed to make_extension().

```sql
-- Install an extension (VillageSQL-specific syntax)
INSTALL EXTENSION vsql_complex;

-- List all installed extensions
SELECT * FROM INFORMATION_SCHEMA.EXTENSIONS;

-- Check a specific extension's version
SELECT EXTENSION_VERSION
FROM INFORMATION_SCHEMA.EXTENSIONS
WHERE EXTENSION_NAME = 'vsql_complex';

```

```text
+------------------+-------------------+
| EXTENSION_NAME   | EXTENSION_VERSION |
+------------------+-------------------+
| vsql_complex     | 0.0.1             |
| vsql_uuid        | 0.2.1             |
+------------------+-------------------+

```

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

### Start server with preview extensions enabled

Source: https://villagesql.com/docs/guides/mysql-ai-agents-mcp

Start the server with preview extensions allowed, which is required for vsql_mcp to work.

```bash
mysqld --vsql_allow_preview_extensions=ON ...
```

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

### Install extension

Source: https://villagesql.com/docs/mysql-9.7/stable/create

Installs the extension named my_awesome_extension in the current session.

```sql
INSTALL EXTENSION my_awesome_extension;

```

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

### Allocate a new page from a segment with storage::Segment::get_header

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

During DML operations, get a segment reference from the root page to allocate new pages. Segments are reserved at create time; see the create and load examples in Per-Column Context for the complete setup pattern.

```cpp
storage::Page root;
root.load(ctx->user()->space, ctx->user()->root_page,
          storage::Page::Latch::EXCLUSIVE, mtr_ref);
storage::Segment::Ref seg = storage::Segment::get_header(root, 0);
storage::Page data_page;
data_page.load_new(seg, mtr_ref);  // allocates a fresh page

```

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

### Enable prometheus_exporter and set port

Source: https://villagesql.com/docs/mysql-9.7/stable/extensions/prometheus-exporter

Quick start that installs the extension, sets the port to 9104, and enables it. Prometheus then scrapes http://127.0.0.1:9104/metrics. The listener binds to localhost unless changed, which is the safe choice.

```sql
INSTALL EXTENSION prometheus_exporter;
SET GLOBAL prometheus_exporter.port = 9104;
SET GLOBAL prometheus_exporter.enabled = ON;

```

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

### SQL usage for vsql_rot13

Source: https://villagesql.com/docs/mysql-9.7/stable/rust-examples

SQL usage example for the vsql_rot13 extension. Shows installation, basic usage, the inverse property, and NULL handling.

```sql
INSTALL EXTENSION vsql_rot13;

SELECT rot13('Hello, World!');
-- 'Uryyb, Jbeyq!'

SELECT rot13(rot13('Hello, World!'));
-- 'Hello, World!' (rot13 is its own inverse)

SELECT rot13(NULL);
-- NULL
```

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

### Install extension and bind account with PROXY privilege

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

Install the extension and bind an account to the method. Because the handler maps to a second account, create that account too and grant it the PROXY privilege that lets the connecting account assume its identity.

```sql
INSTALL EXTENSION vsql_auth_test;
CREATE USER auth_user IDENTIFIED WITH vsql_auth_test;
CREATE USER vsql_auth_test_user;
GRANT SELECT ON *.* TO vsql_auth_test_user;
GRANT PROXY ON vsql_auth_test_user TO auth_user;

```

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

### Install vsql_address_standardizer extension

Source: https://villagesql.com/docs/mysql-9.7/stable/extensions/vsql-address-standardizer

Installs the vsql_address_standardizer extension into the server. Build it from the repository before running this command.

```sql
INSTALL EXTENSION vsql_address_standardizer;
```

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

### Build server and client only with make

Source: https://villagesql.com/docs/mysql-9.7/stable/source

Builds only the server and client. This is faster but does not build the extension SDK or example .veb files, so INSTALL EXTENSION vsql_complex finds no bundle and extension builds that pass -DVillageSQL_BUILD_DIR find no SDK.

```bash
make -j10 mysqld mysql

```

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

### Install and verify extension in SQL

Source: https://villagesql.com/docs/guides/rust-extensions

Install the extension, check it in INFORMATION_SCHEMA, and call the function.

```sql
INSTALL EXTENSION vsql_my_extension;
SELECT * FROM INFORMATION_SCHEMA.EXTENSIONS WHERE EXTENSION_NAME = 'vsql_my_extension';
SELECT my_func('hello');
-- → HELLO

```

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

### Install cargo-vsql from SDK repo

Source: https://villagesql.com/docs/guides/rust-extensions

Clone the vsql-rust-sdk repository and install the cargo-vsql subcommand using cargo install.

```bash
git clone https://github.com/villagesql/vsql-rust-sdk
cd vsql-rust-sdk
cargo install --path cargo-vsql

```

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

### Package and install extension

Source: https://villagesql.com/docs/guides/rust-extensions

Package and install the extension using cargo vsql install.

```bash
cargo vsql install

```

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

### Install prometheus_exporter extension

Source: https://villagesql.com/docs/mysql-9.7/stable/extensions/prometheus-exporter

Installs the prometheus_exporter extension. The code on the repository's default branch does not build against the current SDK; the ported code is on the maintainer's open pull request #4, which declares preview capabilities, so a server built from it must be started with --vsql_allow_preview_extensions=ON.

```sql
INSTALL EXTENSION prometheus_exporter;

```

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

### Install VillageSQL with custom prefix

Source: https://villagesql.com/docs/mysql-9.7/stable/server-development

Configures the build with a custom installation prefix `/opt/villagesql` and then installs the binaries to that location.

```bash
cmake -S . -B build -DCMAKE_INSTALL_PREFIX=/opt/villagesql
cmake --build build --target install

```

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

### Start VillageSQL with systemd

Source: https://villagesql.com/docs/guides/upgrade

Starts the VillageSQL server using systemd. This is the recommended method when using systemd as the service manager.

```bash
sudo systemctl start mysql

```

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

### Register a preview capability with .with() in make_extension()

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

Declares a preview capability object by value at file scope and passes it by reference to `.with()` inside `make_extension()`. The server populates the object's `abi` pointer during registration. Every capability object must be passed to `.with()` exactly once; violations cause `INSTALL EXTENSION` to fail with specific error messages.

```cpp
#include <villagesql/preview/keyring.h>
#include <villagesql/vsql.h>

using KeyringCapability = vsql::preview_keyring::KeyringCapability;

static KeyringCapability g_keyring;

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .func(/* ... */)
        .with(g_keyring))
```

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

### Complete keyring reader extension example

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

Simplified version of the vsql_keyring_reader test extension. Registers two VDFs: keyring_read and keyring_store. Handles null arguments and returns errors when no keyring component is installed.

```cpp
#include <villagesql/preview/keyring.h>
#include <villagesql/vsql.h>

using namespace vsql;
using KeyringCapability = vsql::preview_keyring::KeyringCapability;

static KeyringCapability g_keyring;

void keyring_read(StringArg data_id, StringArg auth_id, StringResult out) {
  if (data_id.is_null()) { out.set_null(); return; }

  const auto [status, value] =
      g_keyring.read(data_id.value(), auth_id.is_null() ? "" : auth_id.value());
  if (status == KeyringCapability::Status::UNAVAILABLE) {
    out.error("No keyring component is installed");
    return;
  }
  if (status != KeyringCapability::Status::OK) { out.set_null(); return; }

  auto buf = out.buffer();
  size_t len = std::min(value.size(), buf.size());
  memcpy(buf.data(), value.data(), len);
  out.set_length(len);
}

void keyring_store(StringArg data_id, StringArg auth_id, StringArg value,
                   IntResult out) {
  if (data_id.is_null() || value.is_null()) { out.set(1); return; }

  KeyringCapability::Status status = g_keyring.write(
      data_id.value(), auth_id.is_null() ? "" : auth_id.value(), value.value());
  if (status == KeyringCapability::Status::UNAVAILABLE) {
    out.error("No keyring component is installed");
    return;
  }
  out.set(status == KeyringCapability::Status::OK ? 0 : 1);
}

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .func(make_func<&keyring_read>("keyring_read")
                  .returns(STRING).param(STRING).param(STRING).build())
        .func(make_func<&keyring_store>("keyring_store")
                  .returns(INT).param(STRING).param(STRING).param(STRING).build())
        .with(g_keyring))

```

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

### Install the extension in VillageSQL

Source: https://villagesql.com/docs/guides/cpp-extensions

Connects to VillageSQL and installs the extension using the `INSTALL EXTENSION` statement.

```sql
INSTALL EXTENSION my_extension;

```

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

### Install vsql_nanoid extension

Source: https://villagesql.com/docs/mysql-9.7/stable/extensions/vsql-nanoid

Build the extension from the repository and install it into the server. It requires OpenSSL for its random source.

```sql
INSTALL EXTENSION vsql_nanoid;
```

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

### Manually install VEB file

Source: https://villagesql.com/docs/mysql-9.7/stable/create

Manually installs the VEB by first querying the veb_dir variable and then copying the .veb file to that directory with sudo.

```bash
# Find the VEF directory
mysql -u root -p -e "SHOW VARIABLES LIKE 'veb_dir';"

# Copy the VEB file
sudo cp my-awesome-extension.veb /path/to/veb_dir/

```

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

### INSTALL EXTENSION vsql_cube

Source: https://villagesql.com/docs/mysql-9.7/stable/extensions/vsql-cube

Installs the vsql_cube extension into the server.

```APIDOC
## INSTALL EXTENSION vsql_cube

### Description
Installs the vsql_cube extension into the server.

### Method
INSTALL

### Endpoint
INSTALL EXTENSION vsql_cube

### Parameters
None

### Request Example
sql
INSTALL EXTENSION vsql_cube;


### Response
#### Success Response
Confirmation that the extension was installed.

#### Response Example
No specific response body is documented.
```

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

### Install vsql_luhn extension

Source: https://villagesql.com/docs/mysql-9.7/stable/extensions/vsql-luhn

Installs the vsql_luhn extension into the server. Build it from the repository before running this command.

```sql
INSTALL EXTENSION vsql_luhn;

```

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

### Install vsql_cube Extension

Source: https://villagesql.com/docs/mysql-9.7/stable/extensions/vsql-cube

Install the vsql_cube extension into the server using the INSTALL EXTENSION statement. Ensure the extension is present by querying the information schema. Output shows the extension name.

```sql
INSTALL EXTENSION vsql_cube;
```

```sql
SELECT EXTENSION_NAME
FROM INFORMATION_SCHEMA.EXTENSIONS
WHERE EXTENSION_NAME = 'vsql_cube';
```

```text
+----------------+
| EXTENSION_NAME |
+----------------+
| vsql_cube      |
+----------------+
```

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

### Install VillageSQL via Command Line

Source: https://villagesql.com/docs

One-line installer for VillageSQL. Run this command in a terminal to download and execute the installation script.

```bash
curl https://install.villagesql.com | bash
```

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

### Installation Flow Steps

Source: https://villagesql.com/docs/mysql-9.7/stable/architecture

Shows the step-by-step installation flow for an extension, from validating the .veb file to persisting registration. Includes rollback on failure and symbol isolation via RTLD_LOCAL.

```text
INSTALL EXTENSION name
    ↓
1. Validate .veb exists in veb_dir
    ↓
2. Calculate SHA256 hash of .veb
    ↓
3. Expand to {datadir}/.veb_expansion_cache/{name}/{sha256}/
    ↓
4. Parse and validate manifest.json
    ↓
5. Load .so library (dlopen with RTLD_LOCAL)
    ↓
6. Call vef_register() entry point
    ↓
7. Register VDFs and custom types
    ↓
8. Persist registration and update cache
    ↓
Success
```

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

### Complete Example: Periodic Heartbeat Worker Extension

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

A minimal extension with a single periodic worker that increments a heartbeat counter on each timer tick. Requires vsql_allow_preview_extensions = ON to install. The worker is enabled via the system variable my_ext.heartbeat_enabled.

```cpp
#include <villagesql/preview/thread_worker.h>
#include <villagesql/vsql.h>

#include <atomic>

static std::atomic<unsigned long long> g_heartbeat{0};

static vef_next_wakeup_t heartbeat_work(vef_wakeup_reason_t reason,
                                        struct vef_thread_handle_t *thread,
                                        void *arg) {
  switch (reason) {
    case VEF_WAKEUP_ENABLE:
      return {1000, 0};  // tick every 1000 ms, no poll fd
    case VEF_WAKEUP_PERIODIC:
      g_heartbeat.fetch_add(1, std::memory_order_relaxed);
      return {};  // keep current sleep_ms and poll_fd
    case VEF_WAKEUP_POLL_FD:
      return {};  // not used in this example
    case VEF_WAKEUP_DISABLE:
      return {};  // ignored
  }
  return {};
}

static vsql::preview_thread_worker::ThreadWorkerCapability<&heartbeat_work>
    g_worker{"heartbeat"};

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .with(g_worker))

```

```sql
SET GLOBAL my_ext.heartbeat_enabled = ON;

```

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

### Initialize project layout

Source: https://villagesql.com/docs/guides/go-sqlc-mysql

Create a Go module for the sqlc MySQL example. Run in a terminal to set up the project directory and module.

```bash
mkdir go-sqlc-mysql && cd go-sqlc-mysql
go mod init go-sqlc-mysql
```

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

### Install extension in SQL

Source: https://villagesql.com/docs/mysql-9.7/stable/rust-sdk

Installs the extension in SQL once the VEB is in the extensions directory.

```sql
INSTALL EXTENSION vsql_rot13;

```

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

### VEB filename example

Source: https://villagesql.com/docs/mysql-9.7/stable/managing

Example of the VEB filename.

```bash
# VEB filename
my_extension.veb
```

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

### Install and configure vsql_slow_query_log from SQL

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

SQL statements to install the extension and configure its system variables. Requires the preview tier to be enabled. Sets the enabled flag to ON and the threshold to 500 ms.

```sql
INSTALL EXTENSION vsql_slow_query_log;
SET GLOBAL vsql_slow_query_log.enabled = ON;
SET GLOBAL vsql_slow_query_log.threshold_ms = 500;

```

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

### Install vsql_statistics extension

Source: https://villagesql.com/docs/mysql-9.7/stable/extensions/vsql-statistics

Installs the vsql_statistics extension into the server. Build it from the repository against SDK 0.0.4 or later before running this command.

```sql
INSTALL EXTENSION vsql_statistics;
```

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

### Create sample database and posts table

Source: https://villagesql.com/docs/guides/mysql-ai-agents-mcp

Creates the mcp_guide database, a posts table with id, title, author, and views columns, and inserts three sample rows. Use this to give the MCP agent real data to query.

```sql
CREATE DATABASE mcp_guide;

CREATE TABLE mcp_guide.posts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(200) NOT NULL,
  author VARCHAR(100) NOT NULL,
  views INT NOT NULL DEFAULT 0
);

INSERT INTO mcp_guide.posts (title, author, views) VALUES
  ('Getting started with VillageSQL', 'Adam', 1520),
  ('Custom types in VEF', 'Priya', 890),
  ('Why we built the MCP extension', 'Tomas', 430);

```

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

### Install the vsql_ai extension

Source: https://villagesql.com/docs/guides/ai-prompts-in-mysql

One-line SQL to install the vsql_ai extension, making ai_prompt() available for use in queries.

```sql
INSTALL EXTENSION vsql_ai;
```

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

### Verify extension installation

Source: https://villagesql.com/docs/mysql-9.7/stable/create

Verifies the extension installation by querying the INFORMATION_SCHEMA.EXTENSIONS table.

```sql
SELECT * FROM INFORMATION_SCHEMA.EXTENSIONS;

```

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

### Build everything with make

Source: https://villagesql.com/docs/mysql-9.7/stable/source

Builds everything, including the server, the mysql client, the extension SDK, and the example extensions' .veb files. Adjust the -j10 parallelism based on your CPU cores, subtracting 2-4 from your total core count.

```bash
make -j10

```

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

### Install vsql_stat_ch extension

Source: https://villagesql.com/docs/mysql-9.7/stable/extensions/vsql-stat-ch

Installs the vsql_stat_ch extension via SQL command. This requires the extension to be built first and the server to be started with the preview capability flag.

```sql
INSTALL EXTENSION vsql_stat_ch;
```

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

### Query status variables after INSTALL EXTENSION

Source: https://villagesql.com/docs/mysql-9.7/stable/rust-preview-capabilities

After `INSTALL EXTENSION`, the status variables appear with the extension name as a prefix. The example shows a call to `vsql_status_var.bump()` and a `SHOW GLOBAL STATUS` query, followed by the resulting output.

```sql
SELECT vsql_status_var.bump();
-- 1
SHOW GLOBAL STATUS LIKE 'vsql_status_var%';

```

```text
Variable_name	Value
vsql_status_var.load	0.500000
vsql_status_var.requests	1

```

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

### Install VillageSQL binaries

Source: https://villagesql.com/docs/mysql-9.7/stable/server-development

Installs VillageSQL binaries system-wide to the default prefix `/usr/local/mysql/`. Requires appropriate permissions.

```bash
cmake --build build --target install

```

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

### Restrict table to GET only with vsql_rest.table_methods

Source: https://villagesql.com/docs/guides/rest-api-from-tables

Sets the global variable vsql_rest.table_methods to allow only GET on the products table. By default every table accepts every method, so set this before the listener starts. A method not granted is refused with a VSQL0001 error.

```sql
SET GLOBAL vsql_rest.table_methods = 'products:GET';

```

```json
{"message":"method not allowed: POST","details":null,"hint":null,"code":"VSQL0001"}

```

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

### Declare keyring capability with VEF_GENERATE_ENTRY_POINTS

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

Include the keyring header and declare a static KeyringCapability object at file scope. Pass it to make_extension().with() so the server populates it at load time. Without a keyring component installed, read() and write() return Status::UNAVAILABLE.

```cpp
#include <villagesql/preview/keyring.h>
#include <villagesql/vsql.h>

using KeyringCapability = vsql::preview_keyring::KeyringCapability;

static KeyringCapability g_keyring;

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .with(g_keyring))

```

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

### EXPLAIN output example

Source: https://villagesql.com/docs/guides/mysql-indexes

Example output from EXPLAIN showing the execution plan, including the key column with the index name and rows examined.

```text
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_user_id   | idx_user_id | 4       | const |   12 |   100.00 | NULL           |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
```

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

### Install and call session_sql_command

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

Commands to install the extension and call the function, followed by the expected output showing the SQL command 'select'.

```sql
INSTALL EXTENSION vsql_mysql_services_session_test;
SELECT vsql_mysql_services_session_test.session_sql_command() AS sql_command;

```

```text
+-------------+
| sql_command |
+-------------+
| select      |
+-------------+

```

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

### Version mismatch error output

Source: https://villagesql.com/docs/mysql-9.7/stable/install

Example error when the manifest version does not match the pinned VERSION. The install fails and nothing is registered.

```text
ERROR 3219 (HY000): Cannot install extension 'my_extension': manifest version
is '0.0.1' but VERSION '1.2.0' was specified

```

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

### trgm_distance example

Source: https://villagesql.com/docs/guides/trigram-similarity

Shows trgm_distance returning the complement of similarity (1 - similarity), useful for ORDER BY to get closest matches first.

```sql
SELECT vsql_trgm.trgm_distance('mysql', 'mysqk');  -- ~0.33

```

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

### Install mysql-client and configure mysql2 gem on macOS

Source: https://villagesql.com/docs/guides/rails-mysql

Installs the MySQL client library via Homebrew, configures the mysql2 gem build to use the keg-only mysql-client directory, and runs bundle install. Required because mysql-client is keg-only and not symlinked into /opt/homebrew.

```bash
brew install mysql-client
bundle config set build.mysql2 --with-mysql-dir=/opt/homebrew/opt/mysql-client
bundle install

```

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

### Example output of user and token identity

Source: https://villagesql.com/docs/guides/jwt-authentication

Example output from the previous query showing the logged-in account and token identity.

```text
+--------------+----------------+
| logged_in_as | token_identity |
+--------------+----------------+
| analyst@%    | analyst        |
+--------------+----------------+

```

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

### Create database and application user

Source: https://villagesql.com/docs/guides/go-sqlc-mysql

Creates the sqlc_guide database and a dedicated application user scoped to that database with a real password.

```sql
CREATE DATABASE sqlc_guide;
CREATE USER 'sqlc_app'@'127.0.0.1' IDENTIFIED BY 'sqlc_app_pw';
GRANT ALL PRIVILEGES ON sqlc_guide.* TO 'sqlc_app'@'127.0.0.1';

```

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

### Example output of ruby -v on macOS system Ruby

Source: https://villagesql.com/docs/guides/rails-mysql

Shows the output when only the system Ruby is installed. Ruby 2.6 cannot run Rails 8.

```bash
ruby 2.6.10p210 (2022-04-12 revision 67958) [universal.arm64e-darwin25]

```

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

### Create manifest.json for a VillageSQL extension

Source: https://villagesql.com/docs/mysql-9.7/stable/rust-api-reference

Required manifest.json file for every extension, placed alongside Cargo.toml. The name must match the INSTALL EXTENSION name and follow validation rules: start with a letter, end with a letter or digit, max 64 characters. An invalid manifest causes INSTALL EXTENSION to fail.

```json
{
  "name": "vsql_my_extension",
  "version": "0.1.0",
  "description": "Brief description of what the extension does",
  "author": "Your Name",
  "license": "GPL-2.0"
}

```

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

### Usage example for COMPLEX type

Source: https://villagesql.com/docs/mysql-9.7/stable/examples

Shows how to install the extension, create a table with a COMPLEX column, insert a complex value, and query it using the provided functions complex_real, complex_imag, and complex_abs.

```sql
INSTALL EXTENSION vsql_complex;

CREATE TABLE signals (
    id INT PRIMARY KEY,
    impedance COMPLEX
);

INSERT INTO signals VALUES (1, '(50.0,10.0)');

SELECT
    complex_real(impedance) as resistance,
    complex_imag(impedance) as reactance,
    complex_abs(impedance) as magnitude
FROM signals;

```

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

### Run the full program

Source: https://villagesql.com/docs/guides/go-sqlc-mysql

Runs the Go program end to end against a live server.

```bash
go run .

```

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

### Get embedding dimensions with ai_embedding

Source: https://villagesql.com/docs/mysql-9.7/stable/extensions/vsql-ai

Generates an embedding vector for the text 'Machine learning' using a local model and returns its dimension count. The example shows 768 dimensions for nomic-embed-text.

```sql
SELECT JSON_LENGTH(ai_embedding('local', 'nomic-embed-text', '', 'Machine learning')) AS dimensions;
```

```text
+------------+
| dimensions |
+------------+
|        768 |
+------------+
```

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

### Correct extension.cc entry point

Source: https://villagesql.com/docs/mysql-9.7/stable/managing

Example of VEF_GENERATE_ENTRY_POINTS macro usage.

```cpp
// extension.cc
VEF_GENERATE_ENTRY_POINTS(
  make_extension()
    .func(...)
)
```

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

### Example slow query log output

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

Example log output produced by the extension for a query slower than the configured threshold. Shows the timestamp, user/host, schema, query time, lock time, rows sent, rows examined, and the original query with a SET timestamp line.

```text
# Time: 2026-06-22T22:53:44Z
# User@Host: root @   Id: 27
# Schema:   Query_time: 0.605084  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 1
SET timestamp=1782168824;
SELECT SLEEP(0.6);

```

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

### Running Totals and Moving Averages with ROWS BETWEEN

Source: https://villagesql.com/docs/guides/window-functions

Uses ROWS BETWEEN to define a frame for running totals and moving averages. The example computes a running total from the start to the current row and a 7-day moving average.

```sql
SELECT
    date,
    amount,
    -- Running total (all rows from start to current)
    SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
    -- 7-day moving average
    AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;
```

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

### Initialize Go module and fetch Testcontainers MySQL dependencies

Source: https://villagesql.com/docs/guides/testing-with-testcontainers

Run these commands to initialize a Go module and fetch the Testcontainers MySQL module and the MySQL driver. The Testcontainers Go library is pulled in as a transitive dependency of the MySQL module.

```bash
go mod init villagesql/testcontainers-guide
go get github.com/testcontainers/testcontainers-go/modules/mysql
go get github.com/go-sql-driver/mysql
```

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

### Register system variables with vsql::preview_sys_var

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

Complete example of registering system variables with the sys_var capability. It creates a capability with three variables (bool, int, str) and attaches an on_change callback to the int variable. The capability must have static storage duration.

```cpp
#include <villagesql/preview/sys_var.h>
#include <villagesql/vsql.h>

namespace sv = vsql::preview_sys_var;

static bool      g_enabled   = true;
static long long g_threshold = 1000;
static char     *g_log_file  = nullptr;

static void on_threshold_change(sv::SysVarChange c) {
  // c.var_name() identifies the variable; c.as_int() returns the new value
}

static auto SYS_VARS = sv::make_capability({
    sv::make_bool("enabled",      "Enable feature",  &g_enabled,   true),
    sv::make_int ("threshold_ms", "Threshold in ms", &g_threshold, 1000, 0, 3600000)
        .on_change<&on_threshold_change>(),
    sv::make_str ("log_file",     "Log file path",   &g_log_file,  "/tmp/myext.log")});

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .with(SYS_VARS))

```

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

### Enable vsql_auth_test.auto_create and create role

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

Enable the auto-create opt-in for the vsql_auth_test extension and prepare the role it provisions. The opt-in is gated behind vsql_auth_test.auto_create, which starts OFF. Create the role first, then connect as an account that does not exist.

```sql
INSTALL EXTENSION vsql_auth_test;
SET GLOBAL vsql_auth_test.auto_create = ON;
CREATE ROLE vsql_role_granted;
GRANT SELECT ON *.* TO vsql_role_granted;

```

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

### REPLACE INTO example

Source: https://villagesql.com/docs/guides/upsert

REPLACE INTO deletes the conflicting row and inserts a new one. It fires BEFORE DELETE and AFTER DELETE triggers, resets AUTO_INCREMENT, and triggers ON DELETE CASCADE. Unspecified columns get default values.

```sql
REPLACE INTO page_views (page, views, last_at)
VALUES ('/home', 100, NOW());

```

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

### SHOW GLOBAL STATUS query and output

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

After INSTALL EXTENSION my_ext, the variable is visible with the extension name as a prefix. The query SHOW GLOBAL STATUS LIKE 'my_ext%' returns the status variables with their current values. Concurrent increments from multiple query threads using a non-atomic ++ may occasionally be lost; this is acceptable for approximate call counters.

```sql
SHOW GLOBAL STATUS LIKE 'my_ext%';

```

```text
Variable_name       Value
my_ext.ext_hits     0
my_ext.ext_misses   0

```

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

### Complete worker example with buffered and streaming queries

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

A worker that runs one buffered query and one streaming query on each tick, logging diagnostics from both. Uses SqlQueryCapability and ThreadWorkerCapability. The worker returns a 5000ms wakeup interval on enable and processes on periodic wakeups.

```cpp
#include <villagesql/preview/sql_query.h>
#include <villagesql/preview/thread_worker.h>
#include <villagesql/vsql.h>

static vsql::preview_sql_query::SqlQueryCapability g_sql;

static vef_next_wakeup_t sql_demo_work(vef_wakeup_reason_t reason,
                                       struct vef_thread_handle_t *thread,
                                       void *arg) {
  if (reason == VEF_WAKEUP_ENABLE) return {5000, 0};
  if (reason != VEF_WAKEUP_PERIODIC) return {};

  auto session = g_sql.open(thread);
  if (!session) return {};

  // Buffered: read a small result set.
  auto rs = session.sql("SELECT id, name FROM mydb.t LIMIT 10").execute();
  if (rs.has_error()) {
    auto e = rs.error();
    // Log e.errno_, e.sqlstate, e.message somewhere extension-owned.
  } else {
    while (rs.next()) {
      long long id          = rs.column_int(0);
      std::string_view name = rs.column_str(1);
      (void)id; (void)name;
    }
  }

  // Streaming: process rows without buffering.
  auto status = session.sql("SELECT v FROM mydb.t").for_each(
      [](const auto &row) {
        long long v = row.column_int(0);
        (void)v;
      });
  for (unsigned i = 0; i < status.warning_count(); ++i) {
    auto w = status.warning(i);
    (void)w;
  }
  return {};
}

static vsql::preview_thread_worker::ThreadWorkerCapability<&sql_demo_work>
    g_worker{"sql_demo"};

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .with(g_worker)
        .with(g_sql))
```

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

### Run MySQL container with environment variables

Source: https://villagesql.com/docs/guides/mysql-on-docker

Starts a MySQL 8.4 container named 'mysql-dev' with root password, database, and user setup. The container runs in detached mode and maps port 3306.

```bash
docker run --name mysql-dev \
  -e MYSQL_ROOT_PASSWORD=rootpass \
  -e MYSQL_DATABASE=myapp \
  -e MYSQL_USER=appuser \
  -e MYSQL_PASSWORD=apppass \
  -p 3306:3306 \
  -d mysql:8.4
```

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

### Example version output for development build

Source: https://villagesql.com/docs/mysql-9.7/stable/source

Example output of SELECT VERSION() for a development build, showing the version string with the git commit hash.

```text
9.7.2-villagesql-0.0.6-dev-5a64e122090

```

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

### Start and commit a mini-transaction with storage::MtrCtx

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

Wrap all page reads and writes in a mini-transaction. Include <villagesql/preview/storage_api.h> for the InnoDB primitives. Committing the mini-transaction releases page latches and writes the redo log records that make changes durable.

```cpp
storage::MtrCtx mtr;
storage::MtrCtx::Ref mtr_ref = mtr.start();
if (mtr_ref == nullptr) { /* OOM — handle error */ return true; }
// ... page operations ...
mtr.commit();

```

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

### Complete example: session_sql_command extension

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

A simplified version of vsql_mysql_services_session_test that reads the SQL command running on the calling session by composing two services: mysql_current_thread_reader and mysql_thd_attributes. It uses VSQL_REQUIRE_SERVICE and VEF_GENERATE_ENTRY_POINTS. Both services are server-core and registered on every server.

```cpp
#include <cstddef>

#include <mysql/components/services/defs/mysql_string_defs.h>
#include <mysql/components/services/mysql_current_thread_reader.h>
#include <mysql/components/services/mysql_thd_attributes.h>
#include <villagesql/preview/mysql_services.h>
#include <villagesql/vsql.h>

using namespace vsql;

static preview_mysql_services::MysqlServices services;
VSQL_REQUIRE_SERVICE(services, mysql_current_thread_reader, thd_reader);
VSQL_REQUIRE_SERVICE(services, mysql_thd_attributes, attrs);

// session_sql_command() -> STRING: the name of the SQL command running on the
// calling session, or NULL when the THD or the attribute cannot be read.
void session_sql_command(StringResult out) {
  if (!thd_reader.valid() || !attrs.valid()) {
    out.error("MySQL session services are not available");
    return;
  }

  MYSQL_THD thd = nullptr;
  // MySQL convention: a false return means success.
  if (thd_reader->get(&thd) || thd == nullptr) {
    out.set_null();
    return;
  }

  mysql_cstring_with_length value{nullptr, 0};
  if (attrs->get(thd, "sql_command", &value) || value.str == nullptr) {
    out.set_null();
    return;
  }

  out.set(std::string_view(value.str, value.length));
}

VEF_GENERATE_ENTRY_POINTS(make_extension().with(services).func(
    make_func<&session_sql_command>("session_sql_command")
        .returns(STRING)
        .no_params()
        .build()))

```

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

### Run Prisma migration

Source: https://villagesql.com/docs/guides/mysql-with-prisma

Initializes the database schema and generates the Prisma client. Run this command to apply the schema and create the migration files.

```bash
npx prisma migrate dev --name init

```

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

### manifest.json example

Source: https://villagesql.com/docs/mysql-9.7/stable/create

Defines the extension's metadata in manifest.json. The $schema field is optional but enables IDE autocomplete and inline validation. The name must match the INSTALL EXTENSION name and follow the naming rules.

```json
{
  "$schema": "https://raw.githubusercontent.com/villagesql/villagesql-docs/main/mysql-9.7/stable/manifest.json.schema.json",
  "name": "my_awesome_extension",
  "version": "1.0.0",
  "description": "My custom VillageSQL extension that does amazing things",
  "author": "Your Name",
  "license": "GPL-2.0"
}
```

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

### Pin a specific service implementation with qualified registry name

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

Uses a qualified registry name (service.component) to request the keyring reader from component_keyring_file instead of the default. If that exact implementation is not registered, the extension fails to install.

```cpp
static preview_mysql_services::ServiceRef<SERVICE_TYPE(keyring_reader_with_status)>
    reader;
static const int reader_req =
    (services.require<SERVICE_TYPE(keyring_reader_with_status)>(
         "keyring_reader_with_status.component_keyring_file", reader),
     0);

```

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

### Declare a StatementEventCapability with VEF_STATEMENT_EVENT_POSTEXECUTE

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

Declares a statement event capability at file scope, passing the firing phase and a handler function to `.with()`. The handler runs synchronously after each query finishes; `VEF_STATEMENT_EVENT_POSTEXECUTE` is the only implemented phase, and declaring a handler for other phases causes the server to reject `INSTALL EXTENSION`.

```cpp
#include <villagesql/preview/statement_event.h>
#include <villagesql/vsql.h>

namespace se = vsql::preview_statement_event;

static void on_statement(const se::StatementEventArgs &args,
                         se::StatementEventResult &result) {
  // inspect args; optionally write an advisory message via result
}

static se::StatementEventCapability<VEF_STATEMENT_EVENT_POSTEXECUTE,
                                    &on_statement>
    g_statement_event;

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .with(g_statement_event))

```

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

### Acquisition failure error message

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

Shows the error message produced when INSTALL EXTENSION fails because a required service is not registered. The error names the service and the extension.

```text
ERROR 3219 (HY000): Failed to load VEF extension 'vsql_mysql_services_missing_test': failed to acquire MySQL service 'vsql_intentionally_missing'

```

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

### Disable preview tier with SET PERSIST

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

Set vsql_allow_preview_extensions = OFF with SET PERSIST. This fails if any extension using a preview capability is currently installed; uninstall those extensions first, then turn the setting off.

```sql
SET PERSIST vsql_allow_preview_extensions = OFF;

```

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

### Access system variables from SQL

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

SQL statements to access and modify the registered system variables after installing the extension. Variables are prefixed with the extension name.

```sql
SELECT @@global.my_ext.threshold_ms;
SET GLOBAL my_ext.threshold_ms = 500;
SET GLOBAL my_ext.log_file = '/var/log/myext.log';

```

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

### Start server with --upgrade=MINIMAL

Source: https://villagesql.com/docs/mysql-9.7/stable/version-policy

Starts the server with minimal upgrade mode, which skips non-essential upgrade steps but still runs VillageSQL schema upgrades when needed. If the schema upgrade fails, the server aborts startup.

```bash
mysqld --upgrade=MINIMAL --datadir=/path/to/data

```

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

### Complete status_var capability example

Source: https://villagesql.com/docs/mysql-9.7/stable/preview-capabilities

Builds a status variable capability with vsql::preview_status_var::make_capability(), passing a braced list of descriptors from make_int(name, value_ptr) or make_double(name, value_ptr). The template deduces the count from the braced list, so no explicit size is required. make_int requires a long long * and make_double requires a double *; these are the only two supported types.

```cpp
#include <villagesql/preview/status_var.h>
#include <villagesql/vsql.h>

namespace sv = vsql::preview_status_var;

static long long g_hits   = 0;
static long long g_misses = 0;

static auto STATUS_VARS = sv::make_capability({
    sv::make_int("ext_hits",   &g_hits),
    sv::make_int("ext_misses", &g_misses)});

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .with(STATUS_VARS))

```

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

### Get current date and time with NOW(), SYSDATE(), CURDATE(), CURTIME(), UTC_TIMESTAMP()

Source: https://villagesql.com/docs/guides/date-time-functions

Use these functions to retrieve the current date and time. NOW() is evaluated once at statement start, while SYSDATE() is evaluated at call time. CURDATE() returns the date only, CURTIME() the time only, and UTC_TIMESTAMP() returns the UTC datetime.

```sql
SELECT NOW();            -- '2024-06-15 14:30:00'  (DATETIME, session timezone)
SELECT SYSDATE();        -- same, but evaluated at call time, not statement start
SELECT CURRENT_TIMESTAMP; -- synonym for NOW()

SELECT CURDATE();        -- '2024-06-15'  (DATE only)
SELECT CURTIME();        -- '14:30:00'    (TIME only)
SELECT UTC_TIMESTAMP();  -- '2024-06-15 21:30:00'  (UTC)

```

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

### Implement data access layer with database/sql

Source: https://villagesql.com/docs/guides/testing-with-testcontainers

A store package with methods to create a user and fetch a user by email, using the standard library's database/sql. This code is not aware it is being tested and is exercised exactly as the application would call it.

```go
// store.go
package store

import (
	"database/sql"
	"fmt"
)

type User struct {
	ID    int
	Email string
	Name  string
	Age   int
}

type UserStore struct {
	db *sql.DB
}

func NewUserStore(db *sql.DB) *UserStore {
	return &UserStore{db: db}
}

func (s *UserStore) CreateUser(email, name string, age int) (int, error) {
	result, err := s.db.Exec(
		"INSERT INTO users (email, name, age) VALUES (?, ?, ?)",
		email, name, age,
	)
	if err != nil {
		return 0, fmt.Errorf("insert user: %w", err)
	}
	id, err := result.LastInsertId()
	if err != nil {
		return 0, fmt.Errorf("read generated id: %w", err)
	}
	return int(id), nil
}

func (s *UserStore) GetUserByEmail(email string) (*User, error) {
	row := s.db.QueryRow(
		"SELECT id, email, name, age FROM users WHERE email = ?",
		email,
	)
	var u User
	if err := row.Scan(&u.ID, &u.Email, &u.Name, &u.Age); err != nil {
		return nil, fmt.Errorf("query user: %w", err)
	}
	return &u, nil
}
```

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

### Install VillageSQL with Shell Script

Source: https://villagesql.com/docs/mysql-9.7/stable/quickstart

Installs VillageSQL directly on your machine using the official installation script. The script asks three questions: which codebase to install, how to install it, and which version.

```bash
curl -fsSL https://install.villagesql.com | bash
```