@evidence-dev/sqlite - npm Package Compare versions

Comparing version 0.0.0-eca5c79a to 0.0.0-ef8688ff

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
@@ -4,0 +133,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
+	}
+};

LICENSE.md

@@ -1,6 +0,4 @@
-# License
-
 MIT License
 
-Copyright \(c\) 2021 Evidence
+Copyright \(c\) 2023 Evidence
 
@@ -7,0 +5,0 @@ 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:

package.json

 {
   "name": "@evidence-dev/sqlite",
-  "version": "0.0.0-eca5c79a",
+  "version": "0.0.0-ef8688ff",
   "description": "SQLite driver for Evidence projects",
@@ -9,5 +9,5 @@ "main": "index.cjs",
   "dependencies": {
-    "@evidence-dev/db-commons": "^0.0.0-eca5c79a",
-    "sqlite": "4.0.25",
-    "sqlite3": "5.1.2"
+    "sqlite": "4.2.1",
+    "sqlite3": "5.1.6",
+    "@evidence-dev/db-commons": "0.0.0-ef8688ff"
   },
@@ -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

94.28%

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-ef8688ff
  (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-ef8688ff

• Updated

  sqlite@4.2.1

• Updated

  sqlite3@5.1.6