- Notifications
You must be signed in to change notification settings - Fork0
Efficiently proxy sqlite tables and access data as typical array of objects
License
beenotung/better-sqlite3-proxy
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
Efficiently proxy sqlite tables and access data as typical array of objects.Powered bybetter-sqlite3🔋
- Type safety support for each table
- support common types of table
- with schema (e.g. id, user_id, title, content)
- with json (e.g. id, value)
- auto run sqlite statements, supports:
- create table (only for key-value proxy)
- select
- insert
- update
- delete
- auto resolve reference row from foreign key into nested objects likeref-db
- auto convert column values into sqlite3 format
- convert
true/falseto1/0 - convert
Dateinstance to GMT timestamp - support searching
null/not nullcolumns
- convert
- extra helper functions:
- toSqliteTimestamp (date): string
- fromSqliteTimestamp (string_or_date): Date
- seedRow (table, filter, extra?): number
- upsert (table, key, date)
- getId (table, key, value)
| 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 |
pick(array, columns, filter?) | select columns 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) andarray.map(fn) are also supported, they will receive proxy-ed rows.
Tips: You can use for-of loop instead ofarray.forEach(fn) if you may terminate the loop early
Tips: You can usefilter(partial) instead ofarray.filter(fn) for better performance
Tips: You can usepick(array, columns, filter?) instead ofarray.map(fn) for better performance
Tips: You can useupdate(array, id, partial) instead ofObject.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.
The results from mapped operations are proxy-ed object identified by id.Getting the properties on the object will trigger select on corresponding column, andsetting the properties will trigger update on corresponding column.
Remark:@beenotung/better-sqlite3-helper is a fork ofbetter-sqlite3-helper. It updates the dependency on better-sqlite3 to v8+ which includes arm64 prebuilds for macOS.
Proxy Relational Tables (click to expand)More Examples inschema-proxy.spec.ts
importDBfrom'@beenotung/better-sqlite3-helper'import{proxySchema,unProxy,find,filter}from'better-sqlite3-proxy'letdb=DB({path:'dev.sqlite3',migrate:{migrations:[/* sql */`-- Upcreate table if not exists user ( id integer primary key, username text not null unique);-- Downdrop table user;`,/* sql */`-- Upcreate 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);-- Downdrop table post;`,],},})typeDBProxy={user:User[]post:Post[]}typeUser={id?:numberusername:string}typePost={id?:numberuser_id:numbercontent:stringcreated_at?:stringauthor?:User}letproxy=proxySchema<DBProxy>(db,{user:['id','username'],// specify columns explicitly or leave it empty to auto-scan from create-table schemapost:[['author',{field:'user_id',table:'user'}],// link up reference fields],})// insert recordproxy.user[1]={username:'alice'}proxy.user.push({username:'Bob'})proxy.post.push({user_id:1,content:'Hello World'})// select a specific columnconsole.log(proxy.user[1].username)// 'alice'// select a specific column from reference tableconsole.log(proxy.post[1].author?.username)// 'alice'// select all columns of a recordconsole.log(unProxy(proxy.post[1]))// { id: 1, user_id: 1, content: 'Hello World', created_at: '2022-04-21 23:30:00'}// update a specific columnproxy.user[1].username='Alice'// update multiple columnsproxy.post[1]={content:'Hello SQLite',created_at:'2022-04-22 08:30:00',}asPartial<Post>asPost// find by columnsconsole.log(find(proxy.user,{username:'Alice'})?.id)// 1// filter by columnsconsole.log(filter(proxy.post,{user_id:1})[0].content)// 'Hello SQLite// delete recorddeleteproxy.user[2]console.log(proxy.user.length)// 1// truncate tableproxy.post.length=0console.log(proxy.post.length)// 0
Proxy Key-Value Records (click to expand)More Examples inkey-value.spec.ts
importDBfrom'@beenotung/better-sqlite3-helper'import{proxyKeyValue,find,filter}from'better-sqlite3-proxy'exportletdb=DB({path:'dev.sqlite3',migrate:false,})typeDBProxy={users:{id:numberusername:string}[]}letproxy=proxyKeyValue<DBProxy>(db)// auto create users table, then insert recordproxy.users[1]={id:1,username:'alice'}proxy.users.push({id:2,username:'Bob'})// select from users tableconsole.log(proxy.users[1])// { id: 1, username: 'alice' }// update users tableproxy.users[1]={id:1,username:'Alice'}console.log(proxy.users[1])// { id:1, username: 'Alice' }// find by columnsconsole.log(find(proxy.users,{username:'Alice'})?.id)// 1// filter by columnsconsole.log(filter(proxy.users,{username:'Bob'})[0].id)// 2// delete recorddeleteproxy.users[2]console.log(proxy.users.length)// 1// truncate tableproxy.users.length=0console.log(proxy.users.length)// 0
This project is licensed withBSD-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
About
Efficiently proxy sqlite tables and access data as typical array of objects