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

kysely-zotero-dialect

v0.2.0

Published

Use kysely within zotero plugins

Downloads

212

Readme

kysely-zotero-dialect

Use kysely within a zotero plugin.

This is a very thin wrapper around Zotero's query methods that allows you to use kysely as a query generator within Zotero, despite the XPCOM plugin environment not supporting any of the node-dependent SQL drivers.

This allows you to make reasonably maintainable sidecar databases with migrations and type-safe schemas and queries without needing to patch into the zotero database or invent your own data storage system.

Approach

We create a new sqlite database in Zotero's data directory using the name of the database, such that a db_name: foo will create {DATA_DIR}/foo.sqlite. All queries are then independent of Zotero's main database, but can still use the packaged XUL/XPCOM sqlite driver.

Installation

ya know how to do this part, but for your copy pasting:

npm add kysely-zotero-dialect
# or
yarn add kysely-zotero-dialect

Usage

Say we have...

  • A plugin: demo
  • A database table: demo_table
  • A sqlite database {zotero_data_dir}/demo.sqlite

Declare Models

Declare typescript models as you normally would with kysely:

src/schema.ts

import { Generated, Insertable, Selectable, Updateable } from "kysely";
export interface Database {
  demo_table: demoTable;
}

export interface demoTable {
  id: Generated<number>;
  cool_value: string;
}

export type Demo = Selectable<demoTable>;
export type NewDemo = Insertable<demoTable>;
export type DemoUpdate = Updateable<demoTable>;

Write Migrations

Assuming you are starting a new project, the easiest way to initialize a database is to use kysely migrations (and you should probably add migrations into your plugin's bootstrap method anyway).

Since someone using our plugin will not have the repository, and most typescript plugins are compiled into a single file (at least currently) we should provide migrations programmatically in the plugin package rather than use migration that depends on the file structure of the migrations.

src/migrations/001_test.ts

import type { Kysely, sql } from "kysely";

export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable("demo_table")
    .addColumn("id", "integer", (col) => col.primaryKey())
    .addColumn("cool_value", "text")
    .execute();
}

export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.dropTable("a_table").execute();
}

src/migrations/index.ts

import * as m001 from "./001_test";
// so in the future you can do...
// import * as m002 from "./002_add_doi";

import { Migration, MigrationProvider } from "kysely";

export const migrationProvider: MigrationProvider = {
  async getMigrations() {
    return migrations;
  },
};
export const migrations: Record<string, Migration> = {
  "001": m001,
  // "002": m002,
};

Initializing a Database

A database is configured with a ZoteroDialectConfig like:

export interface ZoteroDialectConfig {
  db_name: string;
}

such that a database is created in {zotero_data_directory}/{db_name}, like ~/Zotero/demo.sqlite

src/db.ts

import { Kysely } from "kysely";
import { ZoteroDialect } from "kysely-zotero-dialect";

import { Database } from "./schema";
import { migrationProvider } from "./migrations/index";

export const initDB = async (): Promise<Kysely<Database>> => {
  return new Kysely<Database>({
    dialect: new ZoteroDialect({
      db_name: "demo",
    })
  });
};

export const migrateDB = async (db: Kysely<Database>) => {
  const migrator = new Migrator({
    db,
    provider: migrationProvider,
  });
  let { error } = await migrator.migrateToLatest();
  if (error) throw error;
};

export const createDB = async (): Promise<Kysely<Database>> => {
  let db = initDB();
  migrateDB(db);
  return db
}

Integrating with bootstrap plugins

You'll probably want to migrate the database to create/update it when installing, and initialize it on startup!

src/bootstrap.ts

import { createDB, initDB } from './db'

export async function install(): Promise<void> {
  // ...
  createDB();
  // ...
}

export async function startup({id, version, resourceURI, rootURI = resourceURI.spec}){
  // ...
  // actually probably have this in your DemoPlugin.init() method
  // and not literally here, but for the sake of illustrating timing...
  let Demo = new DemoPlugin();
  Demo.db = initDB();
  Zotero.Demo = Demo;
  // ...
}

Create/Read Data

Now you're just using kysely normally!

async function insert(db: Kysely<Database>) {
  db
    .insertInto("a_table")
    .values({cool_value: "hey"})
    .returningAll()
    .executeTakeFirstOrThrow();
}

async function select(db: Kysely<Database>) {
  return await db
    .selectFrom("a_table")
    .where("cool_value", "=", "hey")
    .select(['id', 'cool_value'])
    .execute();
}

Zotero Schema Models

Zotero schema models are included in the models module and can be used like this

import { models } from "kysely-zotero-dialect";

const zotero_db = new Kysely<models.DB>({
  dialect: new ZoteroDialect({db_name: 'zotero'}),
});

which allows you to make type-safe queries directly to the Zotero database tables, however this is not recommended since using the zotero database directly is substantially more complicated and error-prone than using the Zotero API.

These models are not guaranteed to be up to date, though they do include a MODEL_VERSIONS const that should allow you to check if they are if you want to use them or PR an update to them.

Caveats

Return Object Types

Amazingly, the Mozilla XUL Sqlite3 driver's mozIStorageRow object can't return the names of selected columns, and Zotero wraps them in a proxy object rather than properly handling the query.

We attempt to rescue this by introspecting the query, but as a result we are unable to infer columns from a selectAll() (aka SELECT * FROM table) query. When a selectAll() query is used, we return the zotero proxy object, which can select columns if they are known in advance, but otherwise does not have any other Object methods aside from get.

Development

Development Status

This software is in PERPETUAL ALPHA:

We do not guarantee any functionality, nor do we claim to be particularly good at writing javascript. Hell, we don't even know how to test the code because it all runs within Zotero.

PRs are welcome to fix whatever you want, and you are welcome to open any issues as a place of discussion, but unless it's very obvious to us how to fix something, an issue without a PR will likely be left open.

Generating Models

Use the generate-models script :)

Before you do, you should update Zotero and create a fresh profile:

/location/of/zotero -P

and then pass the location of the created database to the script

yarn run generate-models --db /location/of/zotero.sqlite

or call it with python directly from the repo root

python scripts/generate_models.py --db /location/of/zotero.sqlite