haltcase / trilogy Goto Github PK

TypeScript SQLite layer with support for both native C++ & pure JavaScript drivers.

License: MIT License

TypeScript 100.00%

sqlite sql electron knex database promise nwjs sql-js model schema

trilogy's Introduction

trilogy is a simple Promise-based wrapper for SQLite databases. It supports both the native C++ sqlite3 driver and the pure JavaScript sql.js backend — compile natively for speed when you need it, or use sql.js headache-free in cross-platform environments and Electron apps.

It's not an ORM and isn't intended to be one — it doesn't have any relationship features. Instead it focuses on providing a simple, clear API that's influenced more by Mongoose than by SQL.

features · installation · usage · contributing · license

features

🔗 automatically casts data between JavaScript & SQLite types

Define schemas with types like String, Date, or 'increments' — trilogy will handle all the type-casting involved to map accurately between JavaScript and the underlying SQLite database.
🔋 powered by the knex query builder

trilogy uses knex internally to build its queries, but it's also exposed so you can use it to build your own. No need to mess with ridiculous multi-line strings.
🔩 supports multiple swappable backends ( plus in-memory storage )

Both the native sqlite3 module and sql.js (pure JavaScript!) are supported. There is also memory-only storage for fast, unpersisted data handling, which is great for tests and performance critical situations.

You can even swap the backend after you've started, with no changes to the rest of your code!
👮 written in TypeScript

trilogy is written in and provides a first-class experience for TypeScript.
🔌 lifecycle hooks

Any number of hooks (aka subscribers or listeners) can be attached at several points in the lifecycle — for example onQuery, beforeCreate, afterUpdate. These are useful for debugging and extensibility.
💞 perfect for Electron & NW.js

Compiling the sqlite3 module for all the platforms you target with Electron or NW.js can be difficult. That's why trilogy also supports the sql.js backend, which doesn't need to be compiled at all!

installation

Install trilogy

# using yarn
yarn add trilogy

# using npm
npm i trilogy

Install a backend

# using yarn
yarn add sqlite3

# using npm
npm i sqlite3

# using yarn
yarn add sql.js

# using npm
npm i sql.js

usage

Full documentation is available here and includes guides, an API reference, and more.

Here's a quick overview. It uses async & await but is easily usable with vanilla Promises.

import { connect } from 'trilogy'

// defaults to using the `sqlite3` backend
const db = connect('./file.db')

// choose `sql.js` to avoid native compilation :)
const db = connect('./file.db', {
  client: 'sql.js'
})

// set the filename to ':memory:' for fast, in-memory storage
const db = connect(':memory:', {
  // it works for both clients above!
  client: 'sql.js'
})

;(async function () {
  const games = await db.model('games', {
    name: { type: String },
    genre: String,            // type shorthand
    released: Date,
    awards: Array,
    id: 'increments'          // special type, primary key
  })

  await games.create({
    name: 'Overwatch',
    genre: 'FPS',
    released: new Date('May 23, 2016'),
    awards: [
      'Game of the Year',
      'Best Multiplayer Game',
      'Best ESports Game'
    ]
  })

  const overwatch = await games.findOne({ name: 'Overwatch' })

  console.log(overwatch.awards[1])
  // -> 'Best Multiplayer Game'
})()

contributing

This project is open to contributions of all kinds! Don't worry if you're not 100% up to speed on the process — there's a short outline in the Contributor Guide.

You'll also find a reference for the set of labels used to categorize issues, with descriptions of each. (Contributor Guide - issue labels)

Also, please read and follow the project's Code of Conduct.

license

MIT © Bo Lingen / citycide

See license

trilogy's People

Contributors

Stargazers

Watchers

trilogy's Issues

getting Error: no such table: sqlite_sequence error

D:\Vikrant Data\node apps\ISB\electron-app\node_modules\sql.js\js\sql.js:493 Uncaught (in promise) Error: no such table: sqlite_sequence
    at a.handleError (D:\Vikrant Data\node apps\ISB\electron-app\node_modules\sql.js\js\sql.js:493)
    at a.exec (D:\Vikrant Data\node apps\ISB\electron-app\node_modules\sql.js\js\sql.js:491)
    at D:\Vikrant Data\node apps\ISB\electron-app\node_modules\trilogy\dist\trilogy.js:259
a.handleError @ D:\Vikrant Data\node apps\ISB\electron-app\node_modules\sql.js\js\sql.js:493
a.exec @ D:\Vikrant Data\node apps\ISB\electron-app\node_modules\sql.js\js\sql.js:491
(anonymous) @ D:\Vikrant Data\node apps\ISB\electron-app\node_modules\trilogy\dist\trilogy.js:259

modal =>

const db = new Trilogy('./storage.db', {
  client: 'sql.js'
})

db.model('people', {
  name: String,
  age: Number,
  email: String,
  uid: { type: 'increments', primary: true }
});

3.0.0

I've started thinking about any breaking changes I'd like to make for the v3 release. So far they're pretty much all type related and serve to either improve inference or make something stricter, which is an evolution of v2's rewrite to TypeScript.

eliminate (or drastically reduce the size of) the Trilogy class
- basically all it does is duplicate methods on Model instances, but with worse capability for type inference and is therefore less safe
remove dot-delimited strings in favor of explicitly passing the model & column names separately
improve type inference, particularly for models by using the provided schemas
make types stricter where possible

sqljs usage changes

https://github.com/citycide/trilogy/blob/506126c4f4e626a36c48f0d2cf50c34bea07c66c/src/sqljs-handler.js#L8

https://github.com/kripken/sql.js/issues/270#issuecomment-491743275

Newlines in strings are escaped

I'm seeing some surprising behavior from trilogy. I'm updating an article to have content, which is a text column. When I use trilogy, any newlines are turned into '\n' sequences.

I checked, and I don't think it's being done by knex:

var knex = require('knex')({
 client: 'sqlite3',
 connection: {
   filename: "./storage.db"
 }
})

knex('articles').where('id', '=', 195).update({
 content: 'Stuff about Maps.\n\nWith multiple lines,\nseparated by newlines.'
 }).then((data) => { console.log('update produced data', data) })

sqlite> select * from articles where id = 195;
id|name|content|created_at|updated_at|category_id
195|Maps|Stuff about Maps.

With multiple lines,
separated by newlines.|2016-11-11 17:43:16.270|2016-11-11 17:43:16.270|1

var Trilogy = require('trilogy')
var db = new Trilogy('./storage.db')
var content = 'Stuff about Maps.\n\nWith multiple lines,\nseparated by newlines.'
var id = 195
db.update('articles', {
  content
}, {
  id
}).then((data) => { console.log('update produced data', data) })

sqlite> select * from articles where id = 195;
 id|name|content|created_at|updated_at|category_id
 195|Maps|Stuff about Maps.\n\nWith multiple lines,\nseparated by newlines.|2016-11-11 17:43:16.270|2016-11-11 17:43:16.270|1

Here's the table schema:

sqlite> .schema articles
CREATE TABLE "articles" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
  "name" varchar NOT NULL, 
  "content" text, 
  "created_at" datetime NOT NULL, 
  "updated_at" datetime NOT NULL, 
  "category_id" integer);

Am I doing something wrong?

Thanks!

Webpack usage

Hey, where is trilogy supposed to be used, in main or in renderer?
I tried it in renderer, but webpack gives me errors.

potential security vulnerability in latest stable release 2.0.2

knex.js versions before 0.19.5 are vulnerable to SQL Injection attack. Identifiers are escaped incorrectly as part of the MSSQL dialect, allowing attackers to craft a malicious query to the host DB.

In the latest stable release the knex version is 0.17.5.

Please refer CVE-2019-10757

timestamps don't create

description

Using timestamps: true on a model does not create the timestamps on the table, and will error when creating a new object.

steps to reproduce

Create a model with timestamps option to true
Add an item to the table using .create

source

import { connect } from "trilogy";

export default async function test() {
  const db = connect("test.sqlite");

  const model = await db.model(
    "tests",
    {
      id: "increments",
      item: String,
    },
    { timestamps: true }
  );

  await model.create({ item: "test" });
}

test();

error

(node:3784) UnhandledPromiseRejectionWarning: Error: INSERT OR IGNORE into `tests` (`item`) values ('test') - SQLITE_ERROR: no such column: new.created_at

expected

Timestamps should create updated_at and created_at columns on the test table.

actual

The timestamp columns are not created. There's instead a tests_returning_temp table that has the correct columns, but not the correct types.

I can get around this by defining my own createdAt proeperty, but that's kinda leaving me to do the work that should already be here. I couldn't find out exact bugs, but when defining my own property, I did successfully create an entry. I suspect this is a knex issue since I saw something about timestamps bug something on their github issues page.

Here is that resulting schema that is produced:

How can I set up auto-increment/auto-timestamp columns?

Hey - trilogy looks great, but I'd like to have an 'id' column which auto-increments for each added row, and automatically set 'timestamps'.

I see that knex supports them at table creation:

knex.schema.createTable('users', function (table) {
  table.increments();
  table.string('name');
  table.timestamps();
})
Outputs:
create table `users` (`id` int unsigned not null auto_increment primary key, `name` varchar(255), `created_at` datetime, `updated_at` datetime)

But I don't see how to get that functionality with trilogy.

Thanks!

Unable to define multiple indices via the same table

Something like this would be nice, or provide an option to let user define their multiple indices

  db.model('people', schema, (table) => {
      table.index(['col1', 'col2'])
        .index(['col2', 'col3'])
        .unique(['somethingUnique']);
  });

[v2.0.0-rc.1] error

Hi,
switched from beta2 to v2.0.0-rc.1, change :
from import { create } from 'trilogy' to import { connect } from 'trilogy'
Receive this error:

Uncaught (in promise) TypeError: (0 , _trilogy.connect) is not a function

The problem appears when package installed via : npm i [email protected]
and disappears when done via : npm i trilogy@next

I think it's a package compilation problem.

An in-range update of rollup-plugin-babel is breaking the build 🚨

Version 3.0.5 of rollup-plugin-babel was just published.

Branch	Build failing 🚨
Dependency	`[rollup-plugin-babel](https://github.com/rollup/rollup-plugin-babel)`
Current Version	3.0.4
Type	devDependency

This version is covered by your current version range and after updating it in your project the build failed.

rollup-plugin-babel is a devDependency of this project. It might not break your production code or affect downstream projects, but probably breaks your build or test tools, which may prevent deploying or publishing.

Status Details

❌ continuous-integration/travis-ci/push The Travis CI build could not complete due to an error Details

Commits

The new version differs by 2 commits.

c318f09 3.0.5
9af19a7 Cache preflight check results per plugin's instance

See the full diff

FAQ and help

There is a collection of frequently asked questions. If those don’t help, you can always ask the humans behind Greenkeeper.

Your Greenkeeper Bot 🌴

find all api?

bindings for find all(fetching all of data of model(table)?

sqlite error: no such table: tablename_returning_temp

description

I attempted to port a class i had which used knex to manage an sqlite database to this library. I'm getting these type of errors from both the sql.js and the sqlite3 driver.

It seems to happen specifically if I perform several rapid calls to the model.create() function.

Along this line of thinking, I attempted to create a small reproduction repo, but i constructed some similar data to what i'm saving and hammered the create() function as fast as I could, and had no dice getting it to throw. You can reproduce this bug if you run yarn test on this repo on the trilogy branch:

https://github.com/vex-chat/libvex-js

I apologize I wasn't able to get a small reproduction of this, but hopefully you have some idea of what is going on by the stack trace.

error

    no such table: messages_returning_temp
      at c.handleError (node_modules/sql.js/dist/sql-wasm.js:89:254)
      at c.exec (node_modules/sql.js/dist/sql-wasm.js:87:6)
      at Object.runQuery (node_modules/trilogy/dist/helpers.js:118:37)
      at Model.create (node_modules/trilogy/dist/model.js:51:24)

expected

no error should be thrown

actual

error is thrown

Thank you

Add TypeScript/Flowtype definitions

I'm sorry if i'm being too noob here, but i cant import this module to any ts file.
I get an error that says that it cant find it. That it needs a definition(or declarations?) file.

Sorry and thanks in advance.
(Also, could you please link me to a repo of a working example so i can learn from it? Thanks!)

`sql.js` shouldn't be referenced when using `sqlite3` client

Reference #17 where this was uncovered.

Knex: run$ npm install sqlite3 --saveCannot find module

I used it like this:

const db = connect('./store.db', {
  client: 'sqlite3'
})

in MacOS and the error is:

Knex: run$ npm install sqlite3 --saveCannot find module '/Users/elliotyan/Documents/me/electron-with-sqlite3/node_modules/sqlite3/lib/binding/electron-v8.2-darwin-x64/node_sqlite3.node'Require stack:- /Users/elliotyan/Documents/me/electron-with-sqlite3/node_modules/sqlite3/lib/sqlite3-binding.js- /Users/elliotyan/Documents/me/electron-with-sqlite3/node_modules/sqlite3/lib/sqlite3.js- /Users/elliotyan/Documents/me/electron-with-sqlite3/node_modules/knex/lib/dialects/sqlite3/index.js- /Users/elliotyan/Documents/me/electron-with-sqlite3/node_modules/knex/lib/knex.js- /Users/elliotyan/Documents/me/electron-with-sqlite3/node_modules/knex/lib/index.js- /Users/elliotyan/Documents/me/electron-with-sqlite3/node_modules/knex/knex.js- /Users/elliotyan/Documents/me/electron-with-sqlite3/node_modules/trilogy/dist/index.js- /Users/elliotyan/Documents/me/electron-with-sqlite3/preload.jsError: Cannot find module '/Users/elliotyan/Documents/me/electron-with-sqlite3/node_modules/sqlite3/lib/binding/electron-v8.2-darwin-x64/node_sqlite3.node'

Migrations

Hey, what's about migrations mechanism?

Inject function in sql

I see that sql.js has a create_function that should/could work like this:

const sum = (a) => a + 10;
db.create_function('sum', sum);

db.knex('some-table')
  .select('sum(col) AS foo');

However this is not possible. The create_function is available only if I use:

const client = await db.pool.acquire(); // `acquire` is from generic-pool
client.create_function('sum', sum);

const query = db.knex('some-table')
  .select('sum(col) AS foo');

const res = await db.raw(query, true);
console.log('res', res); // never reach this log
db.pool.release(client);

If I use another approach:

  return new Promise((resolve, reject) => {
    db.pool.acquire().then(client => {
      var sum = a => a + '...';
      client.create_function('sum', sum);
      const query = db.knex('some-table').select(db.knex.raw('sum(valorParcela)'));
      const res = db.raw(query, true);
      db.pool.release(client);
      resolve(res);
    });
  });

Then I get Error: no such function: sum which looks obvious because of db.knex.raw.

Wow, that was long. Maybe was easier if you expose the sql.js client?

Electron Vue Project - Error: Failed to compile

description

I built a test project with Vue-CLI 3 and electron-builder plugin running Typescript and Babel. When I tried to connect to Trilogy (sql.js), I get the error below.

source

import Trilogy from 'trilogy'

import store from '@/store'
import { storiesSeed, productsSeed } from '@/database/testDBSeed'

const db = new (Trilogy as any)(store.getters.dbPath, {
	client: 'sql.js'
})

export async function initDB() {
	const didLoadStories = store.getters.didLoadStories
	if (!didLoadStories) {
		await loadStories()
	}

	const didLoadProducts = store.getters.didLoadProducts
	if (!didLoadProducts) {
		await loadProducts()
	}
}

error

 ERROR  Failed to compile with 12 errors                                                                                         21:59:28

 error  in ./node_modules/trilogy/dist/trilogy.mjs

Can't import the named export 'closeSync' from non EcmaScript module (only default export is available)

 error  in ./node_modules/trilogy/dist/trilogy.mjs

Can't import the named export 'dirname' from non EcmaScript module (only default export is available)

 error  in ./node_modules/trilogy/dist/trilogy.mjs

Can't import the named export 'dirname' from non EcmaScript module (only default export is available)

 error  in ./node_modules/trilogy/dist/trilogy.mjs

Can't import the named export 'dirname' from non EcmaScript module (only default export is available)

 error  in ./node_modules/trilogy/dist/trilogy.mjs

Can't import the named export 'dirname' from non EcmaScript module (only default export is available)

 error  in ./node_modules/trilogy/dist/trilogy.mjs

Can't import the named export 'mkdirSync' from non EcmaScript module (only default export is available)

 error  in ./node_modules/trilogy/dist/trilogy.mjs

Can't import the named export 'mkdirSync' from non EcmaScript module (only default export is available)

 error  in ./node_modules/trilogy/dist/trilogy.mjs

Can't import the named export 'openSync' from non EcmaScript module (only default export is available)

 error  in ./node_modules/trilogy/dist/trilogy.mjs

Can't import the named export 'readFileSync' from non EcmaScript module (only default export is available)

 error  in ./node_modules/trilogy/dist/trilogy.mjs

Can't import the named export 'resolve' from non EcmaScript module (only default export is available)

 error  in ./node_modules/trilogy/dist/trilogy.mjs

Can't import the named export 'statSync' from non EcmaScript module (only default export is available)

 error  in ./node_modules/trilogy/dist/trilogy.mjs

Can't import the named export 'writeFileSync' from non EcmaScript module (only default export is available)

expected

What should happen in this situation?
Proceed with database ops.

actual

What happened that's considered unexpected?

The app crashed as soon as i saved file above with trilogy code.

I added '.mjs' to vue.config.js to no avail. I'm really excited about this library but this is an unfortunate development. Has this happened before?

module.exports = { configureWebpack: { resolve: { extensions: ['*', '.mjs', '.js', '.vue', '.json', '.ts'] } } }

BulkInsert

context

Option to insert bulk data in batches

proposed solution

In Knex , insert function (ref) taking either a hash of properties to be inserted into the row, or an array of inserts . Can that be implemented in trilogy as well

alternatives

Unable to insert data at once using trilogy, but as a workaround, knex can be used
eg:

await db.knex<UserDocType>("users").insert(userArry);

Discussions are now open

GitHub Discussions are now available to all repositories, including this one 😎.

You can get started in the community by visiting the introduction post and letting us know how you've been using trilogy!

Node v4 fails with ECONNRESET errors on Travis

While I haven't tested on a local Node v4 instance, Travis fails on anything < 5. It's a pretty common issue but I haven't been able to solve it through the common solutions. For example explicitly setting NPM v3* in the Travis config did not solve the problem for Trilogy as it has elsewhere.

npm ERR! Linux 3.13.0-40-generic
npm ERR! argv "/home/travis/.nvm/versions/node/v4.0.0/bin/node" "/home/travis/.nvm/versions/node/v4.0.0/bin/npm" "install"
npm ERR! node v4.0.0
npm ERR! npm  v3.10.7
npm ERR! code ECONNRESET
npm ERR! errno ECONNRESET
npm ERR! syscall read
npm ERR! network read ECONNRESET
npm ERR! network This is most likely not a problem with npm itself
npm ERR! network and is related to network connectivity.
npm ERR! network In most cases you are behind a proxy or have bad network settings.
npm ERR! network 
npm ERR! network If you are behind a proxy, please make sure that the
npm ERR! network 'proxy' config is set properly.  See: 'npm help config'

npm ERR! Linux 3.13.0-40-generic
npm ERR! argv "/home/travis/.nvm/versions/node/v4.0.0/bin/node" "/home/travis/.nvm/versions/node/v4.0.0/bin/npm" "install"
npm ERR! node v4.0.0
npm ERR! npm  v3.10.7
npm ERR! Callback called more than once.
npm ERR! 
npm ERR! If you need help, you may report this error at:
npm ERR!     <https://github.com/npm/npm/issues>
npm ERR! Please include the following file with any support request:
npm ERR!     /home/travis/build/citycide/trilogy/npm-debug.log

This could potentially be an issue with Travis since related issues say it's intermittent.

Issues creating and locating a database in a React application

description

I'm having trouble locating or creating a local database file in a React application.

I have a local SQLite database sat in the public directory of my React application, which I am attempting to locate with the following code:

const database = connect("test.db", {
    client: "sql.js"
});

My database is never found, and if I change the name to something else ("test2.db", for example), I can see that no database has been created when I check my file system.

error

In order to try and work out what was happening, I added a console.log in this else block in node_modules/trilogy/dist/index.js, beginning on line 43:

else {
    obj.connection.filename = path_1.resolve(obj.dir, path);
    // ensure the directory exists
    console.log(util_1.makeDirPath(path_1.dirname(obj.connection.filename))); // My console.log
    util_1.makeDirPath(path_1.dirname(obj.connection.filename));
}

And I could see that that false was then being logged. That led me to adding a couple of console.logs in the makeDirPath function in node_modules/trilogy/dist/util.js, beginning on line 41:

function makeDirPath(path) {
    console.log("fs_1", fs_1); // My console.log
    const mode = parseInt('0777', 8);
    try {
        fs_1.mkdirSync(path, mode);
        return true;
    }
    catch (err) {
        console.log("err" , err); // My console.log

fs_1 is logged as {} and err is logged as the following:

TypeError: fs_1.mkdirSync is not a function
    at Object.makeDirPath (util.js:45)
    at new Trilogy (index.js:48)
    at push../node_modules/trilogy/dist/index.js.exports.connect (index.js:592)
    at App (App.tsx:10)
    at renderWithHooks (react-dom.development.js:14985)
    at mountIndeterminateComponent (react-dom.development.js:17811)
    at beginWork (react-dom.development.js:19049)
    at beginWork$1 (react-dom.development.js:23940)
    at performUnitOfWork (react-dom.development.js:22776)
    at workLoopSync (react-dom.development.js:22707)
    at renderRootSync (react-dom.development.js:22670)
    at performSyncWorkOnRoot (react-dom.development.js:22293)
    at scheduleUpdateOnFiber (react-dom.development.js:21881)
    at updateContainer (react-dom.development.js:25482)
    at react-dom.development.js:26021
    at unbatchedUpdates (react-dom.development.js:22431)
    at legacyRenderSubtreeIntoContainer (react-dom.development.js:26020)
    at Object.render (react-dom.development.js:26103)
    at Module.<anonymous> (index.tsx:7)
    at Module../src/index.tsx (index.tsx:18)
    at __webpack_require__ (bootstrap:856)
    at fn (bootstrap:150)
    at Object.1 (reportWebVitals.ts:16)
    at __webpack_require__ (bootstrap:856)
    at checkDeferredModules (bootstrap:45)
    at Array.webpackJsonpCallback [as push] (bootstrap:32)
    at main.chunk.js:1

expected

I would expect that my database could either be found at the path provided, or a new database would be created at that location.

actual

No database is found or created, and when I then attempt to create a model, the application crashes. I have also tried various other locations, and specifying the dir property in the options argument when using connect, but to no avail.

It is worth noting that if I use ":memory:" as the path, I can successfully interact with the database, create models, etc.

I appreciate any help you can give me!

Many thanks,
Ian

Awesome Electron

Hi,
Please add Trilogy to "Awesome Electron" (15,200 stars) to increase visibility (users).
Thank you.

sindresorhus/awesome-electron#548

TypeError: SQL.Database is not a constructor

description

I'm writing a plugin for https://github.com/tinytacoteam/zazu
And I'm writing es5.

source

This works fine.

const db = new Trilogy(historyDbPath)

While this not:

const Trilogy = require('trilogy');

const db = new Trilogy(historyDbPath, {
    client: 'sql.js',
  });

error

Cannot use 'new' with an expression whose type lacks a call or construct signature.ts(2351)

TypeError: SQL.Database is not a constructor
    at readDatabase (/Users/linonetwo/Desktop/repo/zazu-firefox-bookmarks/node_modules/trilogy/dist/trilogy.js:125:14)
    at Object.create (/Users/linonetwo/Desktop/repo/zazu-firefox-bookmarks/node_modules/trilogy/dist/trilogy.js:153:30)
    at Pool._createResource (/Users/linonetwo/Desktop/repo/zazu-firefox-bookmarks/node_modules/generic-pool/lib/Pool.js:308:42)
    at Pool._ensureMinimum (/Users/linonetwo/Desktop/repo/zazu-firefox-bookmarks/node_modules/generic-pool/lib/Pool.js:340:12)
    at Pool.start (/Users/linonetwo/Desktop/repo/zazu-firefox-bookmarks/node_modules/generic-pool/lib/Pool.js:414:10)
    at new Pool (/Users/linonetwo/Desktop/repo/zazu-firefox-bookmarks/node_modules/generic-pool/lib/Pool.js:128:12)
    at Object.createPool (/Users/linonetwo/Desktop/repo/zazu-firefox-bookmarks/node_modules/generic-pool/index.js:11:12)
    at connect (/Users/linonetwo/Desktop/repo/zazu-firefox-bookmarks/node_modules/trilogy/dist/trilogy.js:151:15)
    at new Trilogy (/Users/linonetwo/Desktop/repo/zazu-firefox-bookmarks/node_modules/trilogy/dist/trilogy.js:1009:19)

expected

Works as the same.

actual

Maybe this works fine if I use import, but now I have to write require, and it doesn't work.

null values cast to string on find, create and update

When selecting, inserting or updating the database, null values are cast to string. Is there an option to disable this functionality.

Update dependencies and release new version

context

proposed solution

This package depends on knex ^0.20.1

trilogy/package.json

Line 47 in 6d73d73

"knex": "^0.20.1",

Meanwhile, knex itself has gone through 2 major version releases, with lots of changes and features. Among other things, it now supports better-sqlite3.
https://github.com/knex/knex/releases

It'd be lovely if that dependency were updated.

alternatives

Tricky, I'd love to use trilogy and avoid having to manually deal with all of this (installing sqlite, using an ORM, etc.)

additional info

When installing this package, one even gets warnings about old, deprecated dependencies. I suppose updating all dependencies might be in order?

$ npm i trilogy
npm WARN deprecated [email protected]: See https://github.com/lydell/source-map-url#deprecated
npm WARN deprecated [email protected]: Please see https://github.com/lydell/urix#deprecated
npm WARN deprecated [email protected]: See https://github.com/lydell/source-map-resolve#deprecated
npm WARN deprecated [email protected]: https://github.com/lydell/resolve-url#deprecated
npm WARN deprecated [email protected]: Debug versions >=3.2.0 <3.2.7 || >=4 <4.3.1 have a low-severity ReDos regression when used in a Node.js environment. It is recommended you upgrade to 3.2.7 or 4.3.1. (https://github.com/visionmedia/debug/issues/797)

added 170 packages, and audited 726 packages in 9s

v0.11.0 Refactor, v1.0.0-rc.1 release

Update 1-9-17: the new version has been pushed to npm as @next. Give it a shot and throw me any feedback you might have. Docs are currently available as .md files in the next branch here in the repo.

the future of trilogy

I'm looking to make some sweeping changes to trilogy. Most of the codebase will be rewritten to be simpler, and the API is going to change almost entirely. It'll first be published as the @next tag to NPM before eventually becoming the latest release.

When the v0.11.x series is considered whatever you want 'ready' to mean ( 😂 ), it will be released as v1.0.0-rc.1. Then, barring any blocking issues it'll be pushed to v1.0.0 and considered stable. 🍰 Otherwise we'll ride that rc car until the batteries die... then we'll recharge them.

the past of trilogy

Let's back up a bit for a sec - trilogy started as a personal project, and really was a small module in a larger app that just layered over sql.js so I could stop using raw query strings. I also have grown away from chainable APIs of late, so knex by itself did not solve all my problems. Chains have their uses but I was looking for something different for my database.

I liked the idea of document stores that take after Mongo, like nedb and linvodb, but SQLite is generally faster and then there's the whole relational vs non-relational debate and so on. Long story short I wanted to stick to SQLite, but I admired the APIs of these other options.

That's why trilogy's API doesn't really feel like SQL. In the project for which trilogy was originally built, I expose the database over a plugin architecture. I felt knex's chaining would become a pain for plugin authors so I chased a simpler API similar to document stores.

fast forward

Alright so we're here at v0.10.0. There are probably a couple people using trilogy, or at least a few interested based on the repo stars.

With version 0.11.0, the API will undergo major changes. Where currently you would use code that looks like this:

db.createTable('people', {
  id: { type: 'integer', primary: true },
  name: { type: 'text' },
  power_level: { type: 'integer' }
}).then(() => {
  return db.insert('people', {
    id: 456789,
    name: 'citycide',
    power_level: 9001
  })
}).then(() => {
  return db.select('people', 'name', ['power_level', '>', 9000])
}).then(names => {
  console.log(names)
  // -> [{ name: 'citycide' }]
})

... you'd use this instead:

db.model('people', {
  id: { type: Number, primary: true },
  name: String,
  power_level: Number
}).then(people => {
  // could also be `db.create('people', { ... })`
  return people.create({
    id: 456789,
    name: 'citycide',
    power_level: 9001
  })
}).then(() => {
  // could also be `db.find('people.name', ['power_level', '>', 9000])`
  return people.find('name', ['power_level', '>', 9000])
}).then(names => {
  console.log(names)
  // -> ['citycide']
})

So the general idea remains the same but trilogy will now use models to keep track of the schema of your tables based on the types you specify. When you insert or retrieve data, the types will be cast as necessary to fit this schema so that you always receive what you expect to receive.

The model architecture also allows for a couple different ways to use the API. You could just always use db.find('people.name', { ... }), or you could use people.find('name', { ... }) - db.model() returns that model instance.

I also hope to bring support for the native sqlite3 module along with sql.js.

Update: ^ sqlite3 is now supported in addition to sql.js! 🎉 If you update and would like to continue using sql.js, supply it as options.client in the constructor options as the default is now sqlite3 for performance & asynchronous reasons.

API map from old to new

These are the methods I've been able to mostly hash out, and how they relate to the current version of trilogy:

v0.10.0	v0.11.0
`createTable()`	`model()`
`decrement()`	`decr()`
`del()`	`remove()` & `clear()`
`dropTable()`	`dropModel()`
`first()`	`findOne()`
`getValue()`	`get()`
`hasTable()`	`hasModel`
`increment()`	`incr()`
`insert()`	`create()`
`select()`	`find()`
`update()`	`update()` + `set()` (`get()` equivalent)
`queryBuilder`	`knex`

Also, instead of using an options object like { conflict: 'replace' } with insert(), there are new methods findOrCreate() and updateOrCreate().

new methods
`min()`
`max()`
`findOrCreate()`
`updateOrCreate()`

removed	reason
`coercion`	models coerce values as needed, user specifies types
`schemaBuilder`	creating tables outside trilogy doesn't allow trilogy to handle the types; technically still available as `knex.schema` however

signature changes only
`count()`
`raw()`

going forward

I'll be updating the repo soon with my local WIP source. From there I'll be working on reaching feature parity as closely as possible ( or as much as makes sense ) to v0.10.0 before adding additional features. I welcome any feedback on these proposed changes and of course any contributions that help push trilogy toward them. Thanks all!

Update 1-5-17: the next branch has been pushed with the WIP source.

todo

Update tests, which means pretty much a complete overhaul since the entire API has changed
Write new docs which, again, means an overhaul ( update 1-6-17: see WIP here )
Publish to npm under the @next tag
Review feature parity situation with v0.10.0

Cast values on update

Shouldn't we use this on update:

// this is from `create()`
let insertion = types.toDefinition(this, object)

I have an error trying to update with a boolean value.

Support schema getters & setters

It would be nice if column getter is available.

db.model('people', {
  name: String,
  age: Number,
  email: String,
  uid: {
    type: Number,
    primary: true,
    get: (value) => `myCustomPrefix-${value}`
  }
})

about transaction

Is it possible to use knex.transaction with Trilogy ?

v2.0.0

I've started working on the next major for trilogy on the next branch. The goals of v2.0.0 are:

write in TypeScript

This is basically done but is probably always able to be improved, so PRs from other TypeScript users would definitely be appreciated. Flow definitions are removed and Flow is no longer officially supported. TypeScript's tooling is far better.

add plugin support

To keep the core of trilogy to a reasonable size we should implement a plugin system to allow new features. If you'd be interested in writing a trilogy plugin, please check out the progress at #82 and provide feedback.

add lifecycle hooks

Plugins were implemented in early v2 prereleases but added far too much typing complexity and were not type safe. Many use cases of plugins could be achieved with more explicit hooks like beforeCreate, afterUpdate, etc.

improve usability

General API and usage improvements are also on the table and I'm open to proposals.

The next branch holds all the progress for this release, and betas are available on npm as trilogy@next.

Version 10 of node.js has been released

Version 10 of Node.js (code name Dubnium) has been released! 🎊

To see what happens to your code in Node.js 10, Greenkeeper has created a branch with the following changes:

Added the new Node.js version to your .travis.yml
The new Node.js version is in-range for the engines in 1 of your package.json files, so that was left alone

If you’re interested in upgrading this repo to Node.js 10, you can open a PR with these changes. Please note that this issue is just intended as a friendly reminder and the PR as a possible starting point for getting your code running on Node.js 10.

More information on this issue

Greenkeeper has checked the engines key in any package.json file, the .nvmrc file, and the .travis.yml file, if present.

engines was only updated if it defined a single version, not a range.
.nvmrc was updated to Node.js 10
.travis.yml was only changed if there was a root-level node_js that didn’t already include Node.js 10, such as node or lts/*. In this case, the new version was appended to the list. We didn’t touch job or matrix configurations because these tend to be quite specific and complex, and it’s difficult to infer what the intentions were.

For many simpler .travis.yml configurations, this PR should suffice as-is, but depending on what you’re doing it may require additional work or may not be applicable at all. We’re also aware that you may have good reasons to not update to Node.js 10, which is why this was sent as an issue and not a pull request. Feel free to delete it without comment, I’m a humble robot and won’t feel rejected 🤖

FAQ and help

There is a collection of frequently asked questions. If those don’t help, you can always ask the humans behind Greenkeeper.

Your Greenkeeper Bot 🌴

Cannot find module 'sql.js/module'

description

I try to use trilogy in a TypeScript project I'm currently working on. As soon as I use import { connect } from 'trilogy' somewhere I am presented with the error below by tsc:

I planned on using sqlite3 as backend therefore I only used npm i sqlite3, but the error appears wether I am defining the client to be sqlite3 or sql.js. I also tried to npm i sql.js, but the error persists.

steps to reproduce

Create a new project
Run npm install typescript @types/node
Run npm install trilogy sqlite3
Create index.ts with the source code below
Run npx tsc index.ts

source

import { connect } from 'trilogy'

const db = connect(':memory:')

error

node_modules/trilogy/dist/index.d.ts:4:23 - error TS2307: Cannot find module 'sql.js/module' or its corresponding type declarations.

4 import { SqlJs } from 'sql.js/module';

environment

I am using Windows 10 Pro 64 Bit, Node v14.15.3 and npm 6.14.9.

[question] how to perform WHERE and AND clauses using find method ?

https://trilogy.js.org/#/api?id=find

Array syntax is either a key / value pair (equal to) or a length of 3,
 ie. ['age', '<', 65] (allows other comparisons)

For example, if I want to select where age is less than 65 and sex is male

Website not working

Unfortunately, http://trilogy.js.org/ is not working.

No default export and error instantiating Trilogy

Hello.

I'm trying a trivial example of Trilogy with 'sql.js':

import Trilogy from 'trilogy';

const db = new Trilogy(':memory:', {
  // it works for both clients above!
  client: 'sql.js'
});

but get this error:

Failed to compile
C:/Users/yagol/Documents/projects/sql_js-test/src/index.tsx
(4,8): Module '"C:/Users/yagol/Documents/projects/sql_js-test/node_modules/trilogy/dist/trilogy"' has no default export.

Uncaught TypeError: __WEBPACK_IMPORTED_MODULE_0_trilogy___default.a is not a constructor
    at Object../src/index.tsx (index.tsx:7)
    at __webpack_require__ (bootstrap afab123b749fe6c9a0c4:678)
    at fn (bootstrap afab123b749fe6c9a0c4:88)
    at Object.0 (index.tsx:16)
    at __webpack_require__ (bootstrap afab123b749fe6c9a0c4:678)
    at ./node_modules/ansi-regex/index.js.module.exports (bootstrap afab123b749fe6c9a0c4:724)
    at bootstrap afab123b749fe6c9a0c4:724
index.js:2177 C:/Users/yagol/Documents/projects/sql_js-test/src/index.tsx
(4,8): Module '"C:/Users/yagol/Documents/projects/sql_js-test/node_modules/trilogy/dist/trilogy"' has no default export.
__stack_frame_overlay_proxy_console__ @ index.js:2177

After trying to import whitout default imports:

import {Trilogy} from 'trilogy'; // note the brakets

I get the error:

TypeError: __WEBPACK_IMPORTED_MODULE_0_trilogy__.Trilogy is not a constructor
./src/index.tsx
C:/Users/yagol/Documents/projects/sql_js-test/src/index.tsx:7
   4 | import {Trilogy} from 'trilogy';
   5 | // declare var Trilogy: any;
   6 | 
>  7 | const db = new Trilogy(':memory:', {
   8 |   // it works for both clients above!
   9 |   client: 'sql.js'
  10 | });



Uncaught TypeError: __WEBPACK_IMPORTED_MODULE_0_trilogy__.Trilogy is not a constructor
    at Object../src/index.tsx (index.tsx:7)
    at __webpack_require__ (bootstrap 270ce0d30a7f6fb8b756:678)
    at fn (bootstrap 270ce0d30a7f6fb8b756:88)
    at Object.0 (index.tsx:16)
    at __webpack_require__ (bootstrap 270ce0d30a7f6fb8b756:678)
    at ./node_modules/ansi-regex/index.js.module.exports (bootstrap 270ce0d30a7f6fb8b756:724)
    at bootstrap 270ce0d30a7f6fb8b756:724

Note:
I'm using create-react-app-typescript (that is, reactjs + webpack + typescript)

Angular9 compilation error

I ran npm i trilogy and npm i sql.js

and when I try:

import { connect } from 'trilogy'

I got these errors: (The whole project it's working with no errors until I imported trilogy)

ERROR in node_modules/runtypes/lib/runtype.d.ts:11:29 - error TS1005: ';' expected.

11 assert(x: any): asserts x is A;
~
node_modules/runtypes/lib/runtype.d.ts:11:31 - error TS1005: ';' expected.

11 assert(x: any): asserts x is A;
~~
node_modules/runtypes/lib/runtype.d.ts:11:34 - error TS1005: ';' expected.

11 assert(x: any): asserts x is A;
~
node_modules/runtypes/lib/runtype.d.ts:16:12 - error TS1005: ',' expected.

16 check(x: any): A;
~
node_modules/runtypes/lib/runtype.d.ts:16:18 - error TS1005: ';' expected.

16 check(x: any): A;
~
node_modules/runtypes/lib/runtype.d.ts:21:15 - error TS1005: ',' expected.

21 validate(x: any): Result;
~
node_modules/runtypes/lib/runtype.d.ts:21:21 - error TS1005: ';' expected.

21 validate(x: any): Result;
~
node_modules/runtypes/lib/runtype.d.ts:21:32 - error TS1005: '(' expected.

21 validate(x: any): Result;
~
node_modules/runtypes/lib/runtype.d.ts:25:12 - error TS1005: ',' expected.

25 guard(x: any): x is A;
~
node_modules/runtypes/lib/runtype.d.ts:25:18 - error TS1005: ';' expected.

25 guard(x: any): x is A;
~
node_modules/runtypes/lib/runtype.d.ts:25:22 - error TS1005: ';' expected.

25 guard(x: any): x is A;
~~
node_modules/runtypes/lib/runtype.d.ts:25:25 - error TS1005: ';' expected.

25 guard(x: any): x is A;
~
node_modules/runtypes/lib/runtype.d.ts:29:25 - error TS1005: '?' expected.

29 Or(B: B): Union2<this, B>;
~
node_modules/runtypes/lib/runtype.d.ts:29:28 - error TS1005: ',' expected.

29 Or(B: B): Union2<this, B>;
~
node_modules/runtypes/lib/runtype.d.ts:29:32 - error TS1005: ';' expected.

29 Or(B: B): Union2<this, B>;
~
node_modules/runtypes/lib/runtype.d.ts:29:49 - error TS1005: '(' expected.

29 Or(B: B): Union2<this, B>;
~
node_modules/runtypes/lib/runtype.d.ts:33:26 - error TS1005: '?' expected.

33 And(B: B): Intersect2<this, B>;
~
node_modules/runtypes/lib/runtype.d.ts:33:29 - error TS1005: ',' expected.

33 And(B: B): Intersect2<this, B>;
~
node_modules/runtypes/lib/runtype.d.ts:33:33 - error TS1005: ';' expected.

33 And(B: B): Intersect2<this, B>;
~
node_modules/runtypes/lib/runtype.d.ts:33:54 - error TS1005: '(' expected.

33 And(B: B): Intersect2<this, B>;
~
node_modules/runtypes/lib/runtype.d.ts:47:22 - error TS1005: ';' expected.

47 withConstraint<T extends Static, K = unknown>(constraint: ConstraintCheck, options?: {
~~~~~~~
node_modules/runtypes/lib/runtype.d.ts:47:42 - error TS1109: Expression expected.

47 withConstraint<T extends Static, K = unknown>(constraint: ConstraintCheck, options?: {
~
node_modules/runtypes/lib/runtype.d.ts:47:67 - error TS1005: ')' expected.

47 withConstraint<T extends Static, K = unknown>(constraint: ConstraintCheck, options?: {
~
node_modules/runtypes/lib/runtype.d.ts:47:90 - error TS1109: Expression expected.

47 withConstraint<T extends Static, K = unknown>(constraint: ConstraintCheck, options?: {
~
node_modules/runtypes/lib/runtype.d.ts:47:100 - error TS1109: Expression expected.

47 withConstraint<T extends Static, K = unknown>(constraint: ConstraintCheck, options?: {
~
node_modules/runtypes/lib/runtype.d.ts:48:22 - error TS1005: ',' expected.

48 name?: string;
~
node_modules/runtypes/lib/runtype.d.ts:49:17 - error TS1005: ',' expected.

49 args?: K;
~
node_modules/runtypes/lib/runtype.d.ts:50:6 - error TS1005: ';' expected.

50 }): Constraint<this, T, K>;
~
node_modules/runtypes/lib/runtype.d.ts:50:7 - error TS1128: Declaration or statement expected.

50 }): Constraint<this, T, K>;
~
node_modules/runtypes/lib/runtype.d.ts:50:31 - error TS1005: '(' expected.

50 }): Constraint<this, T, K>;
~
node_modules/runtypes/lib/runtype.d.ts:66:17 - error TS1005: ';' expected.

66 withGuard<T extends Static, K = unknown>(guard: (x: Static) => x is T, options?: {
~~~~~~~
node_modules/runtypes/lib/runtype.d.ts:66:37 - error TS1109: Expression expected.

66 withGuard<T extends Static, K = unknown>(guard: (x: Static) => x is T, options?: {
~
node_modules/runtypes/lib/runtype.d.ts:66:57 - error TS1005: ')' expected.

66 withGuard<T extends Static, K = unknown>(guard: (x: Static) => x is T, options?: {
~
node_modules/runtypes/lib/runtype.d.ts:66:82 - merror TS1005: ';' expected.

66 withGuard<T extends Static, K = unknown>(guard: (x: Static) => x is T, options?: {
~~
node_modules/runtypes/lib/runtype.d.ts:66:85 - error TS1005: ';' expected.

66 withGuard<T extends Static, K = unknown>(guard: (x: Static) => x is T, options?: {
~
node_modules/runtypes/lib/runtype.d.ts:66:96 - error TS1109: Expression expected.

66 withGuard<T extends Static, K = unknown>(guard: (x: Static) => x is T, options?: {
~
node_modules/runtypes/lib/runtype.d.ts:67:22 - error TS1005: ',' expected.

67 name?: string;
~
node_modules/runtypes/lib/runtype.d.ts:68:17 - error TS1005: ',' expected.

68 args?: K;
~
node_modules/runtypes/lib/runtype.d.ts:69:6 - error TS1005: ';' expected.

69 }): Constraint<this, T, K>;
~
node_modules/runtypes/lib/runtype.d.ts:69:7 - error TS1128: Declaration or statement expected.

69 }): Constraint<this, T, K>;
~
node_modules/runtypes/lib/runtype.d.ts:69:31 - error TS1005: '(' expected.

69 }): Constraint<this, T, K>;
~
node_modules/runtypes/lib/runtype.d.ts:73:31 - error TS1005: '?' expected.

73 withBrand(brand: B): Brand<B, this>;
~
node_modules/runtypes/lib/runtype.d.ts:73:38 - error TS1005: ',' expected.

73 withBrand(brand: B): Brand<B, this>;
~
node_modules/runtypes/lib/runtype.d.ts:73:42 - error TS1005: ';' expected.

73 withBrand(brand: B): Brand<B, this>;
~
node_modules/runtypes/lib/runtype.d.ts:73:58 - error TS1005: '(' expected.

73 withBrand(brand: B): Brand<B, this>;
~
node_modules/runtypes/lib/runtype.d.ts:79:1 - error TS1128: Declaration or statement expected.

79 }

Can find accecpts Array?

Assume thers are [{id:1,name:"1"},{id:2,name:"2"},{id:3,name:"3"}] in db. I want to find out the items whoes id is 1 or 2, how can I do that?
Can the sytax be like mongodb, just as model.find({id:{$in:[1,2]}})?

Issue while using it with Angular 4

I am trying to use Triology with this boilerplate , when I import trilogy in one of the TS file in src folder and run, it shows following error

Preload memory database content

context

I build an app using this library with sql.js and memory storage. I'd like to persist data in the local storage.

I found that sql.js support that officially - wiki and developer workaround by monkey patching your code in my project - code

proposed solution

Add native support by adding optional option to the config that allows to set localStorage dumps and read from them on init. In the perfect scenario it would be hooked to your update/insert methods to trigger saves to localStorage only after query execution.

alternatives

Monkey patching as I do

Cannot update with timestamps set to true

description

Using the update method on a model with primary or unique field, and timestamps: true option results in exception.

steps to reproduce

Create a model with timestamps option to true
Update an item to the table using .update

source

const { connect } = require("trilogy");

async function test() {
  const db = connect(":memory:");

  const model = await db.model(
    "tests",
    {
      id: { type: Number, primary: true },
      item: String,
    },
    { timestamps: true }
  );
  await model.create({ id: 1, item: "test1" });
  await model.update({ id: 1 }, { item: "test2" });
}

test();

error

node:internal/process/promises:279
            triggerUncaughtException(err, true /* fromPromise */);
            ^

[Error: update `tests` set `item` = 'test2' where `id` = 1 - SQLITE_ERROR: no such column: current_timestamp] {
  errno: 1,
  code: 'SQLITE_ERROR'
}

expected

Update should work

actual

Exception was thrown

Password Protect the SQLite database

It would be nice to have an option to password protect/ encrypt the database.

context

When sqlite is used to embed local database in client softwares, the database will be accessible to users from outside. So if the user can edit and mess-up with the database, it may effect the working of the software.

I think it is a good practice to have at-least a basic level of protection for the database that is accessible from outside.

proposed solution

SQLite have option to set password, and encryptions (with SQLCipher 3/4 etc. )

Issue while using with angular 8 and electron

Cannot find module 'sql.js/module'.

Module not found: Error: Can't resolve 'mssql/package.json'

I am trying to build a electron App with angular and triology

i tried connecting sqlite database by using both sql js and sqlite

`model.create` should/could `needResponse`

Right after creating a new object it would be helpful if created object returns.

Much better if model.create use returning('id'), otherwise how we can get last inserted record?

`find` accept Array

How about find accept an array?

Let's say I want id and name from a table. Right now it's not that hard with knex.raw:

const query = db.knex(TABLE.CHILD)
  .where({ fatherId: id })
  .select('id', 'name')
  .orderBy(sortBy, order);
db.raw(query, true)
  .then(result => resolve(result))
  .catch(e => reject(e));

But it could be:

const model = db.model(TABLE.CHILD);
return model.find(['id', 'name'], { fatherId: id });

haltcase / trilogy Goto Github PK

trilogy's Introduction

features

installation

usage

contributing

license

trilogy's People

Contributors

Stargazers

Watchers

Forkers

trilogy's Issues

description

steps to reproduce

source

error

expected

actual

Version 3.0.5 of rollup-plugin-babel was just published.

description

error

expected

actual

description

source

error

expected

actual

context

proposed solution

alternatives

description

error

expected

actual

description

source

error

expected

actual

context

proposed solution

alternatives

additional info

the future of trilogy

the past of trilogy

fast forward

API map from old to new

going forward

todo

write in TypeScript

add plugin support

add lifecycle hooks

improve usability

Version 10 of Node.js (code name Dubnium) has been released! 🎊

description

steps to reproduce

source

error

environment

context

proposed solution

alternatives

description

steps to reproduce

source

error

expected

actual

context

proposed solution

Recommend Projects

Recommend Topics

Recommend Org