code_dao_sqlx.md

CRUD DAO Functions

gen will generate dao functions if the --generate-dao is passed to gen. The code can be customized with the --dao=dao flag to set the name of the dao package.

Code can be generated in two flavours, SQLX by default and GORM with the flag --gorm

The code generation, will generate functions for

• Retrieving records with paging
• Retrieve a specific record
• Create a record
• Update a record
• Delete a record

Retrieve Paged Records

// GetAllInvoices is a function to get a slice of record(s) from invoices table in the main database// params - page - page requested (defaults to 0)// params - pagesize - number of records in a page (defaults to 20)// params - order - db sort order column// error - ErrNotFound, db Find errorfuncGetAllInvoices(ctx context.Context, page, pagesizeint64, orderstring) (results []*model.Invoices, totalRowsint, errerror) {
sql:="SELECT * FROM `invoices`"iforder!="" {
ifstrings.ContainsAny(order, "'\"") {
order=""
}
}
iforder=="" {
order="InvoiceId"
}
ifDB.DriverName() =="mssql" {
sql=fmt.Sprintf("%s order by %s OFFSET %d ROWS FETCH FIRST %d ROWS ONLY", sql, order, page, pagesize)
} elseifDB.DriverName() =="postgres" {
sql=fmt.Sprintf("%s order by `%s` OFFSET %d LIMIT %d", sql, order, page, pagesize)
} else {
sql=fmt.Sprintf("%s order by `%s` LIMIT %d, %d", sql, order, page, pagesize)
}
sql=DB.Rebind(sql)
ifLogger!=nil {
Logger(ctx, sql)
}
err=DB.SelectContext(ctx, &results, sql)
iferr!=nil {
returnnil, -1, err
}
cnt , err:=GetRowCount(ctx, "invoices")
iferr!=nil {
returnresults, -2, err
}
returnresults, cnt, err
}

Retrieve record

// GetInvoices is a function to get a single record from the invoices table in the main database// error - ErrNotFound, db Find errorfuncGetInvoices(ctx context.Context, argInvoiceIDint32, ) (record*model.Invoices, errerror) {
sql:="SELECT * FROM `invoices` WHERE InvoiceId = ?"sql=DB.Rebind(sql)
ifLogger!=nil {
Logger(ctx, sql)
}
record=&model.Invoices{}
err=DB.GetContext(ctx, record, sql, argInvoiceID, )
iferr!=nil {
returnnil, err
}
returnrecord, nil
}

Create record

// AddInvoices is a function to add a single record to invoices table in the main database// error - ErrInsertFailed, db save call failedfuncAddInvoices(ctx context.Context, record*model.Invoices) (result*model.Invoices, RowsAffectedint64, errerror) {
ifDB.DriverName() =="postgres" {
returnaddInvoicesPostgres( ctx, record)
} else {
returnaddInvoices( ctx, record)
}
}
// addInvoicesPostgres is a function to add a single record to invoices table in the main database// error - ErrInsertFailed, db save call failedfuncaddInvoicesPostgres(ctx context.Context, record*model.Invoices) (result*model.Invoices, RowsAffectedint64, errerror) {
sql:="INSERT INTO `invoices` ( CustomerId, InvoiceDate, BillingAddress, BillingCity, BillingState, BillingCountry, BillingPostalCode, Total) values ( ?, ?, ?, ?, ?, ?, ?, ? )"sql=DB.Rebind(sql)
ifLogger!=nil {
Logger(ctx, sql)
}
rows:=int64(1)
sql=fmt.Sprintf("%s returning %s", sql, "InvoiceId")
dbResult:=DB.QueryRowContext(ctx, sql, record.CustomerID, record.InvoiceDate, record.BillingAddress, record.BillingCity, record.BillingState, record.BillingCountry, record.BillingPostalCode, record.Total,)
err=dbResult.Scan( record.CustomerID, record.InvoiceDate, record.BillingAddress, record.BillingCity, record.BillingState, record.BillingCountry, record.BillingPostalCode, record.Total,)
returnrecord, rows, err
}
// addInvoicesPostgres is a function to add a single record to invoices table in the main database// error - ErrInsertFailed, db save call failedfuncaddInvoices(ctx context.Context, record*model.Invoices) (result*model.Invoices, RowsAffectedint64, errerror) {
sql:="INSERT INTO `invoices` ( CustomerId, InvoiceDate, BillingAddress, BillingCity, BillingState, BillingCountry, BillingPostalCode, Total) values ( ?, ?, ?, ?, ?, ?, ?, ? )"sql=DB.Rebind(sql)
ifLogger!=nil {
Logger(ctx, sql)
}
rows:=int64(0)
dbResult, err:=DB.ExecContext(ctx, sql, record.CustomerID, record.InvoiceDate, record.BillingAddress, record.BillingCity, record.BillingState, record.BillingCountry, record.BillingPostalCode, record.Total,)
iferr!=nil {
returnnil, 0, err
}
id, err:=dbResult.LastInsertId()
rows, err=dbResult.RowsAffected()
record.InvoiceID=int32(id)
returnrecord, rows, err
}

Update record

// UpdateInvoices is a function to update a single record from invoices table in the main database// error - ErrNotFound, db record for id not found// error - ErrUpdateFailed, db meta data copy failed or db.Save call failedfuncUpdateInvoices(ctx context.Context, argInvoiceIDint32, updated*model.Invoices) (result*model.Invoices, RowsAffectedint64, errerror) {
sql:="UPDATE `invoices` set CustomerId = ?, InvoiceDate = ?, BillingAddress = ?, BillingCity = ?, BillingState = ?, BillingCountry = ?, BillingPostalCode = ?, Total = ? WHERE InvoiceId = ?"sql=DB.Rebind(sql)
ifLogger!=nil {
Logger(ctx, sql)
}
dbResult, err:=DB.ExecContext(ctx, sql, updated.CustomerID, updated.InvoiceDate, updated.BillingAddress, updated.BillingCity, updated.BillingState, updated.BillingCountry, updated.BillingPostalCode, updated.Total, argInvoiceID, )
iferr!=nil {
returnnil, 0, err
}
rows, err:=dbResult.RowsAffected()
updated.InvoiceID=argInvoiceIDreturnupdated, rows, err
}

Delete record

// DeleteInvoices is a function to delete a single record from invoices table in the main database// error - ErrNotFound, db Find error// error - ErrDeleteFailed, db Delete failed errorfuncDeleteInvoices(ctx context.Context, argInvoiceIDint32, ) (rowsAffectedint64, errerror) {
sql:="DELETE FROM `invoices` where InvoiceId = ?"sql=DB.Rebind(sql)
ifLogger!=nil {
Logger(ctx, sql)
}
result, err:=DB.ExecContext(ctx, sql, argInvoiceID, )
iferr!=nil {
return0, err
}
returnresult.RowsAffected()
}