{"id":46292,"library":"ruoyi-eggjs-sqlite","title":"ruoyi-eggjs-sqlite","description":"An Egg.js plugin for SQLite database operations, built on top of better-sqlite3. Version 1.1.9 supports Egg.js 2.x and 3.x, with multi-instance configuration, optional snake_case-to-camelCase field conversion (since v1.1.6), built-in transaction support with automatic rollback, SQL execution time logging in development, and error messages including the executed SQL. It provides a simple API: select, selects, insert, update, del, run. The plugin uses synchronous better-sqlite3 API wrapped in promises for Egg.js async context. Key differentiators include automatic camelCase conversion (opt-in) and multi-database support via clients config.","status":"active","version":"1.1.9","language":"javascript","source_language":"en","source_url":"https://github.com/undsky/ruoyi-eggjs-sqlite","tags":["javascript","egg","eggPlugin","egg-plugin","sqlite","sqlite3"],"install":[{"cmd":"npm install ruoyi-eggjs-sqlite","lang":"bash","label":"npm"},{"cmd":"yarn add ruoyi-eggjs-sqlite","lang":"bash","label":"yarn"},{"cmd":"pnpm add ruoyi-eggjs-sqlite","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"Core SQLite3 driver used for all database operations","package":"better-sqlite3","optional":false}],"imports":[{"note":"This is an Egg.js plugin; it must be enabled in plugin.js and accessed via app.sqlite (single instance) or app.sqlite.get(name) (multi-instance). Direct require will not work.","wrong":"const sqlite = require('ruoyi-eggjs-sqlite'); // Wrong: plugin is loaded via Egg.js plugin system, not direct require","symbol":"sqlite","correct":"// In config/plugin.js: exports.sqlite = { enable: true, package: 'ruoyi-eggjs-sqlite' };\n// In controller/service: const { app } = this; app.sqlite.select(...)"},{"note":"Methods return Promises (async), even though better-sqlite3 is synchronous under the hood.","wrong":"app.sqlite.select('SELECT 1').then(...) // No need for .then() because methods return promises; use await","symbol":"app.sqlite","correct":"const { app } = this; await app.sqlite.select('SELECT 1');"},{"note":"For multi-instance setups, retrieve instances via app.sqlite.get(name). The instance has the same API (select, selects, insert, etc.).","wrong":"app.sqlite.db1.select(...) // Wrong: use .get() to retrieve named instances","symbol":"app.sqlite.get","correct":"const db1 = app.sqlite.get('db1'); await db1.select('SELECT * FROM users');"}],"quickstart":{"code":"// config/plugin.js\nexports.sqlite = {\n  enable: true,\n  package: 'ruoyi-eggjs-sqlite',\n};\n\n// config/config.default.js\nconst path = require('path');\nexports.sqlite = {\n  client: {\n    path: path.join(__dirname, '../database.db'), // or ':memory:' for in-memory\n    options: {},\n  },\n};\n\n// app/controller/home.js\nconst Controller = require('egg').Controller;\nclass HomeController extends Controller {\n  async index() {\n    const { app, ctx } = this;\n    // Create table\n    await app.sqlite.run('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)');\n    // Insert\n    const rowid = await app.sqlite.insert(\"INSERT INTO users (name, age) VALUES ('Alice', 30)\");\n    // Select single\n    const user = await app.sqlite.select(`SELECT * FROM users WHERE id = ${rowid}`);\n    // Select all\n    const users = await app.sqlite.selects('SELECT * FROM users');\n    ctx.body = { user, users };\n  }\n}\nmodule.exports = HomeController;","lang":"javascript","description":"Demonstrates plugin setup in config/plugin.js and config/config.default.js, then uses app.sqlite methods: run, insert, select, selects in a controller."},"warnings":[{"fix":"Use better-sqlite3 prepared statements via app.sqlite.run() with placeholders? Actually the plugin's API does not expose prepared statement binding. Consider using raw better-sqlite3 via app.sqlite._db for parameterized queries: app.sqlite._db.prepare('SELECT * FROM users WHERE id = ?').get(userInput). Alternatively, escape inputs or use a query builder.","message":"SQL injection risk: methods like select() and insert() do not use parameterized queries; passing unsanitized user input directly is dangerous.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"For heavy queries, consider offloading to a worker or using a truly async SQLite library like sql.js (WebAssembly) or better-sqlite3's synchronous nature is actually a performance benefit for many use cases, but be aware it can block. No direct fix needed, just be mindful.","message":"Methods are asynchronous (Promise-based) but the underlying better-sqlite3 API is synchronous. This means SQL runs synchronously in the current microtask, which can block the event loop for large queries.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"Upgrade to v1.1.6 or later and set camelCase: true in config if you want automatic conversion.","message":"camelCase conversion was introduced in v1.1.6; prior versions return snake_case field names only.","severity":"deprecated","affected_versions":"<1.1.6"},{"fix":"Ensure you use exactly one of 'client' (single) or 'clients' (multi) in config. Do not set both unless you intend to have a default client AND named clients (not documented).","message":"Multi-instance config uses 'clients' (plural) object with named entries, while single instance uses 'client' (singular). Misconfiguring can lead to 'Cannot read property of undefined' or using the default client incorrectly.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"If you need a shared in-memory database across instances, use a single client with path ':memory:'.","message":"Memory database with path ':memory:' works, but different 'clients' with ':memory:' create separate in-memory databases. They do not share data.","severity":"gotcha","affected_versions":">=1.0.0"}],"env_vars":null,"search_vec":"'1.1.9':20 '2':23 '3':26 'api':68,82 'async':88 'automat':49,93 'better':17,80 'better-sqlite3':16,79 'built':12,44 'built-in':43 'camelcas':38,94 'case':36 'case-to-camelcas':35 'client':105 'config':106 'configur':32 'context':89 'convers':40,95 'databas':10,102 'del':73 'develop':56 'differenti':91 'egg':108,111 'egg-plugin':110 'egg.js':6,22,87 'eggj':3 'eggplugin':109 'error':58 'execut':52,62 'field':39 'includ':60,92 'insert':71 'instanc':31 'javascript':107 'key':90 'log':54 'messag':59 'multi':30,101 'multi-databas':100 'multi-inst':29 'oper':11 'opt':97 'opt-in':96 'option':33 'plugin':7,76,112 'promis':85 'provid':65 'rollback':50 'run':74 'ruoyi':2 'ruoyi-eggjs-sqlit':1 'select':69,70 'simpl':67 'sinc':41 'snake':34 'sql':51,63 'sqlite':4,9,113 'sqlite3':18,81,114 'support':21,47,103 'synchron':78 'time':53 'top':14 'transact':46 'updat':72 'use':77 'v1.1.6':42 'version':19 'via':104 'wrap':83 'x':24,27","created_at":"2026-06-07T12:59:12.365205+00:00","updated_at":"2026-06-07T12:59:12.365205+00:00","problems":[{"fix":"Ensure plugin is enabled in config/plugin.js. If using multi-instance, call app.sqlite.get('dbName').select(...). For single instance, ensure config uses 'client' not 'clients'.","cause":"Plugin may not be enabled, or using wrong instance (e.g., app.sqlite.get('name') for multi-instance).","error":"TypeError: app.sqlite.select is not a function"},{"fix":"Install build tools: `npm install -g node-gyp`. On Windows, install windows-build-tools. Alternatively, use `npm install --build-from-source` or try a different SQLite plugin.","cause":"better-sqlite3 is a dependency of ruoyi-eggjs-sqlite but may fail to install on some systems (requires native compilation).","error":"Error: Cannot find module 'better-sqlite3'"},{"fix":"Run `app.sqlite.run('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)')` before querying.","cause":"Table does not exist; need to create it first.","error":"Error: SQLITE_ERROR: no such table: users"},{"fix":"Check if the query returned undefined (single row select with no match). Handle null/undefined before conversion, or disable camelCase.","cause":"camelCase: true but result is null/undefined; conversion code expects an object.","error":"Error: Cannot convert undefined or null to object"}],"ecosystem":"npm","meta_description":null,"install_score":null,"quickstart_score":null,"quickstart_tag":null,"pypi_latest":null,"cli_name":null,"cli_version":null,"type":"library","homepage":"https://github.com/undsky/ruoyi-eggjs-sqlite#readme","github":"https://github.com/undsky/ruoyi-eggjs-sqlite","docs":null,"changelog":null,"pypi":null,"npm":"ruoyi-eggjs-sqlite","openapi_spec":null,"status_page":null,"smithery":null,"categories":["database"],"base_url":null,"auth_type":null,"provenance":{"verified_status":null,"verified_at":null,"last_verified":"2026-06-07","next_check":"2026-09-05","install_tag":null}}