no such function

This error occurs when you call a function that doesn't exist in SQLite. Some functions require extensions or aren't available in all SQLite builds.

The no such function error means you're calling a function SQLite doesn't recognize.

Understanding the Error

Error: no such function: NOW

The function NOW doesn't exist (SQLite uses different datetime functions).

Common Causes

1. Using Other Database Syntax

Functions from MySQL, PostgreSQL, etc.:

SQL

-- MySQL/PostgreSQL syntax
SELECT NOW();                    -- Use: datetime('now')
SELECT CURDATE();               -- Use: date('now')
SELECT CONCAT(a, b);            -- Use: a || b
SELECT IF(cond, a, b);          -- Use: IIF(cond, a, b) or CASE
SELECT IFNULL(a, b);            -- This one works in SQLite!
SELECT NVL(a, b);               -- Use: IFNULL(a, b)
SELECT SUBSTRING(str, 1, 5);    -- Use: SUBSTR(str, 1, 5)

2. Extension Functions Not Loaded

Some functions need extensions:

SQL

SELECT SQRT(16);      -- Might need math extension
SELECT REGEXP(a, b);  -- Needs regexp extension

3. Typos in Function Name

SQL

SELECT UPER(name) FROM users;   -- Should be UPPER
SELECT LENGHT(name) FROM users; -- Should be LENGTH

4. JSON Functions (Older SQLite)

SQL

SELECT json_extract(data, '$.name');  -- Needs SQLite 3.9+

How to Fix It

Solution 1: Use SQLite Equivalents

SQL

-- Date/Time
SELECT datetime('now');              -- Current datetime
SELECT date('now');                  -- Current date
SELECT time('now');                  -- Current time
SELECT strftime('%Y-%m-%d', 'now');  -- Formatted date

-- String
SELECT UPPER(name);
SELECT LOWER(name);
SELECT LENGTH(name);
SELECT SUBSTR(name, 1, 5);
SELECT TRIM(name);
SELECT REPLACE(name, 'old', 'new');
SELECT name || ' ' || email;         -- Concatenation

-- Conditional
SELECT IIF(age >= 18, 'adult', 'minor');
SELECT CASE WHEN age >= 18 THEN 'adult' ELSE 'minor' END;

-- Null handling
SELECT IFNULL(nickname, name);
SELECT COALESCE(a, b, c);
SELECT NULLIF(a, b);

Solution 2: Load Extensions

JAVASCRIPT

const db = new Database('mydb.db');
db.loadExtension('./regexp.so');

SQL

-- In sqlite3 CLI
.load ./regexp

Solution 3: Create Custom Functions

JAVASCRIPT

// Add custom function
db.function('REGEXP', (pattern, string) => {
  return new RegExp(pattern).test(string) ? 1 : 0;
});

// Now you can use it
db.prepare('SELECT * FROM users WHERE REGEXP(?, name)').all('^A');

Solution 4: Check SQLite Version

SQL

SELECT sqlite_version();

JSON functions require 3.9+, many features improved in 3.25+, 3.35+, etc.

SQLite Core Functions

Always available:

ABS, CHANGES, CHAR, COALESCE, GLOB, HEX, IFNULL, IIF,
INSTR, LAST_INSERT_ROWID, LENGTH, LIKE, LIKELIHOOD,
LIKELY, LOWER, LTRIM, MAX, MIN, NULLIF, PRINTF, QUOTE,
RANDOM, RANDOMBLOB, REPLACE, ROUND, RTRIM, SOUNDEX,
SQLITE_VERSION, SUBSTR, TOTAL_CHANGES, TRIM, TYPEOF,
UNICODE, UNLIKELY, UPPER, ZEROBLOB

Best Practices

Check SQLite documentation for available functions
Use portable SQL if supporting multiple databases
Check SQLite version for newer functions
Create custom functions when needed
Test on target environment before deployment