sqltypescriptpostgresqlnext.js

How can I insert multiple entries in my database with a single function?


I'm creating a trading card app using Next.js. I want my users to be able to add and remove cards from their collections via forms on the site.

I have a Neon database with three tables: users, cards, and collections. Each entry in collections contains a user_id, card_id, and amount. The id's are Foreign Key references to the other tables. The function below is expected to create an entry in the collections table for each card, linking it to the given user and setting the amount to 1.

export async function giveAllCards(
    prevState: TransferState,
    formData: FormData
) {
    const userId = formData.get('user');
    try {
        const cards = await fetchAllCards();
        Promise.all(cards.map((c) => {
            const q = `INSERT INTO fates_gambit.collections (user_id, card_id, amount)
                VALUES (${userId}, ${c.id}, 1)`;
            return new Promise(resolve => sql`${q}`);
        }));
        return prevState;
    } catch(error) {
        return prevState;
    }
}

I don't get any errors, it just doesn't work. I can confirm that userId is passed correctly, and fetchAllCards correctly returns an array containing all cards in the database with corresponding ids.

I have other functions that manipulate the database successfully - fetching data and creating entries in both of the other tables. I've tried taking the results of console.log(q) and pasting them directly into the SQL editor, and the query runs successfully. I originally had the query on the same line as the Promise - not a separate variable - but it didn't work that way either. I've also tried putting quotes around the userId, but still nothing.


Solution

  • I made it work. First, it's important to validate the data. The code in my question leaves a chance for the userId to be undefined, which causes the sql Promise to reject. Beyond that, wrapping the sql callback inside a separate Promise seems to cause issues because it returns a Promise of its own. The following code accomplished my goal:

    import { z } from "zod";
    import postgres from "postgres";
    //...
    
    const TransferSchema = z.object({
        userId: z.string().uuid() // ensure proper formatting
    })
    
    const sql = postgres(process.env.POSTGRES_URL!, {ssl: 'require'});
    
    export async function giveAllCards(
        prevState: TransferState,
        formData: FormData
    ) {
        const validatedFields = TransferSchema.safeParse({
            userId: formData.get('user')
        });
        const state = prevState;
    
        // returning here ensures that userId has a value
        if(!validatedFields.success) {
            state.errors = validatedFields.error.flatten().fieldErrors;
            state.message = 'Missing User Id. Failed to assign collection.';
            return state;
        }
    
        const { userId } = validatedFields.data;
    
        try {
            const cards = await fetchAllCards();
            await Promise.all(cards.map((c) => {
                // no extra promise needed, since postgres already returns a promise
                return sql`INSERT INTO fates_gambit.collections (user_id, card_id, amount)
                            VALUES (${userId}, ${c.id}, 1)`;
            }))
        } catch(error) {
            console.log(error);
            return {
                message: "Database Error: Failed to assign collection."
            }
        }
    
        revalidatePath('/home/cards');
        redirect('/home/cards');
    }