Would be nice to have a bulk insert or update function.

Hello, how about make many-to-many more flexible by support some more customizing tags?

I have some tables for example stores and table consumers, which is related each others as many to many. But in some reason, consumers has field card_id and mapping table for this relationship should be connected by table cards_stores or transactions, using card_id and store_id.
In this case, if I can override keys id to card_id and/or consumer_id to card_id, it is possible that I make relationship between this two models directly. (without make model card and two step relationships)

for example,

SELECT name FROM stores WHERE id IN (
    SELECT store_id FROM cards_stores WHERE card_id = "0000-0000-0000-0000"
)

If I can override modelColumnID like columnFieldID, or

SELECT name FROM consumers WHERE card_id IN (
    SELECT card_id FROM cards_stores WHERE store_id = "000-00-000000"
)

If I can override id in the code reference above.

How do you think about this idea? If you feel good, I will try to make a PR for it.

For the more, In my opinion, we can consider about custom raw query per the relationship. It will be helpful for more cases.

When i run:

$ soda g model People first_name:string last_name:string email:string

Inside models/people.go i can see it sets

....
type People struct {
...
...
// Peoples is not required by pop and may be deleted
type Peoples []People
...

And it should be

...
// Peoples is not required by pop and may be deleted
type People []Person
...

If i run (notice lowercase in the model name)

soda g model people first_name:string last_name:string email:string

it generates

....
type Person struct {
...
...
// Persons is not required by pop and may be deleted
type Persons []People
...

Which is closer but still not correct.

Migrator makes use of fmt.Printf which makes it hard to mute. It's currently flooding parts of our outputs when we don't want it. I would prepare a pull request but it depends on the type of interface you would want to replace it if you want to replace it

https://github.com/gobuffalo/pop/blob/master/migrator.go#L66

The ".Last()" function uses the following statement:

SELECT table.column, ...
FROM table AS alias 
ORDER BY id desc LIMIT 1 

Because fizz uses UUIDs as ids by default, ordering by id wil yield a random result. It should order by created_at

Fizz supports running external commands with exec (https://github.com/gobuffalo/pop/tree/master/fizz#execute-an-external-command).

I would expect that if the command doesn't exit cleanly, the migration would fail. Instead, it continues on as if nothing was wrong. This is preventing me from using this functionality in any meaningful way.

realized it didnt work...

Resurrecting https://github.com/markbates/pop/issues/113 and https://github.com/markbates/pop/pull/114 as this is still an open issue:

You should quote column names when executing an insert query (create, save).
If you have a column named 'end', the execution will fail with pq: syntax error at or near "end".

So,

INSERT INTO mytable (created_at, duration, foo, bar) VALUES (:created_at, :duration, :foo, :bar) > returning id
should end up as

INSERT INTO mytable ("created_at", "duration", "foo", "bar") VALUES (:created_at, :duration, :foo,
:bar) returning id
This was tested on latest postgresql.

A proper solution will need to take into account the SQL Dialect. For instance, MySQL requires backticks, rather than quotes.

Hi!

Previously @mclark4386 fixed issue with the this commit. But after upgrading buffalo to v0.11.0, i saw bug fix is missing.

I get errors whenever I use migrate down commands with Postgres and CockroachDB. They typically look like this:

ERRO[0001] Error: problem deleting migration version 20180417021204: pq: syntax error at or near "%"

The statements in the fizz files are just drop table statements. Am I missing something? Feel like I'm doing something wrong since no other open issues exist. Tried with Cockroach 1.1.7 and Postgres 10

Thanks.

It's an common DB in the enterprise and now also available for Linux.

Is this supported?

From the Discussion in Slack:

@timsayshey

Just me or does anyone else think a BeforeFind callback would be nice so we could append to a query? pseudo code use-case, beforeFind() { tx.Where("deleted_at IS NULL") } — This would save me from adding it all over the place

...

@stanislas-m

In this particular case, though, it can be interesting to have a shortcut for that. Soft delete is a common usage.
But it will mean patching the Destroy method too to support this mode.

@markbates

i agree with @stanislas-m , including that soft delete is a common enough use case that pop should definitely support it out of the box.
i feel like if you have a DeletedAt time.Time 'db:"deleted_at"' attribute pop should go into “soft delete” mode for all queries and exec functions. the only thing i’m struggling with is what mechanism to use to ignore deleted_at.

connection.copy function is creating copy of connection c and putting "Store: c.TX", is it correct?

In some cases its creating copy with store as nil, which causes "invalid memory address or nil pointer dereference" error in later steps.

hi,

in rails activerecord, we have this:

has_many :memberships, dependent: :destroy
has_many :memberships, dependent: :delete_all

i think best not todo delete_all as it will possibility causes hanging fruits if there are subtables in membership as it will issue direct sql statement to delete them

but when :destroy is used, then it will call model destroy method and it shall trigger sub table destroy when needed.

but one more concern is, sometimes we do not query the sub table, for example:
We query A without eagerLoad B, but when we destroy A, we wish that B as a child is also queried and destroyed in this scenario.

would love to see if anyone willing todo this?
or any guide on which code i can look for to work this out.

thank you

Hi,

I was wondering if it could be possible when I'm generating my models to specify which kind of migration file (SQL or Fizz) I want through a flag or something like that ?

I think this kind of feature could offer more flexibility in the migration process. For example, when I want to create a composite primary key. I have to use a little workaround : drop the primary key and execute a raw query.

Abstract

For testing purposes, it's quite common to preload some known data in the database. CSV files are a good way to handle data rows, and many data-manipulation tools have a support for CSV extraction.

Implementation

• The first line of the CSV file will contain the column names
• Next lines will contain the data
• Each data row will be converted by the new module to pop SQL inserts.
• The query can be prepared and reused, since it will use the same INSERT query, but with different data.

It is possible to specify custom path for migrations folder via --path flag. It would be neat if there was a way to specify custom path to models folder, when creating model with soda generate model.

Following the Eager creation release, you can't use Eager on a query anymore: you have to use it on a Connection, so this introduces a breaking change.

This also introduces a bug: if you want to reuse a connection, to make multiple queries, the eager options are still attached and the next query tries to use them.

I think these options have to be attached to the Query, instead of the Connection. 😀

it seems to me as pop.Connection.Create should create time.Time fields in UTC time instead of local time.

pop.Connection.Create uses the pop.Model.touch{Created,Updated}At methods to fill these time.Time values, which themselves use time.Now().

according to time.Now documentation, the time.Time value being returned is in local time:

func Now() Time
    Now returns the current local time.

that's fine, but then, when reading back that value from the db, we read it back as if stored in UTC.

So, we should probably modify pop to always generate time.Time values in UTC:

diff --git a/model.go b/model.go
index b1f9db6..96a0802 100644
--- a/model.go
+++ b/model.go
@@ -140,14 +140,14 @@ func (m *Model) setID(i interface{}) {
 func (m *Model) touchCreatedAt() {
        fbn, err := m.fieldByName("CreatedAt")
        if err == nil {
-               fbn.Set(reflect.ValueOf(time.Now()))
+               fbn.Set(reflect.ValueOf(time.Now().UTC()))
        }
 }
 
 func (m *Model) touchUpdatedAt() {
        fbn, err := m.fieldByName("UpdatedAt")
        if err == nil {
-               fbn.Set(reflect.ValueOf(time.Now()))
+               fbn.Set(reflect.ValueOf(time.Now().UTC()))
        }
 }

For an existing database, it is time-consuming to write all the models。
It would be great to be able to generate a structural definition in batches

I ran into trouble when I added an sqlite3 database configuration to my yaml file. Sodawas telling me that no connection with the requested name exists. I remembered sqlite3 support was removed from the default build of buffalo, so I assumed this means the same would be true for soda, so I took the following steps:

• Re-install buffalo with sqlite3 support: go get -u -v -tags sqlite github.com/gobuffalo/buffalo/buffalo
• Use the same flag to update pop: go get -u -v -tags sqlite github.com/gobuffalo/pop/...
• Install soda with these flags: go install -v -tags sqlite github.com/gobuffalo/pop/soda

After these steps, it works as expected.

I'd like to submit a PR to update the README, but I'm not sure which of these steps are actually necessary.

Anko is continuingly breaking, and it’s out of our control. My suggestion is to switch to using Plush for fizz files. This way we have all the same advantages of using Plush, plus controlabilty, and the dropping of another dependency.

To do this, though, I need some some help from the community.

I need the craziest and outrageously complex .fizz files out there. Please post yours so I can start understanding the scale of issues involved with this change.

I’m hoping that we can support existing .fizz files with zero changes to the syntax, but if there are changes that need to be made, we can release a tool to automate the process.

Please post your .fizz files here please. :)

there are times when we do not wish to save belongs_to

https://stackoverflow.com/questions/2231415/does-activerecord-save-a-belongs-to-association-when-saving-main-object

Taken alone, the title might not be a problem; MySQL intentionally disallows the truncation of tables referred to by foreign key constraints.

The problem is in the interaction between the above, and the setup steps taken by buffalo test. In gobuffalo/suite/model.go, the SetupTest() function calls TruncateAll() to ensure no data exists in the test tables prior to the suite's run. This can lead to;

model.go:26:
    Error Trace:    model.go:26
                        suite.go:61
                        suite.go:88
    Error:          Received unexpected error:
                    Error 1701: Cannot truncate a table referenced in a foreign key constraint ...

A (very) naive (and very messy) work-around is to manually call SET GLOBAL FOREIGN_KEY_CHECKS = 0; in the *_test database before running buffalo test, but that might lead to false positives in the test suite.

A potentially less naive fix would be to modify mysql.TruncateAll() to call SET SESSION FOREIGN_KEY_CHECKS = 0; before executing the RawQuery, and re-setting the previous value (be it 0 or 1) after the truncations are complete. I'm hesitant to open a PR implementing this fix for a couple reasons:

1. That might be a very bad idea.
   I'm no MySQL engineer, so I can't say for sure this is a reasonable thing to do. I assume it is, because my read of SetupTest() is that it's dropping all data from the database, so there should be no data left to be orphaned, but... I don't know very MySQL well, so I don't know what corner cases (or not-so-corner cases) that setting might impact.
2. I'm not sure if it would be correct to modify TruncateAll() in that way.
   Again I assume it would be, but I'm not familiar enough with the consumers of Pop to know if that would lead to surprising behavior. If foreign key constraints are set, truncations should fail. Would changing this function cause logic that should fail, to not fail? Would there be a clean way to add an argument or contextual setting to TruncateAll() that would let us control that behavior? Are there any uses of TruncateAll outside of SetupTest() for such a change to affect, anyway?

/shrug

Hi,

I'm looking for a way to change models directory to be inside app/models , is this possible?

Thanks

Using the following:

type Room struct {
    ID       int          `db:"id"`
    // [...]
    Provider RoomProvider `belongs_to:"room_provider"`
}

rooms := models.Rooms{}
q := tx.Eager("Provider")
if err := q.All(&rooms); err != nil {
    return err
}

The SQL produced is 1 query for the main request, then n requests (one for each row) to fetch providers. In the case of 1 to 1 relations, it should use LEFT JOIN, which is more efficient.

More reference about this issue: https://use-the-index-luke.com/sql/join/nested-loops-join-n1-problem

If I use something like WHERE id in (?) and HAVING count(id) = ? at the same time, it does not compiled expectedly.

The line below is called if there is in (?) on SQL which is built by buildSelectSQL(), but there is another ? for HAVING and sqlx.In() returns error "number of bindVars exceeds arguments". (I cannot sure since I just checked it on pop code and did not get into sqlx code anyway.)

IMHO, we can move this lines into buildWhereClauses(). Can I open a PR for this?

A model that implements TableName with schema does not generate correct ToSQL

When running soda migrate -d in a project using Pop, the last line logged out when the schema is dumped can contain the database URL and therefore the username and password used to connect to it:

    "[POP] 2018/02/21 21:53:23 pg_dump -s --dbname=postgres://USERNAME:PASSWORD@host:5432/app?sslmode=disable"

This project is using -d to make the SQL being run against the database in the migrations visible, but ideally we can avoid leaking credentials in order to achieve that.

The code that logs this message is in postgresql.go in DumpSchema, although I imagine that it also affects some of the other database types.

I'd like to address this issue and am planning to open a pull request to do so. My initial thought was to just not print out the database URL and avoid the issue that way. What do you think of that approach?

Hello @markbates,

I saw several version bumps on the commit logs and updated version.so of soda but anyway there is no tags since 4.0.6. I think we need tags so we can use dep correctly. Am I confused on versioning of soda and pop itself?

Pop is a rad package for CRUD on relational databases. Would there be any interest in expanding its scope to non-relational stores? I'm especially interested in MongoDB/CosmosDB and Google Cloud Datastore.

Happy to go over my rationale in more detail, just wanted to float the idea first.

And my feelings won't be hurt if you tell me to get lost if I'm way off here! 😄

example:

class Paper {
  Code Code `json:"code" has_many:"codes"`
}

class Code {
  PaperID uuid `json:"paper_id" db:"paper_id"`
  Paper Paper `json:"paper" belongs_to:"paper"`//if i remove this line, the error wont occur
}

throwing error compilation
invalid recursive type Paper

i suspect its due to PaperID

I want to use user table for my User model or whatever table for MyCustomModel. How to do that?

Hello, I sent PR #9 for following issue. Please consider this PR.

The problem is, After SQL style database creation and dropping was implemented, and accordingly mysql.urlWithoutDb() was added, the method just returns auto-generated URL with user, pass, host, and port. In case of user specifiy connection information as URL, with options, it was ignored and hard coded default options are provided by the method.

In some condition, the default read timeout option (readTimeout=1s) is not enough and it makes operation failure. For example, when dropping existing database, and truncate all data for running test.

In my cases, I met following errors when I run my test cases on travis-ci:

[mysql] 2018/03/02 21:03:11 packets.go:33: read tcp 127.0.0.1:49306->127.0.0.1:3306: i/o timeout
[mysql] 2018/03/02 21:03:12 packets.go:33: read tcp 127.0.0.1:49308->127.0.0.1:3306: i/o timeout
[mysql] 2018/03/02 21:03:13 packets.go:33: read tcp 127.0.0.1:49496->127.0.0.1:3306: i/o timeout

and

--- FAIL: Test_ActionSuite (87.60s)
    --- FAIL: Test_ActionSuite/Test_RolesResource_A_Protected (3.00s)
        Error Trace:    model.go:20
                        suite.go:47
                        suite.go:88
        Error:          Received unexpected error:
                        driver: bad connection
                        <...>/gobuffalo/pop.(*Connection).timeFunc
                        <...>/gobuffalo/pop.(*Query).Exec
                        <...>/gobuffalo/pop.(*mysql).TruncateAll

mainly for TruncateAll, as see above.

After facing the issue, I tested it on my poor laptop, with limited resources, and I met another errors while just dropping the database:

$ buffalo db drop -e test
v4.0.6

[mysql] 2018/03/05 16:16:01 packets.go:36: read tcp 127.0.0.1:37106->127.0.0.1:3306: i/o timeout
<...>

Not for every running, but sometimes.

Anyway, I checked with the codes (from pop to database/sql because I cannot figure out the reason) and I found and fixed the method.

(Sorry for long history but I think it will help some others using mysql.)

INSERT INTO table_name (created_at, from, id, order, table, updated_at) VALUES (:created_at, :from, :id, :order, :table, :updated_at)
pq: syntax error at or near "from"

I also get this error the columns. The only way to get around is, is to quote the query:

INSERT INTO table_name (created_at, "from", id, "order", "table", updated_at) VALUES (:created_at, :from, :id, :order, :table, :updated_at)

change the default character to utf8mb4.

query := fmt.Sprintf("CREATE DATABASE %s DEFAULT COLLATE utf8_general_ci", deets.Database)
↓
query := fmt.Sprintf("CREATE DATABASE %s DEFAULT COLLATE utf8mb4_general_ci", deets.Database)

https://github.com/markbates/pop/blob/f244edc2b8f1335857465ccc9ede287aad6cd753/mysql.go

For reference, when Mysql 8 is released, I would like to use utf8mb4_ja_0900_as_cs.

Reference
http://mysqlserverteam.com/new-collations-in-mysql-8-0-0/
http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf8-support-in-mysql-8-0/
http://mysqlserverteam.com/mysql-8-0-collations-the-devil-is-in-the-details/
http://mysqlserverteam.com/mysql-8-0-1-accent-and-case-sensitive-collations-for-utf8mb4/

Would it make sense to add support for ordering in a join table? Here's a rough idea on how that could be achieved. If this was the model:

type User struct {
    Books Books `many_to_many:"user_books"`
}

... and this was the migration:

create_table("books", func(t) {})
create_table("user_books", func(t) {
    t.Column("book_id", "uuid", {})
    t.Column("user_id", "uuid", {})
    t.Column("ordering", int, {})
})

Pop could look for whether Books implements sort.Interface, do the sort before saving, and populate the ordering column appropriately. When someone does an eager query, pop can sort the resulting Books according to ordering

Technically I think this could be done above pop but then you'd have to manage the join table manually.

Thoughts?

It would be good that pop allows to use the cloud-sql proxy for postgres (details in here)

TruncateAll() in cockroach.go does a query

"select table_name from information_schema.tables where table_schema = ?;"

to gather table names and then joins all those table names for truncation

tx.RawQuery(fmt.Sprintf("truncate %s cascade;", strings.Join(table_names, ", "))).Exec()

The problem occurs when there are actually no tables in the output of select. By default even the empty "coke" application will not run tests because the query for truncation would become:

level=debug msg="truncate  cascade;"

which is an invalid one and it crashes:

created database coke_test
dumped schema for coke_development
loaded schema for coke_test
INFO[0000] go test -p 1 bitbucket.org/dainiookas/coke bitbucket.org/dainiookas/coke/actions bitbucket.org/dainiookas/coke/grifts bitbucket.org/dainiookas/coke
/models
?       bitbucket.org/dainiookas/coke   [no test files]
level=debug msg="select table_name from information_schema.tables where table_schema = $1;" $1=coke_test
[]
level=debug msg="truncate  cascade;"
--- FAIL: Test_ActionSuite (0.01s)
    --- FAIL: Test_ActionSuite/Test_HomeHandler (0.00s)
        model.go:26:
                        Error Trace:    model.go:26
                                                                suite.go:61
                                                                suite.go:88
                        Error:          Received unexpected error:
                                        pq: relation "cascade" does not exist
                                        github.com/gobuffalo/pop.(*Connection).timeFunc
                                                /Users/dainius/Documents/Go/src/github.com/gobuffalo/pop/connection.go:210
                                        github.com/gobuffalo/pop.(*Query).Exec
                                                /Users/dainius/Documents/Go/src/github.com/gobuffalo/pop/executors.go:21
                                        github.com/gobuffalo/pop.(*cockroach).TruncateAll
                                                /Users/dainius/Documents/Go/src/github.com/gobuffalo/pop/cockroach.go:238
                                        github.com/gobuffalo/pop.(*Connection).TruncateAll
                                                /Users/dainius/Documents/Go/src/github.com/gobuffalo/pop/connection.go:202
                                        github.com/gobuffalo/suite.(*Model).SetupTest
                                                /Users/dainius/Documents/Go/src/github.com/gobuffalo/suite/model.go:25
                                        github.com/gobuffalo/suite.(*Action).SetupTest
                                                /Users/dainius/Documents/Go/src/github.com/gobuffalo/suite/suite.go:61
                                        github.com/stretchr/testify/suite.Run.func2
                                                /Users/dainius/Documents/Go/src/github.com/stretchr/testify/suite/suite.go:88
                                        testing.tRunner
                                                /usr/local/Cellar/go/1.10.2/libexec/src/testing/testing.go:777
                                        runtime.goexit
                                                /usr/local/Cellar/go/1.10.2/libexec/src/runtime/asm_amd64.s:2361
                        Test:           Test_ActionSuite/Test_HomeHandler
FAIL

hi,

previously rawquery was working with count,
but we suspect the latest update stopped working.
it returns 0 all the time, is this true?

hi,

why is there 2 schema.sql? one on root directory and one in migration directory?
are these duplicate ? and we realize soda migrate seems to update migration directory schema.sql

which one is the actual one? can this be only single schema.sql?
it should be used for both schema load, dump, soda migrate.

and another question for soda,
soda drop / create / migrate seems to take environment variable GO_ENV,
but for soda schema load or schema dump, it seems to requires -e flag,
why isn't it picking up GO_ENV if -e flag is not specified?
and why soda create / drop take in parameter 1 as environment, but not able to specify -e flag?

and same goes for soda migrate...

export GO_ENV=test
soda drop
soda create
soda -e test schema load test

thank you

hi

class Author
has_many :books, inverse_of: 'author'
has_many :history_books, inverse_of: 'author', -> { where(category: "history").order("created_at desc") }

end

class Book
belongs_to :author

end

maybe

type Author struct {
  HistoryBooks Book `json:"history_books" inverse_of:"author" where:"category='history'" orderBy:"created_at desc"`

}

There are times when a new column needs to be added to a table, but there is currently no way to tell Fizz in what order to place that new column. Mysql allows you to specify a BEFORE or AFTER command followed by the target column.

in rails, preload is what we are doing in Eager,
Eagerload is a left join in the same statement, which represent different meaning

i like buffalo/pop, When I'm ready to give up orm. i fond this, it give me the rails way to control my migrations and relationship the struct,this package is so cool, i'm so like it ,good job!

Hi,

I'm new to both Go and Buffalo. I don't know what to do when trying to interface with an existing database who's tables primary key's aren't called ID. For instance there is a user table and the primary key is called user_id. From what I can tell pop.Query.Find is the responsible function because it's inserting id as a hard-coded string. Maybe there could be a way of overriding the primary key name like there is a way of overriding the table name by implementing TableNameAble?

Thanks!

Edit: Looking further I guess I can just use pop.Query.Where instead. Is that the proper way of handling this?

how to use the options set mysql loc sslmode and so on , use url can set anything, but i prefer to use key:value mode.