@evidence-dev/sqlite - npm Package Compare versions

Comparing version 0.0.0-a9553e25 to 0.0.0-a968da82

LICENSE.md

+MIT License
+
+Copyright \(c\) 2023 Evidence
+
+Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files \(the "Software"\), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
+
+The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
+
+THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

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
+
+### Patch Changes
+
+- a1fa819e: bump vulnerable deps
+
+## 1.2.0
+
+### Minor Changes
+
+- 4c04edd0: Changed expected environment variables to reduce ambiguity
+
+### Patch Changes
+
+- Updated dependencies [4c04edd0]
+  - @evidence-dev/db-commons@0.2.0
+
 ## 1.1.4
@@ -4,0 +144,0 @@

index.cjs

 const sqlite3 = require('sqlite3');
 const { open } = require('sqlite');
-const path = require('path')
-const { processQueryResults } = require('@evidence-dev/db-commons')
+const path = require('path');
+const stream = require('stream');
+const {
+	inferColumnTypes,
+	asyncIterableToBatchedAsyncGenerator,
+	cleanQuery,
+	exhaustStream
+} = require('@evidence-dev/db-commons');
 
+// 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 : process.env["SQLITE_FILENAME"] || process.env["filename"] || process.env["FILENAME"]
-    const filepath = filename !== ":memory:" ? "../../" + filename : filename
-    try {
-        const db = await open({
-            filename: filepath,
-            driver: sqlite3.Database,
-            mode: sqlite3.OPEN_READONLY,
-        })
-        const result = await db.all(queryString);
-        return processQueryResults(result);
-    } catch(err) {
-        if (err.message) {
-            if(err.errno === 14){
-                throw "Unable to open " + filename + " in " + path.resolve("../../")
-            } else {
-                throw err.message
-            }
-        } else {
-            throw err
-        }
-    }
+	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));
+	}
 }
 
-module.exports = runQuery
+/** @type {import('@evidence-dev/db-commons').RunQuery<SQLiteOptions>} */
+const runQuery = async (queryString, database, batchSize = 100000) => {
+	const filename = database.filename;
+	try {
+		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()
+		});
+		results.expectedRowCount = expected_row_count;
+
+		return results;
+	} catch (err) {
+		if (err.message) {
+			if (err.errno === 14) {
+				throw 'Unable to open ' + filename + ' in ' + path.resolve('../../');
+			} else {
+				throw err.message;
+			}
+		} else {
+			throw err;
+		}
+	}
+};
+
+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-a9553e25",
+  "version": "0.0.0-a968da82",
   "description": "SQLite driver for Evidence projects",
@@ -9,5 +9,5 @@ "main": "index.cjs",
   "dependencies": {
-    "@evidence-dev/db-commons": "^0.0.0-a9553e25",
-    "sqlite": "4.0.25",
-    "sqlite3": "5.1.2"
+    "sqlite": "4.2.1",
+    "sqlite3": "5.1.6",
+    "@evidence-dev/db-commons": "0.0.0-a968da82"
   },
@@ -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");
-        assert.instance(results.rows, Array);
-        assert.instance(results.columnTypes, Array);
-        assert.type(results.rows[0], "object");
-        assert.equal(results.rows[0].number_col, 100);
+	try {
+		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:' }
+		);
+		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);
 
-        let expectedColumnTypes = ['number', 'date', 'date', 'string', 'number'];
-        let expectedColumnNames = ['number_col', 'date_col', 'timestamp_col', 'string_col', 'bool_col'];
-        let expectedTypePrecision = Array(5).fill(TypeFidelity.INFERRED);
+		let expectedColumnTypes = ['number', 'date', 'date', 'string', 'number'];
+		let expectedColumnNames = ['number_col', 'date_col', 'timestamp_col', 'string_col', 'bool_col'];
+		let expectedTypePrecision = Array(5).fill(TypeFidelity.INFERRED);
 
-        assert.equal(true, (expectedColumnTypes.length === actualColumnTypes.length && expectedColumnTypes.every((value, index) => value === actualColumnTypes[index])));
-        assert.equal(true, (expectedColumnNames.length === actualColumnNames.length && expectedColumnNames.every((value, index) => value === actualColumnNames[index])));
-        assert.equal(true, (expectedTypePrecision.length === actualTypePrecisions.length && expectedTypePrecision.every((value, index) => value === actualTypePrecisions[index])));
-    } catch(e) {
-        throw Error(e)
-    }
-})
+		assert.equal(
+			true,
+			expectedColumnTypes.length === actualColumnTypes.length &&
+				expectedColumnTypes.every((value, index) => value === actualColumnTypes[index])
+		);
+		assert.equal(
+			true,
+			expectedColumnNames.length === actualColumnNames.length &&
+				expectedColumnNames.every((value, index) => value === actualColumnNames[index])
+		);
+		assert.equal(
+			true,
+			expectedTypePrecision.length === actualTypePrecisions.length &&
+				expectedTypePrecision.every((value, index) => value === actualTypePrecisions[index])
+		);
+	} catch (e) {
+		throw Error(e);
+	}
+});
 
+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();

LICENSE

-MIT License
-
-Copyright (c) 2021 Evidence Technologies, Inc. 
-
-Permission is hereby granted, free of charge, to any person obtaining a copy
-of this software and associated documentation files (the "Software"), to deal
-in the Software without restriction, including without limitation the rights
-to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
-copies of the Software, and to permit persons to whom the Software is
-furnished to do so, subject to the following conditions:
-
-The above copyright notice and this permission notice shall be included in all
-copies or substantial portions of the Software.
-
-THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
-IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
-FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
-AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
-LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
-OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
-SOFTWARE.

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

Environment variable access

Supply chain risk

Package accesses environment variables, which may be a sign of credential stuffing or data theft.

Found 3 instances in 1 package

Improved metrics

Total package byte prevSize

12807

99.77%

Number of package files

6

20%

Lines of code

160

190.91%

Number of medium quality alerts

0

-100%

Number of lines in readme file

4

Infinity%

Number of low supply chain risk alerts

1

-75%

Dependency changes

• + Added

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

• + Added

  fs-extra@11.2.0
  (transitive)

• + Added

  sqlite@4.2.1
  (transitive)

• + Added

  sqlite3@5.1.6
  (transitive)

• - Removed

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

• - Removed

  fs-extra@10.0.0
  (transitive)

• - Removed

  sqlite@4.0.25
  (transitive)

• - Removed

  sqlite3@5.1.2
  (transitive)

• Updated

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

• Updated

  sqlite@4.2.1

• Updated

  sqlite3@5.1.6