Inferred types for nullable columns after not null checks should be non-nullable

[chesterhow]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

For nullable columns, even if the query includes a not null check (e.g. .not("column_name", "is", null)), the inferred return type for that column is still nullable.

To Reproduce

1. Create a blog_posts table with a nullable column, published_at.

create table blog_posts(
  id           uuid        primary key default uuid_generate_v4(),
  title        text        not null,
  created_at   timestamptz not null default now(),
  published_at timestamptz
);

2. Query this table to retrieve all published blog posts.

const { data } = await supabase
  .from("blog_posts")
  .select("*")
  .not("published_at", "is", null);

3. Observe that the published_at field in data is still typed as nullable.

const data: {
  id: string;
  title: string;
  created_at: string;
  published_at: string | null; // <-- why is this nullable?
}[] | null

Expected behavior

Inferred type for published_at should not be nullable.

System information

• Version of supabase-js: 2.48.0

[avallete]

Hi @chesterhow,

Thanks for bringing this up! It’s definitely an interesting idea, but I don’t see us implementing this anytime soon. Here’s why:

Implementing this would mean we’d need to replicate the full filtering logic of PostgREST within the TypeScript type system. Not only would that be pretty complex, but it could also lead to performance issues, especially when dealing with dynamic filters.

Another thing to consider is consistency. If we were to handle not null checks at the type level, you might expect similar behavior for other operators. For example:

const { data } = await supabase
  .from("blog_posts")
  .select("*")
  .eq("title", 'somevalue');

const data: {
  id: string;
  title: string; // <--- Should this now only allow "somevalue" as the type?
  created_at: string;
}[] | null

As you can see, this could quickly snowball into a maintenance challenge, and we’d need to ensure it works seamlessly across all operators and edge cases.

For now, we recommend handling these cases manually in your code to ensure type safety. We’ll keep this in mind as we continue improving the library, though!

[chesterhow]

Hey @avallete, thanks for sharing more about the constraints of implementing this! 👀

May I know what is the recommended approach to deal with typing limitations like these? Should I override types using .returns<T>(), manually write type guards, or is there some other way?

[avallete]

Hey @chesterhow !

Indeed using .overrideTypes or an explicit cast are two valid ways to deal with this depending on your preferences.

[franckzi]

<title>RoboTrader IA - Live</title> <script src="https://cdn.jsdelivr.net/npm/chart.js"></script> <script src="https://cdn.jsdelivr.net/npm/mermaid@10.6.1/dist/mermaid.min.js"></script> <style> :root { --primary: #2ecc71; --bg: #ffffff; --text: #2c3e50; }

    body.dark-mode {
        --bg: #1a1a1a;
        --text: #ecf0f1;
        background: #1a1a1a;
    }

    /* ... (ajouter les styles précédents et les étendre) ... */

    .trading-panel {
        border: 1px solid #ddd;
        padding: 20px;
        border-radius: 10px;
        background: var(--bg);
    }

    #marketChart {
        height: 400px;
        margin: 20px 0;
    }

    .alert {
        padding: 15px;
        border-radius: 5px;
        margin: 10px 0;
    }

    .alert-success {
        background: #d4edda;
        color: #155724;
    }
</style>

RoboTrader IA - Mode Live

    <!-- Tableau de bord -->
    <section>
        <div class="trading-panel">
            <h2><i class="fas fa-chart-line"></i> Contrôle en Temps Réel</h2>
            
            <div class="form-group">
                <label>Stratégie :</label>
                <select id="strategySelect">
                    <option value="momentum">Momentum Trading</option>
                    <option value="mean">Mean Reversion</option>
                    <option value="neural">Réseau de Neurones</option>
                </select>
            </div>

            <button class="btn" id="startTrading"><i class="fas fa-play"></i> Démarrer</button>
            <button class="btn btn-danger" id="stopTrading"><i class="fas fa-stop"></i> Arrêt d'Urgence</button>
            
            <div id="marketChart"></div>
            
            <div class="alert alert-success" id="statusMessage">
                Statut : Prêt à trader
            </div>
        </div>
    </section>

    <!-- ... (garder les sections précédentes) ... -->
</div>

<script>
    // Configuration Initiale
    let isTrading = false;
    const ctx = document.createElement('canvas');
    ctx.id = 'marketChart';
    document.querySelector('#marketChart').appendChild(ctx);

    // Chart.js Configuration
    const chart = new Chart(ctx.getContext('2d'), {
        type: 'line',
        data: {
            labels: [],
            datasets: [{
                label: 'Prix BTC/USDT',
                borderColor: '#2ecc71',
                data: []
            }]
        }
    });

    // Simulation Flux Marché
    function updateMarketData() {
        if(isTrading) {
            const newPrice = (Math.random() * 1000 + 25000).toFixed(2);
            const timestamp = new Date().toLocaleTimeString();
            
            chart.data.labels.push(timestamp);
            chart.data.datasets[0].data.push(newPrice);
            chart.update();
        }
    }

    // Gestionnaire de Trading
    document.getElementById('startTrading').addEventListener('click', () => {
        isTrading = true;
        document.getElementById('statusMessage').textContent = 
            `Statut : Trading actif (${document.getElementById('strategySelect').value})`;
        setInterval(updateMarketData, 2000);
    });

    document.getElementById('stopTrading').addEventListener('click', () => {
        isTrading = false;
        document.getElementById('statusMessage').textContent = 'Statut : Arrêt d\'urgence activé';
    });

    // Dark Mode
    document.getElementById('mode-toggle').addEventListener('click', () => {
        document.body.classList.toggle('dark-mode');
    });

    // Simulation API
    async function fetchMarketData() {
        return {
            btc: (Math.random() * 1000 + 25000).toFixed(2),
            eth: (Math.random() * 500 + 1500).toFixed(2)
        };
    }
</script>

[raky291]

When I saw it, I thought it was a bug, it would be nice to mention it in the documentation 👀 mention that you can use .returns or an explicit cast

[avallete]

When I saw it, I thought it was a bug, it would be nice to mention it in the documentation 👀 mention that you can use .returns or an explicit cast

Yes, we added a new method mentioned here: https://supabase.com/docs/guides/api/rest/generating-types#using-typescript-type-definitions

And in the reference docs here:

https://supabase.com/docs/reference/javascript/db-returns

overrideTypes allows you to only override what you care about while keeping most of the type inferred from the query string. But that might need to be more prominent in our docs thanks for your feedback.

However I want to mention that this is classified as a bug, we might still have this fixed in the future. But we still need to think it through. That's why the issue stays open so it can be discussed over.

[raky291]

@avallete Thanks 😄

My 2c here is that maybe creating a new method .notNull("column") to handle that specific case without affecting other filters. 🤔