@evidence-dev/sqlite - npm Package Compare versions

Comparing version 0.0.0-e22a6592 to 0.0.0-e2595b47

README.md

+## SQLite Adapter
+
+For connection info, see https://docs.evidence.dev/core-concepts/data-sources/#sqlite

CHANGELOG.md

 # @evidence-dev/sqlite
 
+## 2.0.4
+
+### Patch Changes
+
+- Updated dependencies [1da26c4e]
+  - @evidence-dev/db-commons@1.0.4
+
+## 2.0.3
+
+### Patch Changes
+
+- 2bcbf0ed: Add keywords to improve searchability for datasources
+- Updated dependencies [31381835]
+  - @evidence-dev/db-commons@1.0.3
+
+## 2.0.2
+
+### Patch Changes
+
+- 0e0a4392: Add skeleton README files for adapters
+- 38cc19db: - Remove legacy environment variables
+  - Manually close connection
+- Updated dependencies [fc7fe470]
+  - @evidence-dev/db-commons@1.0.2
+
+## 2.0.1
+
+### Patch Changes
+
+- Updated dependencies
+  - @evidence-dev/db-commons@1.0.1
+
+## 2.0.0
+
+### Major Changes
+
+- cb0fc468: This update includes major changes to the way Evidence interacts with data.
+  Instead of running queries against the production database, and including it
+  with the project as pre-rendered, static JSON data; those queries are now stored as .parquet files.
+
+  .parquet enables the use of DuckDB on the client, allowing for much greater levels of interactivity
+  on pages, and interoperability between different data sources (e.g. joins across postgres & mysql).
+
+### Patch Changes
+
+- bf4a112a: Update package.json to use new datasource field
+- c4822852: Support for streaming results
+- 781d2677: exhaust testconnection streams, improve type inference, add trino/databricks adapters
+- 20127231: Bump all versions so version pinning works
+- cff22ece: Fix SQLite database wrapper
+- 29c149d6: added stricter types to db adapters
+- Updated dependencies [bf4a112a]
+- Updated dependencies [cd57ba69]
+- Updated dependencies [c4822852]
+- Updated dependencies [781d2677]
+- Updated dependencies [20127231]
+- Updated dependencies [29c149d6]
+  - @evidence-dev/db-commons@0.2.1
+
+## 2.0.0-usql.7
+
+### Patch Changes
+
+- 781d2677: exhaust testconnection streams, improve type inference, add trino/databricks adapters
+- Updated dependencies [781d2677]
+  - @evidence-dev/db-commons@0.2.1-usql.5
+
+## 2.0.0-usql.6
+
+### Patch Changes
+
+- Update package.json to use new datasource field
+- Updated dependencies
+  - @evidence-dev/db-commons@0.2.1-usql.4
+
+## 2.0.0-usql.5
+
+### Patch Changes
+
+- Updated dependencies [cd57ba69]
+  - @evidence-dev/db-commons@0.2.1-usql.3
+
+## 2.0.0-usql.4
+
+### Patch Changes
+
+- Support for streaming results
+- Updated dependencies
+  - @evidence-dev/db-commons@0.2.1-usql.2
+
+## 2.0.0-usql.3
+
+### Patch Changes
+
+- 20127231: Bump all versions so version pinning works
+- Updated dependencies [20127231]
+  - @evidence-dev/db-commons@0.2.1-usql.1
+
+## 2.0.0-usql.2
+
+### Patch Changes
+
+- 29c149d6: added stricter types to db adapters
+- Updated dependencies [29c149d6]
+  - @evidence-dev/db-commons@0.2.1-usql.0
+
+## 2.0.0-usql.1
+
+### Patch Changes
+
+- cff22ece: Fix SQLite database wrapper
+
+## 2.0.0-usql.0
+
+### Major Changes
+
+- cb0fc468: This update includes major changes to the way Evidence interacts with data.
+  Instead of running queries against the production database, and including it
+  with the project as pre-rendered, static JSON data; those queries are now stored as .parquet files.
+
+  .parquet enables the use of DuckDB on the client, allowing for much greater levels of interactivity
+  on pages, and interoperability between different data sources (e.g. joins across postgres & mysql).
+
 ## 1.2.1
@@ -4,0 +127,0 @@

index.cjs

 const sqlite3 = require('sqlite3');
 const { open } = require('sqlite');
 const path = require('path');
-const { processQueryResults, getEnv } = require('@evidence-dev/db-commons');
+const stream = require('stream');
+const {
+	inferColumnTypes,
+	asyncIterableToBatchedAsyncGenerator,
+	cleanQuery,
+	exhaustStream
+} = require('@evidence-dev/db-commons');
 
-const envMap = {
-	filename: [
-		{ key: 'EVIDENCE_SQLITE_FILENAME', deprecated: false },
-		{ key: 'SQLITE_FILENAME', deprecated: false },
-		{ key: 'FILENAME', deprecated: true },
-		{ key: 'filename', deprecated: true }
-	]
-};
+// https://gist.github.com/rmela/a3bed669ad6194fb2d9670789541b0c7
+class DBStream extends stream.Readable {
+	constructor() {
+		super({ objectMode: true });
+	}
 
-const runQuery = async (queryString, database) => {
-	const filename = database ? database.filename : getEnv(envMap, 'filename');
-	const filepath = filename !== ':memory:' ? '../../' + filename : filename;
+	static async create({ opts, sql }) {
+		const db = await open(opts);
+		const dbstream = new DBStream();
+		dbstream.stmt = await db.prepare(sql);
+		dbstream.on('end', () => dbstream.stmt.finalize(() => db.close()));
+		return dbstream;
+	}
+
+	_read() {
+		let strm = this;
+		this.stmt
+			.get()
+			.then((result) => strm.push(result ?? null))
+			.catch((err) => strm.emit('error', err));
+	}
+}
+
+/** @type {import('@evidence-dev/db-commons').RunQuery<SQLiteOptions>} */
+const runQuery = async (queryString, database, batchSize = 100000) => {
+	const filename = database.filename;
 	try {
-		const db = await open({
-			filename: filepath,
+		const opts = {
+			filename: filename,
 			driver: sqlite3.Database,
 			mode: sqlite3.OPEN_READONLY
+		};
+
+		const db = await open(opts);
+		const cleaned_query = cleanQuery(queryString);
+		const count_results = await db.all(`WITH root as (${cleaned_query}) SELECT COUNT(*) FROM root`);
+		const expected_row_count = count_results[0]['COUNT(*)'];
+
+		const stream = await DBStream.create({ opts, sql: queryString });
+
+		const results = await asyncIterableToBatchedAsyncGenerator(stream, batchSize, {
+			mapResultsToEvidenceColumnTypes: inferColumnTypes,
+			closeConnection: () => db.close()
 		});
-		const result = await db.all(queryString);
-		return processQueryResults(result);
+		results.expectedRowCount = expected_row_count;
+
+		return results;
 	} catch (err) {
@@ -40,1 +73,40 @@ if (err.message) {
 module.exports = runQuery;
+
+/**
+ * @typedef {Object} SQLiteOptions
+ * @property {string} filename
+ */
+
+/** @type {import('@evidence-dev/db-commons').GetRunner<SQLiteOptions>} */
+module.exports.getRunner = async (opts, directory) => {
+	return async (queryContent, queryPath, batchSize) => {
+		// Filter out non-sql files
+		if (!queryPath.endsWith('.sql')) return null;
+		return runQuery(
+			queryContent,
+			{ ...opts, filename: path.join(directory, opts.filename) },
+			batchSize
+		);
+	};
+};
+
+/** @type {import("@evidence-dev/db-commons").ConnectionTester<DuckDBOptions>} */
+module.exports.testConnection = async (opts, directory) => {
+	const r = await runQuery('SELECT 1;', { ...opts, filename: path.join(directory, opts.filename) })
+		.then(exhaustStream)
+		.then(() => true)
+		.catch((e) => ({ reason: e.message ?? 'File not found' }));
+	return r;
+};
+
+module.exports.options = {
+	filename: {
+		title: 'Filename',
+		type: 'string',
+		secret: false,
+		description:
+			'SQLite filename. This is relative to your source directory, not your project directory.',
+		default: 'needful_things.sqlite',
+		required: true
+	}
+};

package.json

 {
   "name": "@evidence-dev/sqlite",
-  "version": "0.0.0-e22a6592",
+  "version": "0.0.0-e2595b47",
   "description": "SQLite driver for Evidence projects",
@@ -11,3 +11,3 @@ "main": "index.cjs",
     "sqlite3": "5.1.6",
-    "@evidence-dev/db-commons": "^0.0.0-e22a6592"
+    "@evidence-dev/db-commons": "0.0.0-e2595b47"
   },
@@ -18,2 +18,13 @@ "devDependencies": {
   "type": "module",
+  "evidence": {
+    "datasources": [
+      "sqlite"
+    ],
+    "icon": "Sqlite"
+  },
+  "keywords": [
+    "evidence",
+    "evidence-datasource",
+    "sqlite"
+  ],
   "scripts": {
@@ -20,0 +31,0 @@ "test": "uvu test test.js"

test/test.js

 import { test } from 'uvu';
 import * as assert from 'uvu/assert';
 import runQuery from '../index.cjs';
-import { TypeFidelity } from '@evidence-dev/db-commons';
+import { batchedAsyncGeneratorToArray, TypeFidelity } from '@evidence-dev/db-commons';
 import 'dotenv/config';
 
-let results;
-
 test('query runs', async () => {
 	try {
-		results = await runQuery(
-			"select 100 as number_col, DATE('now') as date_col, current_timestamp as timestamp_col, 'Evidence' as string_col, false as bool_col"
+		const { rows: row_generator, columnTypes } = await runQuery(
+			"select null as number_col, null as date_col, null as timestamp_col, null as string_col, null as bool_col union all select 100 as number_col, DATE('now') as date_col, current_timestamp as timestamp_col, 'Evidence' as string_col, false as bool_col",
+			{ filename: ':memory:' }
 		);
-		assert.instance(results.rows, Array);
-		assert.instance(results.columnTypes, Array);
-		assert.type(results.rows[0], 'object');
-		assert.equal(results.rows[0].number_col, 100);
+		const rows = await batchedAsyncGeneratorToArray(row_generator);
+		assert.instance(rows, Array);
+		assert.instance(columnTypes, Array);
+		assert.type(rows[1], 'object');
+		assert.equal(rows[1].number_col, 100);
 
-		let actualColumnTypes = results.columnTypes.map((columnType) => columnType.evidenceType);
-		let actualColumnNames = results.columnTypes.map((columnType) => columnType.name);
-		let actualTypePrecisions = results.columnTypes.map((columnType) => columnType.typeFidelity);
+		let actualColumnTypes = columnTypes.map((columnType) => columnType.evidenceType);
+		let actualColumnNames = columnTypes.map((columnType) => columnType.name);
+		let actualTypePrecisions = columnTypes.map((columnType) => columnType.typeFidelity);
 
@@ -47,2 +47,24 @@ let expectedColumnTypes = ['number', 'date', 'date', 'string', 'number'];
 
+test('query batches results properly', async () => {
+	try {
+		const { rows, expectedRowCount } = await runQuery(
+			'select 1 union all select 2 union all select 3 union all select 4 union all select 5',
+			{ filename: ':memory:' },
+			2
+		);
+
+		const arr = [];
+		for await (const batch of rows()) {
+			arr.push(batch);
+		}
+		for (const batch of arr.slice(0, -1)) {
+			assert.equal(batch.length, 2);
+		}
+		assert.equal(arr[arr.length - 1].length, 1);
+		assert.equal(expectedRowCount, 5);
+	} catch (e) {
+		throw Error(e);
+	}
+});
+
 test.run();

Fixed alerts

No README

Quality

Package does not have a README. This may indicate a failed publish or a low quality package.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

12807

92.79%

Number of package files

6

20%

Lines of code

160

107.79%

Number of medium quality alerts

0

-100%

Number of lines in readme file

4

Infinity%

Dependency changes

• + Added

  @evidence-dev/db-commons@0.0.0-e2595b47
  (transitive)

• + Added

  fs-extra@11.2.0
  (transitive)

• - Removed

  @evidence-dev/db-commons@0.0.0-ffa649e3
  (transitive)

• - Removed

  fs-extra@10.0.0
  (transitive)

• Updated

  @evidence-dev/db-commons@0.0.0-e2595b47