SQLite-JS provides several ways to extend SQLite functionality with JavaScript:

Scalar functions process one row at a time and return a single value. They are useful for data transformation, calculations, text manipulation, etc.

SELECT js_create_scalar('function_name', 'function_code');

• function_name: The name of your custom function (see Function Naming Rules)
• function_code: JavaScript code that defines your function. Must be in the form function(args) { /* your code here */ }

-- Create a custom function to calculate age from birth date
SELECT js_create_scalar('age', '(function(args) {
  const birthDate = new Date(args[0]);
  const today = new Date();
  let age = today.getFullYear() - birthDate.getFullYear();
  const m = today.getMonth() - birthDate.getMonth();
  if (m < 0 || (m === 0 && today.getDate() < birthDate.getDate())) {
    age--;
  }
  return age;
})');

-- Use the function
SELECT name, age(birth_date) FROM people;

Aggregate functions process multiple rows and compute a single result. Examples include SUM, AVG, and COUNT in standard SQL.

SELECT js_create_aggregate('function_name', 'init_code', 'step_code', 'final_code');

• function_name: The name of your custom aggregate function (see Function Naming Rules)
• init_code: JavaScript code that initializes variables for the aggregation
• step_code: JavaScript code that processes each row. Must be in the form function(args) { /* your code here */ }
• final_code: JavaScript code that computes the final result. Must be in the form function() { /* your code here */ }

-- Create a median function
SELECT js_create_aggregate('median', 
  -- Init code: initialize an array to store values
  'values = [];',
  
  -- Step code: collect values from each row
  '(function(args) {
    values.push(args[0]);
  })',
  
  -- Final code: calculate the median
  '(function() {
    values.sort((a, b) => a - b);
    const mid = Math.floor(values.length / 2);
    if (values.length % 2 === 0) {
      return (values[mid-1] + values[mid]) / 2;
    } else {
      return values[mid];
    }
  })'
);

-- Use the function
SELECT median(salary) FROM employees;

Window functions, like aggregate functions, operate on a set of rows. However, they can access all rows in the current window without collapsing them into a single output row.

SELECT js_create_window('function_name', 'init_code', 'step_code', 'final_code', 'value_code', 'inverse_code');

• function_name: The name of your custom window function (see Function Naming Rules)
• init_code: JavaScript code that initializes variables
• step_code: JavaScript code that processes each row. Must be in the form function(args) { /* your code here */ }
• final_code: JavaScript code that computes the final result. Must be in the form function() { /* your code here */ }
• value_code: JavaScript code that returns the current value. Must be in the form function() { /* your code here */ }
• inverse_code: JavaScript code that removes a row from the current window. Must be in the form function(args) { /* your code here */ }

-- Create a moving average window function
SELECT js_create_window('moving_avg',
  -- Init code
  'sum = 0; count = 0;',
  
  -- Step code: process each row
  '(function(args) {
    sum += args[0];
    count++;
  })',
  
  -- Final code: not needed for this example
  '(function() { })',
  
  -- Value code: return current average
  '(function() {
    return count > 0 ? sum / count : null;
  })',
  
  -- Inverse code: remove a value from the window
  '(function(args) {
    sum -= args[0];
    count--;
  })'
);

-- Use the function
SELECT id, value, moving_avg(value) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 
FROM measurements;

Collation sequences determine how text values are compared and sorted in SQLite. Custom collations enable advanced sorting capabilities like natural sorting, locale-specific sorting, etc.

SELECT js_create_collation('collation_name', 'collation_function');

• collation_name: The name of your custom collation (see Function Naming Rules)
• collation_function: JavaScript code that compares two strings. Must return a negative number if the first string is less than the second, zero if they are equal, or a positive number if the first string is greater than the second.

-- Create a case-insensitive natural sort collation
SELECT js_create_collation('natural_nocase', '(function(a, b) {
  // Extract numbers for natural comparison
  const splitA = a.toLowerCase().split(/(\d+)/);
  const splitB = b.toLowerCase().split(/(\d+)/);
  
  for (let i = 0; i < Math.min(splitA.length, splitB.length); i++) {
    if (splitA[i] !== splitB[i]) {
      if (!isNaN(splitA[i]) && !isNaN(splitB[i])) {
        return parseInt(splitA[i]) - parseInt(splitB[i]);
      }
      return splitA[i].localeCompare(splitB[i]);
    }
  }
  return splitA.length - splitB.length;
})');

-- Use the collation
SELECT * FROM files ORDER BY name COLLATE natural_nocase;

When used with sqlite-sync, user-defined functions created via sqlite-js are automatically replicated across the SQLite Cloud cluster, ensuring that all connected peers share the same logic and behavior — even offline. To enable automatic persistence and sync the special js_init_table function must be executed.

SELECT js_init_table();         -- Create table if needed (no loading)
SELECT js_init_table(1);        -- Create table and load all stored functions

The extension also provides a way to directly evaluate JavaScript code within SQLite queries.

SELECT js_eval('javascript_code');

• javascript_code: Any valid JavaScript code to evaluate

-- Perform a calculation
SELECT js_eval('Math.PI * Math.pow(5, 2)');

-- Format a date
SELECT js_eval('new Date(1629381600000).toLocaleDateString()');

Returns the extension version string, or the internal QuickJS engine version when called with an argument.

SELECT js_version();      -- Returns the SQLite-JS version (e.g. '1.3.0')
SELECT js_version(1);     -- Returns the QuickJS engine version

Load file contents into SQLite — as text or as a blob.

SELECT js_load_text('/path/to/file.txt');   -- Returns file contents as text
SELECT js_load_blob('/path/to/file.bin');   -- Returns file contents as a blob

Configures the maximum stack size (in bytes) for the JavaScript engine.

SELECT js_set_max_stack_size(1048576);  -- Set max stack size to 1 MB

Due to a constraint in SQLite, it is not possible to update or redefine a user-defined function using the same database connection that was used to initially register it. To modify an existing JavaScript function, the update must be performed through a separate database connection.

Function names must comply with SQLite identifier rules and must be unique within the database and its schema.

These must follow typical SQL naming conventions:

• Must begin with a letter (A-Z or a-z) or an underscore _
• May contain letters, digits (0-9), and underscores _
• Are case-insensitive
• Cannot match a reserved keyword unless quoted

Examples:

• Valid: identifier1, _temp, user_name
• Invalid: 123abc, select, identifier-name

SQLite supports delimited identifiers, which allow almost any character, as long as the identifier is properly quoted.

You can use:

• Double quotes: "identifier name"
• Square brackets (Microsoft-style): [identifier name]
• Backticks (MySQL-style): `identifier name`

These quoting styles are interchangeable in SQLite. Inside a quoted identifier, you can include:

• Spaces: "my column"
• Special characters: "name@domain", "price€", "weird!name"
• Reserved SQL keywords: "select", "group"

Quoted identifiers are case-sensitive.