createCallbackFunction
Create a SQL function that can be called from queries to trigger a JavaScript callback.
Usage
Access or destructure createCallbackFunction
from the SQLocal
client.
import { SQLocal } from 'sqlocal';
export const { createCallbackFunction } = new SQLocal('database.sqlite3');
NOTE
If you are using the Kysely Query Builder or Drizzle ORM for type-safe queries, you will initialize the client with a child class of SQLocal
. See the corresponding setup page. Usage is the same otherwise.
This method takes a string to name a custom SQL function as its first argument and a callback function as its second argument which the SQL function will call. After running createCallbackFunction
, the function that you defined can be called from subsequent SQL queries. Arguments passed to the function in the SQL query will be passed to the JavaScript callback.
A good use-case for this is making SQL triggers that notify your application when certain mutations are made in the database. For example, let's create a logInsert
callback function that takes a table name and a record name to log a message.
await createCallbackFunction('logInsert', (tableName, recordName) => {
console.log(`New ${tableName} record created with name: ${recordName}`);
});
Then, we can create a temporary trigger that calls logInsert
whenever we insert a row into our groceries
table.
await sql`
CREATE TEMP TRIGGER logGroceriesInsert AFTER INSERT ON groceries
BEGIN
SELECT logInsert('groceries', new.name);
END
`;
Now, a message will be automatically logged whenever a query on the same connection inserts into the groceries
table.
await sql`INSERT INTO groceries (name) VALUES ('bread')`;
New groceries record created with name: bread
NOTE
Each function that you create will be connection-specific. If you create more than one connection using additional SQLocal
instances but want to use the same function in queries sent over the other connections as well, you will need to create the function on each instance.