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:

1. empty_sqlite: Creates an empty SQLite database for use in PostgreSQL columns. Example:

   SELECT empty_sqlite();
   

2. query_sqlite: Run SQL queries on SQLite databases stored as binary. Example:

   SELECT * FROM query_sqlite(database, 'SELECT * FROM todos');
   

3. 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';
   

4. 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.