{"id":46446,"library":"sqlite-cache-for-google-sheets","title":"sqlite-cache-for-google-sheets","description":"Opinionated SQLite cache layer for Google Sheets data, designed for local development workflow where schema changes frequently. Version 9.0.0 is stable, actively maintained on GitHub. Ships TypeScript types. Provides a one-time load from Google Sheets into a local SQLite database, then allows querying via better-sqlite3. Unlike direct API queries, it avoids runtime dependency on Google Sheets and enables fast iteration. Requires API key, doc ID, and sheet ID. Uses a configurable map function to transform rows. Returns a better-sqlite3 database object for querying.","status":"active","version":"9.0.0","language":"javascript","source_language":"en","source_url":"https://github.com/joropeza/sqlite-cache-for-google-sheets","tags":["javascript","typescript"],"install":[{"cmd":"npm install sqlite-cache-for-google-sheets","lang":"bash","label":"npm"},{"cmd":"yarn add sqlite-cache-for-google-sheets","lang":"bash","label":"yarn"},{"cmd":"pnpm add sqlite-cache-for-google-sheets","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"Required for SQLite database creation and querying.","package":"better-sqlite3","optional":false},{"reason":"Used to fetch data from Google Sheets.","package":"googleapis","optional":false}],"imports":[{"note":"Named export, not default.","wrong":"import createDatabase from 'sqlite-cache-for-google-sheets'","symbol":"createDatabase","correct":"import { createDatabase } from 'sqlite-cache-for-google-sheets'"},{"note":"TypeScript type for config object.","symbol":"DatabaseConfig","correct":"import type { DatabaseConfig } from 'sqlite-cache-for-google-sheets'"},{"note":"Type-only import; use 'import type'.","wrong":"import { SheetRow } from 'sqlite-cache-for-google-sheets'","symbol":"SheetRow","correct":"import type { SheetRow } from 'sqlite-cache-for-google-sheets'"},{"note":"CJS require destructure named export, or use default? Actually, there is no default export, so only destructure works.","wrong":"const sqliteCache = require('sqlite-cache-for-google-sheets')","symbol":"all exports","correct":"const { createDatabase } = require('sqlite-cache-for-google-sheets')"}],"quickstart":{"code":"import { createDatabase } from 'sqlite-cache-for-google-sheets';\n\nconst mapFunction = (row) => ({\n  name: row.get('Name'),\n  tags: row.get('Tags'),\n});\n\nconst config = {\n  apiKey: process.env.SHEETS_API_KEY || '',\n  docId: process.env.DOC_ID || '',\n  sheetId: process.env.SHEET_ID || '',\n  mapFunction,\n  primaryKey: 'date',\n  databaseFilename: './cache.db',\n  databaseTableName: 'items',\n};\n\n(async () => {\n  const db = await createDatabase(config);\n  const rows = db.prepare('SELECT * FROM items').all();\n  console.log(rows);\n})();","lang":"typescript","description":"Shows loading data from Google Sheets into a local SQLite database and querying it."},"warnings":[{"fix":"Capture the return value and use it directly for queries.","message":"createDatabase() now returns a better-sqlite3 database object instead of a promise that resolves to void.","severity":"breaking","affected_versions":">=9.0.0"},{"fix":"Ensure the directory exists and is writable.","message":"Config option 'databaseFilename' must be a writable path; defaults to './cache.db'.","severity":"breaking","affected_versions":">=9.0.0"},{"fix":"Use row.get('columnName') instead of row.columnName.","message":"The mapFunction receives a SheetRow object that has a get() method, not a plain object.","severity":"gotcha","affected_versions":">=9.0.0"},{"fix":"Cast values in SQL queries (e.g., CAST(value AS INTEGER)).","message":"Database table is created with all columns as TEXT; no type coercion.","severity":"gotcha","affected_versions":">=9.0.0"}],"env_vars":null,"search_vec":"'9.0.0':25 'activ':28 'allow':50 'api':58,72 'avoid':61 'better':54,90 'better-sqlite3':53,89 'cach':3,9 'chang':22 'configur':81 'data':14 'databas':48,92 'depend':63 'design':15 'develop':18 'direct':57 'doc':74 'enabl':68 'fast':69 'frequent':23 'function':83 'github':31 'googl':5,12,42,65 'id':75,78 'iter':70 'javascript':96 'key':73 'layer':10 'load':40 'local':17,46 'maintain':29 'map':82 'object':93 'one':38 'one-tim':37 'opinion':7 'provid':35 'queri':51,59,95 'requir':71 'return':87 'row':86 'runtim':62 'schema':21 'sheet':6,13,43,66,77 'ship':32 'sqlite':2,8,47 'sqlite-cache-for-google-sheet':1 'sqlite3':55,91 'stabl':27 'time':39 'transform':85 'type':34 'typescript':33,97 'unlik':56 'use':79 'version':24 'via':52 'workflow':19","created_at":"2026-06-07T12:59:58.317708+00:00","updated_at":"2026-06-07T12:59:58.317708+00:00","problems":[{"fix":"import { createDatabase } from 'sqlite-cache-for-google-sheets'","cause":"Default import used instead of named import.","error":"TypeError: createDatabase is not a function"},{"fix":"npm install better-sqlite3","cause":"Missing peer dependency.","error":"Error: Cannot find module 'better-sqlite3'"},{"fix":"Set SHEETS_API_KEY in environment or config.apiKey.","cause":"Missing environment variable or config property.","error":"Error: SHEETS_API_KEY is required"}],"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/joropeza/sqlite-cache-for-google-sheets#readme","github":"https://github.com/joropeza/sqlite-cache-for-google-sheets","docs":null,"changelog":null,"pypi":null,"npm":"sqlite-cache-for-google-sheets","openapi_spec":null,"status_page":null,"smithery":null,"categories":["database","devops"],"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}}