The art of simplicity

Data API builder 2.0 (currently in public preview) is a major release focused on MCP and AI integration. Among its headline features is the ability to expose stored procedures as custom MCP tools , making them discoverable and callable by AI agents. No glue code, no middleware, no extra plumbing. In this post I'll walk through how the feature works, and show a practical example: wiring up a full-text search stored procedure as its own dedicated tool that any MCP client can discover and call by name. The idea: a dedicated search tool By default, DAB's SQL MCP Server exposes tables and views through generic DML tools — things like list_books , get_book , and so on. These are useful for straightforward CRUD, but they're not designed for complex operations like full-text search. With custom-tool: true , you can go further. Set that flag on a stored-procedure entity and DAB dynamically registers the procedure as a named, purpose-built tool in tools/list . The AI agent di...

While investigating some database related performance issues, I discovered the following website:   This site explains everything you as a developer need to know about SQL indexing. Use The Index, Luke is the free web-edition of SQL Performance Explained . It presents indexing in a vendor agnostic fashion but also share product specific guidelines for DB2, MySQL, Oracle, PostgreSQL and SQL Server. Do you think you already know everything you need to know about SQL indexing? Confirm your skills by taking this short 3 minute test: 3-Minute Test: What do you know about SQL performance? I could not say that I did so great:

These days, with Entity Framework Core being so good, it became rare that developers had to write their own SQL queries. Of course this is a great productivity one and even helps us to avoid security issues like SQL injection but this all comes with a cost. I noticed that most (junior) developers no longer are able to write anything but the most trivial SQL queries.  Time to fix that! If you want to sharpen your SQL skills, have a look at https://www.sql-practice.com/ . Here you  get a whole list of exercises that require writing SQL queries. If you get stuck hints are available that help you get on the right track:  

I was talking to a colleague this week and he was sharing a story about some memory issues they had with Marten , the DocumentDB and EventStore on top of PostgreSQL. As I was partially involved during the investigation of the memory issue, I was eager to learn what the root cause was and how they fixed it. Let me share their findings… Marten extensively uses runtime code generation through Roslyn. Although this is really powerful it comes with a cost in terms of memory usage and cold start issues. Luckily this is something you can fix by generating the necessary types upfront and include the generated code in your application assemblies. The documentation describes the required steps quite well: Use the Marten command line extensions for your application Register all document types, compiled query types , and event store projections upfront in your DocumentStore configuration In your deployment process, you'll need to generate the Marten code with dotnet r...

Visual Studio 2019 version 16.3 extends the Performance Profiler with a new ‘Database’ option.  This will enable the new Database tool that captures all database activity during your profiling session. Remark: The database tool works with .NET Core projects  using either ADO.NET or Entity Framework Core . It also works on traces collected using dotnet trace which means we can collect data anywhere that .NET Core runs (including Linux!) and analyze that data in Visual Studio. Let’s try it: Open the Performance Profiler in Visual Studio by clicking Debug > Performance Profiler. Select the checkbox next to “Database” to enable the tool. Click on Start to start the profiling session. Now interact with your application in the ways you’re interested in investigating. When you are done click ‘Stop collection’. Now you get a table of all the queries that happened during your profiling session along with a graph that shows when and how many queries h...

One of the cool features that PostgreSQL has to offer is the support for array data types . This allows you to store an array in a single column without the need to either use a separate table or concatenate the values in a single  string. To use this in Entity Framework through Npgsql you simply need to define a regular .NET array or List<> property: The provider will create text[] columns for the above two properties, and will properly detect changes in them - if you load an array and change one of its elements, calling SaveChanges() will automatically update the row in the database accordingly. More information: https://www.npgsql.org/efcore/mapping/array.html

I didn’t found the documentation really clear on the steps involved when using Grain Persistence in combination with PostgreSQL. So here is my attempt to describe the process: Add a reference to the following NuGet packages: Microsoft.Orleans.OrleansSQLUtils Microsoft.Orleans.Persistence.AdoNet Npgsql Update the cluster configuration to add the storage: Invariant should be set to ‘npgsql’ ConnectionString should be set to the connection to the PostgreSQL instance you want to use UseJsonFormat should be set to true as PostgreSQL has good Json support. Let your grain inherit from Grain<> and specify a state object: Add a StorageProvider attribute on top of your Grain and set the name to the storage name you used inside the cluster config In our example we had set this to ‘OrleansStorage’: Execute the PostgreSQL-Main.sql and PostgreSQL-Persistence.sql scripts inside the OrleansAdoNetContent/P...

I started using Azure Data Studio a few weeks ago to talk to PostgreSQL and I must say that I do like it, especially when comparing it to PGAdmin. I had a few issues where I lost my connection, but a refresh always solved it so far. Yesterday I stumbled over an error when trying to execute the following query: When executing this query, it failed with the following error message: Started executing query at Line 1 Commands completed successfully Unhandled exception while executing query: 'dict' object has no attribute 'encode' Total execution time: 00:00:00.138 The problem happens when I try to query the payloadjson column which contains a JSONB type. As a workaround I explicitly added a cast to JSON to the query: More information: https://github.com/Microsoft/azuredatastudio-postgresql/issues/94

When trying to drop a database in PostgreSQL, it failed with the following error message: 11:32:15 AMStarted executing query at Line 1 database "sampledb" is being accessed by other users Total execution time: 00:00:05.016 Whoops, seems that our database is still in use. Let’s kill all active connections Let’s now try to execute the drop database again:

Some posts ago I complained about my experience when using EF Core in combination with PostgreSQL.  One of the things I didn’t liked was that when EF Core couldn’t translate a LINQ query, it silently felled back to client side evaluation, and executed the query on the in-memory collection. This turns out not be specifically related to the EF Core implementation for PostgreSQL, but is in fact general behavior of .NET Core. I only didn’t noticed that the same thing happened with SQL Server as the EF Core provider for SQL Server is a lot smarter and can translate more LINQ queries correctly. I said the following in the previous post: The only way to discover this is through the logs where you see a warning when the driver couldn’t translate your LINQ statement. If you didn’t check the logs, you aren’t even aware of the issue. Yesterday I discovered that you can change this behavior.  Therefor I had to call the ConfigureWarnings() and set the following option: Now...

To monitor the performance of one of our applications I asked to activate the pg_stat_statements module on PostgreSQL. From the documentation: The pg_stat_statements module provides a means for tracking execution statistics of all SQL statements executed by a server. The module must be loaded by adding pg_stat_statements to shared_preload_libraries in postgresql.conf , because it requires additional shared memory. This means that a server restart is needed to add or remove the module. So I asked the admins to enable the module and restart the server. Once I got confirmation that the restart was done I tried to call the stored procedure: SELECT * FROM pg_stat_statements ORDER BY total_time DESC; Unfortunately this resulted in an error message: Query failed: ERROR: relation "pg_stat_statements" does not exist Let’s check if the module is indeed installed: SELECT * FROM pg_available_extensions That seems OK. It tur...

When trying to create a new ISession instance, NHibernate throwed the following error message: Dialect does not support DbType.Guid Parameter name: typecode at NHibernate.Dialect.TypeNames.Get(DbType typecode) at NHibernate.Mapping.Table.SqlTemporaryTableCreateString(Dialect dialect, IMapping mapping) at NHibernate.Mapping.PersistentClass.PrepareTemporaryTables(IMapping mapping, Dialect dialect) at NHibernate.Impl.SessionFactoryImpl..ctor(Configuration cfg, IMapping mapping, Settings settings, EventListeners listeners) at NHibernate.Cfg.Configuration.BuildSessionFactory() at FluentNHibernate.Cfg.FluentConfiguration.BuildSessionFactory() As you can probably guess from the title, we are using PostgreSQL and it turns out that the default used Dialect doesn’t support Guids. Luckily this was fixed a long time ago and the only thing I had to do was specify explicitly a higher Dialect version:

In a previous post I mentioned we were using PostgreSQL together with Entity Framework Core. One of the things I stumbled over when trying to use NHibernate instead was that Entity Framework Core uses Pascal Casing to generate the names of Tables, Columns and queries whereas NHibernate uses lowercasing(which is in fact more suitable for PostgreSQL, but that is another discussion). One of the reasons I like NHibernate so much is that it is fully customizable and that we can easily change the naming strategy to take the EF Core conventions into account. Start by creating a custom naming strategy: As a second step, register this naming strategy on your NHibernate configuration object(in this example through Fluent NHibernate):

On one of my projects we are using Entity Framework Core. Although my experience on combining Entity Framework Core and SQL Server was quite OK, I couldn’t say the same thing when using the Entity Framework Core drivers for PostgreSQL( https://www.npgsql.org/efcore/index.html ). Although you get the impression that everything works, the moment you have a look at the logs you see 2 obvious issues: For a lot of LINQ statements the driver cannot correctly translate it to a working SQL statement. So what happens is that everything is loaded in memory and the expression is executed on the in-memory collection. The only way to discover this is through the logs where you see a warning when the driver couldn’t translate your LINQ statement. If you didn’t check the logs, you aren’t even aware of the issue. The generated SQL statements are far from optimal. For example every time you add an include an extra correlated subquery is generated which turns out to be quite slow on PostgreSQ...

With the latest release of Azure Data Studio, a new (preview) extension was introduced that supports PostgreSQL. Enabling the extension Open up Azure Data Studio . Click on the Extension tab on the left. Click in the Search Extensions in Marketplace textbox and start typing ‘PostgreSQL’. You should find the PostgreSQL extension from Microsoft. Select he Extension and click Install.

I’m a big fan of Marten, the document database on top of PostgreSQL. With the Marten 3.4 release last week, we finally got full text search support (together with some other bug fixes and some performance improvements). PostgreSQL has built in full text search, with this release this functionality finally becomes available in Marten. Using it is as easy as writing the following LINQ statement: More information here: http://jasperfx.github.io/marten/documentation/documents/querying/linq/#sec24 Remark: To use this feature, you will need to use PostgreSQL version 10.0 or above, as this is the first version that support text search function on jsonb column - this is also the data type that Marten use to store it's data.

In a previous post I explained on how to enable spatial type support for your Entity Framework (core) model. If you added the HasPostgresExtension("postgis") check, you get the following error message when you try to execute the Entity Framework migration and the extension is not installed: could not open extension control file "/usr/share/postgresql/9.5/extension/postgis.control": No such file or directory To install the extension you can follow the instructions on the postgis website: https://postgis.net/install/ . The installation differs based on the host platform you are using for your PostgreSQL instance. After installation has completed you can activate the extension for you database using the following statement: -- Enable PostGIS (includes raster) CREATE EXTENSION postgis;

To enable Spatial Type support for PostgreSQL you have to do an extra step when configuring your DBContext: Calling the UseNetTopologySuite() method activates a plugin for the Npgsql EF Core provider which enables mapping NetTopologySuite's types to PostGIS columns, and even translate many useful spatial operations to SQL. This is the recommended way to interact with spatial types in Npgsql. To check if the PostGIS extension is installed in your database, you can add the following to your DbContext:

After updating Npgsql , Marten started to throw exceptions: [NullReferenceException: Object reference not set to an instance of an object.] Marten.Util.TypeMappings.ToDbType(Type type) +93 Marten.Schema.DuplicatedField..ctor(EnumStorage enumStorage, MemberInfo[] memberPath) +141 Marten.Schema.DocumentMapping.DuplicateField(MemberInfo[] members, String pgType, String columnName) +195 Marten.Events.EventQueryMapping.duplicateField(Expression1 property, String columnName) +94 Marten.Events.EventQueryMapping..ctor(StoreOptions storeOptions) +485 Marten.Storage.StorageFeatures.PostProcessConfiguration() +364 Marten.DocumentStore..ctor(StoreOptions options) +259 Marten.DocumentStore.For(Action1 configure) +60 A quick search through the issues list on Github, brought me to the following page: https://github.com/JasperFx/marten/issues/1044 Good news, the issue is already resolved and closed in Marten v3(alpha 2 version is available).

For an application we are building we had the following requirement: A unique increasing number should be generated for every document on a year by year basis. So at the end of each year the document counter should be reset to 0 and start to increase again . As we are using PostgreSQL as our database, I decided to implement this feature using sequences . First thing I did was creating a function that generates a new sequence based on a specific name: This allows me to create a new sequence dynamically at the beginning of each year. Next thing I did was creating another function that will first invoke the f_create_seq function to see if a sequence already exists and then calls this sequence to get the next number in line. I invoke this function from my application where I pass a year as the sequence name parameter: