PlainSQL vs sqlc

PlainSQL and sqlc both generate Go code from SQL queries. This page shows the same tasks in both tools.

Name a parameter

In sqlc, you name a parameter with sqlc.arg inside the SQL:

-- name: GetUserID :one
SELECT id FROM users WHERE email = sqlc.arg(login_email);

In PlainSQL, the name goes in a comment:

-- plainsql: query GetUserID returns one
-- plainsql: param $1 login_email
SELECT id FROM users WHERE email = $1;

The SQL the database sees is unchanged. Without the param line, PlainSQL names the parameter email from the column. The annotation changes the Go name to loginEmail without touching the SQL.

Return a complete row

Both tools return a shared model when a query selects a complete table row. See sqlc’s selection example. In PlainSQL:

-- plainsql: query GetUser returns one
SELECT * FROM users WHERE id = $1;

This returns (User, error) with no record annotation. The same applies to u.* and to complete RETURNING results. If you select part of a row, rename a column, or add a computed column, PlainSQL generates a struct specific to that query.

Group an author

Sometimes you want a model inside another result, for example a post title next to its author as a User. sqlc does this with sqlc.embed. PlainSQL uses a record annotation:

-- plainsql: query GetPostWithAuthor returns one
-- plainsql: record u as author
SELECT p.title, u.*
FROM posts AS p
JOIN users AS u ON u.id = p.user_id
WHERE p.id = $1;

The result has Title string and Author User. If you select only some of the author’s columns, Author becomes an anonymous struct with those fields. In an outer join, the whole record can be nil.

Use transactions

In both tools, you can start a transaction yourself and bind the queries to it: sqlc’s WithTx or PlainSQL’s db.New(tx). PlainSQL also generates helpers that commit or roll back for you: ReadTx, ReadTxValue, ReadWriteTx, and ReadWriteTxValue. See queries and transactions.

Select query modes

PlainSQL can mark a set of SQL files as read. It checks that those queries only read data, and it puts their methods in a Read interface. Files in readwrite mode go in ReadWrite, which also includes every Read method. See query modes for the configuration.

Generated Go names and files

PlainSQL generates <QueryName>Params when a query has more than one parameter, and <QueryName>Row for a query-specific result. These types live next to their method in the .sql.go file. Shared models and enums live in models.go.