SQL generator & REST api generator based on SQLboiler

Current implementation is only done for postgresql, though other sql databases like mysql should be easy to implement due to simular features. REST api is build on gin-gonic.

Installation

1. Create a new go project (go mod new myproject)
2. Create a new folder called convert in your project, add your .env file with POSTGRESQL_URL & add your sqlboiler.toml config file
3. Create a new file called convert.go in your convert folder
4. Copy the following code into your convert.go file:

package main

import (
	"os"
	"strings"

	"github.com/arjendevos/gosql"
)

func main() {
	newDir, _ := os.Getwd()
	os.Chdir(strings.TrimSuffix(newDir, "/convert"))

	gosql.Convert(&gosql.GoSQLConfig{
		SchemeDir:           "schemes",
		MigrationDir:        "database/migrations",
		ModelOutputDir:      "models",
		ControllerOutputDir: "generated",
		SetupProject:        true,
	})
}

5. Run go mod tidy inside the convert folder
6. Create a new folder called schemes in your project
7. Create a new file called 1_migration.gosql in your schemes folder (you can name it whatever you want, but the number is important)
8. Add your models into the 1_migration.gosql file. Make sure to put @postgresql at the top (only needed for the first migration).
9. Run (cd convert && go run convert.go) in your project folder
10. Everything should be setup now, you can run go run main.go to start your server

Possibilities

Models

Example model:

User {
  id uuid @unique @default(uuid_generate_v4()) @index
  name string
  email string @unique
  password string @hide
  organization Organization
  createdAt dateTime @default(now)
  updatedAt dateTime @default(now)
  deletedAt dateTime
}

Authentication

Add authUser after your model to make it the main table for your user authentication. It will automatically add their relation to all tables. These columns are required:

• email string @unique
• password string

Use @hide to hide entire table from outside world.

@protected has the following options (@protected(LIST, BYID, CREATE, UPDATE, DELETE)):

• LIST - To protect the list endpoint
• BYID - To protect the by id endpoint
• CREATE - To protect the create endpoint
• UPDATE - To protect the update endpoint
• DELETE - To protect the delete endpoint

Data types

Type	PSQL Generator	API Generator
string, string(255)	✅	✅
bool	✅	✅
text	✅	✅
dateTime	✅	✅
int	✅	✅
any	✅	⬜

Attributes

Type	Meaning
? after type	Is nullable
@uniue	Is unique
@default(autoincrement)	Auto increment
@default(uuid_generate_v4())	Auto Generate uuid
@default(now)	Auto generate current time
@default("your default value")	Default string value
@default(false)	Default boolean value
@default(1)	Default int value
@index	Index on that column
@hide	Hide from outside world in the api
@regexp("your regexp")	Regexp validation for creating and updating

Relations

You can create a relation by adding a column name (this should be the table name with lowercase), like: account and you add as type the table name: Account with the first letter as capital. Don't refer both tables to each other, only one of them. Without @unique it is automatically a one to many relation. Optimize it by adding @index to the column.

• Add ? to make it nullable.
• Add @unique to make it a one to one relation.

Query parameters

• limit=3
• page=4
• filter={"column": {"equals":"true"}}
• filter={"column":{"equals":true, "or": {"lessThan": 4}}}
• filter={"column":{"equals":true, "or": {"lessThan": 4, "isNotIn": ["d"]}}
• filter={"relationTable": {"column": {"equals":true}}} (filter on relation)
• rels={"relationTable":{}}
• rels={"relationTable":{"deeperRelation":{ etc... }}} (you can't call the parent relation in the child relation)
• rels={"relationTable":{"_limit":4, "_page":1}} (only for nToMany relations
• order={"column": "desc", "column":"asc"}
• from=organization | from=user | no parameter (organization = get by organization id, get = fetch by user id, no parameter = get by organization id & user id)
• select=["column"] (omitempty fixes this on the json side)
• select relation columns. {"_select":["id", "created_at"]"}
• Add permissions to a role or user

IDs

• id uuid @unique @default(uuid_generate_v4()) @index
• id int @unique @default(autoincrement) @index

Todo

Current implementations & future plans:

• Add order by possibility
• Add rest of the endpoints with bodies etc
• Add or possibility in filter
• Export to typescript types
• Generate postgresql database setup files (client & migrations)
• Auto install deps
• Add authorization on User & Organization
• User authentication
• fetch items based on user_id or organization_id
• expose relation ids in api
• fetch relations for every request except create
• change relations to include relations of relation
• add pagination to relations
• can filter on relation id's
• can filter on if null
• filter in relations in filter
• fix if filter does not exists sql will output: WHERE ()
• limit queries to relations for x role
• setup entire project
• Test all endpoints (high priority)
• middelware is somehow called 3 times (high priority)
• add enum for role (low priority)
• ~~ [ ] add filter to relation (low priority)~~
• Add select columns on relation (_select:["id", "name"])
• Add select columns (normal not on relations)
• Add oauth2 login option
• Add oauth2 google login endpoints
• Add custom marshaller for time.Time (high priority)
• Make oauth2 google endpoints better based on scheme (org etc) (high priority)
• Select columns fix created_at and updated_at omitempty problem (low priority)
• Change From Query in Filter to InnerJoin for better performance (low priority)
• Find a way to pass in args with var names instead of raw sql (medium priority)
• Add oauth2 facebook login endpoints (low priority)
• Add oauth2 apple login endpoints (lowest priority)
• add email option (smtp with default templates) (low priority)
• add password forget endpoints (very low priority) (should implement with email)

Custom options

There is an option to add extra middleware in the auth middleware to handle role access. This will be generated automatically if you turn on SetupProject.