# sqlite-fts-util

> utility functions to help generate SQLite fts5 schema and indexing

Latest version **1.1.0** (published 2024-06-29) · MIT license · 0 weekly downloads

## Install

```sh
npm install sqlite-fts-util
pnpm add sqlite-fts-util
yarn add sqlite-fts-util
bun add sqlite-fts-util
```

## Health

**Score 30/100 (F)** — status: abandoned.

Positive: has types; esm support; no vulnerabilities; high quality score.

Warnings: low downloads.

Negative: abandoned; low maintenance score.

## Facts

| | |
|---|---|
| Version | 1.1.0 |
| Published | 2024-06-29 |
| First published | 2024-06-08 |
| Weekly downloads | 0 |
| License | MIT |
| TypeScript types | bundled |
| Module format | ESM + CommonJS |
| Dependencies | 1 |
| Unpacked size | 11.5 KB |
| Known vulnerabilities | 0 |
| Install scripts | no |
| GitHub stars | 1 |
| Author | JayJamieson |
| Keywords | SQLite, fts5, turso |

## Links

- npm: https://www.npmjs.com/package/sqlite-fts-util
- Repository: https://github.com/JayJamieson/sqlite-fts-util
- Homepage: https://github.com/JayJamieson/sqlite-fts-util#readme
- Issues: https://github.com/JayJamieson/sqlite-fts-util/issues
- npm.io page: https://npm.io/package/sqlite-fts-util

## Dependencies (1)

- [handlebars](https://npm.io/package/handlebars.md) ^4.7.8

## Recent versions

- 1.1.0 (latest) — 2024-06-29
- 1.0.7 — 2024-06-20
- 1.0.6 — 2024-06-09
- 1.0.5 — 2024-06-08
- 1.0.0 — 2024-06-08

## README

# sqlite-fts-util

Easily configure and create SQLite FTS5 tables. This library is intended to be used as a utility as part of your application. There is a single function `fts5Table` that generates SQLite sql necessary for creating a FTS5 virtual table as well as triggers to keep the table up to date.

## Getting started

Assuming the following table structure:

```sql
CREATE TABLE recipes(
  id INTEGER NOT NULL PRIMARY KEY,
  title TEXT,
  directions TEXT
);
```

We can create the following configuration to create a function that will be able to generate SQL for fts5 virtual table and triggers

```js
import fts5Table from "sqlite-fts-util";

const sqlFTS = fts5Table({
  table: "recipes",
  columns: ["title", "directions"],
});

// render actual sql schema to a string
const sql = sqlFTS();
```

Tokenizer and prefix index options can be optionally configured as needed.

```ts
const sqlFTS = fts5Table({
  table: "recipes",
  columns: ["title", "directions"],
  prefix: [2, 3], // optionally configure prefix index of 2 and 3 char prefixes
  tokenize: "porter ascii", // optionally configure tokenizer options
});
```

### Astro DB

FTS5 sql can be generated from an Astro DB definition, some slight modification to your `db/config.ts` is required to get things to work nicely.

The main changes are to assign a local `TableConfig` variable for your `defineDb` configuration object instead if passing it inline.

```ts
import { column, defineDb, defineTable } from "astro:db";

const Comment = defineTable({
  columns: {
    author: column.text(),
    body: column.text(),
  },
});

// This step is important, otherwise types for fts5TableFromAstroDb don't seem to play nicely
const TableConfig = {
  tables: { Comment },
}

export default defineDb(TableConfig);
```

We can then in a separate file use `fts5TableFromAstroDb` with type safety to configure a SQL generating method for FTS5 DDL.

```ts
import { fts5TableFromAstroDb } from "sqlite-fts-util";

const generateSql = fts5TableFromAstroDb(TableConfig, {
  table: "Comment",
  idColumn: "id",
  columns: ["author", "body"]
});
```

## Facets

Helper functions are available for creating queries that can be used for faceting.

### Column

`makeColumnFacetQuery` will give a query that aggregates on any `TEXT` or `NUMBER` column.

```ts
import {makeColumnFacetQuery} from "sqlite-fts-util/facet";

const columnFacet = makeColumnFacetQuery("title", "recipes")
```

### Date

`makeDateFacetQuery` will give a query that aggregates on a `TEXT` column and parsed by SQLite `date` function.

```ts
import {makeDateFacetQuery} from "sqlite-fts-util/facet";

const dateFacet = makeDateFacetQuery("created_at", "recipes")
```

---
_Source: https://npm.io/package/sqlite-fts-util · Machine-readable twin of the npm.io package page. Health data is recomputed on every publish._