import * as db from "./core" ; import * as s from "./schema" ; ( async ( ) => { await ( async ( ) => { const allTables = [ "appleTransactions" , "authors" , "books" , "emailAuthentication" , "tags" ] ; await db . truncate ( db . pool , allTables , "CASCADE" ) ; await db . insert ( db . pool , "authors" , [ { id : 1 , name : "Jane Austen" , isLiving : false , } , { id : 123 , name : "Gabriel Garcia Marquez" , isLiving : false , } , { id : 456 , name : "Douglas Adams" , isLiving : false , } ] ) ; const insertedBooks = await db . insert ( db . pool , "books" , [ { authorId : 1 , title : "Pride and Prejudice" , } , { authorId : 123 , title : "Love in the Time of Cholera" } ] ) ; db . insert ( db . pool , "tags" , [ { tag : "Spanish" , bookId : insertedBooks [ 1 ] . id } , { tag : "1980s" , bookId : insertedBooks [ 1 ] . id } , ] ) ; } ) ( ) ; await ( async ( ) => { console . log ( '\n=== Simple manual SELECT ===\n' ) ; const authorId = 1 , query = db . sql < s . books . SQL > `
        SELECT * FROM ${ "books" } WHERE ${ { authorId } }` , existingBooks = await query . run ( db . pool ) ; console . log ( existingBooks ) ; } ) ( ) ; await ( async ( ) => { console . log ( '\n=== SELECT with a SQLFragment in a Whereable ===\n' ) ; const authorId = 1 , days = 7 , query = db . sql < s . books . SQL > `
        SELECT * FROM ${ "books" } 
        WHERE ${ { authorId , createdAt : db . sql < s . books . SQL > `
            ${ db . self } > now() - ${ db . param ( days ) } * INTERVAL '1 DAY'` , } }` , existingBooks = await query . run ( db . pool ) ; console . log ( existingBooks ) ; } ) ( ) ; await ( async ( ) => { console . log ( '\n=== Simple manual INSERT ===\n' ) ; const newBook = { authorId : 123 , title : "One Hundred Years of Solitude" , } , query = db . sql < s . books . SQL > `
        INSERT INTO ${ "books" } (${ db . cols ( newBook ) })
        VALUES (${ db . vals ( newBook ) })` , insertedBooks = await query . run ( db . pool ) ; console . log ( insertedBooks ) ; } ) ( ) ; await ( async ( ) => { console . log ( '\n=== Many-to-one join (each book with its one author) ===\n' ) ; type bookAuthorSQL = s . books . SQL | s . authors . SQL | "author" ; type bookAuthorSelectable = s . books . Selectable & { author : s . authors . Selectable ; } ; const query = db . sql < bookAuthorSQL > `
        SELECT ${ "books" }.*, to_jsonb(${ "authors" }.*) as ${ "author" }
        FROM ${ "books" } JOIN ${ "authors" } 
          ON ${ "books" }.${ "authorId" } = ${ "authors" }.${ "id" }` , bookAuthors = await query . run ( db . pool ) ; console . log ( bookAuthors ) ; } ) ( ) ; await ( async ( ) => { console . log ( '\n=== One-to-many join (each author with their many books) ===\n' ) ; type authorBooksSQL = s . authors . SQL | s . books . SQL ; type authorBooksSelectable = s . authors . Selectable & { books : s . books . Selectable [ ] ; } ; const query = db . sql < authorBooksSQL > `
        SELECT ${ "authors" }.*, coalesce(json_agg(${ "books" }.*) filter (where ${ "books" }.* is not null), '[]') AS ${ "books" }
        FROM ${ "authors" } LEFT JOIN ${ "books" } 
          ON ${ "authors" }.${ "id" } = ${ "books" }.${ "authorId" }
        GROUP BY ${ "authors" }.${ "id" }` , authorBooks = await query . run ( db . pool ) ; console . dir ( authorBooks , { depth : null } ) ; } ) ( ) ; await ( async ( ) => { console . log ( '\n=== Alternative one-to-many join (using LATERAL) ===\n' ) ; type authorBooksSQL = s . authors . SQL | s . books . SQL ; type authorBooksSelectable = s . authors . Selectable & { books : s . books . Selectable [ ] ; } ; const query = db . sql < authorBooksSQL > `
        SELECT ${ "authors" }.*, bq.* 
        FROM ${ "authors" } CROSS JOIN LATERAL (
          SELECT coalesce(json_agg(${ "books" }.*), '[]') AS ${ "books" }
          FROM ${ "books" }
          WHERE ${ "books" }.${ "authorId" } = ${ "authors" }.${ "id" }
        ) bq` , authorBooks = await query . run ( db . pool ) ; console . dir ( authorBooks , { depth : null } ) ; } ) ( ) ; await ( async ( ) => { console . log ( '\n=== Two-level one-to-many join (using LATERAL) ===\n' ) ; type authorBookTagsSQL = s . authors . SQL | s . books . SQL | s . tags . SQL ; type authorBookTagsSelectable = s . authors . Selectable & { books : ( s . books . Selectable & { tags : s . tags . Selectable [ 'tag' ] ; } ) [ ] ; } ; const query = db . sql < authorBookTagsSQL > `
        SELECT ${ "authors" }.*, bq.*
        FROM ${ "authors" } CROSS JOIN LATERAL (
          SELECT coalesce(jsonb_agg(to_jsonb(${ "books" }.*) || to_jsonb(tq.*)), '[]') AS ${ "books" }
          FROM ${ "books" } CROSS JOIN LATERAL (
            SELECT coalesce(jsonb_agg(${ "tags" }.${ "tag" }), '[]') AS ${ "tags" } 
            FROM ${ "tags" }
            WHERE ${ "tags" }.${ "bookId" } = ${ "books" }.${ "id" }
          ) tq
          WHERE ${ "books" }.${ "authorId" } = ${ "authors" }.${ "id" }
        ) bq` , authorBookTags = await query . run ( db . pool ) ; console . dir ( authorBookTags , { depth : null } ) ; } ) ( ) ; await ( async ( ) => { console . log ( '\n=== Querying a subset of fields ===\n' ) ; const bookCols = < const > [ 'id' , 'title' ] ; type BookDatum = s . books . OnlyCols < typeof bookCols > ; const query = db . sql < s . books . SQL > `SELECT ${ db . cols ( bookCols ) } FROM ${ "books" }` , bookData = await query . run ( db . pool ) ; console . log ( bookData ) ; } ) ( ) ; await ( async ( ) => { console . log ( '\n=== Shortcut functions ===\n' ) ; const authorId = 123 , existingBooks = await db . select ( db . pool , "books" , { authorId } ) ; console . log ( existingBooks ) ; const allBookTitles = await db . select ( db . pool , "books" , undefined , { columns : [ 'title' ] } ) ; console . log ( allBookTitles ) ; const lastButOneBook = await db . selectOne ( db . pool , "books" , { authorId } , { order : [ { by : "createdAt" , direction : "DESC" } ] , offset : 1 } ) ; console . log ( lastButOneBook ) ; const savedBooks = await db . insert ( db . pool , "books" , [ { authorId : 123 , title : "One Hundred Years of Solitude" , } , { authorId : 456 , title : "Cheerio, and Thanks for All the Fish" , } ] ) ; console . log ( savedBooks ) ; const fishBookId = savedBooks [ 1 ] . id , properTitle = "So Long, and Thanks for All the Fish" , [ updatedBook ] = await db . update ( db . pool , "books" , { title : properTitle } , { id : fishBookId } ) ; console . log ( updatedBook ) ; } ) ( ) ; await ( async ( ) => { console . log ( '\n=== Shortcut UPDATE with a SQLFragment in an Updatable ===\n' ) ; const email = "me@privacy.net" ; await db . insert ( db . pool , "emailAuthentication" , { email } ) ; await db . update ( db . pool , "emailAuthentication" , { consecutiveFailedLogins : db . sql `${ db . self } + 1` , lastFailedLogin : db . sql `now()` , } , { email } ) ; } ) ( ) ; await ( async ( ) => { console . log ( '\n=== Shortcut UPSERT ===\n' ) ; await db . insert ( db . pool , "appleTransactions" , { environment : 'PROD' , originalTransactionId : '123456' , accountId : 123 , latestReceiptData : "5Ia+DmVgPHh8wigA" , } ) ; const newTransactions = [ { environment : 'PROD' , originalTransactionId : '123456' , accountId : 123 , latestReceiptData : "TWFuIGlzIGRpc3Rp" , } , { environment : 'PROD' , originalTransactionId : '234567' , accountId : 234 , latestReceiptData : "bmd1aXNoZWQsIG5v" , } ] , result = await db . upsert ( db . pool , "appleTransactions" , newTransactions , [ "environment" , "originalTransactionId" ] ) ; console . log ( result ) ; } ) ( ) ; await ( async ( ) => { console . log ( '\n=== Transaction ===\n' ) ; const email = "me@privacy.net" , result = await db . transaction ( db . Isolation . Serializable , async ( txnClient ) => { const emailAuth = await db . selectOne ( txnClient , "emailAuthentication" , { email } ) ; return db . update ( txnClient , "emailAuthentication" , { consecutiveFailedLogins : db . sql `${ db . self } + 1` , lastFailedLogin : db . sql `now()` , } , { email } ) ; } ) ; console . log ( result ) ; } ) ( ) ; await db . pool . end ( ) ; } ) ( ) ;