SQLC: Int32 Vs. Int64 For Limit/Offset - A Deep Dive

Oct 20, 2025 by Dimemap Team 53 views

SQLC: Understanding int32 vs. int64 for Limit/Offset

Hey guys! Ever stumbled upon a quirky behavior in SQLC, especially when dealing with limit and offset in your queries? I've been there, and today, we're diving deep into a specific issue: the difference in how SQLC generates the data types for limit and offset parameters when you're using a schema file versus connecting to a managed database. Let's break it down and see how to navigate this.

The Core Issue: int32 vs. int64

The heart of the matter lies in how SQLC infers the data types for your query parameters, specifically when you're using limit and offset. Generally speaking, when you have an int64 primary key in your database table, SQLC should ideally generate int64 types for these parameters. This ensures that you can handle large offsets and limits without running into potential overflow issues. With a managed database connection, SQLC seems to get this right. However, when you rely solely on a schema file (schema.sql), there's a chance SQLC might generate int32 types instead. This discrepancy can lead to unexpected behavior and potentially limit the range of values you can use for pagination.

Imagine you're building an e-commerce platform, and you need to paginate through a vast product catalog. If you're using int32 for offset, you're limited to a maximum offset of a little over 2 billion items. While this seems like a lot, it's not a lot in the grand scheme of things! Using int64 gives you a significantly larger range, accommodating larger datasets and future growth. So, getting this right from the start is super important.

The Setup: Schema, Queries, and Configuration

To really understand what's happening, let's look at the setup that highlights this issue. This includes the database schema, the SQL queries, and the SQLC configuration. Let's say we have a simple table called items with an id column defined as BIGSERIAL PRIMARY KEY. Here's what that looks like in SQL:

CREATE TABLE items (
    id BIGSERIAL PRIMARY KEY
);

Next, we have a simple SQL query that uses limit and offset to fetch a subset of items:

-- name: ListItems :many
SELECT *
FROM items
LIMIT $1 OFFSET $2;

This query is designed to paginate through the items table. The parameters $1 and $2 will correspond to the limit and offset values, respectively.

Finally, we have the SQLC configuration file. The configuration file tells SQLC how to connect to your database and generate code. In this case, we have a configuration that uses a schema file:

version: "2"

sql:
  - engine: "postgresql"
    queries: "queries.sql"
    schema: "schema.sql"
    gen:
      go:
        package: "dbsqlc"
        out: "dbsqlc"
        emit_pointers_for_null_types: true
        sql_package: "pgx/v5"

In this setup, you'll notice that the database section is commented out, indicating that we're relying on the schema file alone. Now, if you generate the code using SQLC, the generated Go code will have int32 types for limit and offset.

The Root Cause: Schema File vs. Managed Database

So, why does this happen? The difference arises from how SQLC infers the types when it uses a schema file versus when it connects to a managed database. With a managed database connection, SQLC can query the database to determine the exact data types of the columns and generate the correct Go types. When you're using a schema file, SQLC relies on the information provided in that file. It is possible that the schema file doesn't always provide enough information for SQLC to accurately determine the correct type for limit and offset, leading it to default to int32.

The Fix: Workarounds and Solutions

If you find yourself facing this issue, here are a few ways to get around it and ensure that limit and offset are correctly generated as int64:

Use a Managed Database Connection: The most reliable solution is to uncomment the lines in your SQLC configuration file that define the database connection. This way, SQLC can connect to your database and infer the data types accurately. The advantage of this approach is that it is the most reliable and ensures that the generated code correctly reflects your database schema. However, you might not always want a connection to the database. For example, during CI/CD, you may want to avoid this approach for different reasons.
Explicitly Define Types in SQL Queries: You can explicitly cast the limit and offset parameters within your SQL queries. This forces the database to treat them as the correct type. For example:
```
-- name: ListItems :many
SELECT *
FROM items
LIMIT $1::bigint OFFSET $2::bigint;
```
By casting the parameters to bigint, you ensure that the database interprets them as int64. The downside is that it makes your SQL queries slightly less portable across different database systems.
Manually Adjust the Generated Code: This is the least recommended option, but you could manually edit the generated Go code to change the types of limit and offset from int32 to int64. This is not a great solution because every time you generate the code, you'll need to do it again. It can be a temporary fix, but it's not a sustainable solution.
Update the Schema File: Make sure your schema.sql accurately reflects your database schema, including the correct data types. If there are any discrepancies, correct them and regenerate the code.

Best Practices: Keeping Your Code Clean and Accurate

To make sure you're generating the right code and avoiding these types of issues, it's a good idea to follow some best practices:

Always Use a Managed Database Connection When Possible: This will give you the most accurate results. It's the simplest and most effective approach.
Regularly Review Your Schema File: Keep your schema.sql in sync with your database schema. This helps prevent type mismatches and other issues.
Test Your Queries: Test the generated code, especially when using limit and offset. Write unit tests to ensure that the parameters are working as expected and handling large values correctly.
Stay Updated: Keep your SQLC version up to date. The SQLC team is constantly working on improvements and bug fixes, so updating to the latest version can often resolve issues.

Conclusion: Navigating SQLC's Type Inference

Understanding how SQLC infers data types, especially for limit and offset, is super important for writing efficient and reliable database code. This issue of int32 vs. int64 is something you might encounter, particularly when you're using a schema file instead of a managed database connection. By understanding the root causes, the available workarounds, and following best practices, you can effectively manage this and keep your code clean and accurate. So, go forth, and build awesome things!

I hope you found this guide helpful, guys! Let me know if you have any questions or experiences to share. Happy coding!