Insert N rows to database using SQLC | Discord Gophers | Page 1

sly arrow Oct 2, 2023, 11:34 AM

#

Hello. I have a REST API that uses SQLC to query the database. After exploring the docs for SQLC and more, I couldn't find a way to insert N rows to the DB other than calling the single insert query N times, which will send N calls to the DB :(

strange pond Oct 2, 2023, 11:52 AM

#

Hey, this depends on your database and driver

#

What database are you using?

#

@sly arrow https://docs.sqlc.dev/en/stable/howto/insert.html#using-copyfrom This is the relevant section

sly arrow Oct 2, 2023, 11:55 AM

#

strange pond <@555580364068880414> https://docs.sqlc.dev/en/stable/howto/insert.html#using-co...

I've seen the docs for copyfrom. From what I've read, isn't it just a faster way to insert?

sly arrow Oct 2, 2023, 11:55 AM

#

strange pond Hey, this depends on your database and driver

I'm using PostgreSQL with the lib/pq driver

strange pond Oct 2, 2023, 11:55 AM

#

It let's you insert multiple rows more efficiently than looping

sly arrow Oct 2, 2023, 11:56 AM

#

but I have to loop anyways? the docs still shows inserting a singular row

#

nice username btw :P

strange pond Oct 2, 2023, 11:57 AM

#

No, look at the generated function signature

#

It accepts a slice of arguments

sly arrow Oct 2, 2023, 11:57 AM

#

😳 I missed that

#

So, I can use copyfrom for inserting both single and multiple rows?

strange pond Oct 2, 2023, 11:58 AM

#

I think it's redundant for single row.
Will be faster with multiple rows

#

Though I'm not sure if lib/pq supports that. Might be just pgx.
Should try anyways

sly arrow Oct 2, 2023, 11:59 AM

#

Well, I asked this so that I can avoid having 2 queries: 1 for single row, and one for multiple

#

and the difference in both is just :exec vs :copyfrom

sly arrow Oct 2, 2023, 11:59 AM

#

strange pond Though I'm not sure if lib/pq supports that. Might be just pgx. Should try anywa...

interesting

strange pond Oct 2, 2023, 11:59 AM

#

Yeah I think you can use copyfrom for both single and multi

sly arrow Oct 2, 2023, 11:59 AM

#

Cool, trying

#

Getting a weird error:database/queries/club/member.sql:1:1: :copyfrom doesn't support non-parameter values

#

My query:

-- name: CreateClubMember :copyfrom
INSERT INTO club_member (
    club_name, roll_number, position, extra_groups, comments
)
VALUES (
    (SELECT c.name FROM club AS c WHERE c.name = $1 OR c.alias = $1),
    $2,
    $3,
    $4,
    $5
);

#

cc @strange pond

strange pond Oct 2, 2023, 12:08 PM

#

Not sure, probably postgres doesn't allow copyfrom for complex inserts like this

sly arrow Oct 2, 2023, 12:08 PM

#

this error is from running sqlc generate

#

I didn't run Go yet

strange pond Oct 2, 2023, 12:09 PM

#

Yes, but sqlc knows about postgres

#

So if postgres doesn't allow, sqlc wont either

sly arrow Oct 2, 2023, 12:09 PM

#

So, you're saying that the club_name param is the issue?

#

Yeah, I removed it and got a new error:
error generating code: :copyfrom is only supported by pgx and github.com/go-sql-driver/mysql

#

Damn :(

strange pond Oct 2, 2023, 12:10 PM

#

If you're not married to libpq move to pgx

sly arrow Oct 2, 2023, 12:10 PM

#

is the shift from lib/pq to pgx easy?

strange pond Oct 2, 2023, 12:10 PM

#

Should be simple switch, if you don't do anything fancy

sly arrow Oct 2, 2023, 12:11 PM

#

strange pond If you're not married to libpq move to pgx

Well, there an unrelated bug in lib/pq affecting my code, so I'm happy to leave lib/pq lol

sly arrow Oct 2, 2023, 12:11 PM

#

strange pond Should be simple switch, if you don't do anything fancy

I don't

strange pond Oct 2, 2023, 12:11 PM

#

The docs I sent show how to specify pgx as driver

sly arrow Oct 2, 2023, 12:11 PM

#

Right.

#

I have to change the driver within my Go code as well, right?

#

I doubt I can use pgx for SQLC and lib/pq for the rest of my code

strange pond Oct 2, 2023, 12:12 PM

#

dunno your code

sly arrow Oct 2, 2023, 12:12 PM

#

yeah I don't use lib/pq anywhere lol 💀

#

_ "github.com/lib/pq" this is all I have

strange pond Oct 2, 2023, 12:12 PM

#

sunturtle

sly arrow Oct 2, 2023, 12:23 PM

#

strange pond The docs I sent show how to specify pgx as driver

Got a little further, but errored out again:

breadboard (main) λ sqlc generate
// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.21.0

package .sqlc-auto-gen

import (

        "context"


        "github.com/jackc/pgx/v5"
        "github.com/jackc/pgx/v5/pgconn"


)





type DBTX interface {
        Exec(context.Context, string, ...interface{}) (pgconn.CommandTag, error)
        Query(context.Context, string, ...interface{}) (pgx.Rows, error)
        QueryRow(context.Context, string, ...interface{}) pgx.Row
        CopyFrom(ctx context.Context, tableName pgx.Identifier, columnNames []string, rowSrc pgx.CopyFromSource) (int64, error)
}

func New(db DBTX) *Queries {
        return &Queries{db: db}
}

type Queries struct {
    
        db DBTX
    
}


func (q *Queries) WithTx(tx pgx.Tx) *Queries {
        return &Queries{
                db: tx,
        }
}






# package .sqlc-auto-gen
error generating code: source error: 5:9: expected 'IDENT', found '.'

#

I wonder why my stdout was attacked

#

My sqlc.yaml:

version: 2
sql:
  - engine: "postgresql"
    queries:
      - "./database/queries"
      - "./database/queries/club"
    schema:
      # These are listed separately so that the tables are created in a preferred order
      - "./database/schemas/student.sql"
      - "./database/schemas/faculty.sql"
      - "./database/schemas/guild.sql"
      - "./database/schemas/club.sql"
      - "./database/schemas/course.sql"
      - "./database/schemas/announcement.sql"
    gen:
      go:
        package: ".sqlc-auto-gen"
        out: ".sqlc-auto-gen"
        sql_package: "pgx/v5"
        emit_json_tags: true
        json_tags_case_style: "snake"

strange pond Oct 2, 2023, 12:26 PM

#

Was your package and out always ".sqlc-auto-gen"?

#

Or did you mean ./

sly arrow Oct 2, 2023, 12:28 PM

#

strange pond Was your package and out always ".sqlc-auto-gen"?

Well, I used to use version: 1 of the yaml file

#

oh you meant the preceding .

strange pond Oct 2, 2023, 12:28 PM

#

What do you want the package to be

sly arrow Oct 2, 2023, 12:28 PM

#

pgx?

#

wait

strange pond Oct 2, 2023, 12:28 PM

#

I mean the generated

sly arrow Oct 2, 2023, 12:29 PM

#

Ah!

#

That's the package name that shows up as package foo

#

This worked```yml
gen:
go:
package: "database"
out: ".sqlc-auto-gen"

#

I couldn't understand the error

strange pond Oct 2, 2023, 12:30 PM

#

Not sure why you have the dot there in out

sly arrow Oct 2, 2023, 12:31 PM

#

hidden folder convention

strange pond Oct 2, 2023, 12:31 PM

#

Why hidden

sly arrow Oct 2, 2023, 12:31 PM

#

I don't push those files to Git

strange pond Oct 2, 2023, 12:31 PM

#

You should

sly arrow Oct 2, 2023, 12:31 PM

#

Why?

strange pond Oct 2, 2023, 12:31 PM

#

Because they're part of your code

#

I guess personal preference

#

I always push generated files

sly arrow Oct 2, 2023, 12:32 PM

#

node_modules are also a part of my code, you don't push it yeah

sly arrow Oct 2, 2023, 12:32 PM

#

strange pond I guess personal preference

yes, either way is fine

#

I used to push the auto gen files too

strange pond Oct 2, 2023, 12:32 PM

#

But node modules are dependencies

sly arrow Oct 2, 2023, 12:33 PM

#

but I don't really need version control on them

#

as long as I'm using the same SQLC version, those files are the same through generating

strange pond Oct 2, 2023, 12:33 PM

#

This just prevents situations where someone else generates and gets different files due to tool difference

sly arrow Oct 2, 2023, 12:33 PM

#

that's where Nix comes in

#

I have SQLC pinned to 1.21 in my flake.nix

strange pond Oct 2, 2023, 12:33 PM

#

Ah

#

Well, good luck then

sly arrow Oct 2, 2023, 12:34 PM

#

haha thanks

#

database/queries/club/member.sql:1:1: :copyfrom doesn't support non-parameter values
back to this ig :(

sly arrow Oct 2, 2023, 12:35 PM

#

sly arrow My query: ```sql -- name: CreateClubMember :copyfrom INSERT INTO club_member ( ...

I need a way to have the select here run

#

I wonder if I can abstract it out to another query and reference that thinkingo

#

I guess I'll just ditch allowing the user to supply the name or alias and keep it to name only.

sly arrow Oct 2, 2023, 3:22 PM

#

Weird, after finally resolving all of the errors, my API returns 404 for every object that I have

#

I haven't touched the queries at all

#

just updated the variables to match pgx

#

rest everything in the project is the same

#

getting pgx.ErrNoRows whenever I fetch

#

oh perhaps it didn't link to my DB correctly gopherthinking

#

yup, that was it. worked

#

thanks a lot sha2048. I got everything working

strange pond Oct 2, 2023, 3:28 PM

#

sunturtle

sly arrow Oct 3, 2023, 5:31 PM

#

@strange pond does pgx not support JSON?

#

my APIs that return arrays and jsons broke

#

my row struct is now:


type GetClubRow struct {
    Name             string   `json:"name"`
    Alias            string   `json:"alias"`
    Category         string   `json:"category"`
    ShortDescription string   `json:"short_description"`
    Email            string   `json:"email"`
    IsOfficial       bool     `json:"is_official"`
    Description      []byte   `json:"description"`
    Admins           []byte   `json:"admins"`
    Branch           []string `json:"branch"`
    Faculties        []byte   `json:"faculties"`
    Socials          []byte   `json:"socials"`
}

#

the []byte used to be json.RawMessage

#

type GetClubRow struct {
    Name             string          `json:"name"`
    Alias            string          `json:"alias"`
    Category         string          `json:"category"`
    ShortDescription string          `json:"short_description"`
    Email            string          `json:"email"`
    IsOfficial       bool            `json:"is_official"`
    Description      json.RawMessage `json:"description"`
    Admins           json.RawMessage `json:"admins"`
    Branch           []string        `json:"branch"`
    Faculties        json.RawMessage `json:"faculties"`
    Socials          json.RawMessage `json:"socials"`
}

#Insert N rows to database using SQLC