npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

better-sqlite3-proxy

v2.10.0

Published

Efficiently proxy sqlite tables and access data as typical array of objects.

Downloads

172

Readme

better-sqlite3-proxy

Efficiently proxy sqlite tables and access data as typical array of objects. Powered by better-sqlite3🔋

npm Package Version

Features

  • [x] Type safety support for each table
  • [x] auto run sqlite statements, supports:
    • [x] create table (only for key-value proxy)
    • [x] select
    • [x] insert
    • [x] update
    • [x] delete
  • [x] auto resolve reference row from foreign key into nested objects like ref-db
  • [x] auto convert column values into sqlite3 format
    • [x] convert true/false to 1/0
    • [x] convert Date instance to GMT timestamp
    • [x] support searching null / not null columns
  • [x] extra helper functions:
    • [x] toSqliteTimestamp (date): string
    • [x] fromSqliteTimestamp (string_or_date): Date
    • [x] seedRow (table, filter, extra?): number
    • [x] upsert (table, key, date)
    • [x] getId (table, key, value)

Array Operations Mapping

| Array Operation | Mapped SQL Operation | | ------------------------ | ------------------------------------- | | array.push(...object) | insert | | array[id] = object | insert or update | | update(array,id,partial) | update | | find(array, filter) | select where filter limit 1 | | filter(array, filter) | select where filter | | count(array, filter) | select count where filter | | delete array[id] | delete where id | | del(array, filter) | delete where filter | | array.length = length | delete where id > length | | array.slice(start, end) | select where id >= start and id < end |

for-of loop, array.forEach(fn), array.filter(fn) and array.map(fn) are also supported, they will receive proxy-ed rows.

Tips: You can use for-of loop instead of array.forEach(fn) if you may terminate the loop early

Tips: You can use filter(partial) instead of array.filter(fn) if possible for better performance

Tips: You can use update(array,id,partial) instead of Object.assign(row,partial) to update multiple columns in batch

Pro Tips: If you need complex query that can be expressed in sql, use prepared statement will have fastest runtime performance.

Lazy Evaluation

The results from mapped operations are proxy-ed object identified by id. Getting the properties on the object will trigger select on corresponding column, and setting the properties will trigger update on corresponding column.

Usage Example

Remark: @beenotung/better-sqlite3-helper is a fork of better-sqlite3-helper. It updates the dependency on better-sqlite3 to v8+ which includes arm64 prebuilds for macOS.

More Examples in schema-proxy.spec.ts

import DB from '@beenotung/better-sqlite3-helper'
import { proxySchema, unProxy, find, filter } from 'better-sqlite3-proxy'

let db = DB({
  path: 'dev.sqlite3',
  migrate: {
    migrations: [
      /* sql */ `
-- Up
create table if not exists user (
  id integer primary key
, username text not null unique
);
-- Down
drop table user;
`,
      /* sql */ `
-- Up
create table if not exists post (
  id integer primary key
, user_id integer not null references user (id)
, content text not null
, created_at timestamp not null default current_timestamp
);
-- Down
drop table post;
`,
    ],
  },
})

type DBProxy = {
  user: User[]
  post: Post[]
}
type User = {
  id?: number
  username: string
}
type Post = {
  id?: number
  user_id: number
  content: string
  created_at?: string
  author?: User
}

let proxy = proxySchema<DBProxy>(db, {
  user: ['id', 'username'], // specify columns explicitly or leave it empty to auto-scan from create-table schema
  post: [
    ['author', { field: 'user_id', table: 'user' }], // link up reference fields
  ],
})

// insert record
proxy.user[1] = { username: 'alice' }
proxy.user.push({ username: 'Bob' })
proxy.post.push({ user_id: 1, content: 'Hello World' })

// select a specific column
console.log(proxy.user[1].username) // 'alice'

// select a specific column from reference table
console.log(proxy.post[1].author?.username) // 'alice'

// select all columns of a record
console.log(unProxy(proxy.post[1])) // { id: 1, user_id: 1, content: 'Hello World', created_at: '2022-04-21 23:30:00'}

// update a specific column
proxy.user[1].username = 'Alice'

// update multiple columns
proxy.post[1] = {
  content: 'Hello SQLite',
  created_at: '2022-04-22 08:30:00',
} as Partial<Post> as Post

// find by columns
console.log(find(proxy.user, { username: 'Alice' })?.id) // 1

// filter by columns
console.log(filter(proxy.post, { user_id: 1 })[0].content) // 'Hello SQLite

// delete record
delete proxy.user[2]
console.log(proxy.user.length) // 1

// truncate table
proxy.post.length = 0
console.log(proxy.post.length) // 0

More Examples in key-value.spec.ts

import DB from '@beenotung/better-sqlite3-helper'
import { proxyKeyValue, find, filter } from 'better-sqlite3-proxy'

export let db = DB({
  path: 'dev.sqlite3',
  migrate: false,
})

type DBProxy = {
  users: {
    id: number
    username: string
  }[]
}

let proxy = proxyKeyValue<DBProxy>(db)

// auto create users table, then insert record
proxy.users[1] = { id: 1, username: 'alice' }
proxy.users.push({ id: 2, username: 'Bob' })

// select from users table
console.log(proxy.users[1]) // { id: 1, username: 'alice' }

// update users table
proxy.users[1] = { id: 1, username: 'Alice' }
console.log(proxy.users[1]) // { id:1, username: 'Alice' }

// find by columns
console.log(find(proxy.users, { username: 'Alice' })?.id) // 1

// filter by columns
console.log(filter(proxy.users, { username: 'Bob' })[0].id) // 2

// delete record
delete proxy.users[2]
console.log(proxy.users.length) // 1

// truncate table
proxy.users.length = 0
console.log(proxy.users.length) // 0

License

This project is licensed with BSD-2-Clause

This is free, libre, and open-source software. It comes down to four essential freedoms [ref]:

  • The freedom to run the program as you wish, for any purpose
  • The freedom to study how the program works, and change it so it does your computing as you wish
  • The freedom to redistribute copies so you can help others
  • The freedom to distribute copies of your modified versions to others