gobuffalo / pop Goto Github PK
View Code? Open in Web Editor NEWA Tasty Treat For All Your Database Needs
License: MIT License
A Tasty Treat For All Your Database Needs
License: MIT License
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. :)
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)
there are times when we do not wish to save belongs_to
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?
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.)
Line 59 in 8d249c6
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
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:
go get -u -v -tags sqlite github.com/gobuffalo/buffalo/buffalo
go get -u -v -tags sqlite github.com/gobuffalo/pop/...
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.
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
.
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,
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
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.
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:
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.TruncateAll()
in that way.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
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.
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
Is this supported?
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/
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()))
}
}
It's an common DB in the enterprise and now also available for Linux.
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?
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.
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
From the Discussion in Slack:
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
...
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.
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 aDeletedAt 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.
It would be good that pop allows to use the cloud-sql proxy for postgres (details in here)
Hi!
Previously @mclark4386 fixed issue with the this commit. But after upgrading buffalo to v0.11.0, i saw bug fix is missing.
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
Hi,
I'm looking for a way to change models
directory to be inside app/models
, is this possible?
Thanks
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
.)
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 asINSERT 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,
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
Would be nice to have a bulk insert or update function.
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.
hi,
previously rawquery was working with count,
but we suspect the latest update stopped working.
it returns 0 all the time, is this true?
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
. 😀
realized it didnt work...
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
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.
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.
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! 😄
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 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?
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
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 want to use user
table for my User
model or whatever
table for MyCustomModel
. How to do that?
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!
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?
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.