pglite fusion — Seamlessly integrate SQLite databases…

Pitch

Description

Comments

About this project

pglite-fusion allows you to embed an SQLite database directly within your PostgreSQL tables. This innovative solution simplifies the complexities of multitenancy, enabling you to manage multiple databases effortlessly within a single PostgreSQL instance, while maintaining the integrity and performance you expect.

pglite-fusion is an innovative extension that allows you to seamlessly integrate SQLite databases within your PostgreSQL tables, providing a powerful solution for handling multitenancy. By embedding an SQLite database in a PostgreSQL column, you can manage tenant-specific data more efficiently than ever before.

Key Features:

Multitenancy Solved: Effortlessly manage separate tenants with embedded SQLite databases.
Easy Integration: Simply load the extension and create tables with SQLite columns to get started.

Example Usage:

To utilize pglite-fusion, begin by loading the PG extension and creating a table with an SQLite column:

-- Load PG extension
CREATE EXTENSION pglite_fusion;

-- Create table with an SQLite column
CREATE TABLE people (
    name     TEXT NOT NULL,
    database SQLITE DEFAULT execute_sqlite(
        empty_sqlite(),
        'CREATE TABLE todos (task TEXT)'
    )
);

Insert data into your table like this:

-- Insert a row into the people table
INSERT INTO people VALUES ('frectonz');

-- Create a todo for "frectonz"
UPDATE people
SET database = execute_sqlite(
    database,
    'INSERT INTO todos VALUES (''solve multitenancy'')'
)
WHERE name = 'frectonz';

-- Fetch frectonz's info from db
SELECT 
    name, 
    (
        SELECT json_agg(get_sqlite_text(sqlite_row, 0))
        FROM query_sqlite(
            database, 
            'SELECT * FROM todos'
        )
    ) AS todos
FROM 
    people 
WHERE 
    name = 'frectonz';

API Documentation:

empty_sqlite: Creates an empty SQLite database for use in PostgreSQL columns. Example:
```
SELECT empty_sqlite();
```
query_sqlite: Run SQL queries on SQLite databases stored as binary. Example:
```
SELECT * FROM query_sqlite(database, 'SELECT * FROM todos');
```

execute_sqlite: Perform SQL operations on the SQLite database. Example:

UPDATE people
SET database = execute_sqlite(
    database,
    'INSERT INTO todos VALUES (''solve multitenancy'')'
)
WHERE name = 'frectonz';

Get Data Values: Functions to extract text, integer, or real values from SQLite query results:
- get_sqlite_text
- get_sqlite_integer
- get_sqlite_real

Enhance your multitenancy solutions today with pglite-fusion, merging the flexibility of SQLite with the robust capabilities of PostgreSQL in a straightforward and effective manner.