Package Exports

libsqlx
libsqlx/lib/src/index.js

This package does not declare an exports field, so the exports above have been automatically detected and optimized by JSPM instead. If any package subpath is missing, it is recommended to post an issue to the original package (libsqlx) to support the "exports" field. If that is not possible, create a JSPM override to customize the exports field for this package.

Readme

🏖️ libSQLx

A lightweight wrapper around @libsql/client that adds developer-friendly features like migrations, smarter error handling, query logging, sanitization, and more. Perfect for debugging, monitoring, and improving developer experience when working with libSQL.

Install - Get started using libSQLx
Quickstart - A simple example of how to use libSQLx
Features - The features of libSQLx
- Migrations - Managing database migrations
- Query Logging - Logging queries and their parameters
- Error Handling - Catching and handling errors
- Argument Cleaning - Removing unused arguments
- Utility Functions - Making working with libSQL easier
  - queryString - Join a list of strings into a single string
  - paramterize - Take a list and return an object with its sql placeholder names
  - Jsonify - Convert a string to a JSON object
  - sanitizeLike - Sanitize a string to remove characters that would break glob patterns
  - sanitizeSqlPath - Fully sanitize a string down to only alphanumeric characters

Install

npm install libsqlx

Quickstart

The example below sets up a basic libSQL connection, and handles logging and errors

import { createClient } from "libsqlx";

export const db = createClient({
  url: "https://db.libsql.com",
  authToken: process.env.AUTH_TOKEN,
  syncInterval: 60000,
  onQueryError: (err) => {
    console.error(err);
  },
  onQueryLog: (log) => {
    console.debug(log);
  },
});

await db.batch(
  [
    "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)",
    {
      sql: "INSERT INTO users(name) VALUES (:name)",
      args: { name: "John" },
    },
  ],
  "write"
);

await db.execute({
  sql: "SELECT * FROM users WHERE id = :id",
  args: { id: 1, excessValue: true },
});

Features

Migrations

libSQLx provides a simple way to manage database migrations. It can be used to apply changes to a database, and to rollback changes if needed. You can use migrations in any way you'd like, but we recommend setting up a migrations folder.

Create a migrations/index.ts file in your project
Adding the following to your migrations/index.ts file will allow you to use the libSQLx migration cli.

import { libSqlMigrationCli } from "libsqlx";

libSqlMigrationCli({
  migrationPath: __dirname + "/migrations",
  url: "https://db.example.com",
  authToken: "optional auth token",
})
```

3. The simplest way to install migrations is to install `tsx` as a dev dependency, and add the following to your `package.json`
```json
{
  "scripts": {
    "migrate": "tsx src/migrations/index.ts",
  }
}

To create a new migration, run npm run migrate new --name "create_users_table"
To run migrations, run npm run migrate up
To rollback migrations, run npm run migrate down

Thats it!

Query Logging

When executing a parameterized query, it can be hard to debug what the query actually looks like. It can be extremely time consuming to manually inspect the query string. libSQLx exposes the logQuery:true|false parameter when executing a query. This flag works alongside the onQueryLog: (log: string) => void parameter passed when creating a new client.

Example:

import { createClient } from "libsqlx";

export const db = createClient({
  url: "https://db.libsql.com",
  authToken: process.env.AUTH_TOKEN,
  // Setup logging to log any query with `logQuery: true`
  onQueryLog: (log) => {
    console.debug(log);
  },
});

// This query will be logged as `SELECT * FROM users WHERE id = 1`
await db.execute({
  logQuery: true,
  sql: "SELECT * FROM users WHERE id = :id",
  args: { id: 1, excessValue: true },
});

// This query will not be logged
await db.execute({
  sql: "SELECT * FROM users WHERE id = :id",
  args: { id: 1, excessValue: true },
});

Error Handling

libSQL throws exceptions when they occur. This can include errors from libSQL itself, malformed queries, or an entire host of other unpredictable errors. libSQLx provides a simple way to handle errors and manage them without having to try/catch them. The onQueryError: (err: LibsqlError) => void parameter is passed when creating a new client.

Example:

import { createClient } from "libsqlx";

export const db = createClient({
  url: "https://db.libsql.com",
  authToken: process.env.AUTH_TOKEN,
  // Log the error any time an issue occurs
  onQueryError: (err) => {
    console.error(err);
  },
});

// This query will throw an error, but it will be caught by the error handler
await db.execute({
  sql: "SELECT 1 WHERE id = :id FROM users,",
  args: { id: 1, excessValue: true },
});

Argument Cleaning

libSQL will throw an error if an argument is not used in the query, but libSQLx gets in front of this error, and removes any excess arguments from the object. This is done to remove the need for messy conditionals when working with parameterized queries.

Example:

import { createClient } from "libsqlx";

export const db = createClient({
  url: "https://db.libsql.com",
  authToken: process.env.AUTH_TOKEN,
});

// This query will work just fine, the excess argument will be removed by libSQLx
await db.execute({
  sql: "SELECT * FROM users WHERE id = :id",
  args: { id: 1, excessValue: true },
});

Utility Functions

libSQLx provides a few utility functions to make working with libSQL a little easier.

`queryString`

Returns a single string for SQL queries, allowing for better formatting and readability.

import { queryString } from "libsqlx";

await db.execute({
  sql: queryString("SELECT firstName, lastName", "FROM users", "WHERE id = :id", "  AND email = :email"),
  args: { id: 1, email: "johndoe@gmail.com" },
});

// Returns
("SELECT * FROM users WHERE id = :id");

`paramterize`

Takes an array of values and returns an object with its sql placeholder names, and its prepared arguments. Perfect for IN (...) queries.

import { paramterize } from "libsqlx";

const { args, placeholders } = paramterize("id", [1, 2, 3]);

await db.execute({
  sql: queryString("SELECT * FROM users WHERE id IN (", placeholders, ")"),
  args: args,
});

`Jsonify`

Similar to parsing row results using String(), Number(), Date(), etc. This function will parse a column as JSON, where it is either parsed as an object, or as null

import { Jsonify } from "libsqlx";

const results = await db.execute("SELECT * FROM users");

return results.map((result) => ({
  id: Number(result["id"]),
  name: String(result["name"]),
  // Parse the users address object as JSON
  address: Jsonify(result["address"]),
}));

`sanitizeLike`

When using LIKE in a SQL query, you need to pass the wildcards as a part of the parameter. This function will sanitize the parameter to escape any other wildcards from the string.

import { sanitizeLike } from "libsqlx";

await db.execute({
  sql: "SELECT * FROM users WHERE name LIKE :name",
  args: { name: sanitizeLike("foo%bar") },
});

`sanitizeSqlPath`

JSON paths cannot be parameterized in SQL, so this function will sanitize a string to remove characters that would break the path, or allow for SQL injection. The only characters allowed are alphanumeric characters and underscores.

import { sanitizeSqlPath } from "libsqlx";

const userInputtedValue = "foo';--bar";
const sanitizedPath = sanitizeSqlPath(userInputtedValue);

await db.execute(`SELECT * FROM users WHERE JSON_EXTRACT(metadata, '$.${sanitizedPath}') IS NOT NULL`);

libsqlx

Package Exports

Readme

🏖️ libSQLx

Table of Contents

Install

Quickstart

Features

Migrations

Query Logging

Error Handling

Argument Cleaning

Utility Functions

queryString

paramterize

Jsonify

sanitizeLike

sanitizeSqlPath

`queryString`

`paramterize`

`Jsonify`

`sanitizeLike`

`sanitizeSqlPath`