dartonic 0.0.14

A database query builder inspired by Drizzle. It allows you to connect to various databases (SQLite, PostgreSQL, MySQL) and perform database operations using a fluent API.

Dartonic

🍷 Dartonic Github
A database query builder inspired by Drizzle. It allows you to connect to various databases (SQLite, PostgreSQL, MySQL) and perform database operations using a fluent API.

---

Support 💖

If you find Dartonic useful, please consider supporting its development 🌟Buy Me a Coffee.🌟 Your support helps us improve the package and make it even better!

Table of Contents

• Getting Started
• Installation
• Connecting to a Database
• Defining Tables
  • SQLite Example
  • PostgreSQL Example
  • MySQL Example
• Working with Relationships
• Querying the Database
  • Simple Queries
  • Complex Queries
• Supported Methods & Examples
  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • Join Queries
  • Filter Conditions
  • Transactions
  • Defining Views
  • WITH Clause
• Limitations & Unsupported Types
• Contributing
• License

---

Getting Started

Dartonic is designed to simplify your database interactions in Dart. With its fluent query API, you can build complex queries effortlessly. ✨

---

Installation

Add Dartonic to your pubspec.yaml:

dependencies:
  dartonic: ^0.0.14

Then run:

dart pub get

Or run:

dart pub get add dartonic

---

Connecting to a Database

Dartonic supports multiple databases through connection URIs:

• SQLite (in memory):

  final dartonic = Dartonic("sqlite::memory:");
  

• SQLite (from file):

  final dartonic = Dartonic("sqlite:database/database.db");
  

• PostgreSQL:

  final dartonic = Dartonic("postgres://username:password@localhost:5432/postgres?sslmode=verify-full");
  

• MySQL:

  final dartonic = Dartonic("mysql://user:userpassword@localhost:3306/mydb");
  

Synchronize your tables:

void main() async {
  final dartonic = Dartonic("sqlite::memory:", [usersTable, ordersTable]);
  await dartonic.sync(); // Synchronize tables
}

Get instance Dartonic in anywhere in your project:

final db = dartonic.instance;

🚨 Note: Dartonic uses a singleton pattern – all instances refer to the same connection.

---

Defining Tables

Dartonic is inspired by Drizzle and allows you to define table schemas conveniently. Below is an example of creating a SQLite table with custom column definitions.

SQLite Example

Note: Some modifiers or functions may differ on SQLite. Check the SQLite documentation for supported default functions.

import 'package:dartonic/dartonic.dart';
import 'package:dartonic/columns.dart';

final usersTable = sqliteTable('users', {
  'id': integer().primaryKey(autoIncrement: true),
  'name': text().notNull(),
  'age': integer(),
  'email': text().notNull().unique(),
  'created_at': timestamp().notNull().defaultNow(),
  'updated_at': timestamp().notNull().defaultNow(),
});

final ordersTable = sqliteTable('orders', {
  'id': integer().primaryKey(autoIncrement: true),
  'user_id': integer(columnName: 'user_id'),
  'total': integer(),
});

PostgreSQL Example

Note: Some modifiers or functions may differ on PostgreSQL. Check the PostgreSQL documentation for supported default functions.

final usersTable = pgTable('users', {
  'id': serial().generatedAlwaysAsIdentity(),
  'name': varchar(length: 100).notNull(),
  'age': integer(),
});

MySQL Example

Note: Auto increment is defined differently on MySQL. Ensure your primaryKey() method is correctly implemented for MySQL.

final usersTable = mysqlTable('users', {
  'id': integer().primaryKey(autoIncrement: true),
  'name': varchar(length: 100).notNull(),
});

---

Working with Relationships

Dartonic allows you to define relationships between tables. This makes it easier to perform related queries using JOINs. Relationships are defined through helper methods (for example, a relations function) which let you map the associations.

Here’s an example on how to define one-to-one and one-to-many relationships:

// Defining the base tables.
final usersTable = sqliteTable('users', {
  'id': integer().primaryKey(autoIncrement: true),
  'name': text().notNull(),
  'email': text().notNull().unique(),
});

final profileTable = sqliteTable('profile_info', {
  'id': integer().primaryKey(),
  'user_id': integer(columnName: 'user_id').references(() => 'users.id'),
  'bio': text(),
});

final postsTable = sqliteTable('posts', {
  'id': integer().primaryKey(autoIncrement: true),
  'user_id': integer(columnName: 'user_id').references(() => 'users.id'),
  'content': text(),
});

// Defining relationships.
// For one-to-one relationship: each user has one profileInfo.
final usersRelations = relations(
  usersTable,
  (builder) => {
    'profileInfo': builder.one(
      'profile_info',
      fields: ['users.id'],
      references: ['profile_info.user_id'],
    ),
  },
);

// For one-to-many relationship: each user can have multiple posts.
final postsRelations = relations(
  usersTable,
  (builder) => {
    'posts': builder.many(
      'posts',
      fields: ['users.id'],
      references: ['posts.user_id'],
    ),
  },
);

// Now you can initialize Dartonic with the main tables and include relationship meta-information.
final dartonic = Dartonic("sqlite://database.db", [
  usersTable,
  profileTable,
  postsTable,
  usersRelations,
  postsRelations,
]);

Once the relationships are defined, you can perform JOIN queries with ease:

// Example JOIN query: Get users with their profile bio.
final query = db
    .select({
      'userName': 'users.name',
      'userEmail': 'users.email',
      'bio': 'profile_info.bio',
    })
    .from('users')
    .innerJoin('profile_info', eq("users.id", "profile_info.user_id"));

print(query.toSql());
final result = await query;
print(result);

---

Querying the Database

After synchronizing the tables using sync(), you can build and execute queries using the fluent API provided by Dartonic.

Simple Queries

• SELECT all columns:

  final users = await db.select().from('users');
  print(users);
  

• SELECT specific columns using a map:

  Here, the key represents the alias (renamed column) and the value represents the actual column.

  final result = await db.select({
    'fieldId': 'users.id',
    'fieldName': 'users.name',
  }).from('users');
  print(result);
  

Complex Queries

You can chain multiple methods to build complex queries with joins, filters, ordering, and pagination.

final complexQuery = db
    .select({
      'userName': 'users.name',
      'orderTotal': 'orders.total'
    })
    .from('users')
    .innerJoin('orders', eq("users.id", "orders.user_id"))
    .where(gt("orders.total", 100))
    .orderBy("users.name")
    .limit(10)
    .offset(0);

print(complexQuery.toSql());
final result = await complexQuery;
print(result);

---

Supported Methods & Examples

Below are some examples demonstrating all available methods within Dartonic's query builder.

SELECT

Select columns by specifying a map:

final users = await db
    .select({
      'name': 'users.fullname',
      'age': 'users.birthday'
    })
    .from('users');
print(users);

INSERT

Insert only or insert a record and return the full record as well as partial (only id):

// Insert only
await db
    .insert('users')
    .values({
      'name': "Dan",
      'age': 28
    });

// Insert with returning
final insertedUser = await db
    .insert('users')
    .values({
      'name': "Dan",
      'age': 28
      })
      .returning();

print("Inserted with full RETURNING:");
print(insertedUser);

// Insert and return only id
final insertedPartial = await db
    .insert('users')
    .values({
      'name': "Partial Dan",
      'age': 30
    })
    .returning(['id']);

print("Inserted with partial RETURNING {'id': 1}");
print(insertedPartial);

UPDATE

Update only or update a record and return the updated information:

// Update only
 await db
    .update('users')
    .set({'name': "Daniel", 'age': 29})
    .where(eq("users.id", 1));

// Update with returning
final updatedUser = await db
    .update('users')
    .set({'name': "Daniel", 'age': 29})
    .where(eq("users.id", 1))
    .returning();

print("Updated with full RETURNING:");
print(updatedUser);

DELETE

Delete only or delete a record and get the deleted row's data:

// Delete only
await db
    .delete('users')
    .where(eq("users.id", 3))
    .returning();

// Delete with returning
final deletedUser = await db
    .delete('users')
    .where(eq("users.id", 3))
    .returning();

print("Deleted with full RETURNING:");
print(deletedUser);

Join Queries

Perform various types of JOINs:

// INNER JOIN example: users and orders
final joinQuery = db
    .select({
      'userName': 'users.name',
      'orderTotal': 'orders.total'
    })
    .from('users')
    .innerJoin('orders', eq("users.id", "orders.user_id"))
    .where(gt("orders.total", 100));

print("SQL INNER JOIN with filter:");
print(joinQuery.toSql());

final joinResult = await joinQuery;
print(joinResult);

Filter Conditions

You can use a variety of filter methods:

// Equality filter (eq)
final eqQuery = db.select().from("users").where(eq("users.age", 30));

print("SQL eq:");
print(eqQuery.toSql());
print(await eqQuery);

// Greater-than filter (gt)
final gtQuery = db.select().from("users").where(gt("users.age", 25));

print("SQL gt:");
print(gtQuery.toSql());
print(await gtQuery);

// In array filter
final inArrayQuery = db.select().from("users").where(inArray("users.age", [25, 35]));

print("SQL inArray:");
print(inArrayQuery.toSql());
print(await inArrayQuery);

// Between filter
final betweenQuery = db.select().from("users").where(between("users.age", 26, 34));

print("SQL between:");
print(betweenQuery.toSql());
print(await betweenQuery);

// Composite filter with AND
final andQuery = db.select().from("users").where(
  and([gt("users.age", 25), lt("users.age", 35)])
);

print("SQL and:");
print(andQuery.toSql());
print(await andQuery);

// Composite filter with OR
final orQuery = db.select().from("users").where(
  or([lt("users.age", 25), gt("users.age", 35)])
);

print("SQL or:");
print(orQuery.toSql());
print(await orQuery);

---

Transactions

SQL transaction method dartonic support

final db = dartonic.instance;

await db.transaction((tx) async {
  final [account] = await db
      .select()
      .from('account')
      .where(eq('users.name', 'Dan'));

  if (account['balance'] < 100) {
    tx.rollback();
  }

  await tx
      .update('accounts')
      .set({'balance': account['balance'] - 100.00})
      .where(eq('users.name', 'Dan'));

  await tx
      .update('accounts')
      .set({'balance': account['balance'] + 100.00})
      .where(eq('users.name', 'Andrew'));
});

---

Defining Views

Dartonic allows you to define and use views (virtual tables) to simplify complex queries and improve the organization of your code. Views are declared similarly to tables, using the pgView, sqliteView, or mysqlView helpers, and the as() method to define the underlying query.

Syntax for defining a view:

import 'package:dartonic/dartonic.dart';

final userPostCountsView = pgView('user_post_counts').as(
  (qb) => qb
    .select({
      'user_id': 'posts.user_id',
      'post_count': count('posts.id'),
    })
    .from('posts')
    .groupBy(['posts.user_id']),
);

final dartonic = Dartonic(
  "sqlite::memory:",
  schemas: [users, posts],
  views: [userPostCountsView],
);

// Use a view
final userCounts = await db
    .select()
    .from(userPostCountsView.name)
    .where(gt('post_count', 1));

print(userCounts);

WITH Clause

Common Table Expressions (CTEs), also known as the WITH clause, allow you to define a temporary, named table to be used within a single query. This is ideal for breaking complex queries into smaller, more readable parts.

1. Defining the CTE:

First, define the CTE using db.$with() and the .as() method, which accepts the query that defines it.

import 'package:dartonic/dartonic.dart';

final userPostCounts = db.$with('user_post_counts').as(
  db.select({
    'user_id': 'posts.user_id',
    'post_count': count('posts.id'),
  }).from('posts').groupBy(['posts.user_id']),
);

2. Using the CTE:

To use the CTE in your main query, begin the method chain with db.with_(), passing the CTE object you just created.

🚨 Note: with is a reserved keyword in Dart. Therefore, we use with_ to keep the syntax as close to Drizzle as possible.

final result = await db
    .with_(userPostCounts)
    .select({
      'user_name': 'users.name',
      'total_posts': 'user_post_counts.post_count',
    })
    .from('users')
    .innerJoin('user_post_counts', eq('users.id', 'user_post_counts.user_id'))
    .where(gt('user_post_counts.total_posts', 1));

print(result);

Limitations & Unsupported Types

• SQLite Restrictions:

  • Some advanced SQL features like ILIKE are not natively supported by SQLite. Although Dartonic generates the SQL, not all features will run as expected on SQLite.

  • Ensure you are using SQLite version 3.35.0 or newer if you plan to use the RETURNING clause.

• Other Databases:

  • PostgreSQL fully supports the majority of features such as RETURNING, JOINs, and advanced filters.

  • MySQL support might vary; confirm your MySQL version supports specific SQL clauses used by Dartonic.

---

Contributing

Contributions are very welcome! If you find bugs, have suggestions, or want to contribute new features, please submit an issue or a pull request.

---

License

🍷 Dartonic is released under the MIT License. See the LICENSE file for more details.

---

Made with ❤️ for Dart/Flutter developers! 🎯