DuckDB - anyone tried ?

[peacemaker]

Why DuckDB

Why DuckDB There are many database management systems (DBMS) out there. But there is no one-size-fits all database system. All take different trade-offs to better adjust to specific use cases. DuckDB is no different. Here, we try to explain what goals DuckDB has and why and how we try to achieve...

duckdb.org

 

[Mashiane]

Looks interesting, but then again, as a system based on SQLite, I will always stick to PocketBase.

This can already be used with b4x, https://www.b4x.com/android/forum/t...th-postman-and-then-httputils.144227/#content

 

[aeric]

Earlier I also come across an interesting db call HyperSQL (HSQLDB) which is written in Java.

HSQLDB

hsqldb: Full-featured 100% Java RDBMS

hsqldb.org

I had thought before to support it in my DatabaseBuilder class.

 

[aeric]

Another good alternative is H2.

DuckDB vs. H2 Comparison

Detailed side-by-side view of DuckDB and H2

db-engines.com

 

[Daestrum]

HyperSQL

I like it, easy to embed.

 

[peacemaker]

Strange that https://db-engines.com/en/ranking has no PocketBase and HSQLDB.
Oops, HyperSQL is here, 86th.

https://db-engines.com/en/system/DuckDB;H2;HyperSQL - comparison

 

[Daestrum]

When I was trying HSQLDB - I had it open 3 databases - 2 memory and one disk based. Think it took like 10 lines of java.

public static void initDB(String dbName, String altdbName,int svrPort) throws ClassNotFoundException{

    server = new Server();
    server.setSilent(true);
    server.setLogWriter(null);
    server.setDatabaseName(0, dbName);
    server.setDatabasePath(0, "mem:"+dbName);
    server.setDatabaseName(1, altdbName);
    server.setDatabasePath(1, "file:"+altdbName);
    server.setDatabaseName(2, "groovytest");
    server.setDatabasePath(2, "mem:groovytest");
    server.setPort((int)svrPort); 
}

 

[peacemaker]

I posted it being impressed in DuckDB by

SELECT * FROM read_json("glossary.json")

SELECT * FROM read_json('https://support.oneskyapp.com/hc/en-us/article_attachments/202761627')

SELECT * FROM read_csv('flights.csv', delim = '|', header = true, columns = { 'FlightDate': 'DATE', 'UniqueCarrier': 'VARCHAR', 'OriginCityName': 'VARCHAR', 'DestCityName': 'VARCHAR' });

SELECT * FROM 'https://domain.tld/file.extension';

SELECT
*
FROM
users AS u
JOIN read_parquet('s3://test-duckdb/project/9999-12-31/0.gzip.parquet') AS p
ON u.staff_project = p.id_project

 

[aeric]

Strange that https://db-engines.com/en/ranking has no PocketBase

Basically it is SQLite

a system based on SQLite

Similar to Turso

Turso — SQLite for Production

Hi there... check here... https://turso.tech/ Wow... Something like azure/aws for sqlite... and it has free version too...

www.b4x.com

 

[aeric]

When I was trying HSQLDB - I had it open 3 databases - 2 memory and one disk based. Think it took like 10 lines of java.

public static void initDB(String dbName, String altdbName,int svrPort) throws ClassNotFoundException{

    server = new Server();
    server.setSilent(true);
    server.setLogWriter(null);
    server.setDatabaseName(0, dbName);
    server.setDatabasePath(0, "mem:"+dbName);
    server.setDatabaseName(1, altdbName);
    server.setDatabasePath(1, "file:"+altdbName);
    server.setDatabaseName(2, "groovytest");
    server.setDatabasePath(2, "mem:groovytest");
    server.setPort((int)svrPort); 
}

Do you use jdbc?

 

[Daestrum]

Do you use jdbc?

    Dim driver As String = "org.hsqldb.jdbc.JDBCDriver"
    For p = 0 To chk - 1
        Dim pp As String = ($"jdbc:hsqldb:hsql://localhost:${serverPort}/"$ & tables(p))
        sqlnames(p).Initialize2(driver, pp, user(p), "")
    Next

I had it so the server would use a random port between 20000 and 30000 hence serverPort

 

[aeric]

    Dim driver As String = "org.hsqldb.jdbc.JDBCDriver"
    For p = 0 To chk - 1
        Dim pp As String = ($"jdbc:hsqldb:hsql://localhost:${serverPort}/"$ & tables(p))
        sqlnames(p).Initialize2(driver, pp, user(p), "")
    Next

I had it so the server would use a random port between 20000 and 30000 hence serverPort

What is the purpose of random server Port?

 

[Daestrum]

What is the purpose of random server Port?

If I was looking for a database I would start with 3306 - with random port,any client that connects once validated, would get the port number to connect to.

 

[aeric]

If I was looking for a database I would start with 3306 - with random port,any client that connects once validated, would get the port number to connect to.

3306 is default port number used by MySQL.
So you mean you assign a dedicated port number to each client when they make a connection?

 

[aeric]

For DuckDB, do I need a C++ compiler?
Is there any good tool to directly create and edit the db?
I think there is also no support to use in Android.

 

[peacemaker]

directly create and edit the db

It was declared the built-in CLI interface.

 

[aeric]

It was declared the built-in CLI interface.

I prefer a GUI app if there exist.
Already familiar with DB Browser for SQLite.

 

[tchart]

Another good alternative is H2.

DuckDB vs. H2 Comparison

Detailed side-by-side view of DuckDB and H2

db-engines.com

H2 is good but not as efficient as SQLite. I’ve used it when I need multiple connections writing at the same time. It does slow down when the database gets big (slower than SQLite).

 

[amorosik]

Why DuckDB

Why DuckDB There are many database management systems (DBMS) out there. But there is no one-size-fits all database system. All take different trade-offs to better adjust to specific use cases. DuckDB is no different. Here, we try to explain what goals DuckDB has and why and how we try to achieve...

duckdb.org

What are the differences between DuckDb and the classic Sqlite?
Or better, WHY prefer DuckDb to Sqlite?

 

[peacemaker]

Or better

Just https://www.b4x.com/android/forum/threads/duckdb-anyone-tried.162155/post-994686

Comparison : https://db-engines.com/en/system/DuckDB;SQLite