Repository files navigation

igor is an abstraction layer for PostgreSQL, written in Go. Igor syntax is (almost) compatible withGORM.

You should use igor when your DBMS is PostgreSQL and you want to place an abstraction layer on top of it and do CRUD operations in a smart, easy, secure and fast way.

Thus with igor youdo not create a new schema. In general igor does not support DDL (you can do it with theRaw andExec, but there are not method created ad-hoc for this purpose).

• Always uses prepared statements: no sql injection and good performance.
• Supports transactions
• Supports PostgreSQL JSON and JSONB types withigor.JSON
• Supports PostgreSQLLISTEN/NOTIFY
• Uses a GORM like syntax
• Uses the same logic in insertion and update: handle default values in a coherent manner
• Uses GORM models and conventions (partially, seeDifferences)
• Exploits PostgreSQLRETURNING statement to update models fields with the updated values (even when changed on db side; e.g. when having a default value)
• Automatically handle reserved keywords when used as a table name or fields. Does not quote every field (that's not recommended) but only the ones conflicting with a reserved keyword.

• An ORM (and thus a complete GORM replacement):
  • Does not support associations
  • Does not support callbacks
  • Does not have any specific method for data migration and DDL operations
  • Does not support soft delete

goget-ugithub.com/galeone/igor

igor uses the same syntax of GORM. Thus in a great number of cases you can replace GORM with igor by only changing the import path.

Warning: igor is not a complete GORM replacement. See theDifferences.

Models are thesame used in GORM.

The main differences are:

• Igor does not handle associations. Thus, if you have a field that refers to another table, disable it with the annotationsql:"-" (see the code below).
• Every modelmust implement theigor.DBTable interface. Therefore every model must have the methodTableName() string, that returns the table name associated with the model.
• Every modelmust explicit the primary key field (using the tagigor:"primary_key").
• Since igor does not deal with DDL,sql:"type:<type>" is ignored.

Like:

typeUserstruct {Counteruint64`igor:"primary_key"`UsernamestringPasswordstringNamestringSurnamestringProfileProfile`sql:"-"`}type (User)TableName()string {return"users"}

igor supports PostgreSQL fields natively, without the need to use thepg.Array type - you can use just plain structs.

typeNestMestruct {IDint64`igor:"primary_key"`SliceOfString []stringSliceOfInt64  []int64}

This structure maps this table definition:

CREATETABLEnest_table(    idbigserialnot nullPRIMARY KEY,    slice_of_stringtext[]not null,    slice_of_int64bigint[]not null)

igor allows you to embed types, and overwrite fields of the inner type. In particular, you can add thesql decorator (or change type, potentially).

typeNestMestruct {IDint64`igor:"primary_key"`OverwriteMeint64SliceOfString []stringSliceOfInt64  []int64}typeNestTablestruct {NestMeOverwriteMeint64`sql:"-"`}func (NestTable)TableName()string {return"nest_table"}

TheNestTable type disables the SQL generation for the fieldOverwriteMe that's present in the embedded typeNestMe.

• igor
  • When to use igor
  • What igor does
  • What igor is not
  • Install
  • GORM compatible
  • Model definition
    • Array support
    • Nested types support
  • Methods
    • Connect
    • Log
    • Model
    • Joins
    • Table
    • CTE
    • Select
    • Where
    • Create
    • Delete
    • Updates
    • Pluck
    • Count
    • First
    • Scan
    • Raw
    • Exec
    • Where
    • Limit
    • Offset
    • Order
    • DB
    • Begin
    • Commit
    • Rollback
    • Listen
    • Unlisten
    • UnlistenAll
    • Notify
  • Differences
    • Select and Where call order
    • Models
    • Open method
    • Logger
    • Methods return value
    • Scan and Find methods
    • Scan
    • Delete
    • First
  • Other
    • JSON and JSONB support
    • LISTEN / NOTIFY support
    • Contributing
    • Testing
    • License
    • About the author

import"github.com/galeone/igor"funcmain() {db,err:=igor.Connect("user=galeone dbname=igor sslmode=disable")}

See:Logger.

Model(DBModel) sets the table name for the current query

varloggedboolvarcounteruint64db.Model(User{}).Select("login(?, ?) AS logged, counter",username,password).Where("LOWER(username) = ?",username).Scan(&logged,&counter);

it generates:

SELECT login($1, $2)AS logged, counterFROM usersWHERELOWER(username)= $3 ;

Joins append the join string to the current model

typePoststruct {Hpiduint64`igor:"primary_key"`Fromuint64Touint64Piduint64`sql:"default:0"`MessagestringTime    time.Time`sql:"default:(now() at time zone 'utc')"`LangstringNewsboolClosedbool}typeUserPoststruct {Post}func (UserPost)TableName()string {return"posts"}users:=new(User).TableName()posts:=new(UserPost).TableName()varuserPosts []UserPostdb.Model(UserPost{}).Order("hpid DESC").Joins("JOIN "+users+" ON "+users+".counter = "+posts+".to").Where(`"to" = ?`,user.Counter).Scan(&userPost)

it generates:

SELECTposts.hpid,posts."from",posts."to",posts.pid,posts.message,posts."time",posts.lang,posts.news,posts.closedFROM postsJOIN usersONusers.counter=posts.toWHERE"to"= $1

Table appends the table string to FROM. It has the same behavior of Model, but passing the table name directly as a string

See example inJoins

CTE allows to define a Common Table Expression that precedes the query.

Warning: use it with theTable method.

varusernames []stringvarids []uint64// fill them - not the usage of = any since this is converted to a pq.Arraydb.CTE(`WITH full_users_id AS (SELECT counter FROM users WHERE name = ? AND counter = any(?))`,"Paolo",ids).Table("full_users_id as fui").Select("username").Joins("JOIN users ON fui.counter = users.counter").Scan(&usernames)

it generates:

WITH full_users_idAS (SELECT counterFROM usersWHERE name= $1AND counter= any($2))SELECT usernameFROM full_users_idas fuiJOIN usersONfui.counter=users.counter;

Select sets the fields to retrieve. Appends fields to SELECT (See example inModel).

When select is not specified, every field is selected in the Model order (See example inJoins).

Warning: callingSelect using parameters without type is allowed only if the stored procedure on the DBMS define the type.

Eg: if we have a function on PostgreSQL that accepts two parameters like

login(_usernametext, _passtext, OUT retboolean) RETURNSboolean

we can call this function in that way

db.Select('login(?,?)',username,password)

But, if the DBMS can't infer the parameters (in every other case except the one previous mentioned), wemust make parameters type explicit.

This is due to the use of prepared statements.

db.Select("?::int, ?::int, ?::int",1,2,3)

Where works withDBModels or strings.

When using aDBModel, if the primary key fields is not blank, the query will generate a where clause in the form:

Thus:

db.Model(UserPost{}).Where(&UserPost{Hpid:1,From:1,To:1})

it generates:

SELECTposts.hpid,posts."from",posts."to",posts.pid,posts.message,posts."time",posts.lang,posts.news,posts.closedFROM postsWHEREposts.hpid= $1

Ignoring values that are not primary keys.

If the primary key field is blank, generates the where clauseANDing the conditions:

db.Model(UserPost{}).Where(&UserPost{From:1,To:1})

The conditions will be:

WHEREposts.from= $1ANDposts.to= $2

When using a string, you can use the? as placeholder for parameters substitution. Thus

db.Model(UserPost{}).Where(`"to" = ?`,user.Counter)

it generates:

SELECTposts.hpid,posts."from",posts."to",posts.pid,posts.message,posts."time",posts.lang,posts.news,posts.closedFROM postsWHERE"to"= $1

Where supports slices as well:

db.Model(UserPost{}).Where(`"to" IN (?) OR "from" = ?`, []uint64{1,2,3,4,6},88)

it generates:

SELECTposts.hpid,posts."from",posts."to",posts.pid,posts.message,posts."time",posts.lang,posts.news,posts.closedFROM postsWHERE"to"IN ($1,$2,$3,$4,$5)OR"from"= $6

CreateINSERT a new row into the table specified by the DBModel.

Create handles default values using the following rules:

If a field is blank and has a default value and this default value is the Go Zero value for that field, igor does not generate the query part associated with the insertion of that fields (let the DBMS handle the default value generation).

If a field is blank and has a default value that's different from the Go Zero value for that filed, insert the specified default value.

Create exploits theRETURNING clause of PostgreSQL to fetch the new row and update the DBModel passed as argument.

In that way igor always have the up-to-date fields of DBModel.

post:=&UserPost{From:1,To:1,Pid:10,Message:"hi",Lang:"en",}db.Create(post)

it generates:

INSERT INTO posts("from","to",pid,message,lang)VALUES ($1,$2,$3,$4,$5)  RETURNINGposts.hpid,posts."from",posts."to",posts.pid,posts.message,posts."time",posts.lang,posts.news,posts.closed;

The resulting row (the result ofRETURNING) is used as a source for theScan method, having the DBModel as argument.

Thus, in the example, the variable post.Time has the(now() at time zone 'utc') evaluation result value.

SeeDelete

Updates uses the same logic ofCreate (thus the default value handling is the same).

The only difference is that UpdatesUPDATE rows.

Update tries to infer the table name from the DBModel passed as argumentif aWhere clause has not been specified. Otherwise uses theWhere clause to generate theWHERE part and the Model to generate thefield = $n part.

varuserUserdb.First(&user,1)// handle errorsuser.Username="username changed"db.Updates(&user)

it generates:

UPDATE usersSETusers.username="username changed"WHEREusers.counter=1 RETURNINGusers.counter,users.last,users.notify_story,users.private,users.lang,users.username,users.email,users.name,users.surname,users.gender,users.birth_date,users.board_lang,users.timezone,users.viewonline,users.registration_time

TheRETURNING clause is handled in the same manner ofCreate.

Pluck fills the slice with the query result.It callsScan internally, thus the slice can be a slice of structures or a slice of simple types.

It panics if slice is not a slice or the query is not well formulated.

typeBlackliststruct {Fromuint64Touint64MotivationstringTime       time.Time`sql:"default:(now() at time zone 'utc')"`Counteruint64`igor:"primary_key"`}func (Blacklist)TableName()string {return"blacklist"}varblacklist []uint64db.Model(Blacklist{}).Where(&Blacklist{From:user.Counter}).Pluck(`"to"`,&blacklist)

it generates

SELECT"to"FROM blacklistWHERE blacklist."from"= $1

Count sets the query result to be count(*) and scan the result into value.

varcountintdb.Model(Blacklist{}).Where(&Blacklist{From:user.Counter}).Count(&count

it generates:

SELECTCOUNT(*)FROM blacklistWHERE blacklist."from"= $1

SeeFirst

SeeScan and Find methods

Prepares and executes a raw query, the results is available for the Scan method.

SeeScan and Find methods

Prepares and executes a raw query, the results is discarded. Useful when you don't need the query result or the operation have no result.

tx:=db.Begin()tx.Exec("DROP TABLE IF EXISTS users")tx.Exec(`CREATE TABLE users (counter bigint NOT NULL,last timestamp without time zone DEFAULT timezone('utc'::text, now()) NOT NULL,notify_story jsonb,private boolean DEFAULT false NOT NULL,lang character varying(2) DEFAULT 'en'::character varying NOT NULL,username character varying(90) NOT NULL,password character varying(60) NOT NULL,name character varying(60) NOT NULL,surname character varying(60) NOT NULL,email character varying(350) NOT NULL,gender boolean NOT NULL,birth_date date NOT NULL,board_lang character varying(2) DEFAULT 'en'::character varying NOT NULL,timezone character varying(35) DEFAULT 'UTC'::character varying NOT NULL,viewonline boolean DEFAULT true NOT NULL,remote_addr inet DEFAULT '127.0.0.1'::inet NOT NULL,http_user_agent text DEFAULT ''::text NOT NULL,registration_time timestamp(0) with time zone DEFAULT now() NOT NULL)`)tx.Commit()

Exec does not use prepared statements if there are no parameters to replace in the query. This make it possible to use a single call toExec to execute multiple statements;-terminated. e.g.

tx.Exec(`DROP TABLE IF EXISTS users;        CREATE TABLE users (        counter bigint NOT NULL,        ...    )`)tx.Commit()

Where builds the WHERE clause.

If a primary key is present in the struct passed as argument only that field is used.

user.Counter=2user.Name="paolo"db.Select("username").Where(&user)

it generates:

SELECT usernameFROM usersWHEREusers.counter= $1

becauseCounter is the primary key.

If the primary key is blank every non empty field is and-end.

user.Counter=0// 0 is a blank primary key

it generates

SELECT usernameFROM usersWHEREusers.name= $1

You can use a string to build the where clause and pass parameters if needed.

db.Model(User{}).Select("username").Where("counter IN (?) AND name ILIKE ?",[]uint64{1,2,4,5},"nino")

it generates:

SELECT usernameFROM usersWHERE counterin ($1,$2,$3,$4)AND name ILIKE $5

If a where condition can't be generated, Where panics

Limit sets the LIMIT value to the query

Offset sets the OFFSET value to the query

Order sets the ORDER BY value to the query

DB returns the current*sql.DB. It panics if called during a transaction

Begin initialize a transaction. It panics if begin has been already called.

Il returns a*igor.Database, thus you can use every other*Database method on the returned value.

tx:=db.Begin()

Commit commits the transaction. It panics if the transaction is not started (you have to call Begin before)

tx.Create(&user)tx.Commit()// Now you can use the db variable again

Rollback rollbacks the transaction. It panics if the transaction is not started (you have to call Begin before

ife:=tx.Create(&user);e!=nil {tx.Rollback()}else {tx.Commit()}// Now you can use the db variable again

Listen executesLISTEN channel. Uses f to handle received notifications on channel.

ife:=db.Listen("notification_channel",func(payload...string) {iflen(payload)>0 {pl:=strings.Join(payload,",")fmt.Println("Received notification on channel notification_channel, having payload: "+pl)    }else {fmt.Println("Received notification on channel notification_channel without payload")    }});e!=nil {// handle error}

Unlisten executesUNLISTEN channel. Unregister function f, that was registered with Listen(channel ,f).

e:=db.Unlisten("notification_channel")// handle error

You can unlisten from every channel callingdb.Unlisten("*") or using the methodUnlistenAll

UnlistenAll executesUNLISTEN *. Thus do not receive any notification from any channel.

With Notify you can send a notification with or without payload on a channel.

e=db.Notify("notification_channel")// empty payloade=db.Notify("notification_channel","payload 1","payload 2","test")

When sending a payload, the strings are joined together. Therefore the payload sent with previous call toNotify is:payload 1, payload 2, test

In GORM, you can execute

db.Model(User{}).Select("username")

db.Select("username").Model(User{})

and achieve the same result.

In igor this is not possible. Youmust callModel beforeSelect.

Thus always use:

db.Model(User{}).Select("username")

The reason is that igor generates queries in the formSELECT table.field1, table.filed2 FROM table [WHERE] RETURNING table.field1, table.filed2.

In order to avoid ambiguities when usingJoins, theRETURNING part of the query must be in the formtable.field1, table.filed2, ..., and table is theTableName() result of theDBModel passed asModel argument.

Igor models arethe same as GORM models (except that you have to use theigor tag field to define the primary key). Thesql tag field is used to define default value and column value. Eg:

typeTeststruct {IDuint64`igor:"primary_key column:id_column"`Time    time.Time`sql:"default:(now() at time zone 'utc')"`}

The other main difference is that igor models require the implementation of theDBModel interface.

In GORM, you can optionally define theTableName method on your Model. With igor this is mandatory.

This constraint gives to igor the ability to generate conditions (like theWHERE orINSERT orUPDATE part of the query) that have a counter part on DB size for sure.

If a type does not implement theDBModel interface your program will not compile (and thus you can easily find the error and fix it). Otherwise igor could generate a wrong query and we're trying to avoid that.

Since igor is PostgreSQL only, thegorm.Open method has been replaced with

Connect(connectionStringstring) (*Database,error)

There's nodb.LogMode(bool) method in igor. If you want to log the prepared statements, you have to manually set a logger for igor.

logger:=log.New(os.Stdout,"query-logger: ",log.LUTC)db.Log(logger)

If you want to disable the logger, set it to nil

db.Log(nil)

Privacy: you'llnever see the values of the variables, but only the prepared statement and the PostgreSQL placeholders. Respect your user privacy, do not log user input (like credentials).

In GORM, every method (even the ones that execute queries) returns a*DB.

In igor:

• methods that execute queries returnserror
• methods that build the query returns*Database, thus you can chain the methods (GORM-like) and build the query.

In GORM,Scan method is used to scan query results into a struct. TheFind method is almost the same.

In igor:

• Scan method executes theSELECT query. Thus return an error ifScan fails (see the previous section).

  Scan handle every type. You can scan query results in:

  • slice of struct.Scan(&sliceOfStruct)
  • single struct.Scan(&singleStruct)
  • single value.Scan(&integerType)
  • a comma separated list of values (becauseScan is a variadic arguments function).Scan(&firstInteger, &firstString, &secondInteger, &floatDestination)

• Find method does not exists, is completely replaced byScan.

In addiction to the previous section, there's another difference between GORM ad igor.

Scan methoddo not scans the selected fields into results using the selected fields name, but uses the order (to increase the performance).

Thus, having:

typeConversationstruct {Fromstring`json:"from"`Time   time.Time`json:"time"`ToReadbool`json:"toRead"`}varconvList []Conversationerr:=Db().Raw(`SELECT DISTINCT otherid, MAX(times) as "time", to_read FROM (    (SELECT MAX("time") AS times, "from" as otherid, to_read FROM pms WHERE "to" = ? GROUP BY "from", to_read)    UNION    (SELECT MAX("time") AS times, "to" as otherid, FALSE AS to_read FROM pms WHERE "from" = ? GROUP BY "to", to_read)) AS tmp GROUP BY otherid, to_read ORDER BY to_read DESC, "time" DESC`,user.Counter,user.Counter).Scan(&convList)

Do not cause any problem, but if we change the SELECT clause, inverting the order, like

query:="SELECT DISTINCT otherid, to_read, MAX(times) as time "+...

Scan will fail because it will try to Scan the boolean value in second positionto_read, into thetime.Time field of the Conversation structure.

In GORM, if you do not specify a primary key or a where clause (or if the value of the primary key is blank) the generated query will be

DELETE FROM <table>

That will delete everything from your table.

In igor this is not possible.

Youmust specify aWhere clause or pass toDelete a non empty model that will be used to build the where clause.

db.Delete(&UserPost{})// this panicspost:=UserPost{Hpid:10,From:1,}db.Delete(&post)//generates DELETE FROM posts WHERE hpid = $1, because hpid is a primary keydb.Where(&post).Delete(&UserPost{})// ^ generates the same querydb.Delete(&UserPost{From:1,To:1})// generates: DELETE FROM posts WHERE "from" = $1 AND "to" = $2

In GORMFirst is used to get the first record, with or without a second parameter that is the primary key value.

In igor this is not possible.First works only with 2 parameter.

• DBModel: that's the model you want to fill
• key interface{} that's the primary key value, thatmust be of the same type of theDBModel primary key.

varuserUserdb.First(&user,uint64(1))db.First(&user,"1")// panics, because "1" is not of the same type of user.Counter (uint64)

it generates:

SELECTusers.counter,users.last,users.notify_story,users.private,users.lang,users.username,users.email,users.name,users.surname,users.gender,users.birth_date,users.board_lang,users.timezone,users.viewonline,users.registration_timeFROM usersWHEREusers.counter= $1

Every other GORM method is not implemented.

Igor supports PostgreSQL JSON and JSONB types natively.

Just define the field in the DBModel with the typeigor.JSON.After that, you can work with JSON in the following way:

user:=createUser()varns igor.JSON=make(igor.JSON)// use it like a map[string]interface{}ns["0"]=struct {Fromuint64`json:from`Touint64`json:to`Messagestring`json:message`}{From:1,To:1,Message:"hi bob",}ns["numbers"]=1ns["test"]=2user.NotifyStory=nsife=db.Updates(&user);e!=nil {t.Errorf("updates should work but got: %s\n",e.Error())}// To use JSON with json, use:// printableJSON, _ := json.Marshal(user.NotifyStory)// fmt.Printf("%s\n", printableJSON)varnsNew igor.JSONife=db.Model(User{}).Select("notify_story").Where(&user).Scan(&nsNew);e!=nil {t.Errorf("Problem scanning into igor.JSON: %s\n",e.Error())}

PostgreSQL give us a beautiful method to avoid polling the DBMS, using a simple publish/subscribe model over database connections (read more on thedocs).

Igor gives you the ability to generate notification and subscribe to notifications sent over a channel, using the methodsListen andNotify.

Below there's a working example:

count:=0ife=db.Listen("notification_without_payload",func(payload...string) {count++t.Log("Received notification on channel: notification_without_payload\n")});e!=nil {t.Fatalf("Unable to listen on channel: %s\n",e.Error())}fori:=0;i<4;i++ {ife=db.Notify("notification_without_payload");e!=nil {t.Fatalf("Unable to send notification: %s\n",e.Error())    }}// wait some time to handle all notifications, because are asynchronoustime.Sleep(100*time.Millisecond)ifcount!=4 {t.Errorf("Expected to receive 4 notifications, but counted only: %d\n",count)}// listen on an opened channel should failife=db.Listen("notification_without_payload",func(payload...string) {});e==nil {t.Errorf("Listen on an opened channel should fail, but succeeded\n")}// Handle payload// listen on more channels, with payloadcount=0ife=db.Listen("np",func(payload...string) {count++t.Logf("channel np: received payload: %s\n",payload)});e!=nil {t.Fatalf("Unable to listen on channel: %s\n",e.Error())}// test sending payload with notifyfori:=0;i<4;i++ {ife=db.Notify("np",strconv.Itoa(i)+" payload");e!=nil {t.Fatalf("Unable to send notification with payload: %s\n",e.Error())    }}// wait some time to handle all notificationstime.Sleep(100*time.Millisecond)ifcount!=4 {t.Errorf("Expected to receive 4 notifications, but counted only: %d\n",count)}// test unlistenife=db.Unlisten("notification_without_payload");e!=nil {t.Errorf("Unable to unlisten from notification_without_payload, got: %s\n",e.Error())}// test UnlistenAllife=db.UnlistenAll();e!=nil {t.Errorf("Unable to unlistenAll, got: %s\n",e.Error())}

Do you want to add some new method to improve GORM compatibility or add some new method to improve igor?

Feel free to contribute via Pull Request.

To test igor, you must create a igor user on PostgreSQL and make it own the igor database.On Archlinux, withpostgres as the PostgreSQL superuser this can be achieved by:

createuser -U postgres igorcreatedb -U postgres igor igorpsql -U postgres -d igor -c"GRANT USAGE, CREATE ON SCHEMA public TO igor;"

You can run tests with the usual command:

gotest

Copyright 2016-2023 Paolo Galeone. All right reserved.

Licensed under the Apache License, Version 2.0 (the "License");you may not use this file except in compliance with the License.You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, softwaredistributed under the License is distributed on an "AS IS" BASIS,WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.See the License for the specific language governing permissions andlimitations under the License.

Feel free to contact me (you can find my email address and other ways to contact me in my GitHub profile page).