Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
What I’ve learned so far using GraphQL + Postgres.
-- Postgres:
create extension pgcrypto;
create function gen_random_int(max int, out rand int) as $$
select floor(random() * max)::int + 1;
$$ language sql;
create function url62(out id text) as $$
declare
base text :=
'abcdefghijklmnopqrstuvwxyz' ||
'ABCDEFGHIJKLMNOPQRSTUVWXYZ' ||
'0123456789';
begin
id := '';
for x in 1..11 loop
id := id || substr(base, gen_random_int(length(base)), 1);
end loop;
end;
$$ language plpgsql;
create table users (
user_id text not null unique default 'u-' || url62(),
meta_created_at timestamptz not null default now(),
meta_updated_at timestamptz not null default now(),
meta_deleted_at timestamptz not null default now(),
display_name text not null,
username text not null check (username ~ '^[a-zA-Z_]\w*(?:.?[a-zA-Z_]\w*)*$') );
create table notes (
user_id text not null references users (user_id),
note_id text not null unique default 'n-' || url62(),
meta_created_at timestamptz not null default now(),
meta_updated_at timestamptz not null default now(),
meta_deleted_at timestamptz not null default now(),
title text not null,
data text not null );
insert into users (display_name, username) values ('Nikita', 'rdnkta');
insert into users (display_name, username) values ('Ana', 'nyxerys');
insert into users (display_name, username) values ('Zaydek', 'username_ZAYDEK');
insert into users (display_name, username) values ('Yubin', 'drawing_yubin');
insert into notes (user_id, title, data) values ((select user_id from users where display_name = 'Zaydek'), 'Introduction to GraphQL', 'Hello, world!');
insert into notes (user_id, title, data) values ((select user_id from users where display_name = 'Zaydek'), 'Hello, GraphQL', 'Hello, world!');
insert into notes (user_id, title, data) values ((select user_id from users where display_name = 'Zaydek'), 'GraphQL server using Go, Part 1', 'Hello, world!');
insert into notes (user_id, title, data) values ((select user_id from users where display_name = 'Zaydek'), 'GraphQL server using Go, Part 2', 'Hello, world!');
package main
import (
"database/sql"
"encoding/json"
"fmt"
"log"
"github.com/graphql-go/graphql"
_ "github.com/lib/pq"
)
type User struct {
UserID string
DisplayName string
Username string
Notes []Note
}
type Note struct {
UserID string
NoteID string
Title string
Data string
}
var userType = graphql.NewObject(
graphql.ObjectConfig{
Name: "User",
Fields: graphql.Fields{
"userID": &graphql.Field{
Type: graphql.NewNonNull(
graphql.ID,
),
},
"displayName": &graphql.Field{
Type: graphql.NewNonNull(
graphql.String,
),
},
"username": &graphql.Field{
Type: graphql.NewNonNull(
graphql.String,
),
},
"notes": &graphql.Field{
Type: graphql.NewList(noteType),
Resolve: func(p graphql.ResolveParams) (interface{}, error) {
userID := p.Source.(User).UserID
return ListNotes(userID)
},
},
},
},
)
var noteType = graphql.NewObject(
graphql.ObjectConfig{
Name: "Note",
Fields: graphql.Fields{
"userID": &graphql.Field{
Type: graphql.NewNonNull(
graphql.ID,
),
},
"noteID": &graphql.Field{
Type: graphql.NewNonNull(
graphql.ID,
),
},
"title": &graphql.Field{
Type: graphql.NewNonNull(
graphql.String,
),
},
"data": &graphql.Field{
Type: graphql.NewNonNull(
graphql.String,
),
},
},
},
)
func ListUsers() ([]User, error) {
var users []User
rows, err := db.Query(`
SELECT user_id, display_name, username
FROM users
`)
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
var user User
err := rows.Scan(&user.UserID, &user.DisplayName, &user.Username)
if err != nil {
return nil, err
}
users = append(users, user)
}
err = rows.Err()
if err != nil {
return nil, err
}
return users, nil
}
func GetUser(userID string) (User, error) {
var user User
err := db.QueryRow(`
SELECT user_id, display_name, username
FROM users
WHERE user_id = $1
`, userID).Scan(&user.UserID, &user.DisplayName, &user.Username)
if err != nil {
return User{}, err
}
return user, nil
}
func ListNotes(userID string) ([]Note, error) {
var notes []Note
rows, err := db.Query(`
SELECT user_id, note_id, title, data
FROM notes
WHERE user_id = $1
`, userID)
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
var note Note
err := rows.Scan(&note.UserID, &note.NoteID, &note.Title, &note.Data)
if err != nil {
return nil, err
}
notes = append(notes, note)
}
err = rows.Err()
if err != nil {
return nil, err
}
return notes, nil
}
func GetNote(noteID string) (Note, error) {
var note Note
err := db.QueryRow(`
SELECT user_id, note_id, title, data
FROM notes
WHERE note_id = $1
`, noteID).Scan(&note.UserID, &note.NoteID, &note.Title, &note.Data)
if err != nil {
return Note{}, err
}
return note, nil
}
var schemaConfig = graphql.SchemaConfig{
Query: graphql.NewObject(
graphql.ObjectConfig{
Name: "QueryRoot",
Fields: graphql.Fields{
"users": &graphql.Field{
Description: "List users.",
Type: graphql.NewList(userType),
Resolve: func(p graphql.ResolveParams) (interface{}, error) {
return ListUsers()
},
},
"user": &graphql.Field{
Description: "Get user by ID.",
Type: userType,
Args: graphql.FieldConfigArgument{
"userID": &graphql.ArgumentConfig{
Type: graphql.NewNonNull(
graphql.ID,
),
},
},
Resolve: func(p graphql.ResolveParams) (interface{}, error) {
userID := p.Args["userID"].(string)
return GetUser(userID)
},
},
"notes": &graphql.Field{
Description: "List a user’s notes.",
Type: graphql.NewList(noteType),
Args: graphql.FieldConfigArgument{
"userID": &graphql.ArgumentConfig{
Type: graphql.NewNonNull(
graphql.ID,
),
},
},
Resolve: func(p graphql.ResolveParams) (interface{}, error) {
userID := p.Args["userID"].(string)
return ListNotes(userID)
},
},
"note": &graphql.Field{
Description: "Get note by ID.",
Type: noteType,
Args: graphql.FieldConfigArgument{
"noteID": &graphql.ArgumentConfig{
Type: graphql.NewNonNull(
graphql.ID,
),
},
},
Resolve: func(p graphql.ResolveParams) (interface{}, error) {
noteID := p.Args["noteID"].(string)
return GetNote(noteID)
},
},
},
},
),
}
func check(err error) {
if err != nil {
log.Fatal(err)
}
}
var db *sql.DB
func main() {
// Connect to database:
var err error
db, err = sql.Open("postgres", "postgres://zaydek@localhost/graphql?sslmode=disable")
check(err)
err = db.Ping()
check(err)
defer db.Close()
// Query database:
query := `{
users {
userID
displayName
username
notes {
noteID
title
data
}
}
# users {
# userID
# displayName
# username
# notes {
# noteID
# title
# data
# }
# }
}`
schema, err := graphql.NewSchema(schemaConfig)
check(err)
params := graphql.Params{Schema: schema, RequestString: query}
result := graphql.Do(params)
if len(result.Errors) > 0 {
check(result.Errors[0])
}
marshaled, err := json.MarshalIndent(result, "", "\t")
check(err)
fmt.Printf("%s \n", marshaled)
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.