This plugin is used to generate Facebook's dataloaders for each table in the database. As a dataloader library we use DataLoader which is a Go implementation of Facebook's DataLoader. This plugin works with the SQLc code generator.
Dataloaders comes to us from the GraphQL world, but they can be used in any application where you need to batch and cache requests to the database. The help to reduce the number of queries to the database and improve the performance of the application when you need to get linked data but don't or cannot use joins in your queries.
First of all you need to install the SQLc code generator. You can find the installation instructions here.
After that you need to install the plugin. You can do it by running the following command:
git clone [email protected]:debugger84/sqlc-dataloader.git
cd sqlc-dataloader
make allThen you need to configure your sqlc.yaml file to use the plugin. You can find the configuration instructions here. For example, you can add the following lines to your sqlc.yaml file:
version: '2'
plugins:
- name: dataloader
process:
## The path to the sqlc-dataloader binary
cmd: "../sqlc-dataloader/bin/sqlc-dataloader"
- name: golang
## The path to the sqlc-gen-go wasm fork.
## This fork improves the names of the generated structs if they are in different PostgreSQL schemas.
## But you can use the original sqlc-gen-go wasmas well.
wasm:
url: "https://github.com/debugger84/sqlc-gen-go/releases/download/v1.3.1/sqlc-gen-go.wasm"
sha256: "fe6e5a2b75153ecba02b0c30bf4a11db2120bef537b650299473da133d272bf4"
sql:
- schema:
- "migration"
- "../types/migration"
queries: "queries"
engine: "postgresql"
codegen:
- plugin: dataloader
## The path to the generated code. Should be the same as the path in the golang plugin.
out: "./"
options:
## The package name for the generated code.
## In this case the dataloaders will be added to the subfolder "dataloaders"
## instead of storing in the same folder as other generated files.
package: "dataloader"
## The package name for the generated by golang plugin models.
## Should be set up if the "package" option is configured previously.
model_import: "sqlc-gen-test/test"
## Cache configuration for the dataloaders.
cache:
## The loader's table name in the format schema.tablename.
table: "public.test"
## Type is the type of the cache. Available types: memory, lru, no-cache.
type: "lru"
## Ttl is the time to live for the items in cache. It is used only for lru cache.
## The value should be in the format "1m" - 1 minute, "1h" - 1 hour, "1d" - 1 day.
## If the value is empty, the cache will not expire.
## Example: "1d3h20m40s"
ttl: "1m"
## Size is the size of the cache in items in cache. It is used only for lru cache.
size: 100
## The primary keys columns for the tables. In a format tablename.fieldname.
## The dataloader will use these columns to batch the requests.
## By default, the plugin will use the "id" column as the primary key.
primary_keys_columns:
- "test.test_id"
- "test2.code"
## Skipped tables. The dataloaders will not be generated for these tables.
## By default, the plugin will generate the dataloaders for all tables in the database.
## The name of table should be in the format schema.tablename.
exclude_tables:
- "public.test"
## All the next options should be the same as in the "golang" plugin.
sql_package: "pgx/v5"
default_schema: "test"
overrides:
- db_type: "uuid"
nullable: true
engine: "postgresql"
go_type:
import: "github.com/gofrs/uuid"
package: "uuid"
type: "NullUUID"
- db_type: "uuid"
nullable: false
engine: "postgresql"
go_type:
import: "github.com/gofrs/uuid"
package: "uuid"
type: "UUID"
- plugin: golang
out: "./"
options:
package: "test"
sql_package: "pgx/v5"
default_schema: "test"
exclude:
- "User.createdAt"
- "UpdateStatusInput.id"
## The same overrides as in the dataloader plugin.
## If you wish to change some types, you need to do it in both plugins.
overrides:
- db_type: "uuid"
nullable: true
engine: "postgresql"
go_type:
import: "github.com/gofrs/uuid"
package: "uuid"
type: "NullUUID"
- db_type: "uuid"
nullable: false
engine: "postgresql"
go_type:
import: "github.com/gofrs/uuid"
package: "uuid"
type: "UUID"After you have configured the plugin you can run the sqlc code generator as usual:
sqlc generateThe plugin will generate the dataloaders for each table in the database. The dataloaders will be stored in the subfolder "dataloaders" in the package you have configured in the sqlc.yaml file.
For example, if you have the following table in the database:
CREATE TABLE users (
id uuid PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL
);The plugin will generate the following dataloader:
package dataloader
import (
"context"
uuid "github.com/gofrs/uuid"
"github.com/graph-gophers/dataloader/v7"
"github.com/jackc/pgx/v5"
"sqlc-gen-test/test"
)
type UserLoader struct {
innerLoader *dataloader.Loader[uuid.UUID, test.User]
db test.DBTX
}
func NewUserLoader(db test.DBTX) *UserLoader {
return &UserLoader{
db: db,
}
}
func (l *UserLoader) getInnerLoader() *dataloader.Loader[uuid.UUID, test.User] {
if l.innerLoader == nil {
l.innerLoader = dataloader.NewBatchedLoader(
func(ctx context.Context, keys []uuid.UUID) []*dataloader.Result[test.User] {
testMap, err := l.findItemsMap(ctx, keys)
result := make([]*dataloader.Result[test.User], len(keys))
for i, key := range keys {
if err != nil {
result[i] = &dataloader.Result[test.User]{Error: err}
continue
}
if loadedItem, ok := testMap[key]; ok {
result[i] = &dataloader.Result[test.User]{Data: loadedItem}
} else {
result[i] = &dataloader.Result[test.User]{Error: pgx.ErrNoRows}
}
}
return result
},
)
}
return l.innerLoader
}
func (l *UserLoader) findItemsMap(ctx context.Context, keys []uuid.UUID) (map[uuid.UUID]test.User, error) {
res := make(map[uuid.UUID]test.User, len(keys))
query := `SELECT * FROM test.test WHERE id = ANY($1)`
rows, err := l.db.Query(ctx, query, keys)
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
var result test.User
err := rows.Scan(
&result.ID,
&result.Name,
&result.Email,
)
if err != nil {
return nil, err
}
res[result.ID] = result
}
return res, nil
}
func (l *UserLoader) Load(ctx context.Context, testKey uuid.UUID) (test.User, error) {
return l.getInnerLoader().Load(ctx, testKey)()
}You can use the dataloaders in your application as follows:
package main
import (
"context"
"fmt"
uuid2 "github.com/google/uuid"
"log"
"time"
uuid "github.com/gofrs/uuid"
"github.com/jackc/pgx/v5/pgxpool"
"myproject/dataloader"
)
func main() {
ctx := context.Background()
db, err := pgxpool.Connect(ctx, "postgres://user:password@localhost:5432/db")
if err != nil {
log.Fatal(err)
}
defer db.Close()
loader := dataloaders.NewLoader(db)
id := uuid.Must(uuid.FromString("00000000-0000-0000-0000-000000000001"))
result, err := loader.Load(ctx, id)
if err != nil {
log.Fatal(err)
}
fmt.Printf("Key: %v, Result: %v\n", id, result)
}Real life example of the dataloaders usage you can find in the examples/dataloader folder.