Error on run migration scripts

Hello,

We explore the development processes of Crate Db in order to use it in a project.

We create a simple instance of crate in docker with crate:latest( 5.1.3) and a sample project on .net using .net core 7

Based on your docs we use the stock driver .NET Npgsql (7.0.1). Also add Npgsql.EntityFrameworkCore.PostgreSQL 7.0.1 and Microsoft.EntityFrameworkCore.Design 7.0.1 in order to create the migration scripts

When I run the “dotnet ef migrations add init” command in order to create the migration cs file generate it ok .

When I run “dotnet ef database update” to run the migration in database an error occurred (Npgsql.PostgresException (0x80004005): XX000: line 4:43: mismatched input

‘PRIMARY KEY’ expecting ‘CHECK’)The error has to be with the syntax of the migration script that try to run.
The Code snippet has the error is CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" ( "MigrationId" character varying(150) NOT NULL, "ProductVersion" character varying(32) NOT NULL, CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId") );

In order to check the flow I try on a Postgres DB and create the database and the table just fine.

When I check the docs your crate table has differences with the ANSI SQL base on our site (Compare CrateDB with other databases in the market | Crate.io) that says it supports it.

My question is I am missing something in my code except the standard connection with DBContext in order to have the connection with db to create the script. (The sample project is at GitHub - echofin/crateMVP and have the messages_migration.sql that the second command try to run)

If I don’t have a missing step there will be other differences on ANSI SQL syntax on queries to the database so that I know.

Thank you in advance.

Hi Aristotelis,
The problem here is that the generated CREATE TABLE command is trying to give a name to the PK which is something CrateDB currently does not support, please upvote Support for named PRIMARY KEY constraints · Issue #11025 · crate/crate (github.com)
Please see if it allow you to move forward if you create the table manually without the name for the PK and let us know if you hit any other issues.
I hope this does not put you off from using CrateDB, there are small details like this but it does support very rich SQL syntax including windowing function and subqueries and also special syntax for things like objects that you do not find in other systems.

3 Likes

Hi,
Just wanted to let you know that support for named PK constraints has now been implemented and you can try it out in our latest nightly builds.

1 Like