13 // DB contains information for current db connection
21 blockGlobalUpdate bool
34 // Open initialize a new db connection, need to import driver first, e.g:
36 // import _ "github.com/go-sql-driver/mysql"
38 // db, err := gorm.Open("mysql", "user:password@/dbname?charset=utf8&parseTime=True&loc=Local")
40 // GORM has wrapped some drivers, for easier to remember driver's import path, so you could import the mysql driver with
41 // import _ "github.com/jinzhu/gorm/dialects/mysql"
42 // // import _ "github.com/jinzhu/gorm/dialects/postgres"
43 // // import _ "github.com/jinzhu/gorm/dialects/sqlite"
44 // // import _ "github.com/jinzhu/gorm/dialects/mssql"
45 func Open(dialect string, args ...interface{}) (db *DB, err error) {
47 err = errors.New("invalid database source")
54 switch value := args[0].(type) {
59 } else if len(args) >= 2 {
61 source = args[1].(string)
63 dbSQL, err = sql.Open(driver, source)
69 return nil, fmt.Errorf("invalid database source: %v is not a valid type", value)
74 logger: defaultLogger,
75 callbacks: DefaultCallback,
76 dialect: newDialect(dialect, dbSQL),
82 // Send a ping to make sure the database connection is alive.
83 if d, ok := dbSQL.(*sql.DB); ok {
84 if err = d.Ping(); err != nil && ownDbSQL {
91 // New clone a new db connection without search conditions
92 func (s *DB) New() *DB {
99 type closer interface {
103 // Close close current db connection. If database connection is not an io.Closer, returns an error.
104 func (s *DB) Close() error {
105 if db, ok := s.parent.db.(closer); ok {
108 return errors.New("can't close current db")
111 // DB get `*sql.DB` from current connection
112 // If the underlying database connection is not a *sql.DB, returns nil
113 func (s *DB) DB() *sql.DB {
114 db, _ := s.db.(*sql.DB)
118 // CommonDB return the underlying `*sql.DB` or `*sql.Tx` instance, mainly intended to allow coexistence with legacy non-GORM code.
119 func (s *DB) CommonDB() SQLCommon {
123 // Dialect get dialect
124 func (s *DB) Dialect() Dialect {
128 // Callback return `Callbacks` container, you could add/change/delete callbacks with it
129 // db.Callback().Create().Register("update_created_at", updateCreated)
130 // Refer https://jinzhu.github.io/gorm/development.html#callbacks
131 func (s *DB) Callback() *Callback {
132 s.parent.callbacks = s.parent.callbacks.clone()
133 return s.parent.callbacks
136 // SetLogger replace default logger
137 func (s *DB) SetLogger(log logger) {
141 // LogMode set log mode, `true` for detailed logs, `false` for no log, default, will only print error logs
142 func (s *DB) LogMode(enable bool) *DB {
151 // BlockGlobalUpdate if true, generates an error on update/delete without where clause.
152 // This is to prevent eventual error with empty objects updates/deletions
153 func (s *DB) BlockGlobalUpdate(enable bool) *DB {
154 s.blockGlobalUpdate = enable
158 // HasBlockGlobalUpdate return state of block
159 func (s *DB) HasBlockGlobalUpdate() bool {
160 return s.blockGlobalUpdate
163 // SingularTable use singular table by default
164 func (s *DB) SingularTable(enable bool) {
165 modelStructsMap = sync.Map{}
166 s.parent.singularTable = enable
169 // NewScope create a scope for current operation
170 func (s *DB) NewScope(value interface{}) *Scope {
172 dbClone.Value = value
173 return &Scope{db: dbClone, Search: dbClone.search.clone(), Value: value}
176 // QueryExpr returns the query as expr object
177 func (s *DB) QueryExpr() *expr {
178 scope := s.NewScope(s.Value)
179 scope.InstanceSet("skip_bindvar", true)
180 scope.prepareQuerySQL()
182 return Expr(scope.SQL, scope.SQLVars...)
185 // SubQuery returns the query as sub query
186 func (s *DB) SubQuery() *expr {
187 scope := s.NewScope(s.Value)
188 scope.InstanceSet("skip_bindvar", true)
189 scope.prepareQuerySQL()
191 return Expr(fmt.Sprintf("(%v)", scope.SQL), scope.SQLVars...)
194 // Where return a new relation, filter records with given conditions, accepts `map`, `struct` or `string` as conditions, refer http://jinzhu.github.io/gorm/crud.html#query
195 func (s *DB) Where(query interface{}, args ...interface{}) *DB {
196 return s.clone().search.Where(query, args...).db
199 // Or filter records that match before conditions or this one, similar to `Where`
200 func (s *DB) Or(query interface{}, args ...interface{}) *DB {
201 return s.clone().search.Or(query, args...).db
204 // Not filter records that don't match current conditions, similar to `Where`
205 func (s *DB) Not(query interface{}, args ...interface{}) *DB {
206 return s.clone().search.Not(query, args...).db
209 // Limit specify the number of records to be retrieved
210 func (s *DB) Limit(limit interface{}) *DB {
211 return s.clone().search.Limit(limit).db
214 // Offset specify the number of records to skip before starting to return the records
215 func (s *DB) Offset(offset interface{}) *DB {
216 return s.clone().search.Offset(offset).db
219 // Order specify order when retrieve records from database, set reorder to `true` to overwrite defined conditions
220 // db.Order("name DESC")
221 // db.Order("name DESC", true) // reorder
222 // db.Order(gorm.Expr("name = ? DESC", "first")) // sql expression
223 func (s *DB) Order(value interface{}, reorder ...bool) *DB {
224 return s.clone().search.Order(value, reorder...).db
227 // Select specify fields that you want to retrieve from database when querying, by default, will select all fields;
228 // When creating/updating, specify fields that you want to save to database
229 func (s *DB) Select(query interface{}, args ...interface{}) *DB {
230 return s.clone().search.Select(query, args...).db
233 // Omit specify fields that you want to ignore when saving to database for creating, updating
234 func (s *DB) Omit(columns ...string) *DB {
235 return s.clone().search.Omit(columns...).db
238 // Group specify the group method on the find
239 func (s *DB) Group(query string) *DB {
240 return s.clone().search.Group(query).db
243 // Having specify HAVING conditions for GROUP BY
244 func (s *DB) Having(query interface{}, values ...interface{}) *DB {
245 return s.clone().search.Having(query, values...).db
248 // Joins specify Joins conditions
249 // db.Joins("JOIN emails ON emails.user_id = users.id AND emails.email = ?", "jinzhu@example.org").Find(&user)
250 func (s *DB) Joins(query string, args ...interface{}) *DB {
251 return s.clone().search.Joins(query, args...).db
254 // Scopes pass current database connection to arguments `func(*DB) *DB`, which could be used to add conditions dynamically
255 // func AmountGreaterThan1000(db *gorm.DB) *gorm.DB {
256 // return db.Where("amount > ?", 1000)
259 // func OrderStatus(status []string) func (db *gorm.DB) *gorm.DB {
260 // return func (db *gorm.DB) *gorm.DB {
261 // return db.Scopes(AmountGreaterThan1000).Where("status in (?)", status)
265 // db.Scopes(AmountGreaterThan1000, OrderStatus([]string{"paid", "shipped"})).Find(&orders)
266 // Refer https://jinzhu.github.io/gorm/crud.html#scopes
267 func (s *DB) Scopes(funcs ...func(*DB) *DB) *DB {
268 for _, f := range funcs {
274 // Unscoped return all record including deleted record, refer Soft Delete https://jinzhu.github.io/gorm/crud.html#soft-delete
275 func (s *DB) Unscoped() *DB {
276 return s.clone().search.unscoped().db
279 // Attrs initialize struct with argument if record not found with `FirstOrInit` https://jinzhu.github.io/gorm/crud.html#firstorinit or `FirstOrCreate` https://jinzhu.github.io/gorm/crud.html#firstorcreate
280 func (s *DB) Attrs(attrs ...interface{}) *DB {
281 return s.clone().search.Attrs(attrs...).db
284 // Assign assign result with argument regardless it is found or not with `FirstOrInit` https://jinzhu.github.io/gorm/crud.html#firstorinit or `FirstOrCreate` https://jinzhu.github.io/gorm/crud.html#firstorcreate
285 func (s *DB) Assign(attrs ...interface{}) *DB {
286 return s.clone().search.Assign(attrs...).db
289 // First find first record that match given conditions, order by primary key
290 func (s *DB) First(out interface{}, where ...interface{}) *DB {
291 newScope := s.NewScope(out)
292 newScope.Search.Limit(1)
293 return newScope.Set("gorm:order_by_primary_key", "ASC").
294 inlineCondition(where...).callCallbacks(s.parent.callbacks.queries).db
297 // Take return a record that match given conditions, the order will depend on the database implementation
298 func (s *DB) Take(out interface{}, where ...interface{}) *DB {
299 newScope := s.NewScope(out)
300 newScope.Search.Limit(1)
301 return newScope.inlineCondition(where...).callCallbacks(s.parent.callbacks.queries).db
304 // Last find last record that match given conditions, order by primary key
305 func (s *DB) Last(out interface{}, where ...interface{}) *DB {
306 newScope := s.NewScope(out)
307 newScope.Search.Limit(1)
308 return newScope.Set("gorm:order_by_primary_key", "DESC").
309 inlineCondition(where...).callCallbacks(s.parent.callbacks.queries).db
312 // Find find records that match given conditions
313 func (s *DB) Find(out interface{}, where ...interface{}) *DB {
314 return s.NewScope(out).inlineCondition(where...).callCallbacks(s.parent.callbacks.queries).db
317 //Preloads preloads relations, don`t touch out
318 func (s *DB) Preloads(out interface{}) *DB {
319 return s.NewScope(out).InstanceSet("gorm:only_preload", 1).callCallbacks(s.parent.callbacks.queries).db
322 // Scan scan value to a struct
323 func (s *DB) Scan(dest interface{}) *DB {
324 return s.NewScope(s.Value).Set("gorm:query_destination", dest).callCallbacks(s.parent.callbacks.queries).db
327 // Row return `*sql.Row` with given conditions
328 func (s *DB) Row() *sql.Row {
329 return s.NewScope(s.Value).row()
332 // Rows return `*sql.Rows` with given conditions
333 func (s *DB) Rows() (*sql.Rows, error) {
334 return s.NewScope(s.Value).rows()
337 // ScanRows scan `*sql.Rows` to give struct
338 func (s *DB) ScanRows(rows *sql.Rows, result interface{}) error {
340 scope = s.NewScope(result)
342 columns, err = rows.Columns()
345 if clone.AddError(err) == nil {
346 scope.scan(rows, columns, scope.Fields())
352 // Pluck used to query single column from a model as a map
354 // db.Find(&users).Pluck("age", &ages)
355 func (s *DB) Pluck(column string, value interface{}) *DB {
356 return s.NewScope(s.Value).pluck(column, value).db
359 // Count get how many records for a model
360 func (s *DB) Count(value interface{}) *DB {
361 return s.NewScope(s.Value).count(value).db
364 // Related get related associations
365 func (s *DB) Related(value interface{}, foreignKeys ...string) *DB {
366 return s.NewScope(s.Value).related(value, foreignKeys...).db
369 // FirstOrInit find first matched record or initialize a new one with given conditions (only works with struct, map conditions)
370 // https://jinzhu.github.io/gorm/crud.html#firstorinit
371 func (s *DB) FirstOrInit(out interface{}, where ...interface{}) *DB {
373 if result := c.First(out, where...); result.Error != nil {
374 if !result.RecordNotFound() {
377 c.NewScope(out).inlineCondition(where...).initialize()
379 c.NewScope(out).updatedAttrsWithValues(c.search.assignAttrs)
384 // FirstOrCreate find first matched record or create a new one with given conditions (only works with struct, map conditions)
385 // https://jinzhu.github.io/gorm/crud.html#firstorcreate
386 func (s *DB) FirstOrCreate(out interface{}, where ...interface{}) *DB {
388 if result := s.First(out, where...); result.Error != nil {
389 if !result.RecordNotFound() {
392 return c.NewScope(out).inlineCondition(where...).initialize().callCallbacks(c.parent.callbacks.creates).db
393 } else if len(c.search.assignAttrs) > 0 {
394 return c.NewScope(out).InstanceSet("gorm:update_interface", c.search.assignAttrs).callCallbacks(c.parent.callbacks.updates).db
399 // Update update attributes with callbacks, refer: https://jinzhu.github.io/gorm/crud.html#update
400 func (s *DB) Update(attrs ...interface{}) *DB {
401 return s.Updates(toSearchableMap(attrs...), true)
404 // Updates update attributes with callbacks, refer: https://jinzhu.github.io/gorm/crud.html#update
405 func (s *DB) Updates(values interface{}, ignoreProtectedAttrs ...bool) *DB {
406 return s.NewScope(s.Value).
407 Set("gorm:ignore_protected_attrs", len(ignoreProtectedAttrs) > 0).
408 InstanceSet("gorm:update_interface", values).
409 callCallbacks(s.parent.callbacks.updates).db
412 // UpdateColumn update attributes without callbacks, refer: https://jinzhu.github.io/gorm/crud.html#update
413 func (s *DB) UpdateColumn(attrs ...interface{}) *DB {
414 return s.UpdateColumns(toSearchableMap(attrs...))
417 // UpdateColumns update attributes without callbacks, refer: https://jinzhu.github.io/gorm/crud.html#update
418 func (s *DB) UpdateColumns(values interface{}) *DB {
419 return s.NewScope(s.Value).
420 Set("gorm:update_column", true).
421 Set("gorm:save_associations", false).
422 InstanceSet("gorm:update_interface", values).
423 callCallbacks(s.parent.callbacks.updates).db
426 // Save update value in database, if the value doesn't have primary key, will insert it
427 func (s *DB) Save(value interface{}) *DB {
428 scope := s.NewScope(value)
429 if !scope.PrimaryKeyZero() {
430 newDB := scope.callCallbacks(s.parent.callbacks.updates).db
431 if newDB.Error == nil && newDB.RowsAffected == 0 {
432 return s.New().FirstOrCreate(value)
436 return scope.callCallbacks(s.parent.callbacks.creates).db
439 // Create insert the value into database
440 func (s *DB) Create(value interface{}) *DB {
441 scope := s.NewScope(value)
442 return scope.callCallbacks(s.parent.callbacks.creates).db
445 // Delete delete value match given conditions, if the value has primary key, then will including the primary key as condition
446 func (s *DB) Delete(value interface{}, where ...interface{}) *DB {
447 return s.NewScope(value).inlineCondition(where...).callCallbacks(s.parent.callbacks.deletes).db
450 // Raw use raw sql as conditions, won't run it unless invoked by other methods
451 // db.Raw("SELECT name, age FROM users WHERE name = ?", 3).Scan(&result)
452 func (s *DB) Raw(sql string, values ...interface{}) *DB {
453 return s.clone().search.Raw(true).Where(sql, values...).db
456 // Exec execute raw sql
457 func (s *DB) Exec(sql string, values ...interface{}) *DB {
458 scope := s.NewScope(nil)
459 generatedSQL := scope.buildCondition(map[string]interface{}{"query": sql, "args": values}, true)
460 generatedSQL = strings.TrimSuffix(strings.TrimPrefix(generatedSQL, "("), ")")
461 scope.Raw(generatedSQL)
462 return scope.Exec().db
465 // Model specify the model you would like to run db operations
466 // // update all users's name to `hello`
467 // db.Model(&User{}).Update("name", "hello")
468 // // if user's primary key is non-blank, will use it as condition, then will only update the user's name to `hello`
469 // db.Model(&user).Update("name", "hello")
470 func (s *DB) Model(value interface{}) *DB {
476 // Table specify the table you would like to run db operations
477 func (s *DB) Table(name string) *DB {
479 clone.search.Table(name)
484 // Debug start debug mode
485 func (s *DB) Debug() *DB {
486 return s.clone().LogMode(true)
489 // Begin begin a transaction
490 func (s *DB) Begin() *DB {
492 if db, ok := c.db.(sqlDb); ok && db != nil {
493 tx, err := db.Begin()
494 c.db = interface{}(tx).(SQLCommon)
496 c.dialect.SetDB(c.db)
499 c.AddError(ErrCantStartTransaction)
504 // Commit commit a transaction
505 func (s *DB) Commit() *DB {
506 var emptySQLTx *sql.Tx
507 if db, ok := s.db.(sqlTx); ok && db != nil && db != emptySQLTx {
508 s.AddError(db.Commit())
510 s.AddError(ErrInvalidTransaction)
515 // Rollback rollback a transaction
516 func (s *DB) Rollback() *DB {
517 var emptySQLTx *sql.Tx
518 if db, ok := s.db.(sqlTx); ok && db != nil && db != emptySQLTx {
519 s.AddError(db.Rollback())
521 s.AddError(ErrInvalidTransaction)
526 // NewRecord check if value's primary key is blank
527 func (s *DB) NewRecord(value interface{}) bool {
528 return s.NewScope(value).PrimaryKeyZero()
531 // RecordNotFound check if returning ErrRecordNotFound error
532 func (s *DB) RecordNotFound() bool {
533 for _, err := range s.GetErrors() {
534 if err == ErrRecordNotFound {
541 // CreateTable create table for models
542 func (s *DB) CreateTable(models ...interface{}) *DB {
544 for _, model := range models {
545 db = db.NewScope(model).createTable().db
550 // DropTable drop table for models
551 func (s *DB) DropTable(values ...interface{}) *DB {
553 for _, value := range values {
554 if tableName, ok := value.(string); ok {
555 db = db.Table(tableName)
558 db = db.NewScope(value).dropTable().db
563 // DropTableIfExists drop table if it is exist
564 func (s *DB) DropTableIfExists(values ...interface{}) *DB {
566 for _, value := range values {
567 if s.HasTable(value) {
568 db.AddError(s.DropTable(value).Error)
574 // HasTable check has table or not
575 func (s *DB) HasTable(value interface{}) bool {
577 scope = s.NewScope(value)
581 if name, ok := value.(string); ok {
584 tableName = scope.TableName()
587 has := scope.Dialect().HasTable(tableName)
588 s.AddError(scope.db.Error)
592 // AutoMigrate run auto migration for given models, will only add missing fields, won't delete/change current data
593 func (s *DB) AutoMigrate(values ...interface{}) *DB {
595 for _, value := range values {
596 db = db.NewScope(value).autoMigrate().db
601 // ModifyColumn modify column to type
602 func (s *DB) ModifyColumn(column string, typ string) *DB {
603 scope := s.NewScope(s.Value)
604 scope.modifyColumn(column, typ)
608 // DropColumn drop a column
609 func (s *DB) DropColumn(column string) *DB {
610 scope := s.NewScope(s.Value)
611 scope.dropColumn(column)
615 // AddIndex add index for columns with given name
616 func (s *DB) AddIndex(indexName string, columns ...string) *DB {
617 scope := s.Unscoped().NewScope(s.Value)
618 scope.addIndex(false, indexName, columns...)
622 // AddUniqueIndex add unique index for columns with given name
623 func (s *DB) AddUniqueIndex(indexName string, columns ...string) *DB {
624 scope := s.Unscoped().NewScope(s.Value)
625 scope.addIndex(true, indexName, columns...)
629 // RemoveIndex remove index with name
630 func (s *DB) RemoveIndex(indexName string) *DB {
631 scope := s.NewScope(s.Value)
632 scope.removeIndex(indexName)
636 // AddForeignKey Add foreign key to the given scope, e.g:
637 // db.Model(&User{}).AddForeignKey("city_id", "cities(id)", "RESTRICT", "RESTRICT")
638 func (s *DB) AddForeignKey(field string, dest string, onDelete string, onUpdate string) *DB {
639 scope := s.NewScope(s.Value)
640 scope.addForeignKey(field, dest, onDelete, onUpdate)
644 // RemoveForeignKey Remove foreign key from the given scope, e.g:
645 // db.Model(&User{}).RemoveForeignKey("city_id", "cities(id)")
646 func (s *DB) RemoveForeignKey(field string, dest string) *DB {
647 scope := s.clone().NewScope(s.Value)
648 scope.removeForeignKey(field, dest)
652 // Association start `Association Mode` to handler relations things easir in that mode, refer: https://jinzhu.github.io/gorm/associations.html#association-mode
653 func (s *DB) Association(column string) *Association {
655 var scope = s.Set("gorm:association:source", s.Value).NewScope(s.Value)
657 if primaryField := scope.PrimaryField(); primaryField.IsBlank {
658 err = errors.New("primary key can't be nil")
660 if field, ok := scope.FieldByName(column); ok {
661 if field.Relationship == nil || len(field.Relationship.ForeignFieldNames) == 0 {
662 err = fmt.Errorf("invalid association %v for %v", column, scope.IndirectValue().Type())
664 return &Association{scope: scope, column: column, field: field}
667 err = fmt.Errorf("%v doesn't have column %v", scope.IndirectValue().Type(), column)
671 return &Association{Error: err}
674 // Preload preload associations with given conditions
675 // db.Preload("Orders", "state NOT IN (?)", "cancelled").Find(&users)
676 func (s *DB) Preload(column string, conditions ...interface{}) *DB {
677 return s.clone().search.Preload(column, conditions...).db
680 // Set set setting by name, which could be used in callbacks, will clone a new db, and update its setting
681 func (s *DB) Set(name string, value interface{}) *DB {
682 return s.clone().InstantSet(name, value)
685 // InstantSet instant set setting, will affect current db
686 func (s *DB) InstantSet(name string, value interface{}) *DB {
687 s.values.Store(name, value)
691 // Get get setting by name
692 func (s *DB) Get(name string) (value interface{}, ok bool) {
693 value, ok = s.values.Load(name)
697 // SetJoinTableHandler set a model's join table handler for a relation
698 func (s *DB) SetJoinTableHandler(source interface{}, column string, handler JoinTableHandlerInterface) {
699 scope := s.NewScope(source)
700 for _, field := range scope.GetModelStruct().StructFields {
701 if field.Name == column || field.DBName == column {
702 if many2many, _ := field.TagSettingsGet("MANY2MANY"); many2many != "" {
703 source := (&Scope{Value: source}).GetModelStruct().ModelType
704 destination := (&Scope{Value: reflect.New(field.Struct.Type).Interface()}).GetModelStruct().ModelType
705 handler.Setup(field.Relationship, many2many, source, destination)
706 field.Relationship.JoinTableHandler = handler
707 if table := handler.Table(s); scope.Dialect().HasTable(table) {
708 s.Table(table).AutoMigrate(handler)
715 // AddError add error to the db
716 func (s *DB) AddError(err error) error {
718 if err != ErrRecordNotFound {
720 go s.print(fileWithLineNum(), err)
725 errors := Errors(s.GetErrors())
726 errors = errors.Add(err)
737 // GetErrors get happened errors from the db
738 func (s *DB) GetErrors() []error {
739 if errs, ok := s.Error.(Errors); ok {
741 } else if s.Error != nil {
742 return []error{s.Error}
747 ////////////////////////////////////////////////////////////////////////////////
748 // Private Methods For DB
749 ////////////////////////////////////////////////////////////////////////////////
751 func (s *DB) clone() *DB {
759 blockGlobalUpdate: s.blockGlobalUpdate,
760 dialect: newDialect(s.dialect.GetName(), s.db),
763 s.values.Range(func(k, v interface{}) bool {
764 db.values.Store(k, v)
769 db.search = &search{limit: -1, offset: -1}
771 db.search = s.search.clone()
778 func (s *DB) print(v ...interface{}) {
782 func (s *DB) log(v ...interface{}) {
783 if s != nil && s.logMode == 2 {
784 s.print(append([]interface{}{"log", fileWithLineNum()}, v...)...)
788 func (s *DB) slog(sql string, t time.Time, vars ...interface{}) {
790 s.print("sql", fileWithLineNum(), NowFunc().Sub(t), sql, vars, s.RowsAffected)